MySQL Bugs: #20820: auto inc table not handled correctly when restored from cluster backup

Bug #20820	auto inc table not handled correctly when restored from cluster backup
Submitted:	3 Jul 2006 13:01	Modified:	7 Jul 2006 3:45
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.0	OS:	Linux (Linux 32 bit OS)
Assigned to:	Tomas Ulin	CPU Architecture:	Any

Description:
Hi,

In trying to track down a different bug, I ran into this one today.

Basically there are a couple of issues here. when restored a table with auto inc the first insert will insert a huge number, then 0 and then will fail for each value already in the table and then will pick backup where needed to be.

For example:

Before drop and restore:

mysql> select * from t1;
+----+
| c1 |
+----+
|  3 |
|  1 |
|  2 |
+----+
3 rows in set (0.01 sec)

After drop and restore:

mysql> insert into t1 values(null);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warning;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'c1' at row 1 |
+---------+------+---------------------------------------------+

mysql> select * from t1;
+------------+
| c1         |
+------------+
|          3 |
|          1 |
| 2147483647 |
|          2 |
+------------+
4 rows in set (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------------+
| c1         |
+------------+
|          1 |
| 2147483647 |
|          2 |
|          0 |
|          3 |
+------------+
5 rows in set (0.01 sec)

mysql> insert into t1 values(null);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t1;
+------------+
| c1         |
+------------+
|          1 |
| 2147483647 |
|          2 |
|          0 |
|          3 |
+------------+
5 rows in set (0.00 sec)

mysql> insert into t1 values(null);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> select * from t1;
+------------+
| c1         |
+------------+
|          1 |
| 2147483647 |
|          2 |
|          0 |
|          3 |
+------------+
5 rows in set (0.01 sec)

mysql> insert into t1 values(null);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> select * from t1;
+------------+
| c1         |
+------------+
|          1 |
| 2147483647 |
|          2 |
|          0 |
|          3 |
+------------+
5 rows in set (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------------+
| c1         |
+------------+
|          1 |
| 2147483647 |
|          2 |
|          4 |
|          0 |
|          3 |
+------------+
6 rows in set (0.00 sec)

I have tried the same on MyISAM. MyISAM passed w/o issue.

 

How to repeat:
1. use test;
2. create table t1 (c1 int auto_increment key)ENGINE=NDB;
3. insert into t1 values(null);
4. insert into t1 values(null);
5. insert into t1 values(null);
6. ndb_mgm -c host:port -e "start backup"
7. drop database test;
8. create database test;
9. use test;
10. ndb restore from backup
11. insert into t1 values(null); <- Warnings
12. insert into t1 values(null); <- Inserts 0
13. insert into t1 values(null); <- Duplicate Key Failure 1
14. insert into t1 values(null); <- Duplicate Key Failure 2
15. insert into t1 values(null); <- Duplicate Key Failure 3
16. insert into t1 values(null); <- picks back up with #4

Suggested fix:
Should restore the table to correct auto inc position

reproduce with

create table t1 (a int auto_increment primary key) engine=ndb;
insert into t1 values (1),(2),(3);

- take backup

drop table t1;

-restore backup

insert into t1 values(NULL);

- now error

 select * from t1;
+------------+
| a          |
+------------+
|          1 | 
| 2147483647 | 
|          2 | 
|          3 | 
+------------+

the rest are follow on errors...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8839

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8847

Documented bugfix in 5.0.24 changelog; changed Version to 5.0 per comment from Tomas.

Fix went to 5.0.25 instead.