Bug #3912 Auto increment not correctly initialised when table is altered
Submitted: 27 May 2004 13:02 Modified: 10 Aug 2004 15:10
Reporter: Magnus Blåudd Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-4.1 bk OS:
Assigned to: Martin Skold CPU Architecture:Any

[27 May 2004 13:02] Magnus Blåudd
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
[28 Jun 2004 11:35] Martin Skold
The autoincrement implementation in ha_ndbcluster does not support
AUUTO_INCREMENT as default value only. For inserts it ignores any
user specified values and auto_increments any way. For the copy table
step in ALTER TABLE it does not.
This should be changed so that it always take user specified values as
long as they are higher than latest auto_increment value.
To support this we need 
Ndb::readAutoIncrementValue(<table name>) and
Ndb::setAutoIncrementValue(<table name>) with error if < than highest.
[28 Jun 2004 11:52] Martin Skold
Ndb::setAutoIncrement(<table name> <val>) allready exists,
but Ndb::readAutoIncrementValue(<table name>) is needed.
Alternatively we could implement
Uint64 Ndb::getAutoIncrementValue(<table name>, (Uint64*) <val> = NULL)
that returns new value if given value is lower otherwise returns
given value and sets that as highest.
[10 Jul 2004 0:10] Ross Bergman
A note that may have been covered here (I'm not positive I'm reading Martin's comments correctly re: user-specified values).  I have a table that uses an auto-increment column, but in which I've specifically set a record to have a value of '0' for that column.  While "ANALYZE" does warn that this is the case, a more serious problem is that any attempt to alter the table (even merely altering its COMMENT) results in my '0' value being changed to the next auto-increment value. This doesn't seem like correct behavior to me, but perhaps it's intended as a 'feature'.
[1 Aug 2004 13:25] Martin Skold
ndbcluster part is solved, but bug popped up in MySQL alter table
code where table->auto_increment_field_not_null is not set properly
at table copy phase. Awaiting bug fix from Sergei.