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
