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

[10 Oct 2005 13:00] Laurenz Albe
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.
[10 Oct 2005 19:02] Sergei Golubchik
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
[14 Dec 2005 21:34] Harrison Fisk
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).
[21 Dec 2005 6:58] Greg Lehey
It appears that this bug is disrupting training classes, which must be doubly bad.  I've upgraded te priority from P2 to P1.
[24 Jan 2006 14:00] Guilhem Bichot
taking it (Elliot's ok).
[25 Jan 2006 18:54] Bugs System
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
[18 Feb 2006 16:33] Bugs System
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
[18 Feb 2006 16:36] Guilhem Bichot
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.
[18 Feb 2006 20:34] Guilhem Bichot
the fix is in 5.0.19 and 5.1.8
[21 Feb 2006 2:42] Paul DuBois
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)