On Feb 24th I was in the middle of releasing a feature to production pipeline. Since it was developed under a few months, I need to make sure that the database migration pack should work as I expected. You know, since the diff is huge and I’m senior enough to be responsible for the code base, I need to make sure that I don’t destroy our client database and possibly triggering significant downtime to production.

I have two options here, merge and test the migration; or rebase and test migration. At first, I’m quite confident by rebasing it since… you know, the main might have changed something I didn’t know and since the module is not that coupled with other module, this “rebase” will do little to nothing to the feature I’ve been working on. — spoiler alert, I’m right. Rebase have nothing to do with the problem.

So what I did was: checkout to main branch; git pull on it; then checkout to my branch then do git rebase main. This causes a huge number appearing on the status bar of my Visual Studio Code.

Of course I don’t push it, doing this means I don’t have backup of “current working copy” of the feature I’ve been working on for months!

Then, I ran the migration… as expected it works.

Cannot Add Foreign Key Constraint

Just… to make sure I wont introduce problem when I ran the migration on the production…. I dump the production database, run the “strip those identifiable customer data” script then load it to my development environment, then ran the migration again.

Cannot add foreign key constraint.

One of codebase’s migration script, Feb 24th, 2021.

WOT. Nani the f— is happening here. The migration script with error message “Cannot add foreign key constraint”. It stopped when trying to add foreign key from existing table to the new table. Huh?

I checked the data type, it match. Checked the table name, it match. Checked the migration script, I see nothing wrong there, and yet this script still refuses to work.

Hmmm, maybe rebase wont work, let’s try merge method instead.

My genius instinct kicks in

said me to my self. So I reset the branch by checking out the remote branch and reset the git pointer of my local branch to the one that remote uses. I deleted the database on my local environment and spin up new container for that, thinking that I did some mistake here. You know, being a hipster will invite a whole new dimension of f-ckitery.

After the container are ready to use, I imported the cleaned production database and run the migration again. It…. didn’t work either.

I’m confused, checking again the table name, column type, migration between main branch and this feature branch, and I SEE NOTHING WRONG. Then I proceed to check with the database version on 3 place! THREE! My local, staging server, and production server.

Turns out the staging server is the only one that have different version. My local and production have the same version. Well it’s because I use Docker and I took the liberty of “Meh, let’s just use the production’s spec to test it out.” By that, I got nothing. Really, nothing.

Fixing the Problem

After further debugging and testing and contemplating, I found a blog mentioning that it might be caused by the db engine difference, I just noticed that MySQL may have different DB Engine to store them for each table, and I was like… “Huh, really?”

Turns out it was right. lol.

So…. well, I made a patch on the migration by converting them all to use InnoDB, and then the app can continue run the entire migration pack of current feature branch.

The solution? Oh, well, you can just run a script, taken from a StackOverflow forum…

    $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'your_database_name' 
        AND ENGINE = 'MyISAM'";

    $rs = mysql_query($sql);

    while($row = mysql_fetch_array($rs))
    {
        $tbl = $row[0];
        $sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
        mysql_query($sql);
    }

… and adapt it to framework I use for the project.

// Converts any MyISAM Tables to InnoDB. Because apparently MyISAM didn't support foreign key.
// Solution is adopted from https://stackoverflow.com/a/3857366/4721245.
//
// For more verbose story, please see: [redacted]

$this->db->select("TABLE_NAME");
$table_names = $this->db->get_where("INFORMATION_SCHEMA.TABLES", [
    "TABLE_SCHEMA" => $this->db->database,
    "ENGINE" => "MyISAM"
])->result_array();

foreach ($table_names as $tableName) {
    $this->db->query("ALTER TABLE `$tableName[TABLE_NAME]` ENGINE=INNODB");
}

and then redo the test and anything works as lively as expected.

Conclusion

So whats the moral story here?

1. Migration can be unreliable if you don’t know the characteristic of your DBMS.
   This doesn’t mean you should not use that particular database, but I mean if you do massive migration it’s kinda good if you’re able to test it before releasing it to production.
2. If your local git might rewrite the history of remote branch, you better test that thing before pushing.
3. Software version is not always the cause of the problem. =3= I learned it the hard way. I had 10 minutes of denial before noticing that production server have the same version as the development. =3=

FAQ

• What might’ve caused the db engine differences?
  To be honest, I don’t know, but I’m thinking about the server upgrades. The database engine might ran the old version when we have that specific table before finally got upgraded and triggering this kind of error.
• Do running that patch might cause data loss?
  It might be! So please be really careful when running the script. It’s generally a good idea to backup first when doing something as risky as that!
• How can this be an FAQ when you wrote it before the blog released?
  In this case, “FAQ” stands for “Fully Anticipated Questions”.

Credits:
Photo by Paolo Nicolello on Unsplash.
World Trigger and Hunter×Hunter gif are provided by Giphy. Copyright goes to the respective owner.