The Ultimate Guide to High Availability with MariaDB

Comments - Change IS_DEFAULT value for INFORMATION_SCHEMA.COLLATIONS

11 years, 4 months ago Daniel Bartholomew
Re: Change IS_DEFAULT value for INFORMATION_SCHEMA.COLLATIONS

The collation_server variable sets the default collation for the server (just like character_set_server sets the default character set

Here's some quick examples:

MariaDB [information_schema]> show variables like "character_set_server";
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+

MariaDB [information_schema]> show variables like "collation%";
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

Now let's change things:

MariaDB [information_schema]> set character_set_server="utf8";
Query OK, 0 rows affected (0.00 sec)

When the character_set_server variable is changed, the collation_server variable is changed to the default collation for the chosen character set.

So here's what the vars look like now:

MariaDB [information_schema]> show variables like "character_set_server";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
+----------------------+-------+

MariaDB [information_schema]> show variables like "collation%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

Now we'll change the default collation to be something different:

MariaDB [information_schema]> set collation_server="utf8_unicode_ci";
Query OK, 0 rows affected (0.00 sec)

And here's the vars again:

MariaDB [information_schema]> show variables like "collation%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
Archived  
11 years, 4 months ago Alexander Stehlik
Re: Change IS_DEFAULT value for INFORMATION_SCHEMA.COLLATIONS

Thanks for your reply but this does not really answer my question.

To be more precise: my question is: How can I influence the behavior of the following command:

SET NAMES utf8;

When this command is executed the collation_connection variable is reset to utf8_general_ci, even if the value was different before (e.g. utf8_unicode_ci).

I would like this to default to utf8_unicode_ci without having to supply the COLLATE argument to the SET NAMES command. Is this possible? And if not: is this a bug / missing feature?

 
11 years, 4 months ago Alexander Barkov
Re: Change IS_DEFAULT value for INFORMATION_SCHEMA.COLLATIONS [Answer]

There is no a way to change the default collation for a character set. Default collations are hard-coded. For example, utf8_general_ci is the hard-coded default collation for the character set utf8.

This is a missing feature.

The SQL standard has "CREATE CHARACTER SET" statement for this: https://kb.askmonty.org/en/create-character-set-statement/

So one can do:

CREATE CHARACTER SET utf8my AS GET utf8 COLLATE utf8_unicode_ci;

and then use this:

SET NAMES utf8my;

which effectively sets the connection character set to utf8, and the collation to utf8_unicode_ci.

MariaDB does not support CREATE CHARACTER SET yet.

The only workaround is to make the application send:

SET NAMES utf8 COLLATE utf8_unicode_ci;

instead of just:

SET NAMES utf8;
 
11 years, 4 months ago Alexander Stehlik
Re: Change IS_DEFAULT value for INFORMATION_SCHEMA.COLLATIONS

OK, thank you for the great explanation :)

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.