Description:
Replication errors are generated on the slave when an INSERT is executed on the master that uses NULL for an auto_increment column and the target table on the slave is NDB. The slave error is:
Last_Error: Error 'Can't write; duplicate key in table 'null_inserts_1'' on query. Default database: 'test'. Query: 'INSERT INTO null_inserts_1 SELECT null FROM null_inserts_1'
Note that taking the INSERT statement out of the error message and executing it directly against the slave does not result in errors:
mysql> INSERT INTO null_inserts_1 SELECT null FROM null_inserts_1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
However, the values that are inserted show gaps in the auto_increment column value:
mysql> SELECT * FROM null_inserts_1 ORDER BY 1;
+----+
| i |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 36 |
| 37 |
| 38 |
| 39 |
+----+
8 rows in set (0.00 sec)
How to repeat:
Execute in master:
CREATE TABLE null_inserts_1 (i INT AUTO_INCREMENT PRIMARY KEY) ENGINE = MYISAM;
INSERT INTO null_inserts_1 VALUES (null); # inserts 1
INSERT INTO null_inserts_1 SELECT null FROM null_inserts_1; # inserts 2
INSERT INTO null_inserts_1 SELECT null FROM null_inserts_1; # inserts 3 and 4
Execute in slave:
SELECT * FROM null_inserts_1; # 1,2,3,4
ALTER TABLE null_inserts_1 ENGINE = NDBCLUSTER;
SELECT * FROM null_inserts_1; # 1,2,3,4
Execute in master:
INSERT INTO null_inserts_1 SELECT null FROM null_inserts_1; # inserts 5,6,7,8
INSERT INTO null_inserts_1 SELECT null FROM null_inserts_1; # inserts 9-16
SELECT * FROM null_inserts_1; # 1-16
Execut in slave:
SELECT * FROM null_inserts_1; # 1,2,3,4
SHOW SLAVE STATUS\G # Slave thread error on duplicate key
INSERT INTO null_inserts_1 SELECT null FROM null_inserts_1; # executes successfully, but with auto-increment value gaps
Suggested fix:
Correct handling of insert of NULL values in AUTO_INCREMENT columns when using statement-based replication.
