4

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.

5

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.

  • 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 '16 at 13:11
  • Why would x be disallowed as a name of a column? How are you entering your commands, with what client? – Lennart Aug 10 '16 at 13:16
  • 1
    That was through PhpMyAdmin, so it may be the culpit. – Ninj Aug 10 '16 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 '16 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. – Lennart Aug 10 '16 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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