| Bug #89472 | Execute grant proxy statement with Connector/Python result in a wrong binlog | ||
|---|---|---|---|
| Submitted: | 30 Jan 2018 16:21 | Modified: | 29 Mar 2018 12:41 |
| Reporter: | smoothie xchliu | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / Python | Severity: | S2 (Serious) |
| Version: | 5.7.18 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | binlog, grant, proxy | ||
[29 Mar 2018 12:41]
Chiranjeevi Battula
Hello smoothie, Thank you for the bug report. I could not repeat the issue at my end using Connector / Python 2.1.7 with MySQL 5.7.21 and not seeing any issues. If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL. Thanks, Chiranjeevi.
[29 Mar 2018 12:43]
Chiranjeevi Battula
Binlog--------- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #180329 17:12:22 server id 1 end_log_pos 123 CRC32 0x7fab8e6b Start: binlog v 4, server v 5.7.21-log created 180329 17:12:22 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' HtG8Wg8BAAAAdwAAAHsAAAABAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAe0bxaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AWuOq38= '/*!*/; # at 123 #180329 17:12:22 server id 1 end_log_pos 154 CRC32 0x4ba67d15 Previous-GTIDs # [empty] # at 154 #180329 17:17:06 server id 1 end_log_pos 219 CRC32 0x3a93ee5b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #180329 17:17:06 server id 1 end_log_pos 453 CRC32 0x1cf7baf1 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1522324026/*!*/; SET @@session.pseudo_thread_id=7/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1344274432/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; GRANT PROXY ON ''@'' TO 'test'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*43288C090E985985E3AD988FE9690DC8C03C9A95' WITH GRANT OPTION /*!*/; # at 453 #180329 17:32:44 server id 1 end_log_pos 518 CRC32 0xeb9a5420 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 518 #180329 17:32:44 server id 1 end_log_pos 752 CRC32 0xb721a701 Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1522324964/*!*/; GRANT PROXY ON ''@'' TO 'test'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*43288C090E985985E3AD988FE9690DC8C03C9A95' WITH GRANT OPTION /*!*/; # at 752 #180329 17:34:03 server id 1 end_log_pos 817 CRC32 0xdf96f157 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 817 #180329 17:34:03 server id 1 end_log_pos 1051 CRC32 0x89cc4314 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1522325043/*!*/; GRANT PROXY ON ''@'' TO 'test'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password' AS '*43288C090E985985E3AD988FE9690DC8C03C9A95' WITH GRANT OPTION /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Description: I want to grant a proxy privileges to a user, grant sql should be : GRANT PROXY ON ''@'' TO 'test'@'127.0.0.1' WITH GRANT OPTION; as a mistake I had add the password in the sql: GRANT PROXY ON ''@'' TO 'test'@'127.0.0.1' IDENTIFIED BY PASSWORD '*43288C090E985985E3AD988FE9690DC8C03C9A95' WITH GRANT OPTION; it works on the mysql command line tool,and the binary log logged as bellow: GRANT PROXY ON ''@'' TO 'test'@'127.0.0.1' IDENTIFIED BY PASSWORD '*43288C090E985985E3AD988FE9690DC8C03C9A95' WITH GRANT OPTION; Then ,I wrote a python script in which use a python connector to connect the server and run the sql . the bianry log logged different: GRANT USAGE ON *.* TO ''@'','test'@'127.0.0.1' IDENTIFIED BY PASSWORD '*43288C090E985985E3AD988FE9690DC8C03C9A95' WITH GRANT OPTION This should lead a inconsistency between Master and Slave regardless the binlog format. How to repeat: 1. Connector/Python version: VERSION = (2, 1, 3, '', 0);python 3.4 2. Test code: import mysql.connector sql = " GRANT PROXY ON ''@'' TO 'test'@'127.0.0.1' IDENTIFIED BY PASSWORD '*43288C090E985985E3AD988FE9690DC8C03C9A95' WITH GRANT OPTION;" conn = mysql.connector.connect(dict(host='', port=3306, user='test', password='test')) conn.cursor().execute(sql) conn.close() 3. Use mysqlbinlog to parse the binlog and check the binlog content. Suggested fix: Log the origin sql if the sql is executed successfully as the the document of mysql server comment: Statements that change the mysql database indirectly are logged as statements regardless of the value of binlog_format.This pertains to statements such as GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE