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