phpmysql error - #1273 - #1273 - Unknown collation

Question

I have just installed PhpMyAdmin v4.1.5 English only

I have set it up to access 2 servers - the local one on my PC and the remote one on my server

All is fine for my local PC but when I log in to my remote server I get the message

Error

MySQL said:

#1273 - Unknown collation: 'utf8mb4_general_ci'

Searching the PhpMyAdmin code finds one reference to this in DatabaseInterface.class.php

        if (PMA_MYSQL_INT_VERSION >  50503) {
            $default_charset = 'utf8mb4';
            $default_collation = 'utf8mb4_general_ci';
        } else {
            $default_charset = 'utf8';
            $default_collation = 'utf8_general_ci';
        }

No idea what this is but it seems to be setting the default charset & collation wrongly

Answer 1

You can fix this issue by deleting browser cookie from the begining of time. I have tried this and it is working fine for me.

To delete only cookies:

1. hold down ctrl+shift+delete
2. remove all check boxes except for cookies of course
3. use the drop down on top to select "from the beginning of time
4. click clear browsing data

Answer 2

There are two steps to fix this.

First edit phpMyAdmin/libraries/DatabaseInterface.class.php

Change:

    if (PMA_MYSQL_INT_VERSION >  50503) {
        $default_charset = 'utf8mb4';
        $default_collation = 'utf8mb4_general_ci';
    } else {
        $default_charset = 'utf8';
        $default_collation = 'utf8_general_ci';
    }

To:

    //if (PMA_MYSQL_INT_VERSION >  50503) {
    //    $default_charset = 'utf8mb4';
    //    $default_collation = 'utf8mb4_general_ci';
    //} else {
        $default_charset = 'utf8';
        $default_collation = 'utf8_general_ci';
    //}

Then delete this cookie from your browser "pma_collation_connection".
Or delete all Cookies.

Then restart your phpMyAdmin.

(It would be nice if phpMyAdmin allowed you to set the charset and collation per server in the config.inc.php)

Answer 3

I had read yesterday that the issue was fixed for someone when that person cleared cookies. I had tried that but it did not work for me.

Checking the following section in DatabaseInterface.class.php,

        define(
            'PMA_MYSQL_INT_VERSION',
            PMA_Util::cacheGet('PMA_MYSQL_INT_VERSION', true)
        );

I figured that somehow cache is the problem. So, I remembered that I was restarting the service instead of doing a start and stop.

# restart the service
systemd restart php-fpm

# start and stop the service
systemd stop php-fpm
systemd start php-fpm

Doing a stop followed by a start fixed the issue for me.

Answer 4

When you export you use the compatibility system set to MYSQL40. Worked for me.

Answer 5

Is your MySQL server version 5.5.3 or greater?

The utf8mb4, utf16, and utf32 character sets were added in MySQL 5.5.3.

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-sets.html

Answer 6

This solution worked for me

1) Click the "Export" tab for the database

2) Click the "Custom" radio button

3) Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.

4) Scroll to the bottom and click "GO".

If it's related to wordpress, more info on why it is happening.