MySQL Bugs: #10297: Auto-increment not working with LOAD DATA INFILE and NDB storage

Bug #10297	Auto-increment not working with LOAD DATA INFILE and NDB storage
Submitted:	1 May 2005 21:00	Modified:	9 May 2005 11:52
Reporter:	Jim Hoadley	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Linux (Linux (RHEL 3))
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
I found a problem with LOAD DATA INFILE, and it seems to be related to bug 9675.

What I did:

Case 1:

Using 4.1.11, with an ndbcluster table that contained an auto_increment PRIMARY KEY field, I connected with the mysql client, issued a LOAD DATA INFILE command (infile contained multiple records), quit, then reconnected, then tried issuing LOAD DATA INFILE again.

Case 2:

Using 4.1.11, with an ndbcluster table that contained an auto_increment PRIMARY KEY field, I connected with the mysql client, issued a LOAD DATA INFILE command (infile contained multiple records), quit, then reconnected, then tried to insert a single new record with INSERT INTO...VALUES. 

What I expected to happen:

To be able to insert records and have the auto_increment PRIMARY KEY increment by 1.

What actually happened:

I got these errors:

Case 1:

ERROR 1062 (23000): Duplicate entry 'x' for key 1

Case 2:

ERROR 1105 (HY000): Unknown error

Two interesting notes:

The error doesn't occur during the current mysql connection.

Once thrown off, apparently the internal counter will increment by 1 after each failed subsequent connection attempt, until it retunrs to the correct place.
 

How to repeat:
**************this shows load data infile breaking***********

mysql> CREATE DATABASE test_ndb;
Query OK, 1 row affected (0.00 sec)
  
mysql> USE test_ndb;
Database changed

[...create destination table]

mysql> CREATE TABLE increment_test ( id int(7) NOT NULL auto_increment,
first varchar(30), last varchar(30), PRIMARY KEY (id) ) ENGINE=ndbcluster;
Query OK, 0 rows affected (1.26 sec)

[...create infile]

mysql> quit
Bye
 
shell> vi /tmp/_x
Angie   Land
Terri   Powell
Cara    Stemle

[load data infile first time]

mysql> LOAD DATA INFILE '/tmp/_x' INTO TABLE increment_test (first,last);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

[...ok, load it again]

mysql> LOAD DATA INFILE '/tmp/_x' INTO TABLE increment_test (first,last);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

[...ok. quit mysql and reconnect, try it again]
mysql> quit
Bye
 
mysql> LOAD DATA INFILE '/tmp/_x' INTO TABLE increment_test (first,last);
ERROR 1105 (HY000): Unknown error

[...error!!]

*********this shows inserting a single record breaking***********

[...drop table to start fresh]

mysql> DROP TABLE increment_test;
Query OK, 0 rows affected (0.82 sec)

[...recreate table]
  
mysql> CREATE TABLE increment_test ( id int(7) NOT NULL auto_increment,
first varchar(30), last varchar(30), age int(3), PRIMARY KEY (id) )
ENGINE=ndbcluster;
Query OK, 0 rows affected (1.30 sec)

[...insert a record]

mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');Query OK, 1 row affected (0.01 sec)

[...works, now delete it]
  
mysql> DELETE FROM increment_test WHERE last = 'Weert';
Query OK, 1 row affected (0.01 sec)

[...works, now load data infile]

mysql> LOAD DATA INFILE '/tmp/_x' INTO TABLE increment_test
(first,last,age);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

[...ok, works, now quit, reconnect, and insert a single record]

mysql> quit
Bye
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000):  '2' for key 1

[...error! this time we see the "Duplicate entry" string.
Shows it thought it should submit auto_increment "2"
when we know there are 4 records already in the database with
ids 1, 2, 3, 4.

**********this shows that continually attemping inserts of
single records after the auto_increment counter is set too
low, that you can "walk" it up to where it should be and 
finally get a succussful insert********************

mysql> CREATE TABLE increment_test ( id int(7) NOT NULL auto_increment,
first varchar(30), last varchar(30), age int(3), PRIMARY KEY (id) )
ENGINE=ndbcluster;
Query OK, 0 rows affected (1.32 sec)

[...load data infile with 3 records]
  
mysql> LOAD DATA INFILE '/tmp/_x' INTO TABLE increment_test
(first,last,age);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
  
[...ok]

mysql> quit
Bye

mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000): Duplicate entry '1' for key 1

[...didn't work, counter is really at 4 but insert attempted to use
next id of 1]

mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000): Duplicate entry '2' for key 1

[...didn't work, counter is really at 4 but insert attempted to use
next id of 2]

mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000): Duplicate entry '3' for key 1

[...didn't work, counter is really at 4 but insert attempted to use
next id of 3]

mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');Query OK, 1 row affected (0.00 sec)

[...ok! now broken counter is moved up to 4 and insert works]

Suggested fix:
Looks like there's some problem with NDB and auto_increments across multiple connections in 4.1.11.

A workaround is to assume that the internal record counter is incorrect (because someone has previously used either a LOAD DATA INFILE or SELECT...INSERT statement), and upon each connection:

1) In a loop, issue INSERT statements until the record counter is advanced to the proper place and one succeeds
2) Delete the last record
3) Proceed with your regularly scheduled LOAD DATA INFILE or SELECT...INSERT

Thanks for your bug report. Tested w/4.1.12

Tried to reproduce, but cannot see any if the reported problems,
running on latest 4.1.12 src, two node cluster and using only one
(of two connected) mysql servers:

