Bug #93750 Escaping of column names for GRANT statements does not persist in binary logs
Submitted: 27 Dec 2018 14:23 Modified: 3 Jan 11:09
Reporter: Andrii Ustymenko Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7, 5.7.24 OS:Any
Assigned to: CPU Architecture:x86

[27 Dec 2018 14:23] Andrii Ustymenko
Column based GRANT statements are written to the binary logs without proper escaping of the columns. It can be a problem when column name is a key word of mysql itself. Having been successfully executed on master they break replication thread on the slaves if `mysql` schema is part of the replication.

How to repeat:
Install the latest mysql 5.7 from Oracle repo. 

Example with Centos 7 and yum-utils:

[#]$ rpm -Uhv https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
[#]$ yum-config-manager --disable mysql80-community
[#]$ yum-config-manager --enable mysql57-community
[#]$ yum install -y mysql-community-server

Enable binary logs by adding next lines into  my.cnf

log-bin = mysql-bin
server-id = 20162

Start mysqld

[#]$ systemctl start mysqld

Connect to mysql. Create test schema, table and user.

mysql> create database test_t;
Query OK, 1 row affected (0.13 sec)

mysql> use test_t;
Database changed

mysql> CREATE TABLE `foo` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `from` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    ->   PRIMARY KEY (`id`)
Query OK, 0 rows affected (0.27 sec);

mysql> create user 'user1'@'%' identified by 'AJ<mkf03htiZ' password expire never;
Query OK, 0 rows affected (0.13 sec)

mysql> grant select (`id`,`from`) on `test_t`.`foo` to 'user1'@'%';
Query OK, 0 rows affected (0.21 sec)

With proper escaping grant statement has been successfully executed. But, if we look into binary log escaping is gone:

[#]$ mysqlbinlog -v mysql-bin.000001
#181227 14:10:31 server id 20162  end_log_pos 2119 CRC32 0x54baf289     Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1545916231/*!*/;
GRANT SELECT (id, from) ON `test_t`.`foo` TO 'user1'@'%'
# at 2119

The main problem is when replication thread is reading binary log it also doesn't get the escaping quotes. So, this statement cannot be executed on the slaves and breaks the replication:

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from) ON `test_t`.`foo` TO 'user1'@'%'' at line 1' on query. Default database: 'test_t'. Query: 'GRANT SELECT (id, from) ON `test_t`.`foo` TO 'user1'@'%''

However DDL statements seem to work fine:

mysql> alter table `foo` add column `bar` varchar(1) DEFAULT NULL after `from`;
Query OK, 0 rows affected (1.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

[#]$ mysqlbinlog -v mysql-bin.000001
#181227 14:12:33 server id 20162  end_log_pos 2365 CRC32 0x30c596ea     Query   thread_id=5     exec_time=1     error_code=0
SET TIMESTAMP=1545916353.966895/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
alter table `foo` add column `bar` varchar(1) DEFAULT NULL after `from`
[30 Dec 2018 6:38] Umesh Shastry
Hello Andrii,

Thank you for the report and test case.
Verified as described with 5.7.24 build.

[3 Jan 11:09] Margaret Fisher
Posted by developer:
Added number to changelog entry for Bug #28643405, closing.
[24 Jan 12:31] Umesh Shastry
Bug #94035 marked as duplicate of this one