How can databases be created with utf8_unicode_ci collation as default?

[Archmactrix]

When new databases are created in cPanel they don't get the desired utf8_unicode_ci collation but instead utf8_general_ci even though the former one should be the default collation.

The databases are created in the control panel but not phpMyAdmin.

How can I make the database creation tool create the db with the utf8_unicode_ci collation?

There is also one thing worth mentioning and that is the 'Server connection collation' in phpMyAdmin general Settings on the dashboard, which is utf8_general_ci but should perhaps be utf8_unicode_ci. Please advice me on of how to make it so.

Output from the character and collation variables query:

mysql> show variables like "%character%";show variables like "%collation%";

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Charset in my.cnf:

[mysqld]
init_connect='SET collation_connection=utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

 

[cPanelMichael]

Hello

Adjusting the default encoding and collation in /etc/my.cnf should result in the behavior you are expecting. For example, I added the following entries to the /etc/my.cnf on a test server and restarted MySQL:

collation_server=utf8_unicode_ci
character_set_server=utf8

Upon creating a new database, the correct values were used:

mysql> show variables like "character_set_database";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like "collation_database";
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_unicode_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

Are you sure "utf8_general_ci" is output with the above command for new databases?

Thank you.

 

[Archmactrix]

utf8_unicode_ci

is the output for collation_database like displayed in my previous post, with the command:

mysql> show variables like "%character%";show variables like "%collation%";

 

[cPanelMichael]

That matches what you have listed for your /etc/my.cnf file. Where are you seeing the "utf8_general_ci" collation at?

Thank you.

 

[Archmactrix]

I see it in phpMyAdmin.

 

[cPanelMichael]

I see it in phpMyAdmin.

That does not mean your databases are created with the "utf8_general_ci" collation. It's just what is configured for PHPMyAdmin itself. You can change that value in PHPMyAdmin by selecting a different collation in the drop down box for your session if necessary.

Thank you.

 

[Archmactrix]

That does not mean your databases are created with the "utf8_general_ci" collation. It's just what is configured for PHPMyAdmin itself. You can change that value in PHPMyAdmin by selecting a different collation in the drop down box for your session if necessary.

Thank you.

Then why does it show the collation utf8_general_ci for the tables in phpMyAdmin, I thought it should be the unicode one?

 

[cPanelMichael]

Are you sure those databases/tables were created after you modified the default collation in your /etc/my.cnf file? You can open a support ticket if you want us to take a closer look:

Submit A Ticket

You can post the ticket number here so we can update this thread with the outcome.

Thank you.

 

[Archmactrix]

Yes I'm sure!

I've created several databases in cPanel accounts on the server after the configuration changes few months back. All of them seem to have the utf8_general_ci collation instead of utf8_unicode_ci when displayed in phpMyAdmin.

I must be misunderstanding something.

But right now I'm not sure if I should submit a support ticket.

This a screenshot of the structure view of the most recent database in phpMyAdmin, created months after the changes:

 

[cPanelMichael]

You can review the following MySQL document regarding the character set and table collation for individual tables:

MySQL - Table Character Set and Collation

It explains how MySQL chooses the table character set and collation for each table.

Thank you.

 

[fidividi]

Hello

Adjusting the default encoding and collation in /etc/my.cnf should result in the behavior you are expecting. For example, I added the following entries to the /etc/my.cnf on a test server and restarted MySQL:

collation_server=utf8_unicode_ci
character_set_server=utf8

Upon creating a new database, the correct values were used:

mysql> show variables like "character_set_database";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like "collation_database";
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_unicode_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

Are you sure "utf8_general_ci" is output with the above command for new databases?

Thank you.

I have the same problem!

MySQL 5.1

conf look like this:

[mysqld]
.....
.....
default-table-type=MyISAM
default-storage-engine=MyISAM
collation-server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake

And still, when I try to create a table, they are created using "utf8_general_ci" instead of "utf8_unicode_ci".


In your example, and the way you showed: "show variables like "collation_database";", you are not really showing us the table status, to be able to see the "Collation" under which your database/table is created.

Any help appreciated to what is causing this.

 

[cPanelMichael]

Please feel free to open a support ticket so we can take a closer look:

Submit A Ticket

You can post the ticket number here so we can update this thread with the outcome.

Thank you.

 

[fidividi]

Please feel free to open a support ticket so we can take a closer look:

Submit A Ticket

You can post the ticket number here so we can update this thread with the outcome.

Thank you.

Thank you for your reply. Ticket created, "Your Request id is: 4400725."

 

[cPanelMichael]

To update, this issue was resolved by adding the following to the end of the /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php file:

// Custom Server Settings
$cfg['DefaultConnectionCollation'] = 'utf8_unicode_ci';

Thank you.