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]
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!!!
Thank you very much, it works flawlessly.
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
Thanks a lot for that, saved my life!
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
How to I use the script, please I need this?
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!
Tnx a lot!!!
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.
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.
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!
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.
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
to be
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?
Try running ‘chmod 755 utf.sh’ again.
I like peanutbutter chocolate chip cookies 😉
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$
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. 🙁
Nikki did you get this figured out? Apologies that I dropped for a bit.
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.
Magnificent stuff, thanks mate – had to use this script a couple of times already…!
Hi, whre do I have to insert this script? In the database?