diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-alter-autoinc.result | 174 |
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 00000000..3186b00a --- /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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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=70 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +DROP TABLE t1; |