Hello,
I've been running gnokii's smsd with a mysql database for a few years now,
but have experienced encoding problems multiple times.
Now I've finally managed to have some free time to dig a bit more into that
problem:
(In this thread I only want to focus on the problem with the "from GSM to database"
direction of SMS.)
Directly after having started smsd, everything works fine.
An incoming SMS with special characters is successfully detected as
"unicode message" by smsd, and also inserted into the database table
without any problems. (Verified with debug and xdebug on)
(Also checked the SQL INSERT-statement: the encoding of "buf" is correct
before running mysql_real_query() )
To cut things short, I've printed an output of some mysql-variables right before
inserting a new SMS. At this point in time, these variables read:
> 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
Now after some time has passed (usually about 12 hours after having started smsd)
inserting unicode messages does not work properly any longer.
The INSERT-statement's encoding is still correct (i.e. gnokii/smsd handles
the message without any problems), but the mysql-variable output
reveals, the mysql-connection's encoding has changed:
> character_set_client - latin1
> character_set_connection - latin1
> character_set_database - utf8
> character_set_filesystem - binary
> character_set_results - latin1
> character_set_server - utf8
> character_set_system - utf8
> character_sets_dir - /usr/share/mysql/charsets/
> collation_connection - latin1_swedish_ci
> collation_database - utf8_general_ci
> collation_server - utf8_general_ci
All (unicode) messages inserted into the database now contain
character garbage.
As a workaround, I've successfully tested this procedure:
-) make a query "SET NAMES 'utf8';"
-) and mysql_set_character_set(&mysqlIn, "utf8")
in the DB_InsertSMS function right before
actually making the query to insert the message.
Now I don't know if this problem is related to smsd-msql or
simply the libmysqlclient or anything else. Maybe the mysql-client
does something unexpected when reconnecting to a server.
I have to note that after every restart of smsd there is no problem
for about 12 hours.
Currently I'm running a test if the "SET NAMES 'utf8';" statement
during the DB_ConnectInbox() function is enough...
My setup: Debian Wheezy, gnokii+smsd from debian (i.e. 0.6.30),
mysql 5.5.37.
Best regards,
Paul