Solved: convert MySQL db from Latin1 to UTF8

[mister_v]

Hi,

My MySQL-database is set to latin1,
but I want to convert it to UTF8.

I already added

Code: Select all

default-character-set=utf8

to /etc/mysql/my.cng

But I still get the following:

Code: Select all

Connection id:          371
Current database:       db
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.63-0ubuntu0.11.10.1 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysqld.sock

The server and the databse are still in latin1.
What else should I do?

[chris]

You probably set

Code: Select all

default-character-set=utf8

Under [client] in /etc/mysql/my.cnf

You also need to set it under [mysqld];
Actually

Code: Select all

character-set-server=utf8

would be better.

This should take care of the Server characterset.


For your database you have to do some additional steps.
in MySQL give the following command:

Code: Select all

ALTER DATABASE <database_name> CHARACTER SET utf8;

This sets the default of your database to utf8.
New tables will be created in utf8,
HOWEVER already created tables remain in the old character set.
This command converts the table:

Code: Select all

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8;

Always remember to do backup before you do anything.
I don't guaranty that all data is converted correctly.

You can check every steps with the following commands:
STATUS; : Show client,server and database settings.
SHOW TABLE STATUS; : Show all table settings.
SHOW FULL COLUMNS FROM <table_name>; : Shows info about the columns in a table.

[mister_v]

Thanks for the info.

I did everything for the database, but I still get some weird characters.
What else can it be?

[chris]

Are your text string wrong in the database, or do you see the results in you browser.

Because the problem with character set is that all programs need to know what character set is used.
Not only the database server (MySQL), but also the application server (php/apache, ASP/IIS, ...) and your browser (Internet Explorer, Firefox, Opera, ....)

[mister_v]

I do test it in my browser firefox.

[chris]

You can test if your server and browser are "speaking" in UTF-8 with printing the following string.

Code: Select all

$test='Iñtërnâtiônàlizætiøn';
echo $test."\n";

If you don't see the string correctly,
try adding the HTML line:

Code: Select all

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

In PHP you can also do this with adding the following line in your php-page:

Code: Select all

header('Content-Type: text/html; charset=utf-8');

They both do the same thing:
Telling your browser to read the page in the character-set UTF-8.

[chris]

If you still have problems.
you can also use, in php:

Code: Select all

mb_internal_encoding("UTF-8");

It tells php to do everything in UTF-8 character-set.

[mister_v]

Thanks,

Code: Select all

header('Content-Type: text/html; charset=utf-8');

Works for me.