MySQL Bugs: #7061: No error message for ALTER with AUTO

Bug #7061	No error message for ALTER with AUTO_INCREMENT
Submitted:	7 Dec 2004 2:53	Modified:	14 Feb 2005 11:58
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.3-alpha-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
With a MyISAM table, I can reset an auto_increment with 
ALTER TABLE myisam_table AUTO_INCREMENT=5; 
With InnoDB, I can't do that. Fair enough, it's documented: 
http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html 
However, there is no error message when it fails. 
 

How to repeat:
mysql> create table t_myisam (s1 int not null auto_increment primary key); 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into t_myisam values (null),(null),(null); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 
mysql> alter table t_myisam auto_increment=100; 
Query OK, 3 rows affected (0.01 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 
mysql> insert into t_myisam values (null),(null),(null); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 
mysql> select * from t_myisam; 
+-----+ 
| s1  | 
+-----+ 
|   1 | 
|   2 | 
|   3 | 
| 100 | 
| 101 | 
| 102 | 
+-----+ 
6 rows in set (0.00 sec) 
 
mysql> create table t_innodb (s1 int not null auto_increment primary key) engine=innodb; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into t_innodb values (null),(null),(null); 
Query OK, 3 rows affected (0.01 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 
mysql> alter table t_innodb auto_increment=100; /* won't give error message */ 
Query OK, 3 rows affected (0.02 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 
mysql> insert into t_innodb values (null),(null),(null); 
Query OK, 3 rows affected (0.01 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 
mysql> select * from t_innodb; 
+----+ 
| s1 | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
|  6 | 
+----+ 
6 rows in set (0.00 sec)

Peter G,

I am assigning this to Jan Lindström, and changed the status to a feature request. I asked him to make InnoDB finally to support that syntax. About 20 people have requested this auto-inc feature over years.

Thank you,

Heikki

Similar problem exists in myisam engine.

mysql> create table t_myisam (s1 int not null auto_increment primary key) engine = myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_myisam values (null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table t_myisam auto_increment=100;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t_myisam values (null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table t_myisam auto_increment=100;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into t_myisam values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_myisam;
+-----+
| s1  |
+-----+
|   1 |
|   2 |
|   3 |
| 100 |
| 101 |
| 102 |
| 103 |
+-----+
7 rows in set (0.00 sec)

Jan,

the MyISAM behavior is the right one.

ALTER must not change the column value in OLD rows. It must just set the auto-inc counter for NEW rows.

Regards,

Heikki

I have done patch for this problem
    http://lists.mysql.com/internals/19590

and InnoDB now works like MyIsam:

mysql> create table t_innodb (s1 int not null auto_increment primary key) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_innodb values (null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table t_innodb auto_increment=0;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t_innodb values (null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_innodb;
+----+
| s1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

mysql> alter table t_innodb auto_increment=100;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into t_innodb values (null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_innodb;
+-----+
| s1  |
+-----+
|   1  |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
| 100 |
| 101 |
| 102 |
+-----+
9 rows in set (0.00 sec)

mysql> quit