Merge branch '10.0' into 10.1

1 files changed, 174 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-alter-autoinc.result b/mysql-test/suite/innodb/r/innodb-alter-autoinc.result
new file mode 100644
index 00000000000..82eb448fe45
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-alter-autoinc.result
@@ -0,0 +1,174 @@
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(347),(33101),(123),(45),(6);
+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;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
+LOCK=NONE;
+ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED.
+ALTER TABLE t1 ADD id INT AUTO_INCREMENT;
+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
+ALTER TABLE t1 ADD id INT AUTO_INCREMENT, ADD INDEX(a, id);
+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
+ALTER TABLE t1 ADD id INT NOT NULL, ADD INDEX(id, a);
+SELECT * FROM t1;
+a	id
+6	0
+45	0
+123	0
+347	0
+33101	0
+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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `id` (`id`,`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=latin1
+BEGIN;
+INSERT INTO t1 VALUES(7,0);
+SELECT * FROM t1;
+a	id
+6	45
+45	50
+123	55
+347	60
+33101	65
+7	70
+ROLLBACK;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `id` (`id`,`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=latin1
+ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
+DROP COLUMN id, AUTO_INCREMENT = 42, LOCK=NONE;
+ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED.
+ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
+DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE;
+SELECT * FROM t1;
+a	id
+6	45
+45	50
+123	55
+347	60
+33101	65
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `id` (`id`,`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=latin1
+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;
+a	id
+-123	-45
+6	45
+45	50
+123	55
+347	60
+33101	65
+123	70
+123	75
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `id` (`id`,`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=latin1
+DROP TABLE t1;
+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;
+a	id
+6	0
+45	0
+123	0
+347	0
+33101	0
+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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `id` (`id`,`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=latin1
+BEGIN;
+INSERT INTO t1 VALUES(7,0);
+SELECT * FROM t1;
+a	id
+6	45
+45	50
+123	55
+347	60
+33101	65
+7	70
+ROLLBACK;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `id` (`id`,`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=latin1
+ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
+DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=COPY;
+SELECT * FROM t1;
+a	id
+6	45
+45	50
+123	55
+347	60
+33101	65
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `id` (`id`,`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=latin1
+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;
+a	id
+-123	-45
+6	45
+45	50
+123	55
+347	60
+33101	65
+123	75
+123	80
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`),
+  KEY `id` (`id`,`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=latin1
+DROP TABLE t1;