| Bug #118798 | Replication Break in 8.0.40 salve when from 8.0.33 Master | ||
|---|---|---|---|
| Submitted: | 7 Aug 9:22 | Modified: | 8 Aug 7:20 |
| Reporter: | sathish kumar | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S1 (Critical) |
| Version: | 8.0.40 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | MySQL 8.0.40 | ||
[7 Aug 10:27]
sathish kumar
grant select, show view on . to 'ds_batch_ops'@'10.%.%.%' ;
[7 Aug 10:28]
sathish kumar
Please ignore grant select, show view on . to 'test'@'10.%.%.%' ; This was supposed to be grant select, show view on . to 'ds_batch_ops'@'10.%.%.%' ;
[7 Aug 10:28]
sathish kumar
Please ignore grant select, show view on . to 'test'@'10.%.%.%' ; This was supposed to be grant select, show view on . to 'ds_batch_ops'@'10.%.%.%' ;
[7 Aug 20:54]
Simon Mudd
It is useful to share the following information, anonymised if needed, as different replication settings can modify behaviour significantly. - /etc/my.cnf configuration from master and slave(s) - output of SHOW GLOBAL VARIABLES from master and slave(s) - output of SHOW SLAVE STATUS\G on the slave(s) I am aware of some replication issues between different 8.0 versions and it helps enormously if this information is shared. It can also be shared privately. Note: replication should not break if things are configured consistently but up until about 8.0.35 earlier versions of MySQL 8.0 evolved "gradually" and some of these changes could trigger unexpected issues between different 8.0 versions.
[8 Aug 6:43]
sathish kumar
Steps to reproduce in Master:
mysql>
mysql> create user if not exists 'test1'@'172.%.%.%' identified by 'R9v!t6#QpLm2@Xc8';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select, show view on *.* to 'test1'@'172.%.%.%' ;
Query OK, 0 rows affected (0.00 sec)
mysql> create user if not exists 'test1'@'172.%.%.%' identified by 'R9v!t6#QpLm2@Xc8';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql>
mysql> grant select, show view on *.* to 'test1'@'10.%.%.%' ;
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql>
Slave:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.35.60.5
Master_User: replica_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000007
Read_Master_Log_Pos: 5243
Relay_Log_File: MysqlUpdgrade-Slave-02-relay-bin.000004
Relay_Log_Pos: 3815
Relay_Master_Log_File: binlog.000007
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: 1827
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000007, end_log_pos 5243. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 4974
Relay_Log_Space: 4477
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: 1827
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000007, end_log_pos 5243. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 5605
Master_UUID: fd403aa2-b0b0-11ef-b89a-02086dcea277
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: 250808 06:37:41
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
[8 Aug 6:46]
sathish kumar
mysql> select * from performance_schema.replication_applier_status_by_worker \G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 1827
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000007, end_log_pos 5243; Error 'The password hash doesn't have the expected format.'on query. Default database: ''. Query: 'CREATE USER IF NOT EXISTS 'test1'@'172.%.%.%' IDENTIFIED WITH 'mysql_native_password' AS 'R9v!t6#QpLm2@Xc8''
LAST_ERROR_TIMESTAMP: 2025-08-08 06:37:41.874487
LAST_APPLIED_TRANSACTION: ANONYMOUS
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2025-08-08 06:37:31.715762
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2025-08-08 06:37:31.715762
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2025-08-08 06:37:31.716121
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2025-08-08 06:37:31.716693
APPLYING_TRANSACTION: ANONYMOUS
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2025-08-08 06:37:41.873065
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2025-08-08 06:37:41.873065
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2025-08-08 06:37:41.873369
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)
ERROR:
No query specified
[8 Aug 7:20]
MySQL Verification Team
Hello sathish kumar, Thank you for the report and feedback. Verified as described. regards, Umesh
[8 Aug 7:29]
MySQL Verification Team
Solution is to upgrade primary/source servers to >=8.0.37 before upgrading replicas to >=8.0.37 if you ever run CREATE USER IF NOT EXISTS statements. Bug 36679825 - 8.0.37: CREATE USER IF NOT EXISTS fails with ERROR 1827 if user already exists. See KM: Replication From <= 8.0.36 to >= 8.0.37 Fails With ERROR 1827 (HY000): The password hash doesn't have the expected format. (Doc ID 3035916.1)
[8 Aug 7:36]
MySQL Verification Team
Thank you, Shane for pointing out this - "Solution is to upgrade primary/source servers to >=8.0.37 before upgrading replicas to >=8.0.37 if you ever run CREATE USER IF NOT EXISTS statements." This is duplicate of Bug #35530823 / Bug #111539 password from CREATE USER written as plaintext in binlog , breaks replication (marked private hence not visible) Closure details - https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-37.html Documented fix as follows in the MySQL 8.0.37 and 8.4.0 changelogs: CREATE USER IF EXISTS was not always logged correctly. Closed.
Description: Replication failure with the error : Replication was broken in the 8.0.40 and 8.0.42 version LAST_ERROR_NUMBER: 1827 LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000007, end_log_pos 1482; Error 'The password hash doesn't have the expected format.'on query. Default database: ''. Query: 'CREATE USER IF NOT EXISTS 'ds_app_batch_ops'@'172.%.%.%' IDENTIFIED WITH 'mysql_native_password' AS 'R9v!t6#0pLm2@Xc8'' How to repeat: Master server with 8.0.33, 1 slaves with 8.0.33 , one slave with 8.0.40, one slave with 8.0.42 Run the command with out user creation: grant select, show view on . to 'test'@'10.%.%.%' ; Replication is broken in 8.0.40 and 8.0.42. Where as replication is fine in 8.0.33 error LAST_ERROR_NUMBER: 1827 LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000007, end_log_pos 1482; Error 'The password hash doesn't have the expected format.'on query. Default database: ''. Query: 'CREATE USER IF NOT EXISTS 'ds_app_batch_ops'@'172.%.%.%' IDENTIFIED WITH 'mysql_native_password' AS 'R9v!t6#0pLm2@Xc8'' Suggested fix: Replication should not break in 8.0.40 and 8.0.42