HowTo: Converting database to UTF8

[nihkiruks]

Hi guys,

Here is small how-to for how to convert to utf-8 to be able to use all the languages. Your comments are welcome!

Make sure you have all the backups before doing this.

1. Dump your database:

mysqldump --opt -p whmcs > whmcs.sql

2. Convert it to UTF8 with iconv. Make sure you'll enter correct SOURCE charset. In my case it was Windows-1251

iconv -f CP1251 -t UTF-8  whmcs.sql > whmcs.utf8.sql

3. Import converted SQL back into the database:

mysql -p whmcs < whmcs.utf8.sql

4. Drop 1 index or you'll not be able to convert table's to UTF8 charset

alter table tblknowledgebase drop index `title`;

5. Create this PHP script and execute it on your server. Make sure you'll use your database/user names and password.

<?php
// original script (v1.0) by/from: http://www.phpwact.org/php/i18n/utf-8/mysql
// improved/modified (v1.03) by Bogdan 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/backup prior to running any of the generated queries

// this code is provided AS IS and without any warranty

//die("Make a backup of your MySQL database, then remove this line from the code!");

set_time_limit(0);

// collation you want to change to:
$convert_to   = 'utf8_general_ci';

// character set of new collation:
$character_set= 'utf8';

// DB login information - *modify before use*
$username = 'USERNAME';
$password = 'PASSWORD';
$database = 'whmcs';
$host     = 'localhost';

//-- usually, there is nothing to modify below this line --//

// show TABLE alteration queries?
$show_alter_table = true;
// show FIELD alteration queries?
$show_alter_field = true;

mysql_connect($host, $username, $password);
mysql_select_db($database);

$rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());

print '<pre>';
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";
        elseif ( $row['Default'] != '' )
            $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
        else
            $default = '';

        // 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` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable $default; \r\n";
        }
    }
}
?>

6. Copy it's output and save into .sql file.

7. Open it with your favorite text editor and change "NOT NULL DEFAULT NULL" to "NOT NULL" (script has a bug but I'm not that good to fix it)

8. Save the file and upload it to your server. Then run:

mysql -p whmcs < SCRIPTOUTPUT.sql

9. Add index back:

alter table tblknowledgebase add FULLTEXT KEY `title` (`title`,`article`);

That's it. You're now using UTF8 data and UTF8 charset for database/tables; Works for me. Took few hours to figure it out.

Any questions -- let me know.

Regards,
Sergey.
-----------------------------------
SiteValley.com

[INNOFLAME]

Hi,

I tried to did it with CP1256 but it failed.

Any suggestion?

Thanks

[jozeph]

Thank you very much for howto.

[chrismfz]

Just to mention if anyone having trouble viewing the dump file
normally (weird chars, ???, etc)

Try to dump it with its charset.

mysqldump uses by default utf8 but if someone uses latin1
for example, any non-english letters will be non readable chars.

In a case like this try:

mysqldump --default-character-set=latin1 --opt -p database_name > output.sql

Or replace latin1 with your current charset. After that you can edit se sql file as above.


I had a similar problem when I was trying to switch from Greek ISO to UTF8 and everyone thought and told me that this cannot be done...
That's why...

[nihkiruks]

Hi,

I tried to did it with CP1256 but it failed.

Any suggestion?

Thanks

Hi, what kind of issues you're getting?

[host1plus]

This work like converting databases sucks...

[rudberg]

Hmm, I'm not sure if this could help me, I'd be grateful if someone can shed a light on it...

My problem is that I would prefer being able to write the $LANG files without having to use all the & umlaut ; to write out the characters that many of us non-English native use every day.

I find that the title tells me I've got:

charset={$charset}

and throwing in a {debug} gives me it's UTF-8 allright.

My DB says:
MySQL charset: UTF-8 Unicode (utf
MySQL connection collation: utf8_unicode_ci

...too, so I thought I wouldn't have too much of a problem.

However, as soon as I start to throw in e.g. å, ä, ö into a /LANG/ file, it gets screwed up.

Now, why is that, and does anyone have a recommendation on how to solve it?

Or even some kind of tool to transform chunks of texts containing e.g. currency signs and umlaut characters and rendering the text on the & umlaut ; format?

[nihkiruks]

Language files are not stored in DB and they have nothing to do with DB codepage. You need to play with your editor and encoding for the language files themselves. I believe they should be in UTF-8

[ee99ee]

Awesome! I owe you a beer nihkiruks....

-Chris

[racku]

I used your tutorial and it switched the type, but in WHMCS, it still adds the weird characters. So I editted the database in phpmyadmin and WHMCS shows a ? where the changed character appears. Does this only work on WHMCS 4+? I am still on 3.8.

[Scolpy]

you forget to mention that we have edit configuration.php and add this line:

$mysql_charset="utf8"; 

after $db_name variable.

[nihkiruks]

you forget to mention that we have edit configuration.php and add this line:

$mysql_charset="utf8"; 

after $db_name variable.

For me it works without explicitly setting $mysql_charset in conf file.

[bander33]

Hi
when converting the db
I have to be on the root
or can i do it from the cpanle?

[nihkiruks]

Hi
when converting the db
I have to be on the root
or can i do it from the cpanle?

You have to do it in console and have access to database, database dump file and tools. You don't have to be root but you need to be familiar with console

[Mo9a7i]

nihkiruks

I have PMed you earlier about an issue i'm facing while converting to UTF
I've been using latin something for the DB and windows-1256 for encoding the pages (inputs/outputs)

when i tried your method + the mods you guys were discussing on this thread, some of the data went fine and some didn't and showing up weird letters


I'm wondering if you or anyone here is able to help me see if it's possible to do it and I'll be happy to pay for that task to be done if it needs alot of work.