SQL collate error when transfering wordpress site to new development environment

Question

I transferred all files and database to the new server however, the database is not allowing for wordpress standard tables to get submitted. When I try to insert a new table like "wp_comments" I get a sql query error message that states.

#1273 - Unknown collation: 'utf8mb4_unicode_520_ci'

What can I change the collation to that will allow it to submit successfully and work correctly with WP? and is that one of a few options or the only option to take?

Additional Info: I am copying from a Server version: 5.6.32-78.0-log to a Server version: 5.5.43-37.2-log

Can I just delete this section and allow the rest to insert?

You're probably moving to an older MySQL server version from a newer; the newer ones support this `utf8mb4` character set. If you can, please [edit] your question to tell us server version numbers. You might consider asking support at your new hosting provider. You might consider firing your new hosting provider. Also, consider using a Wordpress plugin like Duplicator to handle your migration. — O. Jones, Jan 25 '17 at 14:33
Thanks @O.Jones I added server versions above. It is an older version I am taking the data too. This server is being upgraded soon but the site is being revamped now so I need a quick solution for this week. — Rookie Recruits, Jan 25 '17 at 14:51
This is a duplicate of http://stackoverflow.com/questions/41850752/issue-when-deploying-mysql-db-utf8mb4-unicode-520-ci-utf8mb4-unicode-ci — Rick James, Jan 25 '17 at 23:34

score 1 · Answer 1 · answered Jan 25 '17 at 15:33

1

Try using the Duplicator plugin to migrate your WordPress instance.

If that doesn't work ... then you can try this.

Edit the .sql file containing your wordpress tables.

Look for the string utf8mb4 -- that's the name of the new snazzy character set that includes all the custom parts of Unicode.

Change it to utf8. That's the name of the older but still perfectly competent Unicode character set.

You'll need to hit occurrences like this, changing them to mention utf8 instead of utf8mb4.

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Then, go look for mentions of utf8mb4_unicode_520_ci. Change them, for best results, to utf8_general_ci.

Try loading your dbms. You may have to muck around for a while to get this to work. (Keep a copy of your original .sql file, right?)

answered Jan 25 '17 at 15:33

O. Jones

103,626
17
118
172

Yes I do have a copy of the original file. Thank you! – Rookie Recruits Jan 25 '17 at 16:07
Changing to utf8 is overkill. Probably just the particular collation is missing. – Rick James Jan 25 '17 at 23:35
So, @RickJames, there's no harm in you writing your own answer to the question, you know. – O. Jones Jan 26 '17 at 01:02

score 1 · Accepted Answer · edited Apr 04 '17 at 07:55

1

Open your .sql file in notepad and find this

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Change the line for

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

edited Apr 04 '17 at 07:55

Nahuel Ianni

3,177
4
23
30

answered Apr 04 '17 at 06:07

yogesh

84
4

SQL collate error when transfering wordpress site to new development environment

2 Answers2