MySQL Bugs: #48382: mark reads from mysql.* and information

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	CPU Architecture:	Any
Tags:	information_schema, MySQL, unsafe

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.