List:Cluster« Previous MessageNext Message »
From:Jim Hoadley Date:April 30 2005 3:42am
Subject:LOAD DATA INFILE bug?
View as plain text  
While looking into workarounds for the 4.1.11 ndbcluster engine
bug described here:

http://bugs.mysql.com/bug.php?id=9675

I found a problem with LOAD DATA INFILE, and it seems to be related,
if not identical. In 4.1.11, after I connect with the mysql client, 
and issue a LOAD DATA INFILE, quit, then reconnect, I am unable to 
INSERT any records.

I did lots of scenario testing, then tried the same tests with 
INSERT...SELECT and got identical results.

I think this is a bug. Here is how to reproduce it.

**************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]


Sorry for the long post but wanted to give test case. Seems like
a pretty big problem that both LOAD DATA INFILE and INSERT...SELECT
for NDB are broken in this GA release. Just my opinion.

-- Jim Hoadley
   Sr Soft Eng
   Dealer Fusion, Inc




__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
Thread
LOAD DATA INFILE bug?Jim Hoadley30 Apr
  • Re: LOAD DATA INFILE bug?Jim Hoadley30 Apr