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:
None 
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:32] Mattias Jonsson 
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",
[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