MySQL Bugs: #18581: Creation of system tables recorded in bin log causing slave failure

Bug #18581	Creation of system tables recorded in bin log causing slave failure
Submitted:	28 Mar 2006 18:15	Modified:	6 Dec 2006 19:54
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.9	OS:	Linux (Linux 32 Bit OS)
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
mysql> show binlog events\G
*************************** 1. row ***************************
   Log_name: master1.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 102
       Info: Server ver: 5.1.9-beta-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: master1.000001
        Pos: 102
 Event_type: Query
  Server_id: 1
End_log_pos: 1889
       Info: use `mysql`;  CREATE TABLE db (   ......... Removed due to size
*************************** 3. row ***************************
   Log_name: master1.000001
        Pos: 1889
 Event_type: Query
  Server_id: 1
End_log_pos: 2066
       Info: use `mysql`; INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y')
*************************** 4. row ***************************
   Log_name: master1.000001
        Pos: 2066
 Event_type: Query
  Server_id: 1
End_log_pos: 2248
       Info: use `mysql`;   INSERT INTO db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y')
*************************** 5. row ***************************
   Log_name: master1.000001
        Pos: 2248
 Event_type: Query
  Server_id: 1
End_log_pos: 3909
       Info: use `mysql`;  CREATE TABLE host (  ......... Removed due to size
*************************** 6. row ***************************
   Log_name: master1.000001
        Pos: 3909
 Event_type: Query
  Server_id: 1
End_log_pos: 6809
       Info: use `mysql`;  CREATE TABLE user (   .......... Removed due to size
*************************** 31. row ***************************

Replicate_Wild_Ignore_Table:
                 Last_Errno: 1050
                 Last_Error: Error 'Table 'db' already exists' on query. Default database: 'mysql'. Query: ' CREATE TABLE db (   Host char(60) binary DEFAULT '' NOT NULL,   Db char(64) binary DEFAULT '' NOT NULL,   User char(16) binary DEFAULT '' NOT NULL,   Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,   Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,   Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,  Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,   Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,   Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,   Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,   References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,   Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,   Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,   Create_tmp_table_priv enum('N','Y') COLLATE utf8_gener

How to repeat:
Create my.cnf that uses the binlog, run mysql_install_db script.

[manager]
default-mysqld-path = /home/ndbdev/jmiller/builds/libexec/mysqld
socket=/tmp/manager.sock
pid-file=/tmp/manager.pid
monitoring-interval = 50
port = 1999

[mysqld]
server-id=1
log-bin    = /space/var/master1
log        = /space/var/master1.log
log-error  = /space/var/master1.err
socket     = /tmp/mysql.sock
port       = 3306
pid-file   = /space/var/hostname.pid1
datadir    = /space/var/
language   = /home/ndbdev/jmiller/builds/share/mysql/english/

Work around:

replicate-ignore-db=mysql in slave my.cnf

See also BUG#22084, BUG#16563

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/14402

ChangeSet@1.2310, 2006-10-25 22:19:06+02:00, mats@romeo.(none) +5 -0
  BUG#18581 (Creation of system tables recorded in binlog causing slave failure):
  Not replicating the mysql database *at all* any more. All changes to
  mysql tables are replicated by replicating the statements that do
  the changes.

Before this report is closed, the manual should be clear on that all
mysql database changes (grants, create user, etc) are now replicated
by statement, even if ROW binlog format is used.

(Not necessarily for manual, but I mention anyway: 
Since there are some benefits in replication DDL changes as rows
instead of statements, we might in later versions of MySQL (perhaps
5.2) change this property and instead replicate mysql database changes
by by row instead.  Or we might introduce a new format (e.g. ROW-ONLY)
for this kind of replication.)

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/14653

ChangeSet@1.2310, 2006-11-01 09:49:03+01:00, mats@romeo.(none) +7 -0
  BUG#18581 (Creation of system tables recorded in binlog causing slave failure):
  Not replicating the mysql database *at all* any more. All changes to
  mysql tables are replicated by replicating the statements that do
  the changes.

Pushed into 5.1.14.

The manual has been updated with this information, in both the RBR/SBR comparison and in the 'Features' section.

Hi Mats,

I have created a test for this case per Calvin/Lars. Could you please give it a review and blessings?

#######################################
# Author: JBM
# Date: 12-04-2006
# Purpose: Bug #16563   RBR: Adding grants to master
#          for slave to connect cause slave to get error
##########################################

#Setup connections
connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,);
connect (slave,127.0.0.1,root,,test,$SLAVE_MYPORT,);
connect (slave1,127.0.0.1,root,,test,$SLAVE_MYPORT,);

#########
# Test 1 straight grants for a user that already exists
# on the slave mysqld
#########

# Connect to and setup slave
connection slave;
GRANT ALL ON *.* TO 'jonathan'@'%';
USE mysql;
SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan';
stop slave;
--wait_for_slave_to_stop
reset slave;

# Connect to and cleanup master
connection master;
reset master;

# Start the slave now that the master has been cleaned
connection slave;
start slave;

# Connect to master and grant permissions
connection master;
GRANT ALL ON *.* TO 'jonathan'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'jonathan'@'%' IDENTIFIED BY 'test';

# Connect back to slave and ensure that the slave is still running
--sync_slave_with_master
--replace_result $MASTER_MYPORT MASTER_MYPORT
--replace_column 1 # 9 # 11 # 23 # 33 #
show slave status;

########
# Test 2: Remove permissions from Master
########

# Now removed the permissions and make sure they are replicated
connection master;
REVOKE REPLICATION SLAVE ON *.* FROM 'jonathan'@'%';
USE mysql;
SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan';

# Connect back to slave and ensure that the slave is still running
--sync_slave_with_master
--replace_result $MASTER_MYPORT MASTER_MYPORT
--replace_column 1 # 9 # 11 # 23 # 33 #
show slave status;
USE mysql;
SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan';

# cleanup the mysql database
connection master;
DROP USER 'jonathan'@'%';
USE mysql;
SELECT user FROM user WHERE user = 'jonathan';

# Ckeck the slave
--sync_slave_with_master
--replace_result $MASTER_MYPORT MASTER_MYPORT
--replace_column 1 # 9 # 11 # 23 # 33 #
show slave status;
USE mysql;
SELECT user FROM user WHERE user = 'jonathan';

# End of 5.1 Test Case

RESULTS:
GRANT ALL ON *.* TO 'jonathan'@'%';
USE mysql;
SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan';
user    Repl_slave_priv
jonathan        Y
stop slave;
Warnings:
Note    1255    Slave already has been stopped
reset slave;
reset master;
start slave;
GRANT ALL ON *.* TO 'jonathan'@'%';
GRANT REPLICATION SLAVE ON *.* TO 'jonathan'@'%' IDENTIFIED BY 'test';
show slave status;
Slave_IO_State  Master_Host     Master_User     Master_Port     Connect_Retry   MasterSeconds_Behind_Master_Table
#       127.0.0.1       root    MASTER_MYPORT   1       master-bin.000001       331   #oneer-bin.00000100003
REVOKE REPLICATION SLAVE ON *.* FROM 'jonathan'@'%';
USE mysql;
SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan';
user    Repl_slave_priv
jonathan        N
show slave status;
Slave_IO_State  Master_Host     Master_User     Master_Port     Connect_Retry   MasterSeconds_Behind_Master_Table
#       127.0.0.1       root    MASTER_MYPORT   1       master-bin.000001       445   #oneer-bin.00000100003
USE mysql;
SELECT user, Repl_slave_priv FROM user WHERE user = 'jonathan';
user    Repl_slave_priv
jonathan        N
DROP USER 'jonathan'@'%';
USE mysql;
SELECT user FROM user WHERE user = 'jonathan';
user
show slave status;
Slave_IO_State  Master_Host     Master_User     Master_Port     Connect_Retry   MasterSeconds_Behind_Master_Table
#       127.0.0.1       root    MASTER_MYPORT   1       master-bin.000001       533   #oneer-bin.00000100003
USE mysql;
SELECT user FROM user WHERE user = 'jonathan';
user

oops, wrong one, sorry