[#MDEV-26053] TRUNCATE on table with Foreign Key Constraint no longer replicated to other nodes

[MDEV-26053] TRUNCATE on table with Foreign Key Constraint no longer replicated to other nodes Created: 2021-06-30 Updated: 2021-12-01 Resolved: 2021-09-17
Status:	Closed
Project:	MariaDB Server
Component/s:	Galera, Storage Engine - InnoDB
Affects Version/s:	10.4, 10.5, 10.6
Fix Version/s:	10.4.22, 10.5.13, 10.6.5

Type:

Bug

Priority:

Critical

Reporter:

Hartmut Holzgraefe

Assignee:

Jan Lindström (Inactive)

Resolution:

Fixed

Votes:

Labels:

None

Issue Links:

Relates
relates to	~~MDEV-26085~~	TRUNCATE on table does not take forei...	Closed

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.

Comments

Comment by Hartmut Holzgraefe [ 2021-06-30 ]

Everything was done with wsrep_osu_method=TOI, just to clarify.

Comment by Hartmut Holzgraefe [ 2021-06-30 ]

Both latest 10.4 and 10.5 releases area actually affected. Latest 10.3.30 is still good though

Comment by Jan Lindström (Inactive) [ 2021-07-01 ]

It seems to be broken from 10.4 i.e. when Galera 4 was introduced. Very strange that it has not been noticed before.

Comment by Jan Lindström (Inactive) [ 2021-07-01 ]

I can repeat issue with InnoDB only and using 10.3.30 :

an@jan-HP-ZBook-15u-G5:~/mysql/10.3/mysql-test$ ./mtr innodb.fk_truncate

Logging: ./mtr  innodb.fk_truncate

vardir: /home/jan/mysql/10.3/mysql-test/var

Checking leftover processes...

Removing old var directory...

Creating var directory '/home/jan/mysql/10.3/mysql-test/var'...

Checking supported features...

MariaDB Version 10.3.30-MariaDB-debug

 - SSL connections supported

 - binaries are debug compiled

 - binaries built with wsrep patch

Collecting tests...

Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

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());

SELECT * FROM author;

id	name

1	Abdul Alhazred

SELECT * FROM book;

id	title	author_id

1	Necronomicon	1

TRUNCATE TABLE book;

SELECT * FROM author;

id	name

1	Abdul Alhazred

SELECT * FROM book;

id	title	author_id

I think now question is TRUNCATE counted as ON DELETE ?

Comment by Jan Lindström (Inactive) [ 2021-07-01 ]

marko I do not remember the rules on TRUNCATE when we have ON DELETE CASCADE constraint?

Comment by Jan Lindström (Inactive) [ 2021-07-01 ]

From Galera point of view both nodes have exactly the same rows so I can't repeat any inconsistency:

jan@jan-HP-ZBook-15u-G5:~/mysql/10.5/mysql-test$ ./mtr galera.galera_fk_truncate --mysqld=--log-bin --mysqld=--log-slave-updates

Logging: ./mtr  galera.galera_fk_truncate --mysqld=--log-bin --mysqld=--log-slave-updates

vardir: /home/jan/mysql/10.5/mysql-test/var

Checking leftover processes...

 - found old pid 33959 in 'mysqld.2.pid', killing it...

   process did not exist!

Removing old var directory...

Creating var directory '/home/jan/mysql/10.5/mysql-test/var'...

Checking supported features...

MariaDB Version 10.5.12-MariaDB-debug

 - SSL connections supported

 - binaries are debug compiled

 - binaries built with wsrep patch

Collecting tests...

Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

connection node_2;

connection node_1;

connection node_1;

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());

TRUNCATE TABLE book;

SELECT * FROM author;

id	name

1	Abdul Alhazred

SELECT * FROM book;

id	title	author_id

connection node_2;

SELECT * FROM author;

id	name

1	Abdul Alhazred

SELECT * FROM book;

id	title	author_id

DROP TABLE book, author;

Comment by Marko Mäkelä [ 2021-07-01 ]

An attempt to TRUNCATE the referenced table does fail as expected:

