12

On MySql, in order to truncate a table or delete rows when it's normally impossible because of foreign keys (InnoDB only), we use this command:

SET FOREIGN_KEY_CHECKS=0;

On MariaDB, while this command is accepted, it does nothing.

The documentation says I have to run these commands instead:

On a per-table basis:

ALTER TABLE `...` DISABLE KEYS;

or, globally:

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;

So I tried to run this script:

SET FOREIGN_KEY_CHECKS=0;
SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;
ALTER TABLE `country` DISABLE KEYS;
DELETE FROM `country` WHERE 1;

And that leads me to:

Cannot delete or update a parent row: a foreign key constraint fails (database.region, CONSTRAINT FK_F62F176F92F3E70 FOREIGN KEY (country_id) REFERENCES country (id))

Using TRUNCATE has the same effect.

If you have any idea on what I may have missed, thank you by advance. Because the documentation and the existing questions weren't much of help here.

3 Answers 3

17

It should work with:

SET FOREIGN_KEY_CHECKS=0

I tried the following:

create table parent (x int not null primary key) engine = innodb;
create table child (x int not null primary key, constraint aaa foreign key (x) references parent (x) on delete restrict) engine = innodb;
insert into parent (x) values (1),(2);

-- test if f.k is active
insert into child (x) values (1),(3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test"."child", CONSTRAINT "aaa" FOREIGN KEY ("x") REFERENCES "parent" ("x"))

insert into child (x) values (1);
SET FOREIGN_KEY_CHECKS=0;
delete from parent;
select * from parent;
Empty set (0.00 sec)

select * from child;
+---+
| x |
+---+
| 1 |
+---+

select @@version
-> ;
+-----------------+
| @@version       |
+-----------------+
| 10.1.16-MariaDB |
+-----------------+

So the problem is not with MariaDB, but with PhpMyAdmin. It has a checkbox on the pages where you can execute SQL, that overrides SET FOREIGN_KEY_CHECKS=. One must uncheck it if one wants to disable foreign key validation.

6
  • Hi @Lennart, thank you for having taken the time to test and answer. However, as you have probably red, I already tried to set FOREIGN_KEY_CHECKS to 0, without success. I tried your example (but i had to rename "x" because it's not accepted as a field name), and guess what? I got the same error again even after set FOREIGN_KEY_CHECKS=0... My version is "10.1.14-MariaDB-1~jessie". I tried to check for a configuration trick, but with no success...
    – Ninj
    Aug 10, 2016 at 13:11
  • Why would x be disallowed as a name of a column? How are you entering your commands, with what client? Aug 10, 2016 at 13:16
  • 1
    That was through PhpMyAdmin, so it may be the culpit.
    – Ninj
    Aug 10, 2016 at 13:58
  • 3
    Woa... You have led me to the right solution. Thank you! PhpMyAdmin now by default forces the "Enabled the foreign key checks" option. It's a checkbox, available on every form where you can import/write SQL! So that is my answer. Credits go to you: edit your answer or write a new one and i'll validate it ;)
    – Ninj
    Aug 10, 2016 at 15:42
  • 1
    I've updated my answer with the information you gave (nice feedback!), but I have never used PhpMyAdmin, so I have basically no idea what I'm talking about ;-). Feel free to add an answer of your own and accept that, in case mine does not make sense. Aug 10, 2016 at 19:04
1

We had a similar issue while using MariaDB4j with Hibernate and C3P0 connection pooling.

Disabling foreign keys with SET FOREIGN_KEY_CHECKS=0; seems not to work when using C3P0 connection pooling with multiple connections. Disabling foreign keys worked perfectly after limiting the number of connections to 1. We had to set the following properties in the hibernate.properties file:

connection.provider_class=org.hibernate.connection.C3P0ConnectionProvider
hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=1
0

For anyone else stumbling upon this with recent MariaDB versions. This behavior was disabled for the update of character set due to issues with importing and exporting. A

Therefore, even with set FOREIGN_KEY_CHECKS=0; any modify tables that change the character set will fail. As this change is done in a patch version, the behavior could be different between different machines.

For more background information and a possible workaround (delete foreign keys and recreate), check the issue tracker in MariaDB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.