Bug #118700 Due to the master enabling the NO_BACKSLASH_ESCAPES sql_mode, an error occurs during replay on the slave.

• Submitted: 22 Jul 12:55
• Modified: 24 Jul 12:45
• Reporter: hao chen
• Status: Need Feedback
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 8.0.40
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[22 Jul 12:55] hao chen

Description:
The steps to reproduce are as follows:

1. Set the master's sql_mode='STRICT_TRANS_TABLES,NO_BACKSLASH_ESCAPES';

2. Create the database and table：
create database db100;
use db100;
CREATE TABLE t9 (
jcase_col587 decimal(65,30) DEFAULT '1.1',
jcase_col586 decimal(65,30) DEFAULT NULL,
jcase_col585 decimal(65,30) NOT NULL DEFAULT '1.1',
jcase_col6246 int NOT NULL DEFAULT '1',
c1 int NOT NULL,
jcase_col6247 int DEFAULT NULL,
c2 int DEFAULT NULL,
c3 double(7,5) DEFAULT NULL,
jcase_col6248 int DEFAULT '1',
c4 datetime DEFAULT NULL,
c5 varchar(500) COLLATE utf8mb4_bin DEFAULT NULL);

insert into t9 values(1.1,2.2,3.3,4,5,6,7,8.8,9,now(),'aaa');
insert into t9 values(1.1,2.2,3.3,4,5,6,7,8.8,9,now(),'aaa');
alter table t9 add column jcase_col4589 BINARY(255) not null default 'a' first;
create table t9_copy as select  /*+PARALLEL(4)*/ * from t9;

3. After waiting for the transaction to be sent to the slave, check the slave's status: SHOW SLAVE STATUS\G

The error on the slave is as follows:

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'a3f84a80-b463-11ef-8f89-287b09c2c4dc:6' at master log mysql-bin.000001, end_log_pos 3365. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

select * from performance_schema.replication_applier_status_by_worker limit 1\G
*************************** 1. row ***************************
                                           CHANNEL_NAME:
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 1067
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'a3f84a80-b463-11ef-8f89-287b09c2c4dc:6' at master log mysql-bi
n.000001, end_log_pos 3365; Error 'Invalid default value for 'jcase_col4589'' on query. Default database: 'db100'. Query: 'CREATE TABLE `t9_copy` (
  `jcase_col4589` binary(255) NOT NULL DEFAULT 'a\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\
0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\
0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\
0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `jcase_col587` decimal(65,30) DEFAULT '1.100000000000000000000000000000',
  `jcase_col586` decimal(65,30) DEFAULT NULL,
......

and relylog as follows:

#250722 20:13:01 server id 1396  end_log_pos 3365 CRC32 0xadef58e7      Query   thread_id=19    exec_time=0     error_code=0
SET TIMESTAMP=1753186381/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t9_copy` (
  `jcase_col4589` binary(255) NOT NULL DEFAULT 'a\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\
0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\
0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\
0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `jcase_col587` decimal(65,30) DEFAULT '1.100000000000000000000000000000',
  `jcase_col586` decimal(65,30) DEFAULT NULL,
......

How to repeat:
See the Description for details

[24 Jul 12:45] MySQL Verification Team

Hi,

Is both master and slave running with "sql_mode='STRICT_TRANS_TABLES,NO_BACKSLASH_ESCAPES';"

What is slave running as? What does "select @@sql_mode;" returns both on master and slave.

Thanks