MySQL Bugs: #68074: Replication fails on the mysql.tables

Bug #68074	Replication fails on the mysql.tables_priv table
Submitted:	13 Jan 2013 20:39	Modified:	17 Jan 2013 20:00
Reporter:	Rogier van Staveren	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.27	OS:	Linux (Slackware 14.0 64 bit)
Assigned to:		CPU Architecture:	Any

Description:
We have configured a master-master configuration using MySQL 5.5.27 and are experiencing a problem when assigning table privileges to users. When an administrator has a wildcard in the hostname the grantors hostname is changed during replication, the wildcard is replaced.

How to repeat:
Master-master configuration has 2 nodes, mysql1.example.com and mysql2.example.com

MySQL administrator has a wildcard in hostname:
On mysql1.example.com
mysql -u root -p
CREATE USER 'adminuser'@'admin%.example.com' IDENTIFIED BY '******';
GRANT ALL PRIVILEGES ON *.* TO 'adminuser'@'admin%.example.com' WITH GRANT OPTION;
FLUSH PRIVILEGES;

MySQL administrator creates a user from host admin1.example.com:
mysql -h mysql1.example.com -u testuser -p
CREATE USER 'testuser'@'localhost';
GRANT USAGE ON *.* TO 'testuser'@'localhost';
GRANT SELECT ON `test`.`test` TO 'testuser'@'localhost';
FLUSH PRIVILEGES;

This adds a row to the mysql.tables_priv table
SELECT * FROM `mysql`.`tables_priv`\G
*************************** 1. row ***************************
Host: localhost
Db: test
User: testuser
Table_name: test
Grantor: adminuser@admin%.example.com
Timestamp: 2013-01-11 03:29:39
Table_priv: Select
Column_priv:

MySQL replication replicates the user to the other node, when looking up the changes on this other node this is what I get:
mysql -h mysql2.example.com -u testuser -p
SELECT * FROM `mysql`.`tables_priv`\G
*************************** 1. row ***************************
Host: localhost
Db: test
User: testuser
Table_name: test
Grantor: adminuser@admin1.example.com
Timestamp: 2013-01-11 03:29:39
Table_priv: Select
Column_priv:

As you can see the wildcard is changed replaced replication!

Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html: "The Timestamp and Grantor columns currently are unused and are discussed no further here. " So this is in design yet and technically not a bug.

But inconsistency is repeatable with both row and statement binary log format, so I set it to "Verified".

I believe content of Grantor field is what returned by function USER(), therefore different on master and slave.

I get slightly different inconsistency:

include/master-slave.inc
Warnings:
Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
Note	####	Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
[connection master]
set global binlog_format='row';
set binlog_format='row';
create table test(f1 int);
CREATE USER 'adminuser'@'1%.0.0.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'adminuser'@'1%.0.0.1' WITH GRANT OPTION;
FLUSH PRIVILEGES;
CREATE USER 'testuser'@'localhost';
GRANT USAGE ON *.* TO 'testuser'@'localhost';
GRANT SELECT ON `test`.`test` TO 'testuser'@'localhost';
FLUSH PRIVILEGES;
show binlog events;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	4	Format_desc	1	120	Server ver: 5.7.1-m11-debug-log, Binlog ver: 4
master-bin.000001	120	Query	1	219	use `test`; create table test(f1 int)
master-bin.000001	219	Query	1	395	use `test`; CREATE USER 'adminuser'@'1%.0.0.1' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
master-bin.000001	395	Query	1	557	use `test`; GRANT ALL PRIVILEGES ON *.* TO 'adminuser'@'1%.0.0.1' WITH GRANT OPTION
master-bin.000001	557	Query	1	640	use `test`; FLUSH PRIVILEGES
master-bin.000001	640	Query	1	749	use `test`; CREATE USER 'testuser'@'localhost'
master-bin.000001	749	Query	1	888	use `test`; GRANT USAGE ON *.* TO 'testuser'@'localhost'
master-bin.000001	888	Query	1	1043	use `test`; GRANT SELECT ON `test`.`test` TO 'testuser'@'localhost'
master-bin.000001	1043	Query	1	1126	use `test`; FLUSH PRIVILEGES
SELECT * FROM `mysql`.`tables_priv`;
Host	Db	User	Table_name	Grantor	Timestamp	Table_priv	Column_priv
localhost	test	testuser	test	adminuser@localhost	0000-00-00 00:00:00	Select	
SELECT * FROM `mysql`.`tables_priv`;
Host	Db	User	Table_name	Grantor	Timestamp	Table_priv	Column_priv
localhost	test	testuser	test	adminuser@1%.0.0.1	0000-00-00 00:00:00	Select	
SELECT * FROM `mysql`.`tables_priv`;
Host	Db	User	Table_name	Grantor	Timestamp	Table_priv	Column_priv
localhost	test	testuser	test	adminuser@localhost	0000-00-00 00:00:00	Select

test case for MTR

Attachment: rpl_bug68074.test (application/octet-stream, text), 712 bytes.