I’m struggling to import a database created by Local from the dev environment to a live staging environment. I’m uploading to a host which I suspect uses an older version of MySQL and it is rejecting the database when importing via PHP MyAdmin.
I am getting the following error:
SQL query:
CREATE TABLE zufhf_alm ( id mediumint(9) NOT NULL AUTO_INCREMENT, name text NOT NULL, repeaterDefault longtext NOT NULL, repeaterType text NOT NULL, pluginVersion text NOT NULL,
UNIQUE KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
MySQL said:
#1273 - Unknown collation: ‘utf8mb4_0900_ai_ci’
Searches for SQL error #1273 suggest that this is an issue for which collations need to be converted. How do I do this? I am able to access the DB locally via Sequel Pro.
It depends on the version of mysql your trying to install on. This collate was added in mysql 8.0.
If you’re restoring to MySQL greater than 5.5; that should probably be “utf8mb4_general_ci” which was the default for those versions of MySQL.
Sequel pro won’t be much help here. It doesn’t yet support MySQL 8.0 databases.
Your best bet is to modify the file you’re trying to import.
You’ll need a text editor which can handle large files. I recommend sublime text.
It should allow you open the file and change each “collate” entry.
You could change them to “utf8mb4_general_ci” or you could remove the “COLLATE” line altogether. MySQL will use the default if none is specified.
If your “COLLATE” line ends with a semi-colon “;” make sure to put that back. That’s how mysql knows its reached the end of a statement. Couldn’t tell from the snippet; didn’t want to assume.
Thanks, this sounds great, although on opening the database as you suggest it isn’t clear to me which part I need to change or remove. A find command for ‘collate’ yields 186 matches which are mostly some variant of a line that looks like this:
comment_author_email varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
I’m not seeing anything that looks like a “COLLATE” line I can delete altogether.
I’m not particularly hot on this aspect of WP dev – it has always pretty much handled itself to date, and it’s only since working with Local that I’ve come up against this. Any further info you can offer about what I should be doing in the file would really be very useful.
Those are defined on each column.
It’s being explicit about how the column data should be stored. Not a bad thing.
Since “utf8mb4_0900_ai_ci” is what’s causing the trouble; I’d look explicitly for those.
If you find one; change it to match what you found in those columns; e.g “utf8mb4_unicode_ci”.
