Bug #30266 show binlog events ignores set variables
Submitted: 7 Aug 2007 3:54 Modified: 7 Aug 2007 6:56
Reporter: Eric Bergen (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.45 OS:Any
Assigned to: CPU Architecture:Any
Tags: show binglog events
Triage: D5 (Feature request)

[7 Aug 2007 3:54] Eric Bergen
Description:
The show binlog events command ignores set variables from the binary log. There is a comment in the source showing that this functionality is missing. The test case below shows the binary log events from importing a small dump file created by mysqldump. The show binlog events output doesn't show any of the set variables used by the import (such as the all important set foriegn_key_checks=0). Using mysqlbinlog on the same log file shows the variables as they should be. 

The issue is documented in the source but not in the manual. See the following comment from sql/log_event.cc:1055 Query_log_event::pack_info

  This (which is used only for SHOW BINLOG EVENTS) could be updated to
  print SET @@session_var=. But this is not urgent, as SHOW BINLOG EVENTS is
  only an information, it does not produce suitable queries to replay (for
  example it does not print LOAD DATA INFILE).

How to repeat:
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| kamet-bin.000001 |       642 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> show binlog events in 'kamet-bin.000001' from 0;
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                 |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------+
| kamet-bin.000001 |   4 | Format_desc |         1 |          98 | Server ver: 5.0.45-log, Binlog ver: 4                                                                |
| kamet-bin.000001 |  98 | Query       |         1 |         187 | use `test`; DROP TABLE IF EXISTS `foo`                                                               |
| kamet-bin.000001 | 187 | Query       |         1 |         338 | use `test`; CREATE TABLE `foo` (
  `foo` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
| kamet-bin.000001 | 338 | Query       |         1 |         443 | use `test`; /*!40000 ALTER TABLE `foo` DISABLE KEYS */                                               |
| kamet-bin.000001 | 443 | Query       |         1 |         538 | use `test`; INSERT INTO `foo` VALUES (1),(2)                                                         |
| kamet-bin.000001 | 538 | Query       |         1 |         642 | use `test`; /*!40000 ALTER TABLE `foo` ENABLE KEYS */                                                |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql>
[2]+  Stopped                 myh client
ebergen@kamet:(~/mysql/mysql-5.0.45) mysqlbinlog kamet-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#070806 20:42:06 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.45-log created 070806 20:42:06 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK;
# at 98
#070806 20:42:11 server id 1  end_log_pos 187   Query   thread_id=7     exec_time=0     error_code=0
use test;
SET TIMESTAMP=1186458131;
SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=1, @@session.unique_checks=0;
SET @@session.sql_mode=524288;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8;
DROP TABLE IF EXISTS `foo`;
# at 187
#070806 20:42:11 server id 1  end_log_pos 338   Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1186458131;
CREATE TABLE `foo` (
  `foo` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
# at 338
#070806 20:42:11 server id 1  end_log_pos 443   Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1186458131;
/*!40000 ALTER TABLE `foo` DISABLE KEYS */;
# at 443
#070806 20:42:11 server id 1  end_log_pos 538   Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1186458131;
INSERT INTO `foo` VALUES (1),(2);
# at 538
#070806 20:42:11 server id 1  end_log_pos 642   Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1186458131;
/*!40000 ALTER TABLE `foo` ENABLE KEYS */;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Suggested fix:
Show binlog events really needs to be fixed to show complete output the way that mysqlbinlog does. As a consolation updating the docs will do.
[7 Aug 2007 6:56] Sveta Smirnova
Thank you for the resonable feature request.
[7 Aug 2007 7:00] Sveta Smirnova
I created separate Bug #30268 about documentation inconsistence.