CODEWITHSUNDEEP
mysqlutf-8collation
jcansell
jcansell

Reputation: 164

PHPMyAdmin forces to use ut8mb4 as default collation

Ok, so I've spent the morning trying to change the default collation on my XAMPP setup.

Here's the problem: I'm using Format() in a view, to convert a double into a string

    CREATE VIEW `test` AS
    SELECT 
        Format(some_data_table.double_number,0) AS string_result
    FROM some_data_table;

When I look at the returned column, its showing as utf8mb4_general_ci. I've tried all manner of settings in my.ini and phpMyAdmin's config.inc.php to no avail.

As a last resort, I'm prepared to add the collation parameter to view.

I'd be grateful for any tested solution

Upvotes: 1

Views: 1001

Answers (2)

Rick James
Rick James

Reputation: 142540

(I won't give you a tested solution without a failing test case.)

Here's a possible explanation:

mysql> SELECT FORMAT(2e7, 0);
+----------------+
| FORMAT(2e7, 0) |
+----------------+
| 20,000,000     |
+----------------+

But you are working in a "locale" where the "thousands separator" is ., not ,.

The solution has nothing to do with COLLATION. Instead, look at the arguments to FORMAT().

mysql> SELECT FORMAT(2e7, 0, 'de_DE');
+-------------------------+
| FORMAT(2e7, 0, 'de_DE') |
+-------------------------+
| 20.000.000              |
+-------------------------+

I am guessing that MS Access and MySQL are assuming different "Locales", hence stumbling over the thousands separator, and possibly other differences.

References on Locale:
http://dev.mysql.com/doc/refman/5.7/en/locale-support.html
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_lc_time_names

Upvotes: 0

jcansell
jcansell

Reputation: 164

Ok - i'm going to post my own answer for anyone else who lands here: (i had seen this somewhere else, but didn't trust it a t the time because there was no explanation).

When the SQL Format() turns a number into a string, it uses the variable character_set_results. PMA's Variables Tab was showing this as "utf8" but then on a line below, it was saying (session value) = utf8mb4.
So i was aware that PMA was overriding the server default. My real problem was that I could find no way to change this override - either by using the [mysqld] skip-character-set-client-handshake setting.. or by editing the php.config.inc file. Today I had a breakthrough.. I established that if I used the same PMA to connect to and older MySQL server, the problem did not occur. This suggested to be that PMA was forcing utf8mb4 on newer (capable) servers, but not older ones. I did a text search of phpmyadmin for the string 'mb4' and found the following code in the class: phpMyAdmin/libraries/DatabaseInterface.class.php

        // Skip charsets for Drizzle
    if (!PMA_DRIZZLE) {
        if (PMA_MYSQL_INT_VERSION >  50503) {
            $default_charset = 'utf8mb4';
            $default_collation = 'utf8mb4_general_ci';
        } else {
            $default_charset = 'utf8';
            $default_collation = 'utf8_general_ci';
        }

the PMA_MYSQL_INT_VERSION > 50503 seems to fit with my theory about older mysql versions, so i've backed up the file and edited the class replacing utf8mb4 with utf8 in this function. phpMyAdmin is now showing what i want in its variables tab, and the Format() function is now returning what i expect.

Upvotes: 1

Related Questions