Bug #118462 Executed SQL on replica is different from source

• Submitted: 17 Jun 2:56
• Modified: 1 Jul 14:09
• Reporter: KILWOO PYO
• Status: Can't repeat
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 8.0.39
• OS: Red Hat (Rocky Linux 8.10)
• Assigned to: MySQL Verification Team
• CPU Architecture: x86 (AMD EPYC 7R32)

[17 Jun 2:56] KILWOO PYO

Description:
Replication has stopped (SQL thread only) after DDL entered on source.
Column to modify is opposite between source and replica.
Diagnosed binary log(mysql-bin.xxxxxx) using mysqlbinlog.
The ALTER TABLE statement found on source's binary log and not found on replica's

DDL entered on source
====
ALTER TABLE A.C MODIFY COLUMN brand_index int;

Expected result
====
```
before:
CREATE TABLE `C` (
...
  `brand_index` varchar(2) DEFAULT NULL,
...

after:
CREATE TABLE `C` (
...
  `brand_index` int DEFAULT NULL,
...
```

Actual result
====

Source:
----
```
before:
CREATE TABLE `C` (
...
  `brand_index` varchar(2) DEFAULT NULL,
...

after:
CREATE TABLE `C` (
...
  `brand_index` int DEFAULT NULL,
...
```

Replica:
----
```
before:
CREATE TABLE `C` (
...
  `brand_index` varchar(2) DEFAULT NULL,
...

after:
CREATE TABLE `C` (
...
  `brand_index` varchar(2) DEFAULT NULL,
...

> select * from performance_schema.replication_applier_status_by_worker limit 1\G
...
      LAST_ERROR_NUMBER: 13146
      LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.003081, end_log_pos 180145036; Column 2 of table 'A.C' cannot be converted from type 'int' to type 'varchar(6(bytes) utf8mb3)'
      LAST_ERROR_TIMESTAMP: 2025-06-11 15:07:19.494513
...
```

How to repeat:
Topology:
Source
-> Replica X
-> Replica Y
-> Replica Z (replicate_do_db=A,P,Q)

Server config:
- Source and Replica
  - MySQL ver 8.0.39
  - transaction_isolation=read-uncommitted
  - binlog_format=mixed
  - gtid_mode = OFF_PERMISSIVE
  - collation-server = utf8mb3_general_ci
  - character-set-filesystem = utf8mb3
  - character-set-server = utf8mb3
- Replica only
  - replicate_do_db = A,P,Q (3 databases only. not 'mysql' and 'sys')
  - read_only = on
  - log_slave_updates = on

Database:
create database A; (19 tables)
create database P; (49 tables)
create database Q; (7560 tables)
create database R; (731 tables)

Table:
use A;
CREATE TABLE `C` (
  `seq` int NOT NULL AUTO_INCREMENT,
  `contents_id` varchar(6) NOT NULL,
  `brand_index` varchar(2) DEFAULT NULL,
  `category_id` varchar(5) NOT NULL,
  `language` varchar(7) NOT NULL,
  `is_privacy` tinyint NOT NULL DEFAULT '0',      
  `item` varchar(10) NOT NULL,    
  `title` varchar(256) DEFAULT NULL,
  `memo` text,
  `admin` varchar(100) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
  PRIMARY KEY (`seq`),
  UNIQUE KEY `UNIQUE_contentsId` (`contents_id`),
  KEY `INDEX_categoryId_brandIndex_language` (`category_id`,`brand_index`,`language`),
  KEY `INDEX_brandIndex` (`brand_index`)
) ENGINE=InnoDB AUTO_INCREMENT=1702 DEFAULT CHARSET=utf8mb3;

Load:
OLTP avg 2000 QPS

Enter SQL on source:
ALTER TABLE A.C MODIFY COLUMN brand_index int;

check replication status on Replica Z

[1 Jul 14:09] MySQL Verification Team

Hi,

I cannot reproduce this on 8.0.42 (I do not see any changes between 8.0.39 and 8.0.42 that could affect this). Is there a chance you can test this with 8.0.42 as no matter what I try I do not reproduce this.