MySQL Bugs: #49567: INSERT DELAYED uses global binlog

Bug #49567	INSERT DELAYED uses global binlog_format, should use session
Submitted:	9 Dec 2009 16:37	Modified:	12 Jan 2015 15:27
Reporter:	Sven Sandberg	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	binlog_format, insert delayed

Description:
INSERT DELAYED binlogs in the format specified by value that @@global.binlog_format had at the time the insert_delayed thread was started (i.e., when the first INSERT DELAYED was executed). This is unintuitive and undocumented. It would be better if it worked like all other variables: log according to the value that @@session.binlog_format had when INSERT DELAYED was executed.

How to repeat:
--source include/have_log_bin.inc
CREATE TABLE t1 (a INT);
SET SESSION BINLOG_FORMAT = ROW;
SET GLOBAL BINLOG_FORMAT = STATEMENT;
INSERT DELAYED INTO t1 VALUES (1);
FLUSH TABLES;

SET SESSION BINLOG_FORMAT = STATEMENT;
SET GLOBAL BINLOG_FORMAT = ROW;
INSERT DELAYED INTO t1 VALUES (1);
FLUSH TABLES;

SHOW BINLOG EVENTS;

Suggested fix:
In sql_insert.cc, handle binlog_format the same way we handle sql_mode:
 - delayed_row should have a field for binlog_format
 - write_delayed should copy the binlog_format field from THD to delayed_row
 - Delayed_insert::handle_inserts should copy the binlog_format field from delayed_row to THD

In addition, in upgrade_lock_type, we should replace the line:

    if (global_system_variables.binlog_format == BINLOG_FORMAT_STMT &&
        log_on && mysql_bin_log.is_open() && is_multi_insert)

... by ...

    if (thd->variables.binlog_format == BINLOG_FORMAT_STMT &&
        log_on && mysql_bin_log.is_open() && is_multi_insert)

. See the comment below this line.

Here's a better test case. It verifies that INSERT DELAYED uses the value of the global binlog_format *as it was when the insert_delayed thread was started*, not as it was when the INSERT DELAYED was executed.

--source include/have_log_bin.inc

CREATE TABLE t1 (a INT);
SET SESSION BINLOG_FORMAT = ROW;
SET GLOBAL BINLOG_FORMAT = STATEMENT;
INSERT DELAYED INTO t1 VALUES (1);
SET GLOBAL BINLOG_FORMAT = ROW;
INSERT DELAYED INTO t1 VALUES (1);
FLUSH TABLES;

SET SESSION BINLOG_FORMAT = STATEMENT;
SET GLOBAL BINLOG_FORMAT = ROW;
INSERT DELAYED INTO t1 VALUES (1);
SET GLOBAL BINLOG_FORMAT = STATEMENT;
INSERT DELAYED INTO t1 VALUES (1);
FLUSH TABLES;

SHOW BINLOG EVENTS;

Verified just as described with recent 5.1.43 from bzr on Mac OS X.

In addition, a comment in sql_insert.cc is wrong in multiple ways. We should clarify the comment, perhaps as follows:

=== modified file 'sql/sql_insert.cc'
--- sql/sql_insert.cc	2009-09-10 07:40:57 +0000
+++ sql/sql_insert.cc	2009-12-09 17:14:04 +0000
@@ -2711,12 +2711,10 @@ bool Delayed_insert::handle_inserts(void
         errcode= query_error_code(&thd, TRUE);
       
       /*
-        If the query has several rows to insert, only the first row will come
-        here. In row-based binlogging, this means that the first row will be
-        written to binlog as one Table_map event and one Rows event (due to an
-        event flush done in binlog_query()), then all other rows of this query
-        will be binlogged together as one single Table_map event and one
-        single Rows event.
+        Log the INSERT DELAYED query in case binlog_format=STATEMENT.
+        Note that an INSERT DELAYED with multiple rows will turn into
+        a normal INSERT when binlog_format=STATEMENT, so there is no
+        risk that we log INSERT DELAYED more than once.
       */
       thd.binlog_query(THD::ROW_QUERY_TYPE,
                        row->query.str, row->query.length,

DELAYED has been deprecated in MySQL 5.6 (http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-6.html), and removed in MySQL 5.7 (http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-0.html).