| Bug #40253 | reset of auto_increment doesn't work | ||
|---|---|---|---|
| Submitted: | 22 Oct 2008 14:36 | Modified: | 22 Oct 2008 18:21 |
| Reporter: | Raffaele Palmieri | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.1.28-rc | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ALTER TABLE, auto_increment | ||
[22 Oct 2008 16:40]
Valeriy Kravchuk
Thank you for a problem report. According to the manual (http://dev.mysql.com/doc/refman/5.1/en/create-table.html): "AUTO_INCREMENT sequences begin with 1." If you reset to valid auto_increment initial value, it works... But the fact that there is no even warning for an ALTER TABLE that does nothing I'd consider a minor bug.
[22 Oct 2008 17:32]
Raffaele Palmieri
So, you are saying that if I set auto_increment value to 1, should it work?
[22 Oct 2008 17:55]
Valeriy Kravchuk
Yes, it works for me. Look:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.28-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `test`.`tabletest` (
-> `stringa` VARCHAR(20) NOT NULL,
-> `id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.75 sec)
mysql> insert into test.tabletest(stringa) values('test');
Query OK, 1 row affected (0.09 sec)
mysql> show table status like 'tablet%'\G
*************************** 1. row ***************************
Name: tabletest
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 232783872
Auto_increment: 2
Create_time: 2008-10-22 19:32:55
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.77 sec)
mysql> delete from test.tabletest;
Query OK, 1 row affected (0.09 sec)
mysql> alter table tabletest auto_increment=1;
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status like 'tablet%'\G
*************************** 1. row ***************************
Name: tabletest
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 232783872
Auto_increment: 1
Create_time: 2008-10-22 19:32:55
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> alter table tabletest auto_increment=0;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status like 'tablet%'\G
*************************** 1. row ***************************
Name: tabletest
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 232783872
Auto_increment: 1
Create_time: 2008-10-22 19:32:55
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
[22 Oct 2008 18:21]
Raffaele Palmieri
Ok thank you, tomorrow I will try with your suggest.
[5 Nov 2008 20:53]
Mikhail Izioumtchenko
if "alter table foo auto_increment=0" being ignored without a warning,
it's not an InnoDB bug as it's the same for myisam tables. The sequence below
is for 5.1.29-rc. So I'm changing assignment/category accordingly.
mysql> show table status like 'i' \G
*************************** 1. row ***************************
Name: i
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 2
Avg_row_length: 7
Data_length: 14
Max_data_length: 1970324836974591
Index_length: 2048
Data_free: 0
Auto_increment: 3
Create_time: 2008-11-05 12:48:01
Update_time: 2008-11-05 12:48:16
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
mysql> alter table i auto_increment=0;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show table status like 'i' \G
*************************** 1. row ***************************
Name: i
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 2
Avg_row_length: 7
Data_length: 14
Max_data_length: 1970324836974591
Index_length: 2048
Data_free: 0
Auto_increment: 3
Create_time: 2008-11-05 12:48:55
Update_time: 2008-11-05 12:48:55
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> alter table i auto_increment=-12;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-12' at line 1
mysql>
Description: If i create an inno db table without set of auto_increment value and then, after removing all rows, i change auto_increment value, it isn't applied. How to repeat: Execute this script: ---------------------------------------------------------------------------- -- i create the table without set auto_increment, so it should start from 0 CREATE TABLE `test`.`tabletest` ( `stringa` VARCHAR(20) NOT NULL, `id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE = InnoDB; insert into test.tabletest(stringa) values('test'); -- delete all rows, so I assure you that if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed. delete from test.tabletest; -- reset auto_increment alter table test.tabletest auto_increment = 0 --------------------------------------------------------------------------- Then you can execute show status command in following manner: SHOW TABLE STATUS FROM test like 'table%'; Suggested fix: I don't know, but it seems to be very strange.