Description:
(Bug probably exists in 3.23 as well, but cannot be fixed in this too old branch).
INSERT DELAYED is written to the binlog when the row is inserted, which is fine.
But when later we do mysqlbinlog|mysql, this issues again the INSERT DELAYED, which get queued again (not executed immediately as they should), so final result of restoration is random.
Replication already does the good thing: it transforms INSERT DELAYED into a normal INSERT.
How to repeat:
Create this file in the sql-bench directory of your MySQL source tree:
#!/usr/bin/perl
use Cwd;
use DBI;
use Benchmark;
$pwd = cwd(); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
$dbh = $server->connect()
|| die $dbh->errstr;
$dbh->do("reset master");
$dbh->do("drop table if exists test1, test2");
$dbh->do("create table test1 (a int)");
$dbh->do("create table test2 (a int)");
for (;;) {
$dbh->do("insert DELAYED into test1 values(1)");
}
$dbh->disconnect; # close connection
Then launch this Perl program, and while it's running do (in another session):
insert into test2 select * from test1;
Then interrupt the Perl program. And see:
MASTER> select count(*) from test1;
+----------+
| count(*) |
+----------+
| 58077 |
+----------+
1 row in set (0.00 sec)
MASTER> select count(*) from test2;
+----------+
| count(*) |
+----------+
| 31039 |
+----------+
1 row in set (0.00 sec)
MASTER> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+------------------+----------+--------------+------------------+
| gbichot2-bin.001 | 4065731 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MASTER> flush logs;
Query OK, 0 rows affected (0.00 sec)
MASTER> Bye
[guilhem@gbichot2 guilhem]$ /m/mysql-4.0/client/mysqlbinlog /m/data/4/1/gbichot2-bin.001 | mysql1
[guilhem@gbichot2 guilhem]$ mysql1 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 4.0.18-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
MASTER> select count(*) from test1;
+----------+
| count(*) |
+----------+
| 58077 |
+----------+
1 row in set (0.00 sec)
MASTER> select count(*) from test2;
+----------+
| count(*) |
+----------+
| 30839 |
+----------+
1 row in set (0.00 sec)
So, after restoring, test2 does not have the same number of rows as originally.
Suggested fix:
We need a variable
@@session.DISABLE_INSERT_DELAYED;
mysqlbinlog should wrap its output with
SET @@session.DISABLE_INSERT_DELAYED=0;
SET @@session.DISABLE_INSERT_DELAYED=1;
Note that the server already has --max-delayed-threads=0 to disable all INSERT DELAYED in the server but we need a *session-level* settable variant.
Maybe @@session.max_delayed_threads ?
Description: (Bug probably exists in 3.23 as well, but cannot be fixed in this too old branch). INSERT DELAYED is written to the binlog when the row is inserted, which is fine. But when later we do mysqlbinlog|mysql, this issues again the INSERT DELAYED, which get queued again (not executed immediately as they should), so final result of restoration is random. Replication already does the good thing: it transforms INSERT DELAYED into a normal INSERT. How to repeat: Create this file in the sql-bench directory of your MySQL source tree: #!/usr/bin/perl use Cwd; use DBI; use Benchmark; $pwd = cwd(); $pwd = "." if ($pwd eq ''); require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; $dbh = $server->connect() || die $dbh->errstr; $dbh->do("reset master"); $dbh->do("drop table if exists test1, test2"); $dbh->do("create table test1 (a int)"); $dbh->do("create table test2 (a int)"); for (;;) { $dbh->do("insert DELAYED into test1 values(1)"); } $dbh->disconnect; # close connection Then launch this Perl program, and while it's running do (in another session): insert into test2 select * from test1; Then interrupt the Perl program. And see: MASTER> select count(*) from test1; +----------+ | count(*) | +----------+ | 58077 | +----------+ 1 row in set (0.00 sec) MASTER> select count(*) from test2; +----------+ | count(*) | +----------+ | 31039 | +----------+ 1 row in set (0.00 sec) MASTER> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +------------------+----------+--------------+------------------+ | gbichot2-bin.001 | 4065731 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MASTER> flush logs; Query OK, 0 rows affected (0.00 sec) MASTER> Bye [guilhem@gbichot2 guilhem]$ /m/mysql-4.0/client/mysqlbinlog /m/data/4/1/gbichot2-bin.001 | mysql1 [guilhem@gbichot2 guilhem]$ mysql1 test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 4.0.18-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. MASTER> select count(*) from test1; +----------+ | count(*) | +----------+ | 58077 | +----------+ 1 row in set (0.00 sec) MASTER> select count(*) from test2; +----------+ | count(*) | +----------+ | 30839 | +----------+ 1 row in set (0.00 sec) So, after restoring, test2 does not have the same number of rows as originally. Suggested fix: We need a variable @@session.DISABLE_INSERT_DELAYED; mysqlbinlog should wrap its output with SET @@session.DISABLE_INSERT_DELAYED=0; SET @@session.DISABLE_INSERT_DELAYED=1; Note that the server already has --max-delayed-threads=0 to disable all INSERT DELAYED in the server but we need a *session-level* settable variant. Maybe @@session.max_delayed_threads ?