MySQL Bugs: #13897: mysqlbinlog outputs wrong 'SET @@session.sql

Bug #13897	mysqlbinlog outputs wrong 'SET @@session.sql_mode' statement
Submitted:	10 Oct 2005 13:00	Modified:	21 Feb 2006 2:42
Reporter:	Laurenz Albe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.13-rc/5.0 BK source	OS:	Linux (RHEL 3 (Linux x86))
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
sql_mode is set to STRICT_TRANS_TABLES in my.cnf, it is displayed
correctly on "show variables like 'sql_mode';".
Binary logging is turned on.

When I format a binary log file with "mysqlbinlog", the following line appears
in the output:
SET @@session.sql_mode=2097152;

This line cannot be parsed my mysql, and recovery fails.

The problem can easily be worked around by replacing the line with something
correct before feeding the output to mysql, but I consider any bug that
prevents smooth database recovery serious.

How to repeat:
Put "sql_mode = STRICT_TRANS_TABLES" into my.cnf

Start the server: mysqld_safe &
Connect to mysql and issue (e.g.) the following commands:

flush logs;
create table test.test(id integer not null primary key);
insert into test.test values(1);
commit;
flush logs;

Run mysqlbinlog on the correct binlog:
mysqlbinlog data/mysql-bin.000009

The output:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#051010 14:54:14 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.13-rc-log created 051010 14:54:14
# at 98
#051010 14:54:37 server id 1  end_log_pos 226   Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1128948877;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=2097152;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33;
create table test.test(id integer not null primary key) engine=innodb;
# at 226
#051010 14:54:48 server id 1  end_log_pos 90    Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1128948888;
insert into test.test values(1);
# at 316
#051010 14:54:48 server id 1  end_log_pos 343   Xid = 92
COMMIT;
# at 343
#051010 14:54:54 server id 1  end_log_pos 386   Rotate to mysql-bin.000010  pos: 4
ROLLBACK;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Suggested fix:
Make mysqlbinlog output STRICT_TRANS_TABLES instead of 2097152.

on the opposite, I believe  SET @@session.sql_mode=2097152; should work
sql_mode is a enum, assigning a number to enum was always ok.
probably it was broken just recently

This bug is quite bad, it pretty much affects all of our windows users as the sql_mode includes strict_trans_tables by default, which means it will affect everyone by default!

There technically is a work around, but making everyone have to edit binary log output by default is  really really bad, both on DBA time and on showing how MySQL works out of the box (or doesn't in this case).

It appears that this bug is disrupting training classes, which must be doubly bad.  I've upgraded te priority from P2 to P1.

taking it (Elliot's ok).

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

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

ChangeSet
  1.2063 06/02/18 17:32:15 guilhem@mysql.com +3 -0
  Fix for BUG#13897 "failure to do SET SQL_MODE=N where N is a number > 31" (the original bug's title isn't the simplest
  symptom). sys_var::check_set() was wrong. mysqlbinlog makes use of such SET SQL_MODE=N
  (where N is interpreted like if SQL_MODE was a field of type SET), so
  this bug affected recovery from binlogs if the server was running with certain SQL_MODE values,
  for example the default values on Windows (STRICT_TRANS_TABLES); to work around this bug people
  had to edit mysqlbinlog's output.

the fix is in 5.0.19 and 5.1.8

Noted in 5.0.19, 5.1.7 changelogs.

          <literal>SET sql_mode = 
          <replaceable>N</replaceable></literal>, where
          <replaceable>N</replaceable> &gt; 31, did not work properly.
          (Bug #13897)