Solved: convert MySQL db from Latin1 to UTF8

Posts regarding the MySQL database server
Post Reply
mister_v
Posts: 188
Joined: Thu Mar 04, 2010 9:19 pm

Solved: convert MySQL db from Latin1 to UTF8

Post by 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?
Last edited by mister_v on Sat Sep 15, 2012 11:26 am, edited 1 time in total.
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: convert MySQL db from Latin1 to UTF8

Post by 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
Posts: 188
Joined: Thu Mar 04, 2010 9:19 pm

Re: convert MySQL db from Latin1 to UTF8

Post by 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
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: convert MySQL db from Latin1 to UTF8

Post by 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
Posts: 188
Joined: Thu Mar 04, 2010 9:19 pm

Re: convert MySQL db from Latin1 to UTF8

Post by mister_v »

I do test it in my browser firefox.
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: convert MySQL db from Latin1 to UTF8

Post by 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
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: convert MySQL db from Latin1 to UTF8

Post by 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
Posts: 188
Joined: Thu Mar 04, 2010 9:19 pm

Re: convert MySQL db from Latin1 to UTF8

Post by mister_v »

Thanks,

Code: Select all

header('Content-Type: text/html; charset=utf-8');
Works for me.
Post Reply