| 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: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 4.0 | OS: | Linux (Linux at least) |
| Assigned to: | Guilhem Bichot | CPU Architecture: | Any |
[10 Mar 2003 8:20]
Guilhem Bichot
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 :)
[11 Mar 2003 5:58]
Guilhem Bichot
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.
[11 Mar 2003 15:50]
Guilhem Bichot
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.
[11 Mar 2003 16:26]
Guilhem Bichot
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
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;