Bug #48382 mark reads from mysql.* and information_schema.* as unsafe
Submitted: 28 Oct 2009 16:12
Reporter: Sven Sandberg Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1+ OS:Any
Assigned to: Assigned Account
Tags: information_schema, MySQL, unsafe
Triage: Triaged: D2 (Serious) / R1 (None/Negligible) / E1 (None/Negligible)

[28 Oct 2009 16:12] Sven Sandberg
Description:
The tables in the mysql and information_schema databases are filled in and updated by the server automatically, without replicating the changes. The contents may generally vary depending on server version, execution history and many other parameters that may vary between master and slave. Hence, it is not safe to log statements like the following in statement format:

# The slave may have tables that the master does not have or vice versa.
INSERT INTO test.t1 SELECT * FROM information_schema.tables;
# Future versions of mysql may define additional time zones.
INSERT INTO test.t2 SELECT * FROM mysql.time_zone;

Such statements should be marked unsafe, so that they get logged in row format.

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_mixed.inc

--connection slave

CREATE TABLE t_slave (a INT);

--connection master

CREATE TABLE t1 (a VARCHAR(1000));
INSERT INTO t1 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test';

SELECT * FROM t1;
sync_slave_with_master;
SELECT * FROM t1;

Suggested fix:
Mark all statements unsafe that read from tables in the information_schema and mysql databases.