Skip to main content

Change Default Collation Server Wide

durangod
  • Edited

Hi,  

Since utf8mb4_unicode_520_ci  seems to be the norm now days, i wanted to change the default collation at sever level.  

I looked in WHM and that setting is not in the mysql config section.  So i googled it and AI  advises to change the /etc/my.cnf file as follows in the mysqld section of the file

 

[mysqld]collation-server=utf8mb4_unicode_520_cicharacter-set-server=utf8mb4init-connect='SET NAMES utf8mb4'

Then restart mysql

 Is this correct?

Is there a WHM UI menu item for this somewhere?

Thanks :)

Comments

15 comments

Date Votes
  • cPRex Jurassic Moderator

    Hey there!  That looks right to me.  There is the WHM >> Edit Database Configuration tool, but it doesn't let you add new entries like this, so command line is the way to go.

    0
  • durangod
    • Edited

    Hi, thanks...   but not sure that worked.  

    When i pull up phpMyAdmin and look at the information_schema  COLLATIONS section it shows that utf8mb4_general_ci is still default.  

    See pic

     

    on the bottom of my etc/my.cnf looks like this 

    # added by dave

    collation-server=utf8mb4_unicode_520_cicharacter-set-server=utf8mb4init-connect='SET NAMES utf8mb4'

    I did restart mysql, i even reboot the server

    Then under the schema CHARACTER_SETS section it shows utf8mb4_general_ci also

    Strange ok maybe it did change but why the difference?

     

    When i do the sql in phpMyAdmin as follows

    SHOW VARIABLES LIKE 'character_set_server';
    SHOW VARIABLES LIKE 'collation_server';

    It comes back utf8mb4  and utf8mb4_unicode_520_ci    hmmmm?

     

     

    0
  • cPRex Jurassic Moderator

    So at this point you're only seeing UTF-8 inside PHPMyAdmin, correct?  Is it still showing that today?

    0
  • durangod
    • Edited

    Yes its the same as it was yesterday (pics above), the default is the general_ci

    0
  • cPRex Jurassic Moderator

    Can you let me know what version of MySQL or MariaDB you're using so I can check this?

    0
  • durangod

    MySQL (10.6.22-MariaDB)

    0
  • cPRex Jurassic Moderator

    Can you run this con your machine and post the output?

    mysql -e "show variables like 'collation%';"

    If things are working as expected, it should look like this, indicating you have the correct collation:

    +----------------------+------------------------+
    | Variable_name        | Value                  |
    +----------------------+------------------------+
    | collation_connection | utf8mb3_general_ci     |
    | collation_database   | utf8mb4_unicode_520_ci |
    | collation_server     | utf8mb4_unicode_520_ci |
    +----------------------+------------------------+
    1
  • durangod
    • Edited

    I guess i cant run it as root?

    This was with csf explorer.

     

    root [/]# mysql -e "show variables like 'collation%';"

    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

    0
  • cPRex Jurassic Moderator

    You should be able to run anything as root.  This most likely indicates your MySQL root password in /root/.my.cnf is out of sync.

    You can reset that here - https://docs.cpanel.net/whm/sql-services/mysql-root-password/

    1
  • durangod

    Ok, i changed the database root password and set it same as root pw

    then i changed the database user password for my cpanel username and set it the same as my cpanel login for cpanel main username. 

    Restarted mysql and same results

    root [/]# mysql -e "show variables like 'collation%';"
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

     

    I guess ill set everything from scratch is the best thing and see what happens.

     

     

    0
  • durangod

    Nope that did not work either..  

    I reset root password - it logged me off and i logged back in with new pw

    I reset db root password to same

    I restarted mysql

    same result

    root [/]# mysql -e "show variables like 'collation%';"
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

     

    0
  • durangod

    Ok i got it, i guess using mysql commands in csf explorer is not allowed, even tried to log into mysql using 

    mysql -u root -p

    So in whm i changed over to the terminal feature, and it worked. 

    results : 

    [root@server1 ~]# mysql -e "show variables like 'collation%';"
    +----------------------+------------------------+
    | Variable_name        | Value                  |
    +----------------------+------------------------+
    | collation_connection | utf8mb3_general_ci     |
    | collation_database   | utf8mb4_unicode_520_ci |
    | collation_server     | utf8mb4_unicode_520_ci |
    +----------------------+------------------------+

    So it seems to be working.   strange how the schema in phpMyAdmin is different huh.... ill have to remember that... 

    Thanks for all your help... :)

     

    0
  • cPRex Jurassic Moderator

    That looks right to me.  I honestly don't have a good explanation for the data in PHPMyAdmin so you can always make a ticket if you'd like us to dig deeper into that.

    0
  • durangod

    No need, i found the answer.  The order or precedence is in reverse, its not from server down its from phpMyAdmin up.  This is according to AI

    AI Overview
     
     
    Sometimes, you may notice that the default collation displayed in phpMyAdmin for a database or table differs from the server's overall default collation.
     
    Here's why this might happen:
    1. Explicit Schema or Table-Level Collation: Even if the MySQL server has a default character set and collation (e.g., utf8mb4 and utf8mb4_0900_ai_ci), a CREATE DATABASE statement can explicitly define a different character set and collation for that specific database. Similarly, individual tables can be created with their own character set and collation, overriding the database's default settings.
    2. Database Export and Import: When you export a database, the export file might contain clauses that set the character set and collation for each table, potentially overriding the server's defaults upon import.
    3. phpMyAdmin Behavior: In some cases, phpMyAdmin might default to a specific collation (like utf8mb4_general_ci) for server connections, even if your server's configured default is different.
    4. Older Versions and Default Collation Changes: In older versions of MySQL, the default character set and collation might have been different (e.g., latin1 and latin1_swedish_ci). If you're importing an older database, it might retain those settings unless you explicitly convert them. 
    In summary, the collation setting follows a hierarchical order: column > table > database > server. The most specific setting takes precedence. So, even if the server has a default collation, individual databases, tables, or even columns can override it with their own defined collation. 

     

    Now we know the rest of the story :) 

     

    0
  • cPRex Jurassic Moderator

    Well that's interesting, and not what I would have expected!

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post