mysqltest: At line 21: query 'TRUNCATE TABLE author' failed: ER_TRUNCATE_ILLEGAL_FK (1701): Cannot truncate a table referenced in a foreign key constraint (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) REFERENCES `test`.`author` (`id`))

Admittedly, it feels confusing to allow TRUNCATE on the referencing table when ON DELETE CASCADE or ON DELETE SET NULL actions exist. But as long as I remember, it has been like that. Already the first implementation of TRUNCATE in InnoDB is only checking that the table is not being referenced by FOREIGN KEY constraints in other tables. It does not care to check the ON DELETE CASCADE or ON DELETE SET NULL.

serg, do you think that we should fix this in the earliest applicable supported version (10.2)? Should we fix it at all in GA versions?

Comment by Hartmut Holzgraefe [ 2021-07-01 ]

> do you think that we should fix this in the earliest applicable supported version (10.2)?

Note that I could only reproduce this in 10.4 and up, and only in latest three 10.4 and 10.5 releases? (Also now finally tried 10.6.2 -> also affected)

> Admittedly, it feels confusing to allow TRUNCATE on the referencing table when ON DELETE CASCADE or ON DELETE SET NULL actions exist.

Why should TRUNCATE be different to DELETE without WHERE on the referencing table?

Comment by Marko Mäkelä [ 2021-07-01 ]

I can repeat this on earlier versions too:

10.2 58252fff15acfe7c7b0452a87e202e3f8e454e19
INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());
TRUNCATE TABLE book;
SELECT * FROM book;
id title author_id
SELECT * FROM author;
id name
1 Abdul Alhazred

Comment by Hartmut Holzgraefe [ 2021-07-01 ]

I can only repeat it with 10.4.18 to .20 and 10.5.9 to .11 (and 10.6.2, did not check the earlier 10.6.x versions yet).

Earlier 10.4 and 10.5 versions work fine for me, and I could not reproduce it with 10.3 at all.

I used the same configuration for all of these tests, just changed the repository URLs to different MariaDB versions before firing up VMs each time.

Using default configuration each time, with just this added:

[mysqld]

server-id=1

bind-address=0.0.0.0

binlog-format=ROW

wsrep_on=ON

wsrep_provider=/usr/lib/galera/libgalera_smm.so

wsrep_cluster_name=test_cluster

wsrep_cluster_address=gcomm://galera-node-1,galera-node-2,

wsrep_sst_method=mariabackup

wsrep_sst_auth=sstuser:Secret23!

wsrep_node_address=galera-node-1

wsrep_node_name=node-1

wsrep_OSU_method='TOI'

(server_id, wsrep_node_address and wsrep_node_name obviously being different on different nodes)

Comment by Valerii Kravchuk [ 2021-07-01 ]

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]> exit

Bye

openxs@ao756:~/dbs/maria10.4$ bin/mysql --socket=/tmp/mysql-node2.sock test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 13

Server version: 10.4.21-MariaDB MariaDB Server

Copyright (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 branch

3.x

* 4.x

openxs@ao756:~/git/galera$ git log -1

commit 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 +0200

    Bump version number for 26.4.8

openxs@ao756:~/git/galera$ cd ../server/

openxs@ao756:~/git/server$ git log -1

commit c7443a0911a98dccfc9c5bda4c2f4d9052516d8f (HEAD -> 10.4, origin/10.4)

Author: Sergei Petrunia <psergey@askmonty.org>

Date:   Thu Jul 1 01:08:28 2021 +0300

    MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP

    Post-merge fix in 10.4: add a testcase for pushdown into IN subquery

Galera 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

Comment by Christian [ 2021-07-02 ]

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.

Comment by Jan Lindström (Inactive) [ 2021-07-02 ]

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.

Comment by Marko Mäkelä [ 2021-07-02 ]

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.

Comment by Christian [ 2021-07-05 ]

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.

Generated at Thu Feb 08 09:42:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-26053] TRUNCATE on table with Foreign Key Constraint no longer replicated to other nodes Created: 2021-06-30 Updated: 2021-12-01 Resolved: 2021-09-17