CODEWITHSUNDEEP
mysqlphpmyadmin
11teenth
11teenth

Reputation: 2103

Cannot set foreign_key_checks to 0 / off

I have a small db that I just added some tables to and set up foreign key constraints...

Now I want to upload some data and have read about using the following to temporarily turn off the check...

SET FOREIGN_KEY_CHECKS = 0;

I am running this query from the SQL window...I get a 'success' message but when I check the setting (via SHOW Variables WHERE Variable_name='foreign_key_checks';) the setting shows as ON and I cannot upload without running into the foreign_key-check error/complaint.

The message after I run the query:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0001 seconds.)

I tried to use the global version and got a message saying my user does not have the necessary privileges...

I don't even know what user I'm access the DB through - I'm using phpMyAdmin after logging into my cloud hosted dedicated server...not through DB user.

I do see the following at the bottom of my phpMyAdmin home page:

Your PHP MySQL library version 5.1.73 differs from your MySQL server version 5.6.36. This may cause unpredictable behavior.

Any help appreciated.

Upvotes: 2

Views: 8445

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

Changing a session variable like foreign_key_checks last only for the duration of the session. Once you open a new connection, the option defaults to the global value.

PhpMyAdmin, like every PHP application, opens a new connection for every request. So as soon as you change foreign_key_checks, it closes the session for which you changed that option, and your change is over.

If you want to change this option, you'll have to execute the change as part of the import script, so it gets handled at the beginning of the connection that processes the import.

Another possibility is to change it with SET GLOBAL ..., but this will affect all sessions, not just the one you use for your import. Also, you need the SUPER privilege to change global options, and you seem not to have that privilege.

Edit: Another option is to uncheck "Enable foreign key checks" at the Import stage... Screenshot of Import in phpMyAdmin

Upvotes: 2

Related Questions