Description:
When a table with autoincrement counter is altered the autoincrement counter for that table in NDB is not correctly initialised. It should be set to the max autoinc value+1.
How to repeat:
I've experienced a little bit scenario with alter table, probably it's not
directly related to table corruption.
check mysql session log:
mysql> show table status;
+--------+--------+------------+------+----------------+-------------+----------
-------+--------------+-----------+----------------+---------------------+------
---------------+------------+-------------------+----------+----------------+---
------+
| Name | Engine | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Collation | Checksum |
Create_options | Comment |
+--------+--------+------------+------+----------------+-------------+----------
-------+--------------+-----------+----------------+---------------------+------
---------------+------------+-------------------+----------+----------------+---
------+
| mytab2 | MyISAM | Fixed | 13 | 25 | 325 |
107374182399 | 2048 | 0 | 14 | 2004-05-26 20:31:47 |
2004-05-26 20:32:21 | NULL | latin1_swedish_ci | NULL |
| |
+--------+--------+------------+------+----------------+-------------+----------
-------+--------------+-----------+----------------+---------------------+------
---------------+------------+-------------------+----------+----------------+---
------+
1 row in set (0.00 sec)
mysql> alter table mytab2 engine=NDB;
Query OK, 13 rows affected (1.12 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> select * from mytab2;
+------+-----------+
| col1 | col2 |
+------+-----------+
| 7 | xdddxxsss |
| 13 | xdddxxsss |
| 12 | xdddxxsss |
| 11 | xdddxxsss |
| 8 | xdddxxsss |
| 10 | xdddxxsss |
| 6 | xdddxxsss |
| 2 | xdddxxsss |
| 4 | xdddxxsss |
| 5 | xdddxxsss |
| 3 | xdddxxsss |
| 9 | xdddxxsss |
| 1 | xdddxxsss |
+------+-----------+
13 rows in set (0.15 sec)
mysql> insert into mytab2 (col2) values('xdddxxsss');
Query OK, 1 row affected (0.02 sec)
mysql> insert into mytab2 (col2) values('xdddxxsss');
Query OK, 1 row affected (0.01 sec)
mysql> insert into mytab2 (col2) values('xdddxxsss');
ERROR 1022 (23000): Can't write, duplicate key in table 'mytab2'
mysql> insert into mytab2 (col2) values('xdddxxsss');
ERROR 1022 (23000): Can't write, duplicate key in table 'mytab2'
mysql> insert into mytab2 (col2) values('xdddxxsss');
ERROR 1022 (23000): Can't write, duplicate key in table 'mytab2'
mysql> insert into mytab2 (col2) values('xdddxxsss');
ERROR 1022 (23000): Can't write, duplicate key in table 'mytab2'
mysql> select * from mytab2;
+------+-----------+
| col1 | col2 |
+------+-----------+
| 0 | xdddxxsss |
| 7 | xdddxxsss |
| 13 | xdddxxsss |
| 12 | xdddxxsss |
| 11 | xdddxxsss |
| 8 | xdddxxsss |
| 10 | xdddxxsss |
| 6 | xdddxxsss |
| 2 | xdddxxsss |
| 4 | xdddxxsss |
| 5 | xdddxxsss |
| 3 | xdddxxsss |
| 9 | xdddxxsss |
| -1 | xdddxxsss |
| 1 | xdddxxsss |
+------+-----------+
15 rows in set (0.01 sec)
mysql> desc mytab2;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| col1 | int(11) | | PRI | NULL | auto_increment |
| col2 | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
mysql> show table status;
+--------+------------+------------+------+----------------+-------------+------
-----------+--------------+-----------+----------------+-------------+----------
---+------------+-------------------+----------+----------------+---------+
| Name | Engine | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time |
Update_time | Check_time | Collation | Checksum | Create_options |
Comment |
+--------+------------+------------+------+----------------+-------------+------
-----------+--------------+-----------+----------------+-------------+----------
---+------------+-------------------+----------+----------------+---------+
| mytab2 | ndbcluster | Fixed | 100 | 0 | 0 |
NULL | 0 | 0 | 5 | NULL | NULL
| NULL | latin1_swedish_ci | NULL | | |
+--------+------------+------------+------+----------------+-------------+------
-----------+--------------+-----------+----------------+-------------+----------
---+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)
Suggested fix:
Initialise autoinc counter after/during ALTER TABLE to max value of autoinc counter+1