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
Triage: Triaged: D2 (Serious)

[9 Dec 2009 16:37] Sven Sandberg
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.
[9 Dec 2009 16:52] Sven Sandberg
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;
[9 Dec 2009 16:58] Valeriy Kravchuk
Verified just as described with recent 5.1.43 from bzr on Mac OS X.
[9 Dec 2009 17:16] Sven Sandberg
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,
[12 Jan 2015 15:27] Stefan Hinz
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).