Bug: last_insert_id() not replicated correctly

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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: Bug: last_insert_id() not replicated correctly

On Tuesday 18 March 2003 17:20, Chris Wilson wrote:
> 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).

Seems, it's already fixed. I tested on 3.23.56 and ypur example worked fine. I
got the following result on both boxes.

mysql> SELECT * FROM test;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
+---+---+



--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ Victoria.Reznichenko@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com





------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php