Autarchy of the Private Cave

Tiny bits of bioinformatics, [web-]programming etc

  • Exits

  • Categories

  • Archives

  • Visitors’ track

    Locations of visitors to this page

  • Tags list

«
»

Convert MySQL database from one encoding/collation into another

8th February 2008

Most frequent use: convert database from latin1_swedish to utf8_general_ci.
Original script found at: MySQL and UTF-8.

Update: the original script had an error, it would generate queries likes this one (note the bold part):

ALTER TABLE `links` CHANGE `link_rel` `link_rel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT NULL;

This is clearly wrong syntax (and logic). I fixed this by making comparison to NULL strict (with three equal signs instead of two):

// Does the field default to null, a string, or nothing?
if ($row['Default'] === NULL)

Update 2: based on comment by banesto, I modified the script; now it does not require specifying the from_collation, it's sufficient to specify to_collation (which will be used for all the fields and tables). The modified code is:

if ($row['Collation'] == '' || $row['Collation'] == $convert_to)
continue;

Here's the script itself: (to copy-paste: first click the "Plain text" header)

PLAIN TEXT
PHP:
  1. <?php
  2. // original script (v1.0) by/from: http://www.phpwact.org/php/i18n/utf-8/mysql
  3. // improved/modified (v1.04) by Bogdan http://bogdan.org.ua/
  4.  
  5. // this script will output all queries needed to change all fields/tables to a different collation
  6. // it is HIGHLY suggested you take a MySQL dump/backup prior to running any of the generated queries
  7.  
  8. // this code is provided AS IS and without any warranty
  9.  
  10. // add text/plain header when used not as a CLI script; PHP CLI SAPI shouldn't output headers
  11. header("Content-Type: text/plain");
  12.  
  13. // precaution
  14. die("Make a backup of your MySQL database, then remove this line from the code!");
  15.  
  17.  
  18. // collation you want to change to:
  19. $convert_to   = 'utf8_general_ci';
  20.  
  21. // character set of new collation:
  22. $character_set= 'utf8';
  23.  
  24. // DB login information - *modify before use*
  25. $username = 'user';
  26. $password = 'pass';
  27. $database = 'database_name';
  28. $host     = 'localhost';
  29.  
  30. //-- usually, there is nothing to modify below this line --//
  31.  
  32. // show TABLE alteration queries?
  33. $show_alter_table = true;
  34. // show FIELD alteration queries?
  35. $show_alter_field = true;
  36.  
  37. mysql_connect($host, $username, $password);
  38. mysql_select_db($database);
  39.  
  40. $rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());
  41.  
  42. while ($row_tables = mysql_fetch_row($rs_tables)) {
  43.     $table = mysql_real_escape_string($row_tables[0]);
  44.    
  45.     // Alter table collation
  46.     // ALTER TABLE `account` DEFAULT CHARACTER SET utf8
  47.     if ($show_alter_table)
  48.         echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\n");
  49.  
  50.     $rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());
  51.  
  52.     while ( $row = mysql_fetch_assoc($rs) ) {
  53.        
  54.         if ( $row['Collation'] == '' || $row['Collation'] == $convert_to )
  55.             continue;
  56.  
  57.         // Is the field allowed to be null?
  58.         if ( $row['Null'] == 'YES' )
  59.             $nullable = ' NULL ';
  60.         else
  61.             $nullable = ' NOT NULL';
  62.  
  63.         // Does the field default to null, a string, or nothing?
  64.         if ( $row['Default'] === NULL || $row['Null'] == 'YES' )
  65.             $default = " DEFAULT NULL ";
  66.         elseif ( $row['Default'] != '' )
  67.             $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
  68.         else
  69.             $default = '';
  70.  
  71.         // Alter field collation:
  72.         // ALTER TABLE `tab` CHANGE `fiel` `fiel` CHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
  73.         if ($show_alter_field) {
  74.             $field = mysql_real_escape_string($row['Field']);
  75.             echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable $default;\n";
  76.         }
  77.     }
  78. }
  79. ?>

DiggSubmit
  • Share/Bookmark

This entry was posted on Friday, February 8th, 2008 at 21:17 and is filed under Links, Notepad, PHP, Programming, Web. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

50 Responses to “Convert MySQL database from one encoding/collation into another”

  1. banesto Says:
    June 22nd, 2008 at 1:47

    BIG thanks for this script. i updated it a bit and it was just priceless. saved several hours of my life :)

  2. banesto Says:
    June 22nd, 2008 at 21:27

    there are several things:
    1) before setting new character set all fields have to be changed to binary equivalents
    2) it's not so good to depend on "convert_from", because db can contain many collations and script will take effect only in one of them. i think it's more convenient to check if ($row['Collation']!='')

  3. Bogdan Says:
    June 24th, 2008 at 9:26

    Banesto,

    1) why convert to binary?
    2) do you think it's a good optional solution to just provide the configuration for "target" character set, and let the script auto-detect the original set? that sounds good to me...

  4. banesto Says:
    June 26th, 2008 at 21:14

    1) because if i leave original type all data gets corrupted
    2) because there's usually need to get one collation for whole db and not to separate them. and it requires me to know what is the original collation in db is right now.

  5. Bogdan Says:
    June 27th, 2008 at 10:35

    Banesto,

    I changed the script a little bit by removing the source_collation and adding the check of row[collation] as you suggested.

    As for the conversion to binary types - I didn't do that, in part because I've no idea on what are the best binary equivalents to all the column types in MySQL, and in part due to the lack of desire to dig into that :)

  6. matt Says:
    July 20th, 2008 at 13:58

    your script above doesn't have your first fix (strict NULL comparison). otherwise, thanks!

  7. Bogdan Says:
    July 20th, 2008 at 15:16

    Matt,

    thanks for reporting that, it is now fixed.

  8. Russ Says:
    August 21st, 2008 at 12:35

    Just what I need, but unfortunately it doesn't do anything for me. When I first run the file I get the message to remove the die() line, which I did, but now it does nothing, no output, zilch, what have I done wrong ?

  9. Russ Says:
    August 21st, 2008 at 12:44

    ok got it to work, I didn;t have the mysql driver uneable din php.ini

    now when i run the generated script I get

    Error

    SQL query:

    ALTER TABLE `adminusers` CHANGE `id` `id` int( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT NULL ;

    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT NULL' at line 1

  10. Bogdan Says:
    August 23rd, 2008 at 12:36

    Russ,

    I thought I fixed that. Quick fix would be to make a global search-and-replace with the NOT NULL DEFAULT NULL string (replacing it with just NOT NULL). This should work, if you do the replace correctly :)

    (I mean replace to be done in the generated query.)

  11. Tim Says:
    September 20th, 2008 at 20:18

    So, I made a php file and put it in my root folder then loaded it in my browser. It output a page like:
    "ALTER TABLE `access` DEFAULT CHARACTER SET utf8;
    ALTER TABLE `access` CHANGE `mask` `mask` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ; "
    etc. It shows this for each table and field.

    But when I actually look at my database nothing has been changed. It's the same encoding/collation as it was to begin with.

    What am I doing wrong?

  12. Bogdan Says:
    September 20th, 2008 at 20:42

    Tim,

    the script itself doesn't modify the database - it only produces all the necessary queries.

    You have to (manually) copy-paste all the generated queries into the "SQL" text field of your favourite MySQL administration/management software, and "Run" (or how the execute command is named in your software). E.g. in PhpMyAdmin you'd go to the database structure page, then choose the SQL tab, paste all the queries, and run them.

  13. Tim Says:
    September 20th, 2008 at 23:08

    Thanks,
    It seems to be working. I get errors like this:
    "SQL query:

    ALTER TABLE `comments` CHANGE `thread` `thread` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT NULL ;

    MySQL said: Documentation
    #1067 - Invalid default value for 'thread' "

    then I change that one manually and rerun the remainder of the queries that start after the erroneous line and repeat.

  14. Raj Says:
    September 22nd, 2008 at 21:21

    hello
    my problem is i have taken backup from mysql>data folder -complete folder of a database
    and now copy it to different pc
    means
    copy from 1PC >Mysql>Data > and copy to 2ndPC >mysql>data

    now when i am browsing it from phpmyadmin , its shwoing the database and table but when i am browsing table its saying table doesnt exist.

    Collation type show in use ?

    Do you have any idea , which can help me ?

    Raj

  15. Bogdan Says:
    September 22nd, 2008 at 22:17

    Raj,

    this isn't MySQL support forum, and your issue doesn't seem to be collation-related.

    However, I would recommend that you use specific tools for MySQL DB export/import, and not just copy data folders.

    If you do copy files - check that MySQL versions are the same, configuration is also the same, and try running the MySQL's "repair" query on all the "copied" files. Might help, but still not a good way.

  16. Andrea Says:
    September 24th, 2008 at 23:10

    This is probably a big ol' newbie problem, but the script doesn't work for me: I'm getting "access denied" errors.

    Specifically, I cut and pasted the script into a file, backed up the database and removed that line, and then uploaded the script to the top level of my Drupal install and went to that URL in Firefox, where I got the following errors:


    Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'user'@'localhost' (using password: YES) in /home/manatee/public_html/acip/collation.php on line 21

    Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user 'manatee'@'localhost' (using password: NO) in /home/manatee/public_html/acip/collation.php on line 22

    Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in /home/manatee/public_html/acip/collation.php on line 22

    Warning: mysql_query() [function.mysql-query]: Access denied for user 'manatee'@'localhost' (using password: NO) in /home/manatee/public_html/acip/collation.php on line 23

    Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/manatee/public_html/acip/collation.php on line 23
    Access denied for user 'manatee'@'localhost' (using password: NO)

    Am I missing something obvious?

    Thanks,
    Andrea

  17. Bogdan Says:
    September 25th, 2008 at 16:40

    Andrea,

    please triple-check these configuration values:

    PLAIN TEXT
    PHP:
    1. $username = 'user'; $password = 'pass'; $database = 'table'; $host = 'localhost';

  18. Martin Westin Says:
    October 7th, 2008 at 15:35

    Hi Bogdan,
    nice script! Looks like a life-saver.

    If you should decide to dive into the whole data-conversion aspect of this, here is a little cheat sheet of how I have converted data before.

    char, varchar and text -> blob
    mediumtext -> mediumblob
    longtext -> longblob
    (I have not used tinyblob but it might be ok for char and varchar)

    There are two ways that the existing data may need conversion. This is something that the user must know and decide. I am not aware of any way to detect it, except by the errors if you do it wrong :)

    You would think that an existing latin1 database would have latin1 data in it. This is what scenario shows.
    Common though is that you can have php set up to use utf8 but the database remains in latin1. Any existing data will then me utf8 squeezed into a latin1 table. This needs a slightly different conversion as in the second scenario.

    # case where data was enterd correctly as latin1 and later needs conversion to utf8
    SET NAMES latin1;
    drop table people;
    CREATE TABLE people (
    id int(11) unsigned NOT NULL auto_increment,
    name varchar(64) default NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    INSERT INTO people VALUES
    (1,'user_a'),
    (2,'user_å'),
    (3,'user_ä');
    select * from people;
    SET NAMES utf8;
    alter table people engine=MyISAM default character set utf8;
    ALTER TABLE people CHANGE name name BLOB;
    ALTER TABLE people CHANGE name name varchar(64) CHARACTER SET utf8;
    select * from people;

    # case where utf8 data was enterd into a table with latin1 and later needs conversion to utf8
    SET NAMES latin1;
    drop table people;
    CREATE TABLE people (
    id int(11) unsigned NOT NULL auto_increment,
    name varchar(64) default NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    SET NAMES utf8;
    INSERT INTO people VALUES
    (1,'user_a'),
    (2,'user_å'),
    (3,'user_ä');
    select * from people;
    alter table people engine=MyISAM default character set utf8;
    ALTER TABLE people CHANGE name name BLOB;
    ALTER TABLE people CHANGE name name varchar(64) CHARACTER SET latin1;
    ALTER TABLE people CHANGE name name varchar(64) CHARACTER SET utf8;
    select * from people;

    I'll get back to you if I manage to implement something like this in your script.

  19. Martin Westin Says:
    October 7th, 2008 at 16:11

    Hi again,
    I got a bit carried away and did some tweaks to your script to support the two data conversions described above.
    There may be cases where it does not work but it works when I have tested it on a bunch of conversions.

    in the configuration at the top:
    // conversion of data for either normal data or "messed up" data
    // either leave empty or choose convert or rescue
    $data_conversion = '';

    and then at the bottom in the field conversion part.

    // Alter field collation:
    // ALTER TABLE `tab` CHANGE `fiel` `fiel` CHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
    if ($show_alter_field) {
    $field = mysql_real_escape_string($row['Field']);

    // charset conversion table for data conversions
    $types = array(
    'default'=>'blob',
    'mediumtext' => 'mediumblob',
    'longtext' => 'longblob'
    );
    if ( !empty($data_conversion) && strpos($row['Collation'], $character_set) !== 0 ) {
    if ( array_key_exists($row['Type'],$types) ) {
    $binary = $types[$row['Type']];
    } else {
    $binary = $types['default'];
    }
    echo "ALTER TABLE `$table` CHANGE `$field` `$field` $binary $nullable $default; \r\n";
    if ( $data_conversion == 'rescue' ) {
    $old_charset = substr($row['Collation'],0,strpos($row['Collation'],'_'));
    echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $old_charset $nullable $default; \r\n";
    }
    }

    echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable $default; \r\n";
    }

  20. Russ Michaels Says:
    October 7th, 2008 at 16:24

    I found another bug. Your script will actually remove the "auto_increment" attribute.
    I didn't know how to read this attribute from the current row to put it back, so I worked round it by modding the script to ignore numeric fields as these do not have a collation anyway.

  21. Bogdan Says:
    October 7th, 2008 at 16:51

    Dear Martin and Russ,

    thank you for valuable feedback!

    With your permission, I'll modify the script to take into account your code and suggestions.

    (I'm at work right now, so most probably will update the script code late tonight.)

    Update: still not done, but now in the process of updating code.

  22. Bogdan Says:
    October 10th, 2008 at 21:23

    Martin,

    why would you need these conversions?

    char, varchar and text -> blob
    mediumtext -> mediumblob
    longtext -> longblob

    blobs are for binary data, so why put the text into binary fields?

  23. Martin Westin Says:
    October 10th, 2008 at 23:58

    When you change the encoding, any existing data will be missinterpreted whenever the charsets differ. Converting the field via a binary type will allow you to trick MySQL into reinterpreting the "meaning" of the data.
    This will not affect A-Z but other (accented) characters.

  24. Martin Westin Says:
    October 11th, 2008 at 0:39

    Sorry. My last post was a bit crippled from being written on my phone in the car.

    There are probably many people that can explain the details of why you do it this way. But what I know is that if you can to "convert" existing data from one Character set to another in MySQL, you do it by making the field a binary (and thus tricking MySQL to reinterpret the raw data stored when you bring it back into a text field in another character set).

    I have implemented two such conversions for two common situations. But you can play around with this quite a bit if you have some really messed up data.

  25. ns Says:
    November 24th, 2008 at 4:20

    pardon the newbie question....

    in the line below, do I enter each table individually or simply put the database name in the place of 'table'?

    $database = 'table';

    thanks

  26. Bogdan Says:
    November 24th, 2008 at 12:02

    Ns,

    I've corrected the text - of course, it must be database name.

  27. jmd Says:
    November 27th, 2008 at 7:11

    Very nice script and the output is generated but when i run the query (Joomla database mysql 5.x)

    i get on every table

    ALTER TABLE `banners` CHANGE `description` `description` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT NULL ;

    MySQL said: Documentation
    #1067 - Invalid default value for 'description' "

    the original installed field values here are

    field: description
    type: text
    collation: utf8_unicode_ci
    attributes: (empty)
    null : No
    default: (empty)
    extra: (empty)

    This is basically fact on all tables and it runs into this error on same fields and stops. Any ideas would be welcome?

    Chad

  28. Bogdan Says:
    November 27th, 2008 at 11:05

    Chad,

    it is the same old problem which is for some reason happening all the time.

    I've just tested the script locally - and for me it works perfectly.

    I can only recommend that after you get all the generated queries, just run a global search-and-replace, replacing each and every "NOT NULL DEFAULT NULL" with "NOT NULL"

    If you would like the script to do that for you (and also contribute to let others avoid this error), please do the following:
    - for the first table where you get the error, run the query

    SHOW FULL FIELDS FROM tablename

    (where tablename is the name of that troublesome table),
    and post the result here. That might help me identify the reason why it is not working for you but does work for me.

  29. Vangelis Says:
    December 5th, 2008 at 12:20

    Thank you very much! This script made my day. I had the same problem with "NOT NULL DEFAULT NULL" but everything worked out fine after changing it to "NOT NULL". Thanks again!

  30. Bogdan Says:
    December 5th, 2008 at 13:10

    If anyone could post here steps to reproduce the "NOT NULL DEFAULT NULL" problem, I would be able to fix it once and forever :)

    (The problem is that I can't reproduce this error on my databases - thus "steps to reproduce" must include a sample of actual database table.)

  31. JMD Says:
    December 5th, 2008 at 13:21

    If you send me an email on which i can respond i give you cpanel and ftp on a test/demo site we host. Should help to have all the fun in the world..... ;-) You have my email......

    Chad

  32. Bogdan Says:
    December 6th, 2008 at 1:23

    done.

  33. Russ Michaels Says:
    December 6th, 2008 at 17:56

    Here is my version of the script if it is of any use to anyone, I made a couple of mods to get around the NULL issues and the auto_increment issue I mentioned before.

    <?php
    // original script (v1.0) by/from: http://www.phpwact.org/php/i18n/utf-8/mysql
    // improved/modified (v1.02) by Bogdan Tokovenko http://bogdan.org.ua/
    // this script will output all queries needed to change all fields/tables to a different collation
    // it is HIGHLY suggested you take a MySQL dump prior to running any of the generated queries
    // this code is provided AS IS and without any warranty
    set_time_limit(0);

    // collation you want to change to:
    $convert_to = 'utf8_general_ci';
    // character set of new collation:
    $character_set= 'utf8';
    $show_alter_table = true;
    $show_alter_field = true;
    // DB login information - modify before use
    $username = 'username';
    $password = 'password';
    $database = 'database_name';
    $host = 'localhost';
    //-- nothing to modify below this line --//
    mysql_connect($host, $username, $password);
    mysql_select_db($database);
    $rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());
    print '';
    while ($row_tables = mysql_fetch_row($rs_tables)) {
    $table = mysql_real_escape_string($row_tables[0]);
    // Alter table collation
    // ALTER TABLE `account` DEFAULT CHARACTER SET utf8
    if ($show_alter_table)
    echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\r\n");
    $rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());
    while ($row=mysql_fetch_assoc($rs)) {
    if ($row['Collation'] == '' || $row['Collation'] == $convert_to)
    continue;
    // Is the field allowed to be null?
    if ( $row['Null'] == 'YES' )
    $nullable = ' NULL ';
    else
    $nullable = ' NOT NULL';
    // Does the field default to null, a string, or nothing?
    if ( $row['Default'] === NULL )
    $default = " DEFAULT NULL";
    else if ($row['Default']!='')
    $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
    else
    $default = '';
    //don't alter int columns as they do not have a collation anyway and it buggers up the extra parameters
    if (strpos($row[Type], 'int') !== false)
    $show_alter_field = false;
    else
    $show_alter_field = true;
    // Alter field collation:
    // ALTER TABLE `tab` CHANGE `fiel` `fiel` CHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
    if ($show_alter_field) {
    $field = mysql_real_escape_string($row['Field']);
    echo "ALTER TABLE `$table` modify `$field` $row[Type] COLLATE $convert_to ; \r\n";
    }
    }
    }
    ?>

  34. paulo Says:
    January 27th, 2009 at 22:40

    Is anyone still following this string? Have searched high and low for a solution and this might be it. Would like to build a simple website with Joomla 1.5.9 adding a few components such as blogs, forums, profile. The problem I am encountering is what you have been discussing here. I've loaded everything tested, but my East Asian fonts come out as ?????. I've converted the database and table to utf8_general_ci, but can't get the columns with a manual for each individual column using the ALTER command. That would take days and I think a problem with the binary.... If it makes a difference, MySQL = 4.1.22, Server Version 5.0.67

    I've tried the scriptd by Bogdan and Russ as well as the link supplied by Bogdan at the top. Since I really know nothing about MySQL I do not understand how to fix the error messages I'm getting:

    1. FIRST ERROR at top and down.)

    ERROR: Unknown Punctuation String @ 1
    STR: <?
    SQL: <?php

    // this script will output the queries need to change all fields/tables to a different collation
    // it is HIGHLY suggested you take a MySQL dump prior to running any of the generated
    // this code is provided as is and without any warranty

    set_time_limit(0);<?php

    2. SECOND ERROR at bottom)

    SQL query:

    <?php // this script will output the queries need to change all fields/tables to a different collation // it is HIGHLY suggested you take a MySQL dump prior to running any of the generated // this code is provided as is and without any warranty set_time_limit(0);

    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php

    // this script will output the queries need to change all fields/table' at line 1

  35. Bogdan Says:
    January 27th, 2009 at 23:56

    Paulo,

    1. did you really mean to say that your MySQL client is at version 4.1.22, and Server's Version is 5.0.67? this is weird, and if it is really the case, then it could be causing you miscellaneous troubles.

    2. instead of converting the database, try to set it to utf8_general_ci *before* Joomla installation; if Joomla allows that - ask it to install with utf8 support in DB from the very beginning. If Joomla doesn't support that - I would suggest that you try Drupal 6.x, it might work perfectly out of the box for you.

    3. the errors you list look like you are trying to feed the PHP script to MySQL.... are you accessing the script with your browser, or just feeding it verbatim to the mysql?

  36. Mike Smullin Says:
    February 12th, 2009 at 2:46

    You just saved me SOOO much time! Thank you! :)

  37. jkal Says:
    April 16th, 2009 at 20:56

    Hi Bogdan,
    thanks for the script very useful...
    however I found on some fields that are indexed I get an #1283 error, I'm not an sql guru so was wondering if you could add a check to see if the field is indexed and ommit it or make another list of such fields, so one can drop the index of those fields convert it then re-add the index. thanks again.

  38. Bogdan Says:
    April 16th, 2009 at 21:14

    Looking at the explanation of error #1283:

    Error: 1283 SQLSTATE: HY000 (ER_BAD_FT_COLUMN)
    Message: Column '%s' cannot be part of FULLTEXT index

    it looks like the problem might be with those specific columns, not with the change of collation.

  39. Ryan Says:
    May 13th, 2009 at 11:42

    What if you have to alter the database?

    ALTER DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

    Should you first create a new DB and copy the tables to it in this new format?

  40. Bogdan Says:
    May 14th, 2009 at 22:05

    database alteration shouldn't have any effect in newer versions of MySQL (I think since 5.x), because individual column collation settings override database-level collation specification.

    I hope I understood your question correctly.

  41. Ryan Says:
    May 15th, 2009 at 3:45

    Ohhh well thats excellent then. Thank you for your response =)

    Wouldn't any new tables being created would still get created with the old collation if the database wasn't altered wouldnt it?

    I also once read somewhere that you have to create a completely new database for it to work properly but perhaps that was only versions of MySQL below 5.x

  42. Bogdan Says:
    May 15th, 2009 at 18:26

    I guess any new tables created inherit the database's collation settings, unless those new tables have their own specific collation settings specified. So if you are working on a database structure "on the fly/as you work", you may want to specify the exact collation you want by default as database's collation, so that any new tables you create have that collation and not the default latin1_swedish.

  43. Max Says:
    May 19th, 2009 at 14:47


    Looking at the explanation of error #1283:

    Error: 1283 SQLSTATE: HY000 (ER_BAD_FT_COLUMN)
    Message: Column '%s' cannot be part of FULLTEXT index

    it looks like the problem might be with those specific columns, not with the change of collation.

    I guess the source of these problem is that ALL columns in fulltext index should have same collation and error appears when we change collation of one of the columns contained in full text index.
    The only solution that I know is to drop index , change collation and recreate index again. But it would be nice if somebody can write the script autоmating this ...

  44. Szymon Błąkała Says:
    May 27th, 2009 at 14:21

    Great script. Thanks a lot.


    // Does the field default to null, a string, or nothing?
    if ( $row['Null'] == 'YES' && $row['Default'] === NULL )
    $default = " DEFAULT NULL";
    elseif ( $row['Default'] != '' )
    $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
    else
    $default = '';

    will fix null problem

  45. JDS Says:
    July 10th, 2009 at 20:31

    Awesome script, thanks. Especially the very smart "output only" aspect of the script so one can review the lines of SQL.

    I have one tiny thing I changed which was a CLI switch. It does not print "< pre >" unless run over HTTP (Line 38):

    if ($_SERVER['HTTP_HOST']){
    print '< pre >';
    }

    An alternative to this is to just output at "Content-type: text/plain". That will tell the browser to print newlines.

    Seeya...

  46. Bogdan Says:
    July 11th, 2009 at 0:50

    JDS,

    I've added text/plain header, that makes this script cleaner indeed.

  47. Dax Says:
    August 31st, 2009 at 8:08

    hi all,

    I have a simple way:

    First export DB to file .sql then find "latin1" and replace all as "utf8" with your text editor (i'm use text wrangler).
    Second, run this script:

    ALTER DATABASE `your DB name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

    Import file .sql for the final step.

    That 's all, very simple. By this way all old data will by change to UTF-8. Have a nice day.

  48. Joaquin Says:
    September 8th, 2009 at 22:31

    Hi,

    There's still an error with some fields that say: "NOT NULL DEFAULT NULL"

    Here's a fix:

    // Does the field default to null, a string, or nothing?
    if ( $row['Default'] === NULL )
    $default = $row['Null'] == 'YES' ? " DEFAULT NULL " : "";
    elseif ( $row['Default'] != '' )
    $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
    else
    $default = '';

  49. Bogdan Says:
    September 9th, 2009 at 10:22

    Joaquin,

    thank you for contributing, I've updated the script.

  50. banu Says:
    September 27th, 2009 at 15:03

    hi, thx for the script, when i tried, everything work except this error in sql.

    SQL query:

    ALTER TABLE `node_revisions` CHANGE `body` `body` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

    MySQL said:

    #1283 - Column 'body' cannot be part of FULLTEXT index

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

«
»