List:Cluster« Previous MessageNext Message »
From:Jim Hoadley Date:April 30 2005 3:45am
Subject:Re: LOAD DATA INFILE bug?
View as plain text  
And here is how to reproduce the INSERT...SELECT problem:
 
Create destination 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.26 sec)
 
Create source table.
 
mysql> CREATE TABLE increment_test_staging ( 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.35 sec)
 
Populate the source table with 3 records.
 
mysql> INSERT INTO increment_test_staging (first,last,age) VALUES
('Steve','Roofer','12');
Query OK, 1 row affected (0.04 sec)
 
mysql> INSERT INTO increment_test_staging (first,last,age) VALUES
('Doogie','Rekkid','41');
Query OK, 1 row affected (0.00 sec)
  
mysql> INSERT INTO increment_test_staging (first,last,age) VALUES
('Stage','Rekkid','43');
Query OK, 1 row affected (0.01 sec)
 
Insert 1 record into dest table.
  
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');Query OK, 1 row affected (0.04 sec)
 
Ok!
 
Delete same record.
 
mysql> DELETE FROM increment_test WHERE last = 'Weert';
Query OK, 1 row affected (0.04 sec)
 
Ok!
 
INSERT...SELECT 3 records from source table into dest table
 
mysql> INSERT INTO increment_test (first,last,age) SELECT first,last,age
FROM increment_test_staging;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
Succeeds in insert (but silently screws up internal counter for the
record.)
 
Disconnect.
 
mysql> quit
Bye
 
Reconnect.

[mysql@db2 mysql]$ mysql test_ndb
  
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 93 to server version: 4.1.11-max-log
  
Insert a record into dest table.
 
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000): Duplicate entry '2' for key 1
 
Fails with Duplicate entry". Notice what auto_increment id it tries to
use: 2.
 
Try inserting another record into dest table.
 
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000): Duplicate entry '3' for key 1
 
Fails with Duplicate entry". Notice what auto_increment id it tries to
use: 3.
 
Try inserting another record into dest table.
 
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000): Duplicate entry '4' for key 1
 
Fails with Duplicate entry". Notice what auto_increment id it tries to
use: 4.
 
Try inserting another record into dest table.
 
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');Query OK, 1 row affected (0.00 sec)
 
Ok! Reason is, the counter is now back to where it should be, at 5.
 
Attempt INSERT...SELECT.
 
mysql> INSERT INTO increment_test (first,last,age) SELECT first,last,age
FROM increment_test_staging;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
Ok! Works now.
 
mysql> quit
Bye
[mysql@db2 mysql]$ mysql test_ndb
  
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 94 to server version: 4.1.11-max-log
 
Let's repeat it to be sure.
 
Try inserting another record into dest table.
  
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000): Duplicate entry '6' for key 1
 
No, looking for 6 when it should be 9.
 
Try inserting another record into dest table.
 
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000): Duplicate entry '7' for key 1
 
No, looking for 7 when it should be 9.
 
Try inserting another record into dest table.
 
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');ERROR 1062 (23000): Duplicate entry '8' for key 1
 
Try inserting another record into dest table.
 
No, looking for 8 when it should be 9.
 
mysql> INSERT INTO increment_test (first,last,age) VALUES
('Doug','Weert','13');Query OK, 1 row affected (0.00 sec)
 
Ok! Reason is, the counter is now back to where it should be, at 9.


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