Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4(EOL), 10.5, 10.6
-
None
Description
When using the KB example for creating a table pair with a foreign key constraint:
CREATE TABLE author (
|
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
name VARCHAR(100) NOT NULL
|
) ENGINE = InnoDB;
|
|
|
CREATE TABLE book (
|
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
title VARCHAR(200) NOT NULL,
|
author_id SMALLINT UNSIGNED NOT NULL,
|
CONSTRAINT `fk_book_author`
|
FOREIGN KEY (author_id) REFERENCES author (id)
|
ON DELETE CASCADE
|
ON UPDATE RESTRICT
|
) ENGINE = InnoDB;
|
|
|
INSERT INTO author (name) VALUES ('Abdul Alhazred');
|
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());
|
Then truncating the "book" table:
TRUNCATE TABLE book;
|
The table is empty as expected on the node where the TRUNCATE was executed, but not on the other nodes in the cluster.
Nothing is logged in the error log, it seems as if the TRUNCATE is just silently ignored.
The earliest version I can reproduce this on is MariaDB 10.5.9, so it seems to be a rather recent regression, on earlier versions it works as expected.
I filed this as "critical" as it can easily lead to data inconsistencies across the cluster which may only actually be detected, and lead to problems, much later.
Attachments
Issue Links
- relates to
-
MDEV-26085 TRUNCATE on table does not take foreign keys in account
-
- Closed
-
Activity
To me it seems that there are two issues discussed in this Ticket:
The initial problem is, that a `TRUNCATE TABLE` is not executed on the replica node, if there is ANY FK constraint, even if it is `ON DELETE NO ACTION ON UPDATE NO ACTION`.
That the referenced table might also not be handled correctly in case of `ON DELETE CASCADE`, or a similar action, seems to be a second issue.
What we know about problem that TRUNCATE TABLE is not replicated to second node:
- Problem does not repeat with 10.5.8 and does repeat with 10.5.9
- Problem does not repeat with 10.4.17 and does repeat 10.4.18
- Problem does not repeat with mtr. mtr seems to change environment and problem is not repeatable after you use it.
jplindst, if there is a Galera replication problem of TRUNCATE TABLE, then please continue handling that in this ticket, and assign this ticket to yourself.
And file a separate ticket for the bug that we wrongly allow TRUNCATE on the referencing table when ON DELETE CASCADE or ON DELETE SET NULL actions exist. (Users could expect TRUNCATE to be like DELETE, but we are not executing those actions.) Because that bug has existed for many years, we need a decision from serg in which versions to fix it, or whether to fix it at all, or only based on some SQL_MODE. Maybe an acceptable work-around for users who expect such sloppy TRUNCATE would be to SET foreign_key_checks=0.
also with wsrep_slave_FK_checks=OFF, TRUNCATE of tables with FKs on galera replica nodes does work. So, I guess, since it is not affected by wsrep_slave_FK_checks, it is not a galera problem.
I've built both MariaDB 10.4 and Galera 4.x from current GitHub sources and in my test the table is truncated on other nodes:
...MariaDB [test]> truncate table book;Query OK, 0 rows affected (1.428 sec)MariaDB [test]> select * from book;Empty set (0.001 sec)MariaDB [test]> select * from author;+----+----------------+| id | name |+----+----------------+| 1 | Abdul Alhazred |+----+----------------+1 row in set (0.001 sec)MariaDB [(none)]> show variables like 'wsrep_node_name';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| wsrep_node_name | node1 |+-----------------+-------+1 row in set (0.002 sec)MariaDB [test]> exitByeopenxs@ao756:~/dbs/maria10.4$ bin/mysql --socket=/tmp/mysql-node2.sock testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 13Server version: 10.4.21-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [test]> show variables like 'wsrep_node_name';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| wsrep_node_name | node2 |+-----------------+-------+1 row in set (0.002 sec)MariaDB [test]> select * from book;Empty set (0.002 sec)MariaDB [test]> select * from author;+----+----------------+| id | name |+----+----------------+| 1 | Abdul Alhazred |+----+----------------+1 row in set (0.001 sec)MariaDB [test]> show variables like '%version%';+-----------------------------------+------------------------------------------+| Variable_name | Value |+-----------------------------------+------------------------------------------+| in_predicate_conversion_threshold | 1000 || innodb_version | 10.4.21 || protocol_version | 10 || slave_type_conversions | || system_versioning_alter_history | ERROR || system_versioning_asof | DEFAULT || tls_version | TLSv1.1,TLSv1.2,TLSv1.3 || version | 10.4.21-MariaDB || version_comment | MariaDB Server || version_compile_machine | x86_64 || version_compile_os | Linux || version_malloc_library | system || version_source_revision | c7443a0911a98dccfc9c5bda4c2f4d9052516d8f || version_ssl_library | OpenSSL 1.1.1f 31 Mar 2020 || wsrep_patch_version | wsrep_26.22 |+-----------------------------------+------------------------------------------+15 rows in set (0.002 sec)These are exact commits used:
openxs@ao756:~/dbs/maria10.4$ cd ~/git/galera/openxs@ao756:~/git/galera$ git branch3.x* 4.xopenxs@ao756:~/git/galera$ git log -1commit d54ebf885e302380d1724761220f970a32327fe6 (HEAD -> 4.x, tag: release_26.4.8, origin/4.x)Author: Teemu Ollakka <teemu.ollakka@galeracluster.com>Date: Fri Mar 26 16:02:14 2021 +0200Bump version number for 26.4.8openxs@ao756:~/git/galera$ cd ../server/openxs@ao756:~/git/server$ git log -1commit c7443a0911a98dccfc9c5bda4c2f4d9052516d8f (HEAD -> 10.4, origin/10.4)Author: Sergei Petrunia <psergey@askmonty.org>Date: Thu Jul 1 01:08:28 2021 +0300MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SPPost-merge fix in 10.4: add a testcase for pushdown into IN subqueryGalera was built with cmake ., while MySQL 10.4:
cmake . -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/maria10.4 -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DPLUGIN_TOKUDB=NO -DWITH_SSL=system