Bug #114472 MySQL replica encounter error when master and replica innodb_strict_mode=OFF
Submitted: 25 Mar 7:17 Modified: 25 Mar 7:20
Reporter: bin wang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:V8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[25 Mar 7:17] bin wang
Description:
when master and replica  set global innodb_strict_mode=OFF;
the master execute the SQL, but the slave thread in replica run error, because
the innodb_strict_mode of slave thread doesn't change alse ON.

How to repeat:
step1:
master:
create database test;
create table test.test(a int);
insert into test.test values(1);
set global innodb_strict_mode=OFF;

replica:
set global innodb_strict_mode=OFF;
step2:
master:
create table test.test1(
	col_1 varchar(10),
	col_2 varchar(10),
	col_3 varchar(10),
	col_4 varchar(10),
	col_5 varchar(10),
	col_6 varchar(10),
............................
.............................
	col_195 varchar(10),
	col_196 varchar(10),
	col_197 varchar(10)
	) CHARSET=utf8mb4 row_format=dynamic;

replica:
show slave status
..............................
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1118
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'a5cd6b45-ea6f-11ee-b957-024203971dbe:9' at source log mysql-bin.000003, end_log_pos 6446. 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: 2
                  Master_UUID: a5cd6b45-ea6f-11ee-b957-024203971dbe
-----------------------
it is because 
create table test.test1(
	col_1 varchar(10),
	col_2 varchar(10),
	col_3 varchar(10),
	col_4 varchar(10),
	col_5 varchar(10),
	col_6 varchar(10),
............................
.............................
	col_195 varchar(10),
	col_196 varchar(10),
	col_197 varchar(10)
	) CHARSET=utf8mb4 row_format=dynamic;
doesn't execute success by the  slave thread.

the errlog is

[ERROR] [MY-011825] [InnoDB] Cannot add field `col_197` in table `test`.`test1` because after adding it, the row size is 8126 which is greater than maximum allowed size (8126) for a record on index leaf page.
[ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction 'a5cd6b45-ea6f-11ee-b957-024203971dbe:9' at source log mysql-bin.000003, end_log_pos 6446; Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.' on query. Default database: ''. Query: 'create table test.test1(        col_1 varchar(10),      col_2 varchar(10),      col_3 varchar(10),      col_4 varchar(10),      col_5 varchar(10),      col_6 varchar(10),      col_7 varchar(10),      col_8 varchar(10),      col_9 varchar(10),      col_10 varchar(10),     col_11 varchar(10),     col_12 varchar(10),     col_13 varchar(10),     col_14 varchar(10),     col_15 varchar(10),     col_16 varchar(10),     col_17 varchar(10),     col_18 varchar(10),     col_19 varchar(10),     col_20 varchar(10),     col_21 varchar(10),     col_22 varchar(10),     col_23 varchar(10),     col_24 varchar(10),     col_25 varchar(10),     col_26 varchar(10),     col_27 varchar(10),     col_28 varchar(10),     col_29 varchar(10),     col_30 varchar(10),     col_31 varchar(10),     col_32 varchar(10),     col_33 varchar(1, Error_code: MY-001118

Suggested fix:

skip the strict check in slave thread.

in order to keep the data of master and replica should be consistent.
[25 Mar 7:18] bin wang
suggest patch

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: ha_innodb.patch (application/octet-stream, text), 530 bytes.

[25 Mar 7:20] MySQL Verification Team
Hello bin wang,

Thank you for the report and contribution.

regards,
Umesh