MySQL replica encounter error when master and replica innodb_strict_mode=OFF
Description
Environment
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
Activity
Hi @wangbincmss
Our Engineering team will review your PR. If everything is good, Then they may merge the PR. Please wait until the review is done.
@Aaditya Dubey hi I have already commit is to the MySQL commuity for review, It seems will take a long time to wait for the the MySQL commuity, Can you review the pr and merge to the branch quickly ?
Hi @wangbincmss
Thank you for the Pull request.
Hi @wangbincmss
Thank you for the report.
Verified as described:
Started Percona Server 8.0.36 as Source/Replica
On Source:
master [localhost:22640] {msandbox} ((none)) > use test
Database changed
master [localhost:22640] {msandbox} (test) > create table test.test(a int);
Query OK, 0 rows affected (0.03 sec)
master [localhost:22640] {msandbox} (test) > insert into test.test values(1);
Query OK, 1 row affected (0.06 sec)
master [localhost:22640] {msandbox} (test) > set global innodb_strict_mode=OFF;
Query OK, 0 rows affected (0.00 sec)
master [localhost:22640] {msandbox} (test) > 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)
-> ) CHARSET=utf8mb4 row_format=dynamic;
Query OK, 0 rows affected (0.02 sec)
On Replica:
slave1 [localhost:22641] {msandbox} ((none)) > set global innodb_strict_mode=OFF;
Query OK, 0 rows affected (0.00 sec)
On Source, Added Additional columns:
:~$ for i in {7..200}; do ./n1 -e "alter table test.test1 add column col_$i varchar(10)"; done
On Replica:
slave1 [localhost:22641] {msandbox} ((none)) > show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 127.0.0.1
Source_User: rsandbox
Source_Port: 22640
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 50565
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 49711
Relay_Source_Log_File: mysql-bin.000001
Replica_IO_Running: Yes
Replica_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1118
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 49923. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
...
ErrorLog:
2024-03-29T08:15:51.223635Z 12 [ERROR] [MY-011825] [InnoDB] Cannot add field `col_197` in table `test`.`#sql-ib1067-98341462` because after adding it, the row size is 8126 which is greater than maximum allowed size (8126) for a record on index leaf page.
2024-03-29T08:15:51.230545Z 12 [ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000001, end_log_pos 49923; Error 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs' on query. Default database: ''. Query: 'alter table test.test1 add column col_197 varchar(10)', Error_code: MY-001118
2024-03-29T08:15:51.231999Z 11 [Warning] [MY-010584] [Repl] Replica SQL for channel '': ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: MY-001756sending the report to engineering for further review and updates.
Details
Details
Assignee
Reporter
Upstream Bug URL
Needs QA
Affects versions
Priority
Medium
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.