Fun with Database / Wordpress Migrations

There's websites that like to be moved, and then there's Wordpress. Wordpress both does, and does not like being moved.

While you can transplant it to a new hosting platform, and put it in the same folder so it would be visible at the same place, the data may not migrate so easily. The guts of Wordpress is the database, which is not in the web files themselves.

If you're exporting and re-importing manually, you might run into errors when importing on the new platform; one of them is below.

If you are trying to import a database and getting an error like “#1273 - Unknown collation: 'utf8mb4_unicode_520_ci'”, this may be related to trying to import from a system running MySQL v5.6+, and your new platform uses a version of MySQL less than v5.6.

You could upgrade the server to MySQL v5.6, but if Mohammad can't go to that particular mountain, then we'll bring the mountain to Mohammad, and convert the database.

First, please try exporting the database from the current host in compatibility mode for “MYSQL40” -- you should obtain a .SQL or .ZIP file. Once you have it, please try importing it on your destination platform with compatibility mode "none".

You will likely then get an error like:

“#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM AUTO_INCREMENT=1' at line 23”

The cause is the "TYPE=" part -- it means the database is using the “type” keyword, which was depreciated in MySQL 5.0+, but it’s present because we just exported in compatibility mode for 4.0.

This can be fixed by opening the file in a text editor like Notepad, finding “TYPE=” (You may need to allow matches that aren't case-sensitive or exact whole-word matches) , and replacing it with “ENGINE=”.

Most databases have several hundred results for this, so unless it is a very small database, if you get only 20-30 results, you might need to adjust these settings.

Retrying the import of this modified database will hopefully yield better results, and have your website visible as it was before.

Other things that you might need to check:

  • If you had to make a new database on the new platform, you'll probably have to update your wp-config.php with the new database name (and database user / password)
  • Your permalinks may be broken -- for some reason, your preferences aren't saved in the database. Log into your wp-admin, and go to "Settings" -> "Permalinks" to re-set the preference, or otherwise futz with it to refresh it.


Popular posts from this blog

Atom editor: Hide project tab / tree view

Removing the watermark from LIV

Videos not working in Irfanview