Garbled UTF-8 characters with MySQL and utf8 charset/utf8_general_ci collation.

[GoogleCodeExporter]

Basically LuaDBI will read characters like İ as ? and Ç as ▒, when getting 
results after executing a query.

Original issue reported on code.google.com by maranda3...@gmail.com on 21 Aug 2013 at 10:42

[GoogleCodeExporter]

Effectively connection collation was wrong but still LuaDBI should allow 
specifying a connection collation, in the Connection Object.

Original comment by maranda3...@gmail.com on 21 Aug 2013 at 11:00

[GoogleCodeExporter]

Connection collation and other parameters are handled via SQL commands such as 
'SET NAMES'. This is the standard way to do it.

Original comment by MWild1 on 21 Aug 2013 at 11:19

[GoogleCodeExporter]

Matthew, yes I know and did test that, but that produces no result the output 
is still garbled.

I did run via LuaDBI:
SET collation_connection = utf8_general_ci;
SET collation_server = utf8_general_ci;
SET NAMES utf8;

but result is still garbled:
| name  секунд узнаем
| Result: nil

related session variables are _all_ set to use utf8:
| character_set_client  utf8
| character_set_connection  utf8
| character_set_database    utf8
| character_set_filesystem  binary
| character_set_results utf8
| character_set_server  utf8
| character_set_system  utf8
| character_sets_dir    /usr/share/mysql/charsets/
| collation_connection  utf8_general_ci
| collation_database    utf8_general_ci
| collation_server  utf8_general_ci

More then this I have no idea what else is needed to get clean UTF-8 output.

Original comment by maranda3...@gmail.com on 22 Aug 2013 at 2:21

[GoogleCodeExporter]

How did you create your database,

Normally 

CREATE DATABASE database_name DEFAULT CHARACTER SET utf8 DEFAULT COLLATE 
utf8_general_ci; 

should be enough.

Dont forget to create your tables with this at the end: create table .... 
DEFAULT CHARSET=utf8 

And instead of sending set names sql with every sql query, you can set it in 
your mysql conf only once.

under [mysqld]
add these 2 rows:
character_set_server    = utf8
collation_server        = utf8_general_ci

That's all.
SkyBlue

Original comment by cenkil...@gmail.com on 22 Aug 2013 at 11:35

[GoogleCodeExporter]

> More then this I have no idea what else is needed to get clean UTF-8 output.

Note that data put into the database with the wrong connection parameters will 
be translated incorrectly by MySQL. So test again with new data, storing and 
retrieving, with the new connection parameters.

Original comment by MWild1 on 22 Aug 2013 at 11:37

[GoogleCodeExporter]

SkyBlue, I didn't create it but the DB is created with the correct statements 
and has utf8 charset and collation.

MattJ, that's it, perhaps I suppose the data in the db could have been inserted 
with the wrong connection collation.

Original comment by maranda3...@gmail.com on 22 Aug 2013 at 11:57

[GoogleCodeExporter]

Original comment by MWild1 on 30 Jul 2014 at 10:08

• Changed state: Invalid