MySQL Bugs: #136: INSERT SELECT does not write the same num of rows on master and slave

Bug #136	INSERT SELECT does not write the same num of rows on master and slave
Submitted:	9 Mar 2003 15:55	Modified:	11 Mar 2003 16:26
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0	OS:	Linux (Linux at least)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
With a certain table structure, an INSERT SELECT that inserts X rows, inserts Y rows when replayed by mysqlbinlog | mysql. This seems to be triggered by auto_increment columns, and when using a DBI program (strange, isn't it?).
Here is my output to make it more clear :

MASTER> create table t(a int not null auto_increment primary key, b int);
Query OK, 0 rows affected (0.00 sec)

MASTER> create table u(a int not null auto_increment primary key, b int);
Query OK, 0 rows affected (0.00 sec)

MASTER> reset master;truncate table t;truncate table u;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--- In another window I launch this Perl DBI program (30000 inserts) :

#! /usr/bin/perl

use DBI;
$opt_loop_count = 30000;
$dbh=
    DBI->connect("dbi:mysql:database=test;host=localhost",
                        "root", "",
{
    RaiseError => 1,
    PrintError => 1,
    AutoCommit => 1,
});

for ($j = 0; $j < $opt_loop_count ; $j = $j + 1) 
{
    $dbh->do(
             "insert into t values (null,0)")
        || print $dbh->errstr;
}

$dbh->disconnect;			       

---
While the Perl program is running, in my initial client I do :

MASTER> insert into u select * from t;
Query OK, 20967 rows affected (0.18 sec)
Records: 20967  Duplicates: 0  Warnings: 0

After my Perl program has finished, I inspect things :

MASTER> select count(*) from t; select count(*) from u;
+----------+
| count(*) |
+----------+
|    30000 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|    20967 |
+----------+
1 row in set (0.00 sec)

MASTER> select min(a),max(a),min(b),max(b) from t; select min(a),max(a),min(b),max(b) from u;
+--------+--------+--------+--------+
| min(a) | max(a) | min(b) | max(b) |
+--------+--------+--------+--------+
|      1 |  30000 |      0 |      0 |
+--------+--------+--------+--------+
1 row in set (0.01 sec)

+--------+--------+--------+--------+
| min(a) | max(a) | min(b) | max(b) |
+--------+--------+--------+--------+
|      1 |  20967 |      0 |      0 |
+--------+--------+--------+--------+
1 row in set (0.01 sec)

MASTER> flush logs;
Query OK, 0 rows affected (0.00 sec)

Then I replay the binary log with mysqlbinlog|mysql, and
I inspect again, and 'u' has changed :

MASTER> select count(*) from t; select count(*) from u;
+----------+
| count(*) |
+----------+
|    30000 |
+----------+
1 row in set (0.01 sec)

+----------+
| count(*) |
+----------+
|    21553 |
+----------+
1 row in set (0.00 sec)

MASTER> select min(a),max(a),min(b),max(b) from t; select min(a),max(a),min(b),max(b) from u;
+--------+--------+--------+--------+
| min(a) | max(a) | min(b) | max(b) |
+--------+--------+--------+--------+
|      1 |  30000 |      0 |      0 |
+--------+--------+--------+--------+
1 row in set (0.01 sec)

+--------+--------+--------+--------+
| min(a) | max(a) | min(b) | max(b) |
+--------+--------+--------+--------+
|      1 |  21553 |      0 |      0 |
+--------+--------+--------+--------+
1 row in set (0.01 sec)

I saw no bug if instead of using a DBI program I used a 30000 INSERTS plain SQL text file executed with the "source" command.
I saw no bug if table 'b' has no auto_increment column.

I'm using DBI 1.18 and Msql-Mysql-modules-1.2216, I have rebuilt them to be sure.

How to repeat:
create table t(a int not null auto_increment primary key, b int);
create table u(a int not null auto_increment primary key, b int);
reset master;truncate table t;truncate table u;

#In another window launch a Perl DBI program like above 
#While it is running, in the initial client do 

insert into u select * from t;

#When Perl program has finished do :

select count(*) from t; select count(*) from u;
select min(a),max(a),min(b),max(b) from t; select min(a),max(a),min(b),max(b) from u;
#record output
flush logs;

#Then replay the binary log with mysqlbinlog|mysql, and do

select count(*) from t; select count(*) from u;
select min(a),max(a),min(b),max(b) from t; select min(a),max(a),min(b),max(b) from u;

Replication is broken the same way :

MASTER> reset master ; drop table t,u;
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.10 sec)

MASTER> CREATE TABLE `t` (
    ->   `a` int(11) NOT NULL auto_increment,
    ->   `b` int(11) default NULL,
    ->   PRIMARY KEY  (`a`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

MASTER> CREATE TABLE `u` (
    ->  `a` int(11) NOT NULL auto_increment,
    ->   `b` int(11) default NULL,
    ->   PRIMARY KEY  (`a`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

MASTER> select count(*) from t; select count(*) from u;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

#launch the Perl DBI program and while it runs do

MASTER> insert into u select * from t;
Query OK, 922 rows affected (0.07 sec)
Records: 922  Duplicates: 0  Warnings: 0

#wait for the Perl program to finish and do

MASTER> select count(*) from t; select count(*) from u;
+----------+
| count(*) |
+----------+
|    30000 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|      922 |
+----------+
1 row in set (0.00 sec)

Then on the slave :
SLAVE>  select count(*) from t; select count(*) from u;
+----------+
| count(*) |
+----------+
|    30000 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|      926 |
+----------+
1 row in set (0.00 sec)

I will take care of this bug. This way, if it's bogus, nobody
but me will lose time :)

I'll work on this today. But I don't know yet if it will be quickly fixable. It's quite serious ; I can repeat it easily.

It works even for such simple table structures :
create table t(a int);create table u(a int);
but then the problem occurs more rarely (one every fifth
try). Whereas with this
create table t(a int, b timestamp, key(a,b));
create table u(a int, b timestamp, key(a,b));
it's one every other try at least. One has to wait several 
seconds (5 on my machine, which means around 10000 inserted lines)
to make it happen.
I'm now sending a patch for review to Serg and dev-public.

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html