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.
