Adding upstream version 1:10.5.12.upstream/1%10.5.12upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 104 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/t/innodb-alter-autoinc.test b/mysql-test/suite/innodb/t/innodb-alter-autoinc.test
new file mode 100644
index 00000000..e01c44aa
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-alter-autoinc.test
@@ -0,0 +1,104 @@
+--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.
+SET @old_sql_mode = @@sql_mode;
+SET @@sql_mode = 'STRICT_TRANS_TABLES';
+ALTER TABLE t1 ADD PRIMARY KEY(a);
+SET @@sql_mode = @old_sql_mode;
+
+# 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;