Bug #2491 INSERT DELAYED causes binary log unusable by mysqlbinlog (replication is ok)
Submitted: 23 Jan 2004 5:54 Modified: 11 Mar 2004 10:27
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.0 OS:Any (all)
Assigned to: Jani Tolonen CPU Architecture:Any

[23 Jan 2004 5:54] Guilhem Bichot
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 ?
[4 Mar 2004 7:16] Jani Tolonen
Will be fixed in next 4.0 release.
[11 Mar 2004 10:27] Jani Tolonen
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html