The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL 4.1 collations

Discussion in 'General Discussion' started by Earendil, Jan 5, 2005.

  1. Earendil

    Earendil Well-Known Member

    Joined:
    Jun 10, 2004
    Messages:
    102
    Likes Received:
    0
    Trophy Points:
    16
    By default the server collation is set to latin1_swedish_ci
    How can I change this to latin1_general_ci ?
     
  2. I-Web

    I-Web Well-Known Member

    Joined:
    Jul 7, 2003
    Messages:
    138
    Likes Received:
    0
    Trophy Points:
    16

    Did you ever find an answer to this?
     
  3. I-Web

    I-Web Well-Known Member

    Joined:
    Jul 7, 2003
    Messages:
    138
    Likes Received:
    0
    Trophy Points:
    16
    Also is there anyway to change the setting globally.... MySQL has set them all by default to swedish and i want them set to general
     
  4. Earendil

    Earendil Well-Known Member

    Joined:
    Jun 10, 2004
    Messages:
    102
    Likes Received:
    0
    Trophy Points:
    16
    You can set it in your my.cnf
     
  5. I-Web

    I-Web Well-Known Member

    Joined:
    Jul 7, 2003
    Messages:
    138
    Likes Received:
    0
    Trophy Points:
    16
    Whats the line i need to add for this??

    And how do i convert all my existing databases/tables to the collation i want......
     
  6. deieno

    deieno Well-Known Member

    Joined:
    Nov 16, 2003
    Messages:
    69
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Floripa - Brazil
    Anyone has an answer, I have the same problem after update mysql.
     
  7. Daniel Artes

    Daniel Artes Well-Known Member

    Joined:
    Jun 21, 2003
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    USA - Baltimore
    SAME PROBLEAM OVER HERE...

    wehen I export the data base into another data base everyhtings goes wrng coz of the collation thingy... -_-
     
  8. casey

    casey Well-Known Member

    Joined:
    Jan 17, 2003
    Messages:
    2,303
    Likes Received:
    0
    Trophy Points:
    36
    Location:
    If there is trouble, it will find me
    MySQL 4.1 and character sets/collations

    Probably nobody here is in this situation, but if somebody has a clue, please let me know.
    I have gone through the trouble of converting EUC-JP encoded databases to UTF-8. The conversion went fine.

    My problem is that even though I have converted the database to UTF-8 the characters do not show correctly (at all--just jibberish) in phpMyAdmin. They come up fine on the website, but I cannot edit anything using phpMyAdmin. Not a big deal, because I hardly ever use phpMyAdmin, but it is a convenience I'd rather not do without.

    What do I need to do to the tables or whatever to make the characters show up properly?
     
  9. haze

    haze Well-Known Member

    Joined:
    Dec 21, 2001
    Messages:
    1,550
    Likes Received:
    3
    Trophy Points:
    38
    Curious.. what OS is this ?
     
  10. casey

    casey Well-Known Member

    Joined:
    Jan 17, 2003
    Messages:
    2,303
    Likes Received:
    0
    Trophy Points:
    36
    Location:
    If there is trouble, it will find me
    It's CentOS.
     
  11. deddy

    deddy Well-Known Member

    Joined:
    Oct 13, 2003
    Messages:
    131
    Likes Received:
    0
    Trophy Points:
    16
    MySQL 4.1.10a BUG

    latin1_swedish_ci is a BUG within MySQL 4.1.10a
    Normally it should be fixed in 4.1.9 but it is not.
    Not nice indeed.
     
  12. Fredi

    Fredi Member

    Joined:
    Apr 9, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Same is most problem!

    Anione can give the answer for correcting?
     
  13. deddy

    deddy Well-Known Member

    Joined:
    Oct 13, 2003
    Messages:
    131
    Likes Received:
    0
    Trophy Points:
    16
    vi /etc/my.cnf

    and insert

    default-character-set=latin1
    default-collation=latin1_general_ci

    This will fix the problem with latin1_swedish_ci

    but unfortunatly not the problem regarding
    foreign chars :(
     
  14. Fredi

    Fredi Member

    Joined:
    Apr 9, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Thank You deddy,

    However this does not solve whole problem!

    I have a client using russian language.
    For these client to use phpMyadmin - impossible!

    The Coding windows-1251 and KOI-8 - are not supported!

    Attempt to change all in

    default-character-set=cp1251
    default-collation=cp1251_general_ci

    I get absolutely incorrect encoding. :eek:



    We can expect correction from CP?
     
  15. casey

    casey Well-Known Member

    Joined:
    Jan 17, 2003
    Messages:
    2,303
    Likes Received:
    0
    Trophy Points:
    36
    Location:
    If there is trouble, it will find me
    #mysqldump -uUSER -pPASSWORD --default-character-set=binary USER_db > db.sql

    Will get you a dump file in the proper encoding.

    I used this file to replace the collations.

    #replace "latin1" "utf8" -- db.sql

    Voila! Now the database works in phpMyAdmin perfectly. The catch? Now it doesn't work on the site. All I get on the site now is ??????? (a bunch of question marks).

    This is not a cpanel issue, but a mysql issue, so I doubt whether cpanel is going to try to come up with any automatic solutions. If we work together, though, we should be able to figure it out.
     
  16. Fredi

    Fredi Member

    Joined:
    Apr 9, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Thes problem - inconsistency mysql and phpMyadmin encoding.

    Work incorrect!!!

    I can not switch the codings in phpMyadmin.

    Much people in the world use other codings, differring from UTF-8. No correct collation encoding mysql and phpMyadmin in stable version CP.

    This problem requires urgent correcting the problem in CP.

    When we can expect this correction? Much users can not work correct!
     
  17. casey

    casey Well-Known Member

    Joined:
    Jan 17, 2003
    Messages:
    2,303
    Likes Received:
    0
    Trophy Points:
    36
    Location:
    If there is trouble, it will find me
    Maybe you're not reading correctly, or maybe you're not understanding. The code I posted will fix the mysql and phpMyAdmin issues. It will not work for your website, though (at least it didn't for me). You can replace utf8 with whatever character set you're using. Just make sure it is one that mysql recognizes.

    The second point is that it works EXACTLY correctly, the way that the mysql developers intended it to. This is NOT a cpanel problem. If you're waiting for cpanel to "fix" something that isn't broken, then you'll be waiting a long time.
     
  18. Fredi

    Fredi Member

    Joined:
    Apr 9, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Yes, You rights!
    This works for mysql,

    However this does not work in site!

    In put instead of letters get (???????) You think this correct?

    I do not expect quick correction from CP. I search for the way to correct this by itself.
     
  19. casey

    casey Well-Known Member

    Joined:
    Jan 17, 2003
    Messages:
    2,303
    Likes Received:
    0
    Trophy Points:
    36
    Location:
    If there is trouble, it will find me
    Of course, ???? is not correct. I hope we can work together to find out how to solve the issue. I am wondering if something needs to be added to my.cnf
     
  20. casey

    casey Well-Known Member

    Joined:
    Jan 17, 2003
    Messages:
    2,303
    Likes Received:
    0
    Trophy Points:
    36
    Location:
    If there is trouble, it will find me
    Found it!

    Okay, here's my step-by-step guide to fixing the 2byte character issue.

    First, get a backup of your working database:

    mysqldump -uUSER -pPASSWORD --default-character-set=binary DATABASE > db.sql

    Now, if you have latin1 collations or some other collation that doesn't match your character set, do the following:

    replace "latin1" "utf8" -- db.sql
    (if you do not use utf8, replace with whatever you're using)

    Now you have the database in the proper format.

    mysql -uUSER -pPASSWORD DATABASE < db.sql

    At this point, your database should be viewable from within phpMyAdmin without any problems, but your website probably shows up as ????????????????

    To solve this, go into the file that holds your mysql connection and add this right after the connection:

    mysql_query("SET NAMES 'utf8'");
    (replace with some other character set if you're not using utf8)

    Voila! Now you can view your database on your site and within phpMyAdmin without garbled characters!
     
Loading...

Share This Page