Table of Contents
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