Hi all!
Using mysql 2.23.54a as both master & slave:
** On master:
mysql> CREATE DATABASE repl_test;
Query OK, 1 row affected (0.03 sec)
mysql> USE repl_test;
Database changed
mysql> CREATE TABLE test (
-> a INT UNSIGNED AUTO_INCREMENT NOT NULL ,
-> b INT UNSIGNED NOT NULL,
-> PRIMARY KEY (a)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test (b) VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test (b) VALUES (LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test (b) VALUES (LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
+---+---+
3 rows in set (0.00 sec)
** On slave:
mysql> USE repl_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM test;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
3 rows in set (0.00 sec)
Looking at the binlog it appears that the problem is on the master and that
LAST_INSERT_ID gets set to the same value as INSERT_ID rather than the previous value
(ie this problem only affects inserts that are inserting into tables with auto
increment columns).
Relevant bit of binlog is:
# at 472606546
#030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0
error_code=0
use repl_test;
SET TIMESTAMP=1047548285;
CREATE TABLE test (
a INT UNSIGNED AUTO_INCREMENT NOT NULL ,
b INT UNSIGNED NOT NULL,
PRIMARY KEY (a)
);
# at 472606683
#030313 9:38:05 server id 101 Intvar
SET INSERT_ID = 1;
# at 472606705
#030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0
error_code=0
SET TIMESTAMP=1047548285;
INSERT INTO test (b) VALUES (1);
# at 472606770
#030313 9:38:05 server id 101 Intvar
SET LAST_INSERT_ID = 2;
# at 472606792
#030313 9:38:05 server id 101 Intvar
SET INSERT_ID = 2;
# at 472606814
#030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0
error_code=0
SET TIMESTAMP=1047548285;
INSERT INTO test (b) VALUES (LAST_INSERT_ID());
# at 472606894
#030313 9:38:06 server id 101 Intvar
SET LAST_INSERT_ID = 3;
# at 472606916
#030313 9:38:06 server id 101 Intvar
SET INSERT_ID = 3;
# at 472606938
#030313 9:38:06 server id 101 Query thread_id=5122 exec_time=0
error_code=0
SET TIMESTAMP=1047548286;
INSERT INTO test (b) VALUES (LAST_INSERT_ID());
# at 472606546
#030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0
error_code=0
use repl_test;
SET TIMESTAMP=1047548285;
CREATE TABLE test (
a INT UNSIGNED AUTO_INCREMENT NOT NULL ,
b INT UNSIGNED NOT NULL,
PRIMARY KEY (a)
);
# at 472606683
#030313 9:38:05 server id 101 Intvar
SET INSERT_ID = 1;
# at 472606705
#030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0
error_code=0
SET TIMESTAMP=1047548285;
INSERT INTO test (b) VALUES (1);
# at 472606770
#030313 9:38:05 server id 101 Intvar
SET LAST_INSERT_ID = 2;
# at 472606792
#030313 9:38:05 server id 101 Intvar
SET INSERT_ID = 2;
# at 472606814
#030313 9:38:05 server id 101 Query thread_id=5122 exec_time=0
error_code=0
SET TIMESTAMP=1047548285;
INSERT INTO test (b) VALUES (LAST_INSERT_ID());
# at 472606894
#030313 9:38:06 server id 101 Intvar
SET LAST_INSERT_ID = 3;
# at 472606916
#030313 9:38:06 server id 101 Intvar
SET INSERT_ID = 3;
# at 472606938
#030313 9:38:06 server id 101 Query thread_id=5122 exec_time=0
error_code=0
SET TIMESTAMP=1047548286;
INSERT INTO test (b) VALUES (LAST_INSERT_ID());
Let me know if any more info needed!
Regards,
Chris
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
