Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Leigh Maher
21,830 PointsHow do I deal with different versions of mySql server?
I've been working on a Wordpress site locally using MAMP, which is using mySql Server version: 5.5.42.
The other day I went to upload it to my hosting server, and it wouldn't allow me to import the tables. It was telling me that the collation didn't match. Locally I had utf8mb4 but it didn't recognise that on my hosting server. It only recognises utf8.
I contacted the host and they said that my hosting plan had mySql Server version: 5.1.73, and that I couldn't upload a database that was running on a more recent version.
Is there anything I can do to make these compatible?
Thanks.
5 Answers
Luke Pettway
16,593 PointsIf you have the option to change, you can do that but it might be worth trying to install WordPress using either a 1-click install or uploading the files and creating the site, and then importing the data export from the WordPress Admin Panel. This process is different from using phpMyAdmin because you aren't working with database tables, but rather a simple data export.
https://en.support.wordpress.com/export/
I've had to do this very process a couple of times for various reasons. The collation on the table shouldn't matter as it will just import the raw data. Keep in mind any plugin data such as ACF will have to be exported separately as well.
Note: Re submitted so that it can be marked as best answer.
Dean Wiseman
20,901 PointsDid you try changing the collation on your local MySQL to match your hosts?
Check out this blog: http://ben.lobaugh.net/blog/201740/script-to-convert-mysql-collation-from-utf8mb4-to-utf8
Leigh Maher
21,830 PointsThanks Dean. I just attempt this but couldn't get it to work. I've also read from the link that Luke sent, that it might not be a good idea to change this as it can cause character encoding issues?
Luke Pettway
16,593 PointsSince your issue is simply that you can't import the tables, you could always install WordPress and then use the Export and Import setting within the Admin panel, as it only outputs the raw data. I've had to do that for similar reasons.
Leigh Maher
21,830 PointsThanks for the reply Luke. That's exactly what I am doing but it won't allow it.
Luke Pettway
16,593 PointsAre you doing the import through MySQL itself or through WordPress?
Luke Pettway
16,593 PointsAnother suggestion found here: https://wordpress.org/support/topic/collation-error-utf8-mb4
Convert the tables to utf8-general_ci and then export again. Keep in mind though that this could cause some encoding issues
Leigh Maher
21,830 PointsIn phpMyAdmin.
Leigh Maher
21,830 PointsThanks for the link Luke. I'd be hesitant to make any changes that could cause issues down the line. I think the only safe option is to change to a host that uses a more up to date version of mySql?
Luke Pettway
16,593 PointsIf you have the option to change, you can do that but it might be worth trying to install WordPress using either a 1-click install or uploading the files and creating the site, and then importing the data export from the WordPress Admin Panel. This process is different from using phpMyAdmin because you aren't working with database tables, but rather a simple data export.
https://en.support.wordpress.com/export/
I've had to do this very process a couple of times for various reasons. The collation on the table shouldn't matter as it will just import the raw data. Keep in mind any plugin data such as ACF will have to be exported separately as well.
Leigh Maher
21,830 PointsSorry Luke, for some reason I can't mark you answer as the best. If you want to post your answer again as a separte reply, I'll mark that as the best answer. Thanks again.
Leigh Maher
21,830 PointsBrilliant : ) Thanks a lot Luke. I can't believe the solution was so simple. This has saved me a load of time on changing host. Thank you.