Avatar of badwolfff
badwolfffFlag for United Kingdom of Great Britain and Northern Ireland

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
MySQL ServerLinux DistributionsLinux

Avatar of undefined
Last Comment
badwolfff
Avatar of badwolfff
badwolfff
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of gheist
gheist
Flag of Belgium image

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/
Avatar of badwolfff
badwolfff
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of gheist
gheist
Flag of Belgium image

You cannot downgrade 2 mayor versions via export.
I doubt accounting software cares about database server version when it is linked against client libraries.
Avatar of badwolfff
badwolfff
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of gheist
gheist
Flag of Belgium image

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.
Avatar of badwolfff
badwolfff
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of gheist
gheist
Flag of Belgium image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of badwolfff
badwolfff
Flag of United Kingdom of Great Britain and Northern Ireland image

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!
Linux
Linux

Linux is a UNIX-like open source operating system with hundreds of distinct distributions, including: Fedora, openSUSE, Ubuntu, Debian, Slackware, Gentoo, CentOS, and Arch Linux. Linux is generally associated with web and database servers, but has become popular in many niche industries and applications.

71K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo