| Bug #40216 | optimize table resets auto_increment value on archive tables | ||
|---|---|---|---|
| Submitted: | 21 Oct 2008 16:32 | Modified: | 29 Oct 2008 15:21 |
| Reporter: | Mattias Jonsson | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Archive storage engine | Severity: | S2 (Serious) |
| Version: | 5.1, 5.7.17 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[21 Oct 2008 16:52]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[30 Oct 2008 21:41]
Geoffroy Cogniaux
This bug is reported on version 5.1, but I verified it on version 6.0.6 too. I guess it happens because in the ha_archive::optimize function, auto_increment value is updated on closed 'share->archive_write' instead of the new opened azio_stream called 'writer'. ( I tested it and it works ) But I'm surprised to see that this value is first reseted and then updated each time a row is inserted in the new optimized table. I think auto_increment value should be copied only once from the old version of the table to the new optimized one, shouldn't it ? , even if ha_archive::optimize finished with a different (ie. less) row count . Best regards, Geoffroy.
[23 Jan 2017 4:27]
MySQL Verification Team
Bug #84598 marked as duplicate of this one
Description: when doing 'OPTIMIZE TABLE' on an archive table with auto_increment, it resets the auto_increment value to zero. How to repeat: CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, KEY(a)) ENGINE Archive; INSERT INTO t1 VALUES (),(),(),(); SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; AUTO_INCREMENT 5 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, KEY `a` (`a`) ) ENGINE=ARCHIVE AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize status OK SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; AUTO_INCREMENT 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, KEY `a` (`a`) ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 SELECT * FROM t1; a 1 2 3 4 DROP TABLE t1; Suggested fix: === modified file 'storage/archive/ha_archive.cc' --- storage/archive/ha_archive.cc 2007-12-20 21:11:37 +0000 +++ storage/archive/ha_archive.cc 2008-10-21 16:15:00 +0000 @@ -1339,6 +1339,8 @@ dbug_tmp_restore_column_map(table->read_set, org_bitmap); share->rows_recorded= (ha_rows)writer.rows; + if (table->found_next_number_field) + writer.auto_increment= stats.auto_increment_value - 1; } DBUG_PRINT("info", ("recovered %llu archive rows",