Getting an error: 'foreign_key_checks' can't be set to the value of '1'

I'm running the latest nightly build [9.3.0.5055] and trying to do a direct table export to a remote server.

The source and targets are running MySQL 5.5.24 which seems to want 'foreign_key_checks' to be either 'ON' or 'OFF', not '1' or '0'.

None of the tables actually have foreign keys, and I've successfully transferred several other (large) tables, but there are two that crash with this error every time I try.

I can't find an option to change the 'foreign_key_checks' values for live transfers, so I'm hoping that a fix / switchable setting can be included in the next nightly build.

Same thing but totally outdated: http://www.heidisql.com/forum.php?t=6345

Can you post the value of your server variable sql_mode?

SELECT @@SQL_MODE;

According to http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_foreign_key_checks there is no ON or OFF value, just 1 and 0.

SELECT @@SQL_MODE; returns nothing SELECT @SQL_MODE; returns null SELECT SQL_MODE; returns an error

The v5.7 (and also v5.5) reference docs do show that 0 and 1 are valid entries, which is why I'm confused over the error.

However, if I look on the Host => Variables tab in Heidi then I see 'foreign_key_checks' clearly reported as 'ON' ...both in the global and session spaces, and if I try to edit the values then Heidi reports them as Boolean, so maybe that's the issue?

Forgot to mention that I'm using InnoDB tables

The variables tab and its editor is a totally different place, and has nothing to do with the error message you get when you change the foreign_key_checks variable per query.

Which version is working for you - please test:

SET FOREIGN_KEY_CHECKS=1;
SET FOREIGN_KEY_CHECKS='1';
SET FOREIGN_KEY_CHECKS='ON';

Okay... valid settings are:

0 1 TRUE FALSE ON OFF and 'ON' 'OFF'

Invalid settings are:

'0' '1' 'TRUE' 'FALSE'

It seems (from the error message) that Heidi is trying to use '0' and '1', which would explain things.

(by 'valid', I mean that no error is generated)

HeidiSQL's export tool does the following:

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
...
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;

Please test these both queries again. They don't have quotes around the 1 and 0, so that's still mysterious.

That's the stupid part - those queries both work fine, and this issue only affects 2 tables out of about 40 that I was transferring

this issue only affects 2 tables out of about 40 that I was transferring

The SET FOREIGN_KEY_CHECKS=... query is only fired once at the very start of the export and once in the very end. It is not executed once per table. So I am wondering now that you say it crashes only for 2 tables out of 40. Or do you export these tables one by one?

I originally exported all of the tables in one go, but these two tables failed.

I then tried exporting them individually and/or together (multiple times) and they each fail with the same error every time.

I wondered if the tables might be corrupted in some way so I ran all of the repair options against them and, while nothing was reported, they still fail during the transfer with that same error.

Probably these tables are very huge, so that some packet was too large and crashed the MySQL connection. That could explain the above error message. You can try to increase the max_allowed_packet variable in that case, e.g. to 100M.

Actually, not - one of the tables which continues to fail is just 200MB, yet other tables (which copy without an issue) are up to 8GB each

The mystery is solved ... I updated to the latest nightly build and the error message changed to a complaint about max_allowed_packet. I upped it as suggested and the remaining tabled exported correctly :-)

Good to hear that :)