While attempting to upgrade WHMCS version 5.x to 7.x, I kept getting MySQL errors from the updater script, and here’s how I was able to fix it.

The Errors

The first few errors I kept getting looked like this, saying table tblupdatehistory  already exists:

So as you would think, I just went ahead and dropped that table, but then one after another I kept getting errors about the  created_at  and  updated_at columns already existing, something like this:

The Problem

The problem stems from the fact that WHMCS doesn’t care enough to spend the time to write a sql script that will actually check if those columns, or tables exist, before running the commands.  Basically that means, if you have any type of error during your upgrade, they EXPECT YOU to restore a copy of the database, instead of writing a sql script that actually checks for columns before attempting to ALTER the table and add the column.  So as usual, I had to come up with my own solution to their sloppy sql scripts.

The Solution

To determine the exact solution I needed to come up with, I had to inspect the sql upgrade file they were trying to import that was throwing the errors.  The specific file ended up being the upgrade600alpha1.sql  file, that’s where I found the MySQL code that was attempting to ALTER  the table and ADD COLUMN  without actually checking if the column already exists (that’s after the code that attempts to add the tblupdatehistory  table without checking that it exists).

In order to get the upgrade to complete all the way, I had to literally fix each error one at a time as it showed on the upgrade page, and at the end, this is the MySQL code I had to run through phpMyAdmin to get past those errors:

As you can see, I first drop the tables that are created by the upgrade database file, then drop the columns that are added by that file as well.  You will notice that the port column is also dropped, as this is added by the MySQL import as well.  Once I did this, and resolved a few other issues (I also had to add userid  column to tblinvoiceitems  as well), and voila!  Profit!

If you want to see what tables have a specific column in them, you can run this command and it will return the tables the column is found in:

Example Errors