Description:
Hi,
I seem to be having some problems with INSERT...SELECT statements when using table type NDB , in that they are not properly incrementing auto_increment fields in a table.
I had a look through the bugs database (bugs.mysql.com) and found a different but similiar bug mentioned in regards to "Auto_increment problems with 4 replicas" (MySQL Bug: #8809), and the two could possibly be related. I have sucessfully replicated the problem with MySQL 4.1.17 (custom source compile) and the recently released MySQL 4.1.10a (with ndb-* rpms).
Bascially, after the INSERT..SELECT statement, the auto_increment field works fine for the currently connected client. However, the client disconnects, and tries to insert, the auto_increment field is not correct after the previous client's INSERT...SELECT statement.
This problem doesn't seem to affect the client that stays connected however.
I'm using RedHat Linux Enterpise v3.1, kernel 2.4.21-15.0.3.ELsmp (on both nodes), with MySQL 4.1.10a as downloaded in rpm format from mysql.com.
NoOfReplicas is 2, but had the same error with NoOfreplicas 4 (config.ini for ndb_mgmd) (see bug #8809).
How to repeat:
# Create a new database
mysql> create database test_database;
Query OK, 1 row affected (0.00 sec)
# use it.
mysql> use test_database;
Database changed
# Create a table with an auto_increment field, and table type is NDB||NDBCLUSTER
mysql> create table test_table(test_id int primary key auto_increment not null default NULL, test_data varchar(255)) TYPE=NDB;
Query OK, 0 rows affected, 1 warning (0.55 sec)
# great! it worked. not sure about that warning though.
# check to see what mysql thinks about the table. Notice auto_increment field is set to '1'
mysql> show table status \G;
*************************** 1. row ***************************
Name: test_table
Engine: ndbcluster
Version: 9
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
ERROR:
No query specified
# insert some plain data into the field, and allow mysql to fill in value for id.
mysql> insert into test_table (test_data) values ('something');
Query OK, 1 row affected (0.01 sec)
# great it worked.
# check the table status. notice auto_increment field is set to '2'
mysql> show table status \G;
*************************** 1. row ***************************
Name: test_table
Engine: ndbcluster
Version: 9
Row_format: Dynamic
Rows: 1
Avg_row_length: 0
Data_length: 0
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: 2
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> select * from test_table;
+---------+-----------+
| test_id | test_data |
+---------+-----------+
| 1 | something |
+---------+-----------+
1 row in set (0.04 sec)
# data was inserted. Grab the id and put the same data back into the table with "INSERT...SELECT" syntax
mysql> insert into test_table (test_data) select test_data from test_table where test_id='1';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
# good, it worked. Check the table status. Notice that auto_increment field is set to '3'
mysql> show table status \G;
*************************** 1. row ***************************
Name: test_table
Engine: ndbcluster
Version: 9
Row_format: Dynamic
Rows: 2
Avg_row_length: 0
Data_length: 0
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: 3
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
ERROR:
No query specified
# close the connection
mysql> exit
Bye
root@cluster4:~ [1004]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 44369 to server version: 4.1.10a-Max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test_database;
Database changed
# with a new client, check the table status.. note that auto_increment field is set to '2'!!! Something
# is amiss!
mysql> show table status \G;
*************************** 1. row ***************************
Name: test_table
Engine: ndbcluster
Version: 9
Row_format: Dynamic
Rows: 2
Avg_row_length: 0
Data_length: 0
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: 2
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.05 sec)
ERROR:
No query specified
# but the data is still okay
mysql> select * from test_table;
+---------+-----------+
| test_id | test_data |
+---------+-----------+
| 2 | something |
| 1 | something |
+---------+-----------+
2 rows in set (0.04 sec)
# well, then try and do a simple insert
mysql> insert into test_table (test_data) values('something else');
ERROR 1062 (23000): Duplicate entry '2' for key 1
# HELP! an duplicate key (where there shouldn't be one)
# now, the table status again . Note the auto_increment field is set to '3' now.
mysql> show table status \G;
*************************** 1. row ***************************
Name: test_table
Engine: ndbcluster
Version: 9
Row_format: Dynamic
Rows: 2
Avg_row_length: 0
Data_length: 0
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: 3
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
ERROR:
No query specified
# try the same exact query.
mysql> insert into test_table (test_data) values('something else');
Query OK, 1 row affected (0.00 sec)
# and it works!
Suggested fix:
The workaround for this problem, as I have tested was to replace INSERT..SELECT statements with seperate INSERT and SELECT statements when using NDB table type.
