asked on

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

badwolfff

ASKER

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

gheist

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/

badwolfff

ASKER

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

gheist

You cannot downgrade 2 mayor versions via export.
I doubt accounting software cares about database server version when it is linked against client libraries.

badwolfff

ASKER

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

gheist

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.

badwolfff

ASKER

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.

ASKER CERTIFIED SOLUTION

gheist

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

badwolfff

ASKER

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!

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

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