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
