#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Bristol, England
    Posts
    114
    Rep Power
    17

    Collation problem upgrading from 3.23 to 4.1


    Hi,

    I've just tried upgrading to 4.1, but have a problem with the new collation feature in 4.1.

    I first tried to convert my old 3.23 db to utf8 during the installation procedure, but all the text and varchar columns had their lengths drastically shortened e.g. from 255 to 85, which caused a fatal loss of data.

    Then I tried installing it using latin1, but all the tables ended up with a collation of latin1_swedish_ci instead of latin1_general_ci, which I would have thought would be the logical default. This still gives some problems when comparing my data and it will be a huge job to go through manually changing all columns to latin1_general_ci.

    So, does anyone know of a way to make MySQL upgrade my old databases using latin1_general_ci instead of the swedish version? Also, if anyone knows of a way to convert to utf8 without the enormous loss of data, I would be very interested in that too.

    Debbie
    QuicknEasySalesPro.com
    - your quick and easy, yet powerful solution for managing your
    membership site sales, downloads and affiliates.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Mar 2003
    Posts
    704
    Rep Power
    64
    You can set the character set and the collation of the database and/or server to be what you want it to be and unless you override this, it should be applied by default to your tables/columns. Here's some more info:

    http://dev.mysql.com/doc/mysql/en/charset-server.html
    blockcipher
    ---------------
    Gratuitously stolen...
    mysql> SELECT * FROM user WHERE clue > 0;
    0 Results Returned.

    PHP5/MySQL/UTF-8
    My Tech Blog
  4. #3
  5. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,054
    Rep Power
    1390
    Debbie-Leigh - what version of MySQL (ie. 4.1.what?) are you using? There used to be a bug that utf-8 data took up 3x the amount of space, but this should have been fixed (I thought it had been) before 4.1.1 or so. The current version is 4.1.13a.

    --Simon
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Bristol, England
    Posts
    114
    Rep Power
    17

    Collation problem upgrading from 3.23 to 4.1 - solution


    So, it turns out that, if you want to upgrade to 4.1.x from an earlier version i.e. those that don't have all this charset/collation stuff, you have to fiddle with the my.ini file before you start the server for the first time using your old data. Otherwise the server will use it's own default charset/collation settings to convert your old data.

    So here's what I did, for anyone who has this problem:

    1. I removed my old data from the data store folder so it wouldn't be fiddled with as the installation program starts the server automatically when it finishes.
    2. I then added the following lines to the my.ini file:

    [client]
    default-character-set=latin1
    default-collation=latin1_general_ci

    [mysqld]
    default-character-set=latin1
    default-collation=latin1_general_ci

    3. I stopped the server and put my data back into the data store folder and restarted the server.
    4. The server now converted my old data into the new format using my charset/collation sequence preference and any new databases/tables/columns do actually use the server default.

    Hope that helps.

    Thanks to blockcipher who pointed me in the right direction.

    Debbie

    Comments on this post

    • SimonGreenhill agrees
    QuicknEasySalesPro.com
    - your quick and easy, yet powerful solution for managing your
    membership site sales, downloads and affiliates.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Mar 2003
    Posts
    704
    Rep Power
    64
    No problem. Glad it worked for ya.
    blockcipher
    ---------------
    Gratuitously stolen...
    mysql> SELECT * FROM user WHERE clue > 0;
    0 Results Returned.

    PHP5/MySQL/UTF-8
    My Tech Blog

IMN logo majestic logo threadwatch logo seochat tools logo