Some background…

Recently we’ve made some migrations from Percona MySQL Server 5.1 to MySQL 5.5.

 

It seems that Percona MySQL Server, have additional collations, which if used will cause problems after migrating to pure MySQL.

In my case the problematic collation was:

utf8_general50_ci  –>  Corresponding to id #253 in MySQL collations

After the migration, one of the databases was throwing the following error every time you try to access one of the tables:

ERROR 1273 (HY000): Unknown collation '#253' in table 'some_table' definition

After listing MySQL 5.5 collations:

mysql> show collations ;

it seems that there’s no collation with id “#253”

Here’s the steps which fixed the problem for me:

Let’s suggest we have the following scenario:

• server_1  – The server which was migrated from Percona to MySQl
• problematic_database – The database which contains the bad collation table
• server_2   – Test server with installed Percona Server
• bad_table  – The table whose collation is broken

 Make a copy of the “problematic_database”

# Stop mysql on server_1 
server_1# /etc/init.d/mysql stop

# Make a copy of the database directory
server_1# cp -ar /var/lib/mysql/problematic_database /root/work/problematic_database

 

Setup MySQL Percona – 5.X to another test server

On “server_2”, install Percona-server ,Percona-client packages from Percona’s website (or repo).

Also shut down the Percona server.

 

Copy the database  from original to percona server

server_1# rsync -av --progress /root/work/problematic_database root@server_2:/var/lib/mysql/

 

Dump the problematic table

Login to the Percona server and execute the following:

# First make sure Percona is started after the DB copy
/etc/init.d/mysql start

# Dump the problematic table 
mysqldump --opt --allow-keywords problematic_database bad_table > /root/work/bad_table.sql

# Copy the dump back to the original server
rsync -av --progress /root/work/bad_table.sql root@server_1:/root/work/

 

Finally drop / re-import the bad table

Currently on our original server_1 , we must have a working dump of table “bad_table” located here: /root/work/bad_table.sql

First we must edit the SQL file, and substitute the no-longer-supported collation with a some supported one.

In my case I changed “utf8_general50_ci” to “utf8_general_ci”

After finish editing the table dump, we must drop / re-import the table:

# Make sure we are on server_1

# Drop the bad table
mysql> use problematic_database;
mysql> drop table bad_table;

# Re-import the dump
server_1# mysql problematic_database < /root/work/bad_table.sql