Fundamental bug in replication

Hello, everyone:

I have just discovered a very serious bug in replication that would not be
easy to correct, as it requires a change in the protocol.

The following will not replicate right:

create table parent(id int not null auto_increment primary key);
create table child(id int not null auto_increment primary key, parent_id int);
insert into parent values (NULL);
insert into child (parent_id) values (2);
insert into child (parent_id) values (last_insert_id());

On the master we have:

mysql> select * from child;
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 2 |
| 2 | 1 |
+----+-----------+
2 rows in set (0.00 sec)

And on the slave:

mysql> select * from child;
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 2 |
| 2 | 2 |
+----+-----------+

I have run into this issue on 3.23.40, but I know that nothing has been done
to correct the problem since then, so all of the currently existing MySQL
versions are affected by this bug.

The problem is in that in the above example the last insert depends on two
auto_increment values, while the current replication protocol is passing only
one.

Workaround: When inserting into a table with an auto-increment column, insert
the actual value of last_insert_id() as a constant rather than using the
function.

Possible fix: clean up the code to clearly distinguish between the two
different insert_id concepts - last id inserted, and last id referenced, and
pass both of them in replication.


--
Sasha Pachev
Create online surveys at http://www.surveypro.com/

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Fundamental bug in replication

Hi,

On Sat, May 17, 2003 at 03:02:45PM -0600, Sasha Pachev wrote:
> I have just discovered a very serious bug in replication that would not be
> easy to correct, as it requires a change in the protocol.
>
> The following will not replicate right:
>
> create table parent(id int not null auto_increment primary key);
> create table child(id int not null auto_increment primary key, parent_id int);
> insert into parent values (NULL);
> insert into child (parent_id) values (2);
> insert into child (parent_id) values (last_insert_id());
>
> I have run into this issue on 3.23.40, but I know that nothing has been done
> to correct the problem since then, so all of the currently existing MySQL
> versions are affected by this bug.
>

I didn't test your queries, but this may have been fixed in 3.23.56.

http://www.mysql.com/doc/en/News-3.23.56.html

Fixed bug in logging to binary log (which affects replication) a query
that inserts a NULL in an auto_increment field and also uses
LAST_INSERT_ID().

> The problem is in that in the above example the last insert depends on two
> auto_increment values, while the current replication protocol is passing only
> one.
>

The fix in 3.23.56 also needs two auto_increment values.


Regards,

Fred.

--
Fred van Engen XB Networks B.V.
email: fred.van.engen@xbn.nl Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Fundamental bug in replication (auto_increment and LAST_INSERT_ID() in the same query)

Hi Sasha!

Sasha Pachev wrote:
> The following will not replicate right:
>
> create table parent(id int not null auto_increment primary key);
> create table child(id int not null auto_increment primary key, parent_id int);
> insert into parent values (NULL);
> insert into child (parent_id) values (2);
> insert into child (parent_id) values (last_insert_id());
>
> I have run into this issue on 3.23.40, but I know that nothing has been done
> to correct the problem since then, so all of the currently existing MySQL
> versions are affected by this bug.

Yes, as M. van Engen said, fortunately this bug has already been fixed in
3.23.56 and 4.0.9 :)
I tested the above queries with the latest 3.23 and I have the same good
results on the master and slave.
Here is the binlog generated by my master :
create table child(id int not null auto_increment primary key, parent_id int);
# at 316
#030518 15:59:21 server id 1 Intvar
SET INSERT_ID = 1;
# at 338
#030518 15:59:21 server id 1 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1053266361;
insert into parent values (NULL);
# at 399
#030518 15:59:21 server id 1 Intvar
SET INSERT_ID = 1;
# at 421
#030518 15:59:21 server id 1 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1053266361;
insert into child (parent_id) values (2);
# at 490
#030518 15:59:21 server id 1 Intvar
SET LAST_INSERT_ID = 1;
# at 512
#030518 15:59:21 server id 1 Intvar
SET INSERT_ID = 2;
# at 534
#030518 15:59:21 server id 1 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1053266361;
insert into child (parent_id) values (last_insert_id());

As you can see, we log INSERT_ID and LAST_INSERT_ID when needed.

Regards,
Guilhem
--
For technical support contracts, visit https://order.mysql.com/?ref=mgbi
Are you MySQL certified? visit http://www.mysql.com/certification/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Guilhem Bichot
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org