Problems setting connection charset
Hello MySQL gurus,
I've got a serious problem setting connection charset to utf8. I'm using MySQL C API with server version 4.1.13a on
two machines: one running RH 7.3 and another - Debian Sarge. Both systems' locale is set to en_US.UTF-8. ( this shouldn't matter though, right? My app that tries to connect to the DB runs on a different machine )
The following function in my app
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
bool ConnectToFrontend( DBInfo* node )
{
mysql_init(node->front_db);
if ( !mysql_real_connect( node->front_db,
node->front_ip,
node->front_login,
node->front_password,
node->front_path,
atoi(node->front_port ),
NULL, 0) )
{
return false;
}
char cmd[255];
strcpy( cmd, "SET NAMES \'utf8\'" );
if( mysql_real_query( node->front_db , cmd, strlen(cmd) ) != 0 )
{
return false;
}
strcpy( cmd , "SET CHARACTER SET \'utf8\'" );
if( mysql_real_query( node->front_db, cmd, strlen(cmd) ) != 0 )
{
return false;
}
printf( "Connection charset: %s" , mysql_character_set_name(node->front_db) );
return true;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
is supposed to connect to the database and set connection charset to utf8. However, the last printf stubbornly prints 'latin1', no matter what I do.
I've tried using the 'mysql_set_character_set()' function instead of executing 'SET NAMES' myself, but then I get a linker error ( the function is declared in mysql.h, but the linker cannot find its definition - shouldn't it be in libmysqll.lib ??? )
I've tried setting the 'init-connect='SET NAMES=utf8' in /etc/my.cnf, no difference.
Here is my /etc/my.cnf:
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8
[mysqld]
port = 3306
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_log_arch_dir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user = mysql
datadir = /var/lib/mysql
default-character-set=utf8
set-variable=max_connections=800
set-variable=open_files_limit=4096
set-variable=max_connect_errors=500
set-variable=connect_timeout=10
set-variable=thread_concurrency=8
set-variable=key_buffer=5M
set-variable=max_allowed_packet=16M
set-variable=sort_buffer=1M
set-variable=record_buffer=1M
set-variable=myisam_sort_buffer_size=5M
set-variable=table_cache=1024
set-variable=thread_cache_size=100
#init-connect='SET NAMES utf8'
[safe_mysqld]
open-files-limit = 8192
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The server is 4.1.13a . I've downloaded source from www.mysql.com and compiled it with utf8 support with
CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors \
-fno-exceptions -fno-rtti" ./configure \
--prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static --with-charset=utf8
make && make install
if I connect to the server from CLI, and execute
mysql> show variables like 'char%';
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
The actual database amd its tables were created without and charset-specific commands, although later on I've tried executing 'alter table XXXX convert to character set utf8' to no avail.
the question: WHAT THE HECK??? I'm at a loss here. No matter what I do, mysql_character_set_name() still returns 'latin1'. Please advise as this is driving me nuts and the deadline is dangerously close....
Thanks in advance,
Leszek Koltunski
PS. Even though the connection charset is 'latin1' and I insert Chinese characters , my app *can* select the characters back correctly. My app is thus fully working . The problem is though that a cow-orker is writing another part of the system in Java, he uses Connector-J, and when he retrieves the damn Chinese characters I've inserted, they get mangled. What gives???? ( also, when I try to select the characters using MySQL Query Browser, they get mangled )