SQL database collation incompatiblity

[WillParticipant]

I’m receiving the following error when attempting to migrate a multisite from my local system to the remote server:

Error: The database backup uses MySQL features not available in the old MySQL version (5.1.66) that this site is running on. You must upgrade MySQL to be able to use this database.

I am aware that I am running a more recent version of mySQL and this is causing a problem with incompatibility of the collation methods. In non-multisite versions I’ve been able to fix this simply by exporting the database, running a search and replace on the sql file to

1. Find utf8mb4_unicode_520_ci
2. Replace with utf8mb4_general_ci

however this method does not seem to be working with multisite. Any suggestions? (I mean other than reinstalling my wampserver with an older version of mysql).

[Dee NutbourneModerator]

Hi,

Version 5.1 of MySQL does not support utf8mb4. You can try replacing ‘utf8mb4_unicode_520_ci’ with ‘utf8_general_ci’. If your database is not using any utf8mb4 only characters, then this may work.

However, we recommend updating the old MySQL database on the server to version 5.5. Version 5.1 was end-of-lifed in 2013, and no longer receives even security updates.

Best Wishes,
David N

[WillParticipant]

Hey David:

Thx very much for your response. Unfortunately I have no control over what version of mySQL the host runs. It’s very annoying as this is the second host in two weeks where I’ve encountered this. Puzzling why they insist in running outdated versions.

[Dee NutbourneModerator]

Hi,

Replacing ‘utf8mb4_unicode_520_ci’ with ‘utf8_general_ci’ in the database backup is the most likely way for this to work (though I would recommend making the change on a second copy of the file) if you cannot change the MySQL version.

I’m afraid that there is nothing that UpdraftPlus itself can do, as this is an internal MySQL issue.

Best Wishes,
David N

[Author]

Posts