Tag: C++

Additional mysql server UTF8 confutation

I spent a few day with patching LinuxMCE database connection to pass UTF8 option to the server. But without luck. However I found a way to configure mysql server to skip client’s request about charset and send all data in the defined one. To do that just add following lines in the my.cnf file under mysqld section:
[mysqld]
init_connect='SET NAMES utf8; SET collation_connection = utf8_general_ci;'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

The option init_connect replaces setting ‘SET NAMES utf8’ from the client and skip-character-set-client-handshake tells to server to ignore charset sent by client and use it default one instead.

I tested this approach with LinuxMCE and it works. The Russian text is displayed on the Orbiter correctly. I tested it with Perl as well and found that Perl script still should set option mysql_enable_utf8 to true.

[via Saiweb]

Perl, C++, mysql and UTF8

Recently I finished with Russian translation of LinuxMCE UI. But as I know now it was the easiest part. After addition Russian descriptions in the database I faced with problem of displaying them. I made a research and found following. To have UTF8 data in the mysql database a few steps should be done:

  • default charset for server should be set to utf8 in the /etc/mysql/my.cnf:
    [mysqld]
    default-character-set=utf8
  • default charset for client should be set to utf8 in the /etc/mysql/my.cnf:
    [client]
    default-character-set=utf8

    After that the charset for mysql shell will be UTF8 but not latin1 as in the stock version.
  • default charset for desired database should be set to utf8:
    alter database pluto_main charset=utf8;
  • default charset for desire table should be set to utf8:
    alter table Text_LS charset=utf8;
  • default charset for all text fields in the table should be also set to utf8:
    alter table Text_LS modify column `Description` longtext CHARACTER SET utf8 COLLATE utf8_general_ci

    • the client application should pass UTF8 flag to tell mysql about charset for connection. Here is a Perl example:
    my $dbh = DBI->connect("dbi:mysql:pluto_main;host=localhost", "root") or die "Cannot connect to database: $DBI::err!";
    $dbh->{'mysql_enable_utf8'} = 1;
    $dbh->do('SET NAMES utf8');

To display UTF8 text in the Perl script just set UTF8 charset for STDOUT:
binmode STDOUT, ":utf8";

But with LinuxMCE the situation is more complicate. Its UI is developed on C++. So, After a googling I found the way to set UTF8 for connection. There is a function mysql_options() in the Mysql C API. It should be called after mysql_init() but before mysql_connect() or mysql_real_connect() and allows to set desire charset:
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, 'utf8');
and run some SQL statement when connecting to the MySQL server:
mysql_options(&mysql, MYSQL_INIT_COMMAND, 'SET NAMES utf8');
But it didn’t help. The Russian text is still displayed as question mark. So, have to dig LinuxMCE code to solve that. Otherwise the Russian translation won’t be added.