Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I tried to change the charset for our MySQL via my.cnf and it failed. I added the charset settings listed below:

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# ------------- MYSQL CHARSETS --------------
character_set_system = utf8
character_set_server = utf8
character_set_results = utf8
character_set_database = utf8
character_set_connection = utf8
character_set_client = utf8

collation_connection = utf8_general_ci
collation_database = utf8_general_ci
collation_server = utf8_general_ci

When I had these enabled, MySQL failed to start. How can I make sure everything defaults to utf8 - always?

Update

There are still a few places having issues in being UTF8

character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci

Unfortunately I still haven't come up with a soluton... any ideas?

Currently I'm using this configuration successfully:

[mysqld]
default-character-set = utf8
init-connect='SET NAMES utf8'
character-set-server = utf8


[mysql]
default-character-set = utf8

But PHPMyAdmin is still showing some latin1

character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci
share|improve this question

migrated from stackoverflow.com Nov 23 '11 at 7:41

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers

You need to incorporate the following into your my.cnf file

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
default-character-set = utf8    
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

You can find more information on the following manual pages

In response to Mark's answer, if you need to convert existing tables/databases to the correct character set you will need a suitable ALTER query. Something similar to the below:

ALTER TABLE `table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Alternatively there's a great PHP script here which does it for you.

share|improve this answer
    
I've followed your instructions and checked the documentation links... still having a few issues (see ^) –  Webnet Nov 21 '11 at 20:22
    
Whenever I add collation-server = utf8_general_ci to the mysqld section mysql fails to restart –  Webnet Nov 21 '11 at 20:26
    
Can you look what's in the log file? It should give you an error message as to why it failed. I take it your mysqld was compiled with the utf8 general character set included? –  Ben Swinburne Nov 21 '11 at 21:50
    
Yes I think it was... which log file? –  Webnet Nov 22 '11 at 18:00
    
They're in various places- usually /var/log/. Which cos are you running? Look for mysql.log mysql.err anything in a mysql folder if it exists. Also look in /var/log/messages –  Ben Swinburne Nov 22 '11 at 18:33
add comment

Ben's answer is ok, but changing the settings will ABSOLUTELY NOT alter existing tables character sets or collations. Do that with an appropriate ALTER TABLE statement.

Note that if you don't specify it, tables are created in the database default character set. This is a per-database setting which can be adjusted with ALTER DATABASE. This is also not set in my.cnf.

share|improve this answer
    
New databases will inherit the default mysql settings though, right? –  Webnet Nov 21 '11 at 19:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.