Description:
ARCHIVE auto_increment table field reset on OPTIMIZE TABLE or REPAIR TABLE.
On InnoDB or MyISAM tables this works fine.
How to repeat:
CREATE TABLE `_test` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`logged` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=Archive
;
INSERT INTO _test
(logged, `data`)
VALUES
(NOW()),
(NOW())
;
SELECT `AUTO_INCREMENT` AS 'ai_before_optimize'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'db'
AND TABLE_NAME = '_test';
OPTIMIZE TABLE _test
;
SELECT `AUTO_INCREMENT` AS 'ai_after_optimize'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'db'
AND TABLE_NAME = '_test'
;
#DROP TABLE _test
#;
Result:
+--------------------+
| ai_before_optimize |
+--------------------+
| 3 |
+--------------------+
…
+-------------------+
| ai_after_optimize |
+-------------------+
| 1 |
+-------------------+
InnoDB table, for example:
+--------------------+
| ai_before_optimize |
+--------------------+
| 3 |
+--------------------+
…
+-------------------+
| ai_after_optimize |
+-------------------+
| 3 |
+-------------------+
Documentation advises "To achieve better compression, you can use OPTIMIZE TABLE or REPAIR TABLE", but this reset ID sequence generation.
http://dev.mysql.com/doc/refman/5.7/en/archive-storage-engine.html
