| Bug #34106 | auto_increment is reset to 1 when table is recovered from crash | ||
|---|---|---|---|
| Submitted: | 28 Jan 2008 15:18 | Modified: | 31 Jan 2008 1:11 |
| Reporter: | Harrison Fisk | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Maria storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.23-maria-alpha | OS: | Linux |
| Assigned to: | Guilhem Bichot | CPU Architecture: | Any |
[28 Jan 2008 15:25]
Guilhem Bichot
We should re-test this once Monty has finished fixing the auto_increment bug reported by Guilhem
[31 Jan 2008 1:11]
Michael Widenius
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Description: When my maria table is recovered after a crash, the auto_increment counter is reset to 1. This causes all future INSERT statements to fail. We can also verify the counter is at 1 by looking at SHOW TABLE STATUS, such as: mysql> select max(id) from maria_test; +---------+ | max(id) | +---------+ | 1048576 | +---------+ 1 row in set (0.01 sec) mysql> show table status like 'maria_test'\G *************************** 1. row *************************** Name: maria_test Engine: MARIA Version: 10 Row_format: Page Rows: 1048576 Avg_row_length: 44 Data_length: 47177728 Max_data_length: 137438945280 Index_length: 9003008 Data_free: 0 Auto_increment: 1 <------- should be 1048577 Create_time: 2008-01-28 10:07:35 Update_time: 2008-01-28 10:09:02 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) How to repeat: create table maria_test (id int auto_increment, primary key (id)) engine=maria; insert into maria_test values (); insert into maria_test SELECT NULL from maria_test; insert into maria_test SELECT NULL from maria_test; insert into maria_test SELECT NULL from maria_test; insert into maria_test SELECT NULL from maria_test; insert into maria_test SELECT NULL from maria_test; insert into maria_test SELECT NULL from maria_test; insert into maria_test SELECT NULL from maria_test; insert into maria_test SELECT NULL from maria_test; insert into maria_test SELECT NULL from maria_test; insert into maria_test SELECT NULL from maria_test; Do a kill -9 <mysqld> during one of the statements. Restart MySQL and let it finish crash recovery. Login and try to do the following: -- gets error insert into maria_test values (); -- verify the auto_increment value show table status like 'maria_test'; Suggested fix: Fix the auto_increment to not be 1 after crash recovery.