I am embarrassed to say I spent 3 hours today trying to figure out why my foreign keys were not working properly in mySQL. I had several child tables that all had records related to a parent table and was utilizing foreign keys with on delete cascade set up to automatically delete records in the child tables. I went through the common symptoms:
- Using InnoDB engine on the tables in question (mySQL defaults to myISAM if you don’t specify and myISAM does not support foreign keys).
- Charsets and collation are identical on all parent and child tables.
- Datatypes are the same for the columns.
- Index set on columns in child tables and in parent table if not primary keys.
- No inline references on the column specifications
I tried recreating the tables, removing and recreating the indexes, removing and recreating the foreign keys, and always checking the error logs. Nothing worked. I searched Google up and down (at least I thought I did) and couldn’t find anything either. Until…
Low and behold in a small comment on a StackExchange article there was a reference to a mySQL bug in a couple versions of mySQL 5.5 (I had version 5.5.41 installed on my system) that caused issues with foreign keys. There was no other reference (other than the follow-up comment by another user of “no way they let a bug like that happen before releasing that version” or something similar) to the bug or any workarounds. As I had been fighting with this for close to 3 hours I figured what the heck, let’s upgrade and see what happens.
Here are the steps I took to upgrade to mySQL 5.6.22 on my Ubuntu 12.04 LTS system:
sudo apt-get -y remove mysql-server
sudo apt-get -y autoremove
sudo apt-get -y install software-properties-common
sudo add-apt-repository -y ppa:ondrej/mysql-5.6
sudo apt-get update
sudo apt-get -y install mysql-server
Before upgrading I made sure to backup my database, but if everything goes right you shouldn’t have to worry about that. But as my day was going I figured better safe than sorry.
Luckily there were no issues upgrading and when everything came back online low and behold my foreign keys started functioning properly. I am now happy to report that when I delete a parent record the child records are deleted and no orphans are left behind.
Latest posts by Jeff Mould (see all)
- Laravel 5.4 Redirect after Password Reset Email Sent - September 8, 2017
- How to Fix Laravel 5.2 Token Mismatch Errors - June 30, 2016
- How to Get Sirius XM Deals - May 27, 2016