HowTo: Converting database to UTF8

[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-c...-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';

[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

[Jbro]

Hi No I am talking to EhsanCH

OK but I think you need to go to phpmyadmin in in ticket tables see for real your characters have changed

[EhsanCh]

are you sure you did this steps : ?

-save file as utf8 with signator encoding.
-Make sure the following is included in your configuration.php file after convert
$mysql_charset = 'utf8';

[Jbro]

Please go to your phpmyadmin and view your ticket tables and you will see the characters are not correct. Your DB is not utf8 YOU ARE Wrong.

[abdofikry]

my database is in latin1 but i have some content in tickets and other tables already in utf8
and i converted like you said .

the result is that tickets data that was already in utf8 was converted to utf8 so it got corrupted and became unreadable .

how can we fix that ? is there a way of preserving the aleady utf8 data during the convertion ? or a way to fix the corrupted data after convertion ??

[rabia123]

Go to the following this is the step by step tutorials:

docs.moodle.org/22/en/Converting_your_MySQL_database_to_UTF8