Disable foreign key checks#

All Aiven for MySQL® services have foreign key checks enabled by default helping in keeping referential integrity across tables. However, you might want to disable it for a particular session. For example, when migrating to an Aiven for MySQL you may face errors related to foreign key violations similar to:

ERROR 3780 (HY000) at line 11596: Referencing column 'g_id' and referenced column 'g_id' in foreign key constraint 'FK_33b11dcfac6148578da087b07c2f388f' are incompatible.

The following explains how to temporarily disable Aiven for MySQL foreign key checking for the duration of a session.

Prerequisites#

The mysqlsh client installed. You can install this by following the MySQL shell installation documentation.
An Aiven account with an Aiven for MySQL service running.

Variables#

There are a few variables you need to substitute when running the commands. To find the values for the substitution, go to Aiven Console > your Aiven for MySQL service > Overview > the Connection information section > the MySQL tab.

Variable	Description
`HOST`	Hostname for MySQL connection
`PORT`	Port for MySQL connection
`PASSWORD`	Password of your Aiven for MySQL connection
`DB_NAME`	Database Name of your Aiven for MySQL connection

Check the foreign key check flag#

To check the foreign key check flag you need to take the following steps:

Connect to your Aiven for MySQL service with the following command:

mysql --user avnadmin --password=PASSWORD --host HOST --port PORT DB_NAME

Run the following command to check the default configuration for your foreign key checks.
```
SHOW VARIABLES LIKE 'foreign_key_checks';
```

Verify that the foreign keys are enabled by default. You can expect to receive the following output:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.05 sec)

Disable foreign key checks#

To disable the foreign key checks for the session, you give an additional parameter when you connect to your Aiven for MySQL using the mysqlsh:

mysql                   \
  --user avnadmin       \
  --password=PASSWORD   \
  --host HOST           \
  --port PORT DB_NAME   \
  --init-command="SET @@SESSION.foreign_key_checks = 0;"

Once again, we can check the current status of the foreign key checks by running the following:

SHOW VARIABLES LIKE 'foreign_key_checks';

As result, we can see that the foreign key checks are disabled for this session:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.04 sec)

The same flag works when running a set of commands saved in a file with extension .sql.

Variable	Description
`FILENAME`	File which the extension is `.sql`, for e.g. filename.sql

You can paste the following command on your FILENAME:

SHOW VARIABLES LIKE 'foreign_key_checks';

Now you can set the init-command flag to disable the foreign key checks, and run the commands in this file.

mysql                   \
  --user avnadmin       \
  --password=PASSWORD   \
  --host HOST           \
  --port PORT DB_NAME   \
  --init-command="SET @@SESSION.foreign_key_checks = 0;" < FILENAME

More resources#

Read the official documentation to understand possible implications that can happen when disabling foreign key checks in your service.