Learn about Centmin Mod LEMP Stack today
Register Now

MariaDB charset issue

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by hitman, Jul 21, 2014.

  1. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    hallo
    after i followed this guide here
    "To change defaults respectively for character set and collation to utf8 and utf8_general_ci, you need to add under [mysqld] group in /etc/my.cnf the following line character-set-server=utf8:"

    my databases still stay as latin?
    what can i do?
     
  2. eva2000

    eva2000 Administrator Staff Member

    30,193
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,143
    Local Time:
    6:43 AM
    Nginx 1.13.x
    MariaDB 5.5
    from https://community.centminmod.com/th...db-mysql-default-character-set-collation.398/

    at very bottom of guide
    Changing existing database's character set data can be tricky as it can screw up existing database's existing non-latin1 character set based data if there is any if you're existing database came from a latin1 based MySQL server originally. If it did come from a latin1 based MySQL server original, leave your default at latin1 instead of UTF8.

    If you must change your database from latin1 to UTF8, do it on a separate test VPS server first to make sure it works and always backup and download a copy of your database before changes are made.

    You're on your own for changing existing database's character sets which involve 4 levels of changes from server level, database level, table level and column level as outlined in multi-page documentation at http://dev.mysql.com/doc/refman/5.5/en/charset-syntax.html. The guide at https://community.centminmod.com/th...db-mysql-default-character-set-collation.398/ outlines just the first level for server level defaults for newly created MySQL databases.
     
    Last edited: Jul 21, 2014
  3. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    the database is empty and i am already in a test vps
    i am on MariaDB [ (none) ]
    the character_set_client connection and results are in latin1 the rest are in utf8
    also the collation_connection is latin1
    the colaltion_database and server are in utf8

    any ideas?
     
  4. eva2000

    eva2000 Administrator Staff Member

    30,193
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,143
    Local Time:
    6:43 AM
    Nginx 1.13.x
    MariaDB 5.5
  5. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    yeap i already have done this
    i have uncommented this line in my.cnf but the result is not changing :(
     
  6. eva2000

    eva2000 Administrator Staff Member

    30,193
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,143
    Local Time:
    6:43 AM
    Nginx 1.13.x
    MariaDB 5.5
    did you restart mysql server as instructed ?

    you should see this in SSH window when you type

    Code:
    mysqladmin var | grep -E 'character|collation' | tr -s ' '
    should see
    Code:
    | 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/ |
    | collation_connection | utf8_general_ci |
    | collation_database | utf8_general_ci |
    | collation_server | utf8_general_ci |
    
     
  7. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    ok the mysqladmin command show the correct results but when i go with
    mysql> SHOW VARIABLES LIKE 'character_set%';

    latin1 appears
    why is that?
     
  8. eva2000

    eva2000 Administrator Staff Member

    30,193
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,143
    Local Time:
    6:43 AM
    Nginx 1.13.x
    MariaDB 5.5
    because that is your server mysql client connection character set hence only character_set_client connection reports as latin1

    tried logging out of SSH and back in and try
     
  9. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    i logged out of ssh and i am back in

    when i run this mysqladmin var | grep -E 'character|collation' | tr -s ' '
    then everything is fine

    when i run this mysql> SHOW VARIABLES LIKE 'character_set%';
    it shows as latin1

    what shall be done?
     
  10. eva2000

    eva2000 Administrator Staff Member

    30,193
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,143
    Local Time:
    6:43 AM
    Nginx 1.13.x
    MariaDB 5.5
    what does your /etc/my.cnf look like ?

    i have no such problems

    Code:
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 3
    Server version: 5.5.38-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> SHOW VARIABLES LIKE 'character_set%';
    +--------------------------+----------------------------+
    | 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/ |
    +--------------------------+----------------------------+
    Are you even using Centmin Mod + MariaDB MySQL ? as I notice your mysql client prompt says mysql> and not MariaDB>
     
  11. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    xaxaxaaxxa yes i am using centmin mod with MariaDB :p :p
    i write it by hand and i forgot to put it :p

    my my.cnf is not touched except i removed the "#" symbol before the character-set-server=utf8
     
  12. eva2000

    eva2000 Administrator Staff Member

    30,193
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,143
    Local Time:
    6:43 AM
    Nginx 1.13.x
    MariaDB 5.5
    Then it should be working AFAIK
     
  13. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    ok i will leave it for now and proceed with the database creation
    i have 2 questions though
    how to safely delete a database?
    and i have and issue with the domain shall i create a new topic or make the question here?
     
  14. eva2000

    eva2000 Administrator Staff Member

    30,193
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,143
    Local Time:
    6:43 AM
    Nginx 1.13.x
    MariaDB 5.5
    start a new thread so others can search and find the answer too ;)
     
  15. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    ok,but you did not answer me on how to safely delete a database?
    which command to use?
     
  16. eva2000

    eva2000 Administrator Staff Member

    30,193
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,143
    Local Time:
    6:43 AM
    Nginx 1.13.x
    MariaDB 5.5
    oh i meant for both questions for deleting database and domain have separate new threads :)
     
  17. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    oh ok i am on it
     
  18. hitman

    hitman Member

    115
    9
    18
    Jul 18, 2014
    Ratings:
    +13
    Local Time:
    11:43 PM
    i finally fixed the issue by adding these lines in my.cnf

    [client]
    default-character-set=utf8

    [mysql]
    default-character-set=utf8


    [mysqld]
    collation-server = utf8_unicode_ci
    init-connect='SET NAMES utf8'
    character-set-server = utf8
     
  19. eva2000

    eva2000 Administrator Staff Member

    30,193
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,143
    Local Time:
    6:43 AM
    Nginx 1.13.x
    MariaDB 5.5
    strange that kind of setup is only required for MySQL <5.5 versions and not for MySQL 5.5+
     
  20. NeiPCs

    NeiPCs Member

    32
    8
    8
    Jun 28, 2014
    Ratings:
    +10
    Local Time:
    6:43 PM
    1.11.1
    5.5
    Well, thanks for share this fix anyway :)
     
    • Like Like x 1