I have having difficulty importina a db on a centos 6.5 - I get and "Unknown character set" error.

I have two VPS servers: Centos 7 (server A) and Centos 6.5 (server B)
I need to dump a db called max_site_data from server A to B.

On server A I dumped the db so:
mysqldump -u root -p[PASSWORD] --database max_site_data >/tmp/max_site_data.sql

Then I copied that file over to server B via SCP
After that I tried this command on server B:
mysql -u root -p < /tmp/massimor_mftestreplication.sql

Resulting error:
ERROR 1115 (42000) at line 22: Unknown character set: 'utf8mb4'

The mySQL version on server A is:
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.23                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.23-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

Open in new window


And on server B:
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.73-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

Open in new window



I checked the DB on server A and it uses utf8mb4 and utf8mb4_unicode_ci
I also see that that this character set was introduced by mySQL in version 5.5.3:
https://stackoverflow.com/questions/21911733/error-1115-42000-unknown-character-set-utf8mb4-in-mysql

The problem is each of these servers has a mySQL version that cannot be changed due to the software running on each.
The server A database is connected to an eCommerce site where I can easily backup the db and try some script to change the collation and charset to plain utf8. If the site works then I can leave it otherwise I am back to square one.

The question is this: can anyone here please tell me how I could change the server A's db's charset/collation to plain utf8 using phpmyadmin mysql query interface?

Also of course if anyone here knows more about this problem and can help me resolve it in a different way please go ahead and suggest.

thanks in advance
badwolfffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

badwolfffAuthor Commented:
By the way, searching still more on Google I came across a suggestion I wanted to ask you guys about. It is a command line code for shell to run on server A


ALTER DATABASE max_site_data CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

Is it a way in which I could achieve the same results I am looking for?
Any errors in the command line?

thanks
0
gheistCommented:
While is mostly works, MySQL does not recommend such downgrades past 1 version
Here mysql 5.6 for RHEL6 https://www.softwarecollections.org/en/scls/rhscl/rh-mysql56/
0
badwolfffAuthor Commented:
Hi Gheist, thanks for the reply.
As I already mentioned I can't change the version of mysql on either server.
On server A it gets updated along with cPanel updates.
On server B it is the highest version compatible with an accounting software that needs to run on that server.

Is there a single line query that can help me change all instances of the string "utf8mb4" to "utf8" on the whole database that I could run in Phpmyadmin or at command line via shell?

I have found out that doing that will not break my eCommerce site so that is the best solution. Only I don't know how to word the query.

thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gheistCommented:
You cannot downgrade 2 mayor versions via export.
I doubt accounting software cares about database server version when it is linked against client libraries.
0
badwolfffAuthor Commented:
I don't have to downgrade
I only need a MySQL query that can find and rename utf8mb4 with utf8 on the whole database in one go

Thanks
0
gheistCommented:
It will not work  4-byte unicode will not "rename" into 2-byte unicode.
I see no technical backing of requiring EOL MySQL Server for accounting software.
0
badwolfffAuthor Commented:
Will their latest software runs on MySQL 5.1 max. They need to upgrade but haven't done so yet. In any case the bottom line is, their software runs on that MySQL, with all respect, whether you see any technical backing or not.

Also I don't understand likewise why I can't change the charset and collation of an opencart/Wordpress db when I know for sure that they run on UTF8 too. I have a backup of the db so I can fix this later but all I need is a query that's all.
0
gheistCommented:
You cannot change 4-byte character to 2-byte character in a snap.
first new tables:
ALTER DATABASE x CHARACTER SET utf8
then each existing table:
ALTER TABLE x.y CONVERT TO CHARACTER SET utf8

probably wordpress does not work during conversion or after it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
badwolfffAuthor Commented:
You were right. The site got corrupted. So I restored the DB and created a read-only access to the centos 7 DB from the centos 6.5 server. That worked like magic!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.