Bug #84756 MySQL SHA-256 Authentication Plugin breaks replication with syntax error (1064)
Submitted: 31 Jan 2017 20:53 Modified: 1 Feb 2017 7:59
Reporter: Marcelo Altmann (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.17, 5.6.35 OS:Any
Assigned to: CPU Architecture:Any

[31 Jan 2017 20:53] Marcelo Altmann
Description:
MySQL SHA-256 Authentication Plugin will break replication if password replied contain single quotes.

master [localhost] {msandbox} ((none)) > SELECT Host, User, plugin, authentication_string FROM mysql.user  WHERE user = '1';
+-----------+------+-----------------+---------------------------------------------------------------------+
| Host      | User | plugin          | authentication_string                                               |
+-----------+------+-----------------+---------------------------------------------------------------------+
| 127.0.0.1 | 1    | sha256_password | $5$#}KW'ruOeWUx$93GZL3JpdqzmvEqO/vyQNuuty3uh9T6ZTlEYLcIC32/ |
+-----------+------+-----------------+---------------------------------------------------------------------+
1 row in set (0.01 sec)

slave1 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               . . .
               Last_SQL_Errno: 1064
               Last_SQL_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 ')ZK$VoyNrsWSfMRz8eYCw0.OQ8NHw7mhfN1nPm87Zgb1rG2'' at line 1' on query. Default database: ''. Query: 'ALTER USER '9'@'127.0.0.1' IDENTIFIED WITH 'sha256_password' AS '$5$w&
                                                                                                          &*]r	\~N')ZK$VoyNrsWSfMRz8eYCw0.OQ8NHw7mhfN1nPm87Zgb1rG2''

How to repeat:
1. Create a replication env:
make_replication_sandbox /opt/mysql/5.6.35 --how_many_nodes=1

2. Create a some users on master:
for i in {1..9999}; 
do 
    ./use -e "set old_passwords=2; CREATE USER '$i'@'127.0.0.1' IDENTIFIED WITH sha256_password; SET PASSWORD FOR '$i'@'127.0.0.1' = PASSWORD('$i');"; 
done

3. Monitor replication via SHOW SLAVE STATUS\G
slave1 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 23700
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 272479
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 3693
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   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 'ruOeWUx$93GZL3JpdqzmvEqO/vyQNuuty3uh9T6ZTlEYLcIC32/'' at line 1' on query. Default database: ''. Query: 'SET PASSWORD FOR '1'@'127.0.0.1'='$5$#}KW'ruOeWUx$93GZL3JpdqzmvEqO/vyQNuuty3uh9T6ZTlEYLcIC32/''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3483
              Relay_Log_Space: 272889
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1064
               Last_SQL_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 'ruOeWUx$93GZL3JpdqzmvEqO/vyQNuuty3uh9T6ZTlEYLcIC32/'' at line 1' on query. Default database: ''. Query: 'SET PASSWORD FOR '1'@'127.0.0.1'='$5$#}KW'ruOeWUx$93GZL3JpdqzmvEqO/vyQNuuty3uh9T6ZTlEYLcIC32/''
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 00023700-1111-1111-1111-111111111111
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 170131 15:45:10
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 00023700-1111-1111-1111-111111111111:1-1376
            Executed_Gtid_Set: 00023700-1111-1111-1111-111111111111:1-13
                Auto_Position: 1
1 row in set (0.00 sec)

Suggested fix:
Maybe escape quotes. Please note that some passwords will also generate double quotes.
[1 Feb 2017 7:59] Umesh Shastry
Hello Marcelo,

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

Thanks,
Umesh