⚝
One Hat Cyber Team
⚝
Your IP:
216.73.216.124
Server IP:
50.28.103.30
Server:
Linux host.jcukjv-lwsites.com 4.18.0-553.22.1.el8_10.x86_64 #1 SMP Tue Sep 24 05:16:59 EDT 2024 x86_64
Server Software:
nginx/1.28.0
PHP Version:
8.3.12
Buat File
|
Buat Folder
Eksekusi
Dir :
~
/
www
/
server
/
mysql
/
mysql-test
/
suite
/
innodb
/
t
/
View File Name :
innodb-alter-autoinc.test
--source include/have_innodb.inc CREATE TABLE t1 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES(347),(33101),(123),(45),(6); # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. ALTER TABLE t1 ADD PRIMARY KEY(a); # We cannot assign AUTO_INCREMENT values during online index creation. --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, LOCK=NONE; --error ER_WRONG_AUTO_KEY ALTER TABLE t1 ADD id INT AUTO_INCREMENT; --error ER_WRONG_AUTO_KEY ALTER TABLE t1 ADD id INT AUTO_INCREMENT, ADD INDEX(a, id); ALTER TABLE t1 ADD id INT NOT NULL, ADD INDEX(id, a); SELECT * FROM t1; # Test with a non-default increment and offset SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 30; ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=COPY; SHOW CREATE TABLE t1; # The autoinc next value should increase. It is not rolled back. BEGIN; INSERT INTO t1 VALUES(7,0); SELECT * FROM t1; ROLLBACK; SHOW CREATE TABLE t1; # We cannot assign AUTO_INCREMENT values during online index creation. --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, DROP COLUMN id, AUTO_INCREMENT = 42, LOCK=NONE; ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE; SELECT * FROM t1; SHOW CREATE TABLE t1; INSERT INTO t1 SET a=123; INSERT INTO t1 VALUES(-123,-45); ALTER TABLE t1 AUTO_INCREMENT = 75; INSERT INTO t1 SET a=123; SELECT * FROM t1; SHOW CREATE TABLE t1; DROP TABLE t1; # ALGORITHM=INPLACE should deliver identical results to ALGORITHM=COPY. CREATE TABLE t1 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES(347),(33101),(123),(45),(6); ALTER TABLE t1 ADD PRIMARY KEY(a); ALTER TABLE t1 ADD id INT NOT NULL, ADD INDEX(id, a); SELECT * FROM t1; ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE; SHOW CREATE TABLE t1; # The autoinc next value should increase. It is not rolled back. BEGIN; INSERT INTO t1 VALUES(7,0); SELECT * FROM t1; ROLLBACK; SHOW CREATE TABLE t1; ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=COPY; SELECT * FROM t1; SHOW CREATE TABLE t1; INSERT INTO t1 SET a=123; INSERT INTO t1 VALUES(-123,-45); ALTER TABLE t1 AUTO_INCREMENT = 75; INSERT INTO t1 SET a=123; SELECT * FROM t1; SHOW CREATE TABLE t1; DROP TABLE t1;