MySQL: "Cannot add foreign key constraint."

[jasonb]

I recently upgraded Roundcube Mail to the latest version, but had been getting the following error when it tried to upgrade the database schema:

Code: Select all

ERROR 1215 (HY000) at line 2: Cannot add foreign key constraint

In resolving this, I came across the useful article "Dealing With MySQL Error Code 1215: 'Cannot Add Foreign Key Constraint'." It was at step 8 that I came across the problem.

My parent database was users. I had previous issued the following command to look at its schema:

Code: Select all

show create table users

This led me to determine that it was using MyISAM as its database engine. All I had to do was change it to use InnoDB instead:

Code: Select all

alter table users InnoDB

The main issue is that only InnoDB can use foreign keys—MyISAM cannot.

---

Incidentally, here is a query you can use that will tell you all of the tables (from all non-system databases) that are not using InnoDB, and which will format that output into a series of commands that can be used to change those tables:

Code: Select all

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql') AND engine = 'MyISAM' AND table_type = 'BASE TABLE' ORDER BY table_schema,table_name;

I put this into a text file (query), called it from a Linux command line and redirected the output, and then ran the resulting series of commands to easily update all of my databases to use the InnoDB engine:

Code: Select all

mysql -u root -p -e "source /path/to/query" > /path/to/update
mysql -u root -p -e "/path/to/update"

The only caveat is that you need to edit update in order to remove the first line.