Sal Ferrarello
You are here: Home / Programming / WordPress Unknown collation utf8mb4_unicode_520_ci

WordPress Unknown collation utf8mb4_unicode_520_ci

Last updated on by

Recently I had to move a WordPress database from a site running an old version of MySQL, specifically 5.6.33, and it lead to some problems.

First Problem: Unknown collation utf8mb4_unicode_520_ci

#1273 - Unknown collation: 'utf8mb4_unicode_520_ci'

This was the first error message I received when trying to import the database. It turns out this can be avoided by modifying the export file to use sql_mode MYSQL40 (for additional optional reading see sql_mode MYSQL40).

When exporting, in this case through phpMyAdmin, use Export Method: Custom.

phpMyAdmin Export Method Custom

Then under Format-specific options: choose MYSQL40.

Format Specific mysql40

This resolves the Unknown collation error.

Second Problem: Error in your SQL syntax

#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=20125' at line 19

Unfortunately, once the first issue was resolved, I found another issue. The TYPE keyword was removed in MySQL 5.5.

The older TYPE option that was synonymous with ENGINE was removed in MySQL 5.5. When upgrading to MySQL 5.5 or later, you must convert existing applications that rely on TYPE to use ENGINE instead.
— from MySQL Documentation on TYPE/ENGINE

To address this issue, you’ll need to open your .sql file in a text editor and find-and-replace all instances of TYPE= with ENGINE=.

Save your updated .sql file and now your import should work.

Update Note:

Thanks to both Matt and Leland‘s comments below, I’ve updated this post. Previously, I suggested replacing TYPE=MyISAM however they both pointed out they were dealing with databases using TYPE=InnoDB. They were still able to apply the fix by updating the SQL to ENGINE=InnoDB. Based on this, it sounds like updating TYPE= to ENGINE= should do the trick regardless of the actual type being used.

Sal Ferrarello
Sal Ferrarello (@salcode)
Sal is a PHP developer with a focus on the WordPress platform. He is a conference speaker with a background including Piano Player, Radio DJ, Magician/Juggler, Beach Photographer, and High School Math Teacher. Sal can be found professionally at WebDevStudios, where he works as a senior backend engineer.

Reader Interactions

Comments

  1. Matt says

    Great walk-through. The only difference in my situation was find-and-replace all instances of ‘TYPE=InnoDB’ with ‘ENGINE-InnoDB’. Worked like a charm. Thank you!

    Reply

  2. Ryan says

    Hey, I have exactly the same errors but it appears there is no “TYPE=MyISAM” in my .sql file hence I cant make any replacements. Its showing cant find the text “TYPE=MyISAM”

    Please help

    Reply

  12. kuldeep says

    #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 ‘phpMyAdmin SQL Dump
    — version 2.8.0.1
    — http://www.phpmyadmin.net
    —
    — Host:’ at line 1

    I am getting this error please do try to solve it.

    Thanks

    Reply

    • Sal Ferrarello says

      Hi kuldeep,

      If I understand you correctly, it sounds like you have a general problem with your SQL syntax.

      This post is about a specific situation (`#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’`), that I was able to resolve and wanted to document for future reference.

      You might want to try posting your question to the Database Administrator Stack Exchange and tag your question with mysql. When you do that it may be helpful to post the first few lines of your SQL file.

      Best of luck.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *