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)

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.