Script to convert MySQL collation from utf8mb4 to utf8

May 11, 2015

I ran into a strange situation with WordPress where the MySQL server did not support utf8mb4. I needed to quickly convert the tables back to utf8 to get the site running again.

Though this script does work I highly recommend if you are running WordPress that you upgrade your MySQL server to support utf8mb4 for security. WordPress made this change to fix a security vulnerability introduced by The Trojan Emoji.

[bash]
DB="your_database_name"
USER="your_db_user"
PASS="your_db_pass"
(
echo ‘ALTER DATABASE `’"$DB"’` CHARACTER SET utf8 COLLATE utf8_general_ci;’
mysql -p$PASS -u $USER "$DB" -e "SHOW TABLES" –batch –skip-column-names \
| xargs -I{} echo ‘ALTER TABLE `'{}’` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’
) \
| mysql -p$PASS -u $USER "$DB"
[/bash]

24 thoughts on “Script to convert MySQL collation from utf8mb4 to utf8

  1. Félix Vanneste (June 20, 2015)

    Hello

    I can’t get your script working… Can you help me please!?
    I get the following error :

    echo ‘ALTER DATABASE `'”$DB”‘` CHARACTER SET utf8 COLLATE utf8_general_ci;’
    mysql -p$PASS -u $USER “$DB” -e “SHOW TABLES” –batch –skip-column-names \
    | xargs -I{} echo ‘ALTER TABLE `'{}’` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’
    MySQL a répondu: Documentation

    #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 ‘echo ‘ALTER DATABASE `'”$DB”‘` CHARACTER SET utf8 COLLATE utf8_general_ci;’
    ‘ at line 1

    Thank you so much!!!

  2. Marcel (June 24, 2015)

    Thank you very much, it works flawlessly.

  3. JP (June 24, 2015)

    How do I use this script? My host is on sql 5.1 so my hard work can no longer be pushed to live. If I use wp version <4.2 for new projects, then I assume all will be ok. It's my current developments that I MUST convert to utf8. Any help would be great. Moving hosts isn't an option. Thanks

  4. David (July 17, 2015)

    Thanks a lot for that, saved my life!

  5. etienne (July 20, 2015)

    Hello!

    I have the same problem as you with the charset between my local and online server…

    And when I try your script, I have the same issue as Félix Vanneste…

    Thanks in advance for your support!

    Best regards,
    Etienne

  6. Stephane (July 26, 2015)

    How to I use the script, please I need this?

  7. Brett (September 24, 2015)

    This is not a SQL query here folks, but rather a BASH shell script. To get the script you work open your command line and use the following steps:

    1. nano utf.sh
    2. paste the script inside
    3. control c to save the script
    4. chmod 755 utf.sh (in terminal) for the correct Read/write permissions
    5. run the script by type ./utf.sh

    6. Wallah your collation has changed!

  8. shov (September 25, 2015)

    Tnx a lot!!!

  9. Jack (October 14, 2015)

    how can i execute the script on my online webserver? i have created a file nammed utf.sh and i upload it to the server with 755 permissions but i can’t execute it.

  10. Nikki (October 26, 2015)

    Has anyone used this with MAMP? If so, how would you run this? Totally desperate right now to get my client’s new site off my local MAMP server and onto their hosted server (which doesn’t support utf9mb4).

    1. Ben Lobaugh (blobaugh) (October 26, 2015)

      It should work fine on any system that you can run a bash script on.

      1. Nikki (October 26, 2015)

        Thanks Ben – now I have to figure out how to run a bash script. I got steps 1-3 just fine through terminal…can you help me better understand 4 & 5?

        4. chmod 755 utf.sh (in terminal) for the correct Read/write permissions
        5. run the script by type ./utf.sh

        Thanks!

  11. Nikki (October 26, 2015)

    One more thing (sorry!) – do I need to specify the DB, UN, PW in the script with or without the “

    1. Ben Lobaugh (blobaugh) (October 26, 2015)

      The quotes are needed.

      Step 4 is setting the permissions on the file itself. What did you name it? With your terminal still open after saving the file run: chmod 755 . In the provided example it was saved as utf.sh.

      Step 5 just runs it. Still in your terminal type ‘./‘. The ./ tells the terminal to look in the current directory for the script instead of trying to run a system command.

      1. Nikki (October 26, 2015)

        So this is the script I ran:

        DB=“nikki”
        USER=“root”
        PASS=“root”
        (
        echo ‘ALTER DATABASE `'”$DB”‘` CHARACTER SET utf8 COLLATE utf8_general_ci;’
        mysql -p$PASS -u $USER “$DB” -e “SHOW TABLES” –batch –skip-column-names \
        | xargs -I{} echo ‘ALTER TABLE `'{}’` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’
        ) \
        | mysql -p$PASS -u $USER “$DB”

        —–

        This is what I did in terminal and got back 2 errors. (p.s. I will totally be sending you cookies for all this help!)

        Last login: Mon Oct 26 14:35:57 on ttys000
        You have new mail.
        Nikedog:~ HD$ nano utf.sh
        Nikedog:~ HD$ nano utf.sh
        Nikedog:~ HD$ nano utf.sh
        Nikedog:~ HD$ chmod 755 utf.sh
        Nikedog:~ HD$ chmod 755
        usage: chmod [-fhv] [-R [-H | -L | -P]] [-a | +a | =a [i][# [ n]]] mode|entry file …
        chmod [-fhv] [-R [-H | -L | -P]] [-E | -C | -N | -i | -I] file …
        Nikedog:~ HD$ ./utf.sh
        ./utf.sh: line 9: mysql: command not found
        ./utf.sh: line 6: mysql: command not found
        Nikedog:~ HD$

        Does any of that make sense to you? Better yet, have I screwed things up that now need to be fixed??

        1. Ben Lobaugh (blobaugh) (October 26, 2015)

          Hmm, it does make sense. It looks like MAMP has not registered the mysql command to your system. It should be a simple fix. At the end of the script change

          | mysql -p$PASS -u $USER “$DB”

          to be

          | /Applications/MAMP/Library/bin/mysql -p$PASS -u $USER “$DB”

  12. Nikki (October 26, 2015)

    Now it’s telling me that permission is denied. Would that ONLY happen if I got the db/user/pass information wrong? (which i don’t believe i do based on the fact that i pulled it out of the config file)

    What kind of cookies do you like?

    1. Ben Lobaugh (blobaugh) (October 26, 2015)

      Try running ‘chmod 755 utf.sh’ again.

      I like peanutbutter chocolate chip cookies 😉

  13. Nikki (October 26, 2015)

    Peanut butter chocolate chip it is!

    So this is the error I got this time.

    Last login: Mon Oct 26 14:44:00 on ttys000
    You have new mail.
    Nikedog:~ HD$ nano utf.sh
    Nikedog:~ HD$ chmod 755
    usage: chmod [-fhv] [-R [-H | -L | -P]] [-a | +a | =a [i][# [ n]]] mode|entry file …
    chmod [-fhv] [-R [-H | -L | -P]] [-E | -C | -N | -i | -I] file …
    Nikedog:~ HD$ ./utf.sh
    -bash: ./utf.sh: Permission denied
    Nikedog:~ HD$ chmod 755 utf.sh
    Nikedog:~ HD$ ./utf.sh
    ./utf.sh: line 6: mysql: command not found
    ERROR 1045 (28000): Access denied for user ‘“root”’@’localhost’ (using password: YES)
    Nikedog:~ HD$

    1. Nikki (October 27, 2015)

      And, of course, for all your help I will be visiting your wish list 🙂 Thanks for all your help yesterday – I’m hoping we can figure out the little buggy that is keeping this from working. Once I can get this bash script to work I’m off to the races. As a head’s up, I’m working with Host My Site on this, whose servers are the ones who won’t accept the new collate. They sent me your link – I suspect you may be getting some increased activity coming from them. Never thought I’d praise Go Daddy over another provider…hell has thusly frozen over. 🙁

      1. Ben Lobaugh (blobaugh) (November 4, 2015)

        Nikki did you get this figured out? Apologies that I dropped for a bit.

  14. Toni (February 22, 2016)

    After converting from utf8mb4 to utf8_general_ci, which I did manually via PHPMyAdmin, all special characters as well as some spaces are displayed as question marks. Is there some trick to avoid this?

    I’d appreciate some help. I upgraded my version of MAMP and later discovered that the majority of my hosts still don’t use utf8mb4. I have a number of projects I’ve developed locally that I cannot get to run properly live.

  15. Henning (July 6, 2016)

    Magnificent stuff, thanks mate – had to use this script a couple of times already…!

  16. Peaches (July 23, 2016)

    Hi, whre do I have to insert this script? In the database?