MySQL Bugs: #14765: mysql binlog stores SQL modes that don't work

Bug #14765	mysql binlog stores SQL modes that don't work
Submitted:	8 Nov 2005 17:04	Modified:	22 May 2006 20:24
Reporter:	Max Mether	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.15/BK 5.0.16	OS:	Windows (Win/Linux)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
If using any other SQL Mode than "" the binlog stores the numerical value of the SQL Mode. 

The SET command with that numerical value does not work however. 

How to repeat:
1) Turn on the binary log

2) Log in to MySQL and change the SQL Mode

mysql >set session sql_mode = STRICT_ALL_TABLES;

3) Issue a query that gets into the bin log

mysql> CREATE TABLE a_test (a int);
Query OK, 0 rows affected (0.11 sec)

mysql> DROP TABLE a_test;
Query OK, 0 rows affected (0.00 sec)

4) Check out the bin log:

SET @@session.sql_mode=4194304;
CREATE TABLE a_test (a int);
# at 608
#051108 17:59:12 server id 60  end_log_pos 689  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1131469152;
DROP TABLE a_test;

5) Try to issue the SET command:

mysql> SET @@session.sql_mode=4194304;
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of '4194304'

This looks like the same problem described in BUG#13897, which I'm currently investigating.

Hi,
just to say that queries like SET SQL_MODE=1879867; used to work; I made them work intentionally in the past. So the fix is to make them work again (they must have been broken since??).
Thanks!

Extended test sql_mode to check for this specific value, confirmed that it no longer occurs.  As susepcted, this is a duplicate of BUG#13897.

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/5633

Test case change.
No changelog entry needed.