• Skip to primary navigation
  • Skip to main content
Sal Ferrarello
  • About Sal Ferrarello
  • Speaking
  • Connect
    Mastodon GitHub Twitter (inactive)
You are here: Home / Programming / WordPress Unknown collation utf8mb4_unicode_520_ci

WordPress Unknown collation utf8mb4_unicode_520_ci

Last updated on December 19, 2016 by Sal Ferrarello

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.

Share this post:

Share on TwitterShare on FacebookShare on LinkedInShare on EmailShare on Reddit

Filed Under: Programming Tagged With: MySQL

Reader Interactions

Comments

  1. Matt says

    December 12, 2016 at 5:12 pm

    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

    December 15, 2016 at 9:10 am

    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
    • Sal Ferrarello says

      December 15, 2016 at 10:58 am

      Hi Ryan,

      It sounds like more troubleshooting is necessary. I’d start with Matt’s comment above and see if that helps.

      If you do get it sorted, I’d be interested to hear more about the problem and how you solved it.

      Best of luck.

      Reply
      • Ryan says

        December 18, 2016 at 4:57 am

        Hey Sal,

        Same issue. Note yet resolved.. Am trying several other options

        Reply
      • Ryan says

        December 18, 2016 at 5:06 am

        Hi,

        Se screenshot from the link shared
        http://prnt.sc/dkui5m

        I am completely restless now after days of trying to resolve this seemingly simple issue….

        Reply
        • Sal Ferrarello says

          December 19, 2016 at 8:56 am

          Hi Ryan,

          Since TYPE=MyISAM doesn’t appear in SQL file, I’d try searching for

          TYPE=

          instead and see if there is any value being set.

          Reply
    • Leland says

      December 19, 2016 at 2:27 pm

      Does anything come up for “TYPE=” (not necessarily “TYPE=ISAM”)?

      Mine was “InnoDB” instead of “ISAM” for example. I just search/replaced “TYPE=InnoDB” for “ENGINE=InnoDB” and everything worked fine.

      P.S. Thanks Sal, I ran into this *exact* problem (except the ISAMInnoDB part) and got it solved thanks to this blog post.

      Reply
  3. Pasquale says

    December 16, 2016 at 9:05 am

    Great, worked!
    Thanks

    Reply
  4. Wendel Parrião says

    January 16, 2017 at 11:11 am

    Oh man, thank you soo much!!! u save my day

    Reply
  5. Brandon says

    January 24, 2017 at 7:13 pm

    Thank you so much!

    Reply
  6. SAZ says

    January 29, 2017 at 1:29 am

    Thanks. You just made my night.. otherwise I wouldn’t have slept.

    Thanks

    Reply
  7. Nur says

    February 13, 2017 at 6:11 pm

    Thank you so much. You save my lots of trouble.

    Reply
  8. Ken says

    February 14, 2017 at 10:19 pm

    Thanks you so much, Saves my day sir.

    Reply
  9. Pedro says

    March 6, 2017 at 9:57 pm

    Thanks very very much !!! It helped a lot !

    Pedrp

    Reply
  10. Everaldo Matias says

    March 14, 2017 at 8:10 am

    Work for me and solved my life..

    Very thanks..

    Reply
  11. Alex Gutierrez says

    March 31, 2017 at 10:33 am

    I was going crazy trying to find a solution for this and this helped! You made my day. Thank You

    Reply
  12. kuldeep says

    December 14, 2017 at 6:28 am

    #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

      December 14, 2017 at 11:35 am

      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 Cancel reply

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

Copyright © 2023 · Bootstrap4 Genesis on Genesis Framework · WordPress · Log in