MySQL Bugs: #41980: SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG

Bug #41980	SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
Submitted:	8 Jan 2009 20:10	Modified:	18 Mar 2009 15:22
Reporter:	Serdar S. Kacar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.30, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Guangbao Ni	CPU Architecture:	Any
Tags:	INSERT .. SELECT, limit, SBL, sbr

Description:
Excerpt :
"Statement-based replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements is unsafe since the order of the rows affected is not defined. .. when using STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued. "

Thıs is a correct statement IF AND ONLY IF binary logging is on.
Morover, the statement asserts a warning is issued, not an error.

However, current implementation disregards effective SQL_LOG_BIN state and, at least for INSERT .. SELECT .. LIMIT statements, throws "statement is not safe for statement-based replication is now issued." as ERROR !

How to repeat:
C:\>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 186
Server version: 5.1.30-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> drop table if exists r1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists r2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table r1 (a int primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> create table r2 (a int primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@SQL_LOG_BIN = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@SQL_LOG_BIN;
+---------------+
| @@SQL_LOG_BIN |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> insert r1 select * from r2 limit 1;
ERROR 1592 (HY000): Statement is not safe to log in statement format.
mysql>

Suggested fix:
when @@SESSION.SQL_LOG_BIN is 0
Do not issue a warning or an error that begins with "Statement is not safe to log .." and update the documents. 
We have nothing to do with binary logging in this case.

When @@SESSION.SQL_LOG_BIN is 1 
Correct either documentation or implementation regarding warning/error selection.

Thank you for the report.

Regarding to error most likely you have SQL_MODE set to STRICT_ALL_TABLES or STRICT_TRANS_TABLES which convert warnings to errors. So this is correct and not a bug.

Regarding to warning/error in case of @@SQL_LOG_BIN = 0; bug is verified as described.

Workaround: change session SQL_MODE to less strict and ignore warning or use session binary log format row.

SQL_MODE was STRICT_TRANS_TABLES.

In Bug #41809, I can understand the case as the tables were InnoDB.

But this bug (Bug #41980) is repeatable even when the test tables are all MyISAM. So, who is converting the warning to the error?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65881

2793 Guangbao Ni	2009-02-11
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66714

2805 Guangbao Ni	2009-02-18
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
      
      Don't judge whether the binary logging function is turned on, 
      directly issue the unsafe warning/error message when sql_mode=statement.
      
      Fixed with adding a condition which judge if the logging function is turned on.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66749

2805 Guangbao Ni	2009-02-18
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
      
      When binlog_format is STATEMENT and the statement is unsafe before,      
      the unsafe warning/error message was issued without checking
      whether the SQL_LOG_BIN was turned on or not.
      
      Fixed with adding a condition to check if SQL_LOG_BIN is turned on.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67623

2805 Guangbao Ni	2009-02-26
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
            
      When binlog_format is STATEMENT and the statement is unsafe before,      
      the unsafe warning/error message was issued without checking
      whether the SQL_LOG_BIN was turned on or not.
            
      Fixed with adding two conditions:
      1) to check if SQL_LOG_BIN is turned on, OR
      2) the toplevel SQL_LOG_BIN is on and in sub statement (for the sp disabled the binary logging automatically)
      So add a enabled_bin_log flag in THD to indicate whether the toplevel SQL_LOG_BIN is on or not when in sp.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67636

2805 Guangbao Ni	2009-02-26
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
                  
      When binlog_format is STATEMENT and the statement is unsafe before,      
      the unsafe warning/error message was issued without checking
      whether the SQL_LOG_BIN was turned on or not.
                  
      Fixed with adding two conditions:
      1) to check if SQL_LOG_BIN is turned on, OR
      2) in sub statement and the top level SQL_LOG_BIN is on(for the sp disabled the binary logging automatically)
      So add a enabled_bin_log flag in THD to indicate whether the top level SQL_LOG_BIN is on or not when in sp.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67935

2827 Guangbao Ni	2009-02-26
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
      
      When binlog_format is STATEMENT and the statement is unsafe before,
      the unsafe warning/error message was issued without checking
      whether the SQL_LOG_BIN was turned on or not.
      
      Fixed with adding a sql_log_bin_toplevel flag in THD to check
      whether SQL_LOG_BIN is ON in current session whatever the current is in sp or not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68067

2827 Guangbao Ni	2009-02-28
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
      
      When binlog_format is STATEMENT and the statement is unsafe before,
      the unsafe warning/error message was issued without checking
      whether the SQL_LOG_BIN was turned on or not.
      
      Fixed with adding a sql_log_bin_toplevel flag in THD to check
      whether SQL_LOG_BIN is ON in current session whatever the current is in sp or not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68068

2827 Guangbao Ni	2009-02-26
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
      
      When binlog_format is STATEMENT and the statement is unsafe before,
      the unsafe warning/error message was issued without checking
      whether the SQL_LOG_BIN was turned on or not.
      
      Fixed with adding a sql_log_bin_toplevel flag in THD to check
      whether SQL_LOG_BIN is ON in current session whatever the current is in sp or not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68102

2827 Guangbao Ni	2009-03-03
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
      
      When binlog_format is STATEMENT and the statement is unsafe before,
      the unsafe warning/error message was issued without checking
      whether the SQL_LOG_BIN was turned on or not.
      
      Fixed with adding a sql_log_bin_toplevel flag in THD to check
      whether SQL_LOG_BIN is ON in current session whatever the current is in sp or not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68347

2832 Guangbao Ni	2009-03-05
      BUG#41980 SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 !
            
      When binlog_format is STATEMENT and the statement is unsafe before,
      the unsafe warning/error message was issued without checking
      whether the SQL_LOG_BIN was turned on or not.
            
      Fixed with adding a sql_log_bin_toplevel flag in THD to check
      whether SQL_LOG_BIN is ON in current session whatever the current is in sp or not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68350

3105 Guangbao Ni	2009-03-05 [merge]
      Merge bug#41980 from 5.1-bugteam

merged into mysql-5.1-bugteam and mysql-6.0-bugteam

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:zhou.li@sun.com-20090311061050-ihp0g77znonq1tuq) (merge vers: 5.1.33) (pib:6)

Documented bugfix in the 5.1.33 changelog as follows:

        When --binlog_format was set to STATEMENT, a statement unsafe
        for statement-based logging caused an error or warning to be
        issued even if sql_log_bin was set to 0.

Set status to NDI pending merge to 6.0 tree.

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:matthias.leich@sun.com-20090310140952-gwtoq87wykhji3zi) (merge vers: 6.0.11-alpha) (pib:6)

Bugfix also noted in the 6.0.11 changelog; closed.

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)