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