[marty@localhost mysql-4.1]$ ~/MySQL/run/bin/mysql -u root test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.1.12-debug
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> CREATE DATABASE test_ndb;
Query OK, 1 row affected (0.00 sec)
 
mysql> USE test_ndb;
Database changed
mysql> CREATE TABLE increment_test ( id int(7) NOT NULL auto_increment, first varchar(30), last varchar(30), PRIMARY KEY (id) ) ENGINE=ndbcluster;
Query OK, 0 rows affected (0.60 sec)
 
mysql> quit
Bye
[marty@localhost mysql-4.1]$ ~/MySQL/run/bin/mysql -u root test_ndb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.1.12-debug
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> LOAD DATA INFILE '/tmp/_x' INTO TABLE increment_test (first,last);
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3
 
mysql> select * from increment_test;
+----+----------------+------+
| id | first          | last |
+----+----------------+------+
|  2 | Terri   Powell | NULL |
|  3 | Cara    Stemle | NULL |
|  1 | Angie   Land   | NULL |
+----+----------------+------+
3 rows in set (0.05 sec)
 
mysql> LOAD DATA INFILE '/tmp/_x' INTO TABLE increment_test (first,last);
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3
 
mysql> select * from increment_test;
+----+----------------+------+
| id | first          | last |
+----+----------------+------+
|  6 | Cara    Stemle | NULL |
|  2 | Terri   Powell | NULL |
|  4 | Angie   Land   | NULL |
|  5 | Terri   Powell | NULL |
|  3 | Cara    Stemle | NULL |
|  1 | Angie   Land   | NULL |
+----+----------------+------+
6 rows in set (0.01 sec)
 
mysql> quit
Bye
[marty@localhost mysql-4.1]$ ~/MySQL/run/bin/mysql -u root test_ndb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 4.1.12-debug
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> LOAD DATA INFILE '/tmp/_x' INTO TABLE increment_test (first,last);
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3
 
mysql> select * from increment_test;
+----+----------------+------+
| id | first          | last |
+----+----------------+------+
| 34 | Terri   Powell | NULL |
| 35 | Cara    Stemle | NULL |
|  6 | Cara    Stemle | NULL |
|  2 | Terri   Powell | NULL |
| 33 | Angie   Land   | NULL |
|  4 | Angie   Land   | NULL |
|  5 | Terri   Powell | NULL |
|  3 | Cara    Stemle | NULL |
|  1 | Angie   Land   | NULL |
+----+----------------+------+
9 rows in set (0.04 sec)
 
mysql> DROP TABLE increment_test;
Query OK, 0 rows affected (0.40 sec)
 
mysql> CREATE TABLE increment_test ( id int(7) NOT NULL auto_increment,
    -> first varchar(30), last varchar(30), age int(3), PRIMARY KEY (id) )
    -> ENGINE=ndbcluster;
Query OK, 0 rows affected (0.81 sec)
 
mysql> INSERT INTO increment_test (first,last,age) VALUES
    -> ('Doug','Weert','13');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from increment_test;
+----+-------+-------+------+
| id | first | last  | age  |
+----+-------+-------+------+
|  1 | Doug  | Weert |   13 |
+----+-------+-------+------+
1 row in set (0.01 sec)
 
mysql> DELETE FROM increment_test WHERE last = 'Weert';
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from increment_test;
Empty set (0.01 sec)
 
mysql> LOAD DATA INFILE '/tmp/_x' INTO TABLE increment_test
    -> (first,last,age);
Query OK, 3 rows affected, 6 warnings (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 6
 
mysql> select * from increment_test;
+----+----------------+------+------+
| id | first          | last | age  |
+----+----------------+------+------+
|  2 | Angie   Land   | NULL | NULL |
|  4 | Cara    Stemle | NULL | NULL |
|  3 | Terri   Powell | NULL | NULL |
+----+----------------+------+------+
3 rows in set (0.01 sec)
 
mysql> quit
Bye
[marty@localhost mysql-4.1]$ ~/MySQL/run/bin/mysql -u root test_ndb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 4.1.12-debug
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> INSERT INTO increment_test (first,last,age) VALUES
    -> ('Doug','Weert','13');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from increment_test;
+----+----------------+-------+------+
| id | first          | last  | age  |
+----+----------------+-------+------+
| 34 | Doug           | Weert |   13 |
|  2 | Angie   Land   | NULL  | NULL |
|  4 | Cara    Stemle | NULL  | NULL |
|  3 | Terri   Powell | NULL  | NULL |
+----+----------------+-------+------+
4 rows in set (0.07 sec)

I cannot reproduce this one.
Please add info how and on what setup
it was verified.

Martin, thank you. You say you can't reproduce it. Did you try using 4.1.11?

Not sure what additional information you requier. Besides the version difference in our tests, I already pointed out that I'm using hugemem kernel and have 2 NDB nodes per host (4 NDB nodes total).

[root@db1 root]# uname -a
Linux db1.dealerfusion.com 2.4.21-27.0.4.ELhugemem #1 SMP Sat Apr 16 18:22:53 EDT 2005 i686 i686 i386 GNU/Linux

Is your conclusion that it has already been fixed in 4.1.12?

Thx.

-- Jim

when i delete row from particular table then insert again data in my table then increment id is increment after that particular id deleted. how can possible my id increment by 1 that id exit in particular table. is it possible my id not count deleted row.

The auto_increment values in ndb comes from an ever increasing sequence (with
possibly holes). There is no reuse of deleted values.