Ben Lobaugh Online

I think, therefore I am. I am, therefore I sail

Script to convert MySQL collation from utf8mb4 to utf8

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.

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"

Previous

Script to convert MySQL from InnoDB to MyISAM

Next

A sailors engagement

24 Comments

  1. Félix Vanneste

    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. Thank you very much, it works flawlessly.

  3. JP

    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

    Thanks a lot for that, saved my life!

  5. etienne

    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

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

  7. Brett

    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. Tnx a lot!!!

  9. Jack

    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

    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).

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

      • Nikki

        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

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

    • 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.

      • Nikki

        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??

        • 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

    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?

  13. Nikki

    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$

    • Nikki

      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. 🙁

  14. Toni

    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. Magnificent stuff, thanks mate – had to use this script a couple of times already…!

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

Comments are closed.

Powered by WordPress & Drip City Coffee