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.
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.