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,
ALGORITHM=INPLACE, 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, ALGORITHM=INPLACE, 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;