Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.6, 10.9, 10.10, 10.11, 11.0, 11.1
-
None
Description
Set to Minor because tampering with foreign keys via FOREIGN_KEY_CHECKS=OFF rarely ends well anyway. The scenario below is particularly unfortunate for replication, because on the master the first ALTER, which fails, apparently somehow disables the broken foreign key, so the second ALTER succeeds without complaints; but since the first ALTER fails, it is not written to the binary log, so the slave only sees the second ALTER, and thus the second ALTER fails on the slave. I don't even know whether it is InnoDB, or replication, or server here to blame.
--source include/have_partition.inc
|
--source include/have_innodb.inc
|
--source include/master-slave.inc
|
|
|
SET FOREIGN_KEY_CHECKS = OFF; |
CREATE TABLE t1 (b TEXT, c INT, UNIQUE(b), FOREIGN KEY (c) REFERENCES t2 (a)) ENGINE=InnoDB; |
CREATE TABLE t2 (a INT) ENGINE=InnoDB; |
SET FOREIGN_KEY_CHECKS = ON; |
--error ER_ERROR_ON_RENAME
|
ALTER TABLE t1 FORCE; |
ALTER TABLE t2 PARTITION BY KEY(a); |
|
|
--sync_slave_with_master
|
|
|
# Cleanup
|
--connection master
|
DROP TABLE t1, t2; |
--source include/rpl_end.inc |
|
10.6 8171f9da |
2023-07-14 0:35:45 8 [ERROR] Slave SQL: Error 'Cannot delete or update a parent row: a foreign key constraint fails' on query. Default database: 'test'. Query: 'ALTER TABLE t2 PARTITION BY KEY(a)', Gtid 0-1-3, Internal MariaDB error code: 1217 |
2023-07-14 0:35:45 8 [Warning] Slave: Cannot delete or update a parent row: a foreign key constraint fails Error_code: 1217 |
2023-07-14 0:35:45 8 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 693 |
Reproducible on 10.6+.
The test case behaves differently on 10.4-10.5, the second CREATE TABLE there just fails. Maybe it can be modified to fail the same way on 10.4-10.5, I didn't try.