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 (utf8)

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.

[Mo9a7i]

I finally did the conversion using Notepad++ on a quadcore windows server.

 

That instead of using iconv which puts lots of people in a lot of hassle

[craig.johnson]

Thanks a lot. I couldn't do it by myself

[firas]

Hello!

 

I have an issue with converting the DB from latin to UTF-8, i did complete everything as here

 

mysql -p whmcs < SCRIPTOUTPUT.sql

 

i recive the error

 

ERROR 1067 (42000) at line 2: Invalid default value for 'gateway'

 

any suggestion please?

[Patty]

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

$mysql_charset="utf8";

after $db_name variable.

Nice tip! I've changed that to $mysql_charset="iso-8859-1"; and my support tickets don't have weird characters problems any more.

 

Pity it doesn't work for the PDF quotes as well.

[EhsanCh]

download EmEditor

1-dump database

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

2-open file as utf8 in emeditor

3-replace all "latin1" with "utf8"

4-save file as utf8 with signator

5-import new file

[Jbro]

what version of emeditor did you exactly use?

[holodyn]

1) Backup

mysqldump --user=USERNAME -p --default-character-set=latin1 -c --insert-ignore --skip-set-charset -r whmcs_database.sql DATABASE_NAME

 

2) Convert

iconv -f ISO8859-1 -t UTF-8 whmcs_database.sql > whmcs_database.utf8.sql

 

3) Drop / Re-Create Database

mysql --user=USERNAME -p --execute="DROP DATABASE DATABASE_NAME; CREATE DATABASE DATABASE_NAME CHARACTER SET utf8 COLLATE utf8_general_ci;"

 

4) Edit file using Notepad++ or other UTF8 compliant editor

 

Find and replace "CHARSET=latin1" with "CHARSET=utf8"

Find and remove "set latin1 collate latin1_general_ci" from table field lines

Save the file

 

5) Import the file

 

mysql --user=USERNAME --max_allowed_packet=16M -p --default-character-set=utf8 DATABASE_NAME < whmcs_database.utf8.sql

 

6) Make sure the following is included in your configuration.php file

 

$mysql_charset = 'utf8';

 

7) Make sure in your WHMCS Settings that utf-8 is setup as your "System Charset"

 

WHMCS Administration > Settings > General Settings > Localization

Confirm / Update "System Charset" field value to read "utf-8"

 

DONE!

 

In my case I was testing for Greek characters. On an Windows machine running English charset, you can type Greek characters by using the ALT+NNNN keystroke, as referenced here: http://www.usefulshortcuts.com/alt-codes/accents-alt-codes.php

[EhsanCh]

what version of emeditor did you exactly use?

i use emeditor 8.06 .

the key is "--default-character-set=latin1 " parametr in mysqldump.

mysqldump --default-character-set=latin1 --opt --user=USERNAME -p whmcs_database > whmcs.sql

 

Make sure the following is included in your configuration.php file after convert

 

$mysql_charset = 'utf8';

Edited December 1, 2011 by EhsanCh

[Jbro]

EhsanCH

 

I did exactly as you instructed. When I open in emeditor or edit plus I can see all the fonts have successfully converted I mean I can see fonts in different languages appear but

when I import it to my site It does not work and characters are not readable.

 

Now my question are you sure your site is OK?

Did you go to phpmyadmin and try to view your old tickets and see if in phpmyadmin the language appears OK?

I mean inside tickets table or where ever you have that language?

 

one point I did not drop the old table I just imported it to my present database

Do I need to drop all tables and import fresh?

[nihkiruks]

I didn't checked the database with phpMyAdmin. There is also no sense in checking it via console as it'll depend on the character set for both console and mysql client. I've also converted the dump using iconv so have no idea about other editors. And sure you need to drop you DB before restoring it from dump. Don't forget to make all the needed backups