Bug #118798 Replication Break in 8.0.40 salve when from 8.0.33 Master

• Submitted: 7 Aug 9:22
• Modified: 7 Aug 10:28
• Reporter: sathish kumar
• Status: Open
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 8.0.40
• OS: Ubuntu
• CPU Architecture: Any
• Tags: MySQL 8.0.40

[7 Aug 9:22] sathish kumar

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

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