[MDEV-33378] Slave aborts with Internal MariaDB error code: 1452 and HA_ERR_NO_REFERENCED_ROW on FK constraint Created: 2024-02-03  Updated: 2024-02-05

Status: Open
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Andrei Elkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates

 Description   

This looks like a major failure in replication. Please review and adjust prio as needed.

# Requires standard m/s setup. Tested with both ROW and MIXED.
 
SET sql_mode='',enforce_storage_engine=MyISAM;
 
CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY(c2) REFERENCES t (c1)) ENGINE=foo;
 
SHOW WARNINGS;
 
SHOW CREATE TABLE t;
 
INSERT INTO t VALUES (1,2);

Leads to:

11.4.0 8bf9f21855606ddeb0fe90fb1e70165adbec6746 (Optimized)

 
2024-02-03 16:42:57 6 [ERROR] Slave SQL: Could not execute Write_rows_v1 event on table test.t; Cannot add or update a child row: a foreign key constraint fails (`test`.`t`, CONSTRAINT `t_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t` (`c1`)), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW; the event's master log binlog.000001, end_log_pos 0, Gtid 0-1-5, Internal MariaDB error code: 1452
 
2024-02-03 16:42:57 6 [Warning] Slave: Cannot add or update a child row: a foreign key constraint fails (`test`.`t`, CONSTRAINT `t_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t` (`c1`)) Error_code: 1452
 
2024-02-03 16:42:57 6 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000001' position 1166; GTID position '0-1-4'
 
2024-02-03 16:42:57 6 [Note] Slave SQL thread exiting, replication stopped in log 'binlog.000001' at position 1166; GTID position '0-1-4', master: 127.0.0.1:10177

11.4.0 8bf9f21855606ddeb0fe90fb1e70165adbec6746 (Debug)

 
2024-02-03 16:37:18 6 [ERROR] Slave SQL: Error 'Cannot add or update a child row: a foreign key constraint fails (`test`.`t`, CONSTRAINT `t_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t` (`c1`))' on query. Default database: 'test'. Query: 'INSERT INTO t VALUES (1,2)', Gtid 0-1-5, Internal MariaDB error code: 1452
 
2024-02-03 16:37:18 6 [Warning] Slave: Cannot add or update a child row: a foreign key constraint fails (`test`.`t`, CONSTRAINT `t_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t` (`c1`)) Error_code: 1452
 
2024-02-03 16:37:18 6 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000001' position 1075; GTID position '0-1-4'



 Comments   
Comment by Roel Van de Paar [ 2024-02-03 ]

The master sees no FK issue:

11.4.0 8bf9f21855606ddeb0fe90fb1e70165adbec6746 (Debug)

 
11.4.0-dbg>SET sql_mode='',enforce_storage_engine=MyISAM;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
11.4.0-dbg>CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY(c2) REFERENCES t (c1)) ENGINE=foo;
 
Query OK, 0 rows affected, 3 warnings (0.007 sec)
 
 
 
11.4.0-dbg>SHOW WARNINGS;
 
+---------+------+-------------------------------------------+
 
| Level   | Code | Message                                   |
 
+---------+------+-------------------------------------------+
 
| Warning | 1286 | Unknown storage engine 'foo'              |
 
| Warning | 1266 | Using storage engine InnoDB for table 't' |
 
| Note    | 1266 | Using storage engine MyISAM for table 't' |
 
+---------+------+-------------------------------------------+
 
3 rows in set (0.000 sec)
 
 
 
11.4.0-dbg>SHOW CREATE TABLE t;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                      |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t     | CREATE TABLE `t` (
 
  `c1` int(11) NOT NULL,
 
  `c2` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`c1`),
 
  KEY `c2` (`c2`)
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
11.4.0-dbg>INSERT INTO t VALUES (1,2);
 
Query OK, 1 row affected (0.001 sec)

Though, importantly, notice the missing FK from the table definition.

Comment by Roel Van de Paar [ 2024-02-03 ]

MTR Does not readily reproduce the issue. Please use CLI.
Latest attempt (does not work yet):

--source include/have_binlog_format_row.inc
 
--source include/master-slave.inc
 
 
 
SET sql_mode='',enforce_storage_engine=MyISAM;
 
CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY(c2) REFERENCES t (c1)) ENGINE=foo;
 
SHOW WARNINGS;
 
SHOW CREATE TABLE t;
 
INSERT INTO t VALUES (1,2);
 
 
 
--sync_slave_with_master
 
--source include/rpl_end.inc

Comment by Kristian Nielsen [ 2024-02-03 ]

I'm guessing this is because the table is created as the default storage engine on the slave (which is probably InnoDB on the CLI but MyISAM in mtr).

Foreign key constraints are ignored in MyISAM, but enforced in InnoDB (and the INSERT violates the constraint).

It's supported to have different storage engine on the slave from the master (and this can then cause errors like this).
However, maybe the sql_mode='',enforce_storage_engine=MyISAM is replicated incorrectly? I'm not familiar with that mode, or how it's documented to work in replication. It looks like it has some effect, as the CREATE TABLE with unknown engine doesn't fail outright on the slave.

Comment by Roel Van de Paar [ 2024-02-05 ]

Thank you Kristian for the input. I checked further:

> Maybe the sql_mode='',enforce_storage_engine=MyISAM is replicated incorrectly?
It seems neither of these is replicated to the slave. This seems very concerning as both options can significantly affect if and how tables are created.

Generated at Thu Feb 08 10:38:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.