smsd + libmysqlclient charset changes after some time

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

smsd + libmysqlclient charset changes after some time

p.wassi
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
 

_______________________________________________
gnokii-users mailing list
[hidden email]
https://lists.nongnu.org/mailman/listinfo/gnokii-users
Reply | Threaded
Open this post in threaded view
|

Re: smsd + libmysqlclient charset changes after some time

Jan Derfinak
On Thu, 17 Jul 2014, [hidden email] wrote:

Hello.

> 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...

That is interesting and weird. Do I understand right, that there is no
explicit reconnecting from smsd side?
From the first look it seem to me like something internal in mysql client
library but I don't know for sure. Please continue testing and let us know
the results.

Jan

PS:
I found this link about mysql client debuging:
http://dev.mysql.com/doc/refman/5.1/en/debugging-client.html


--
_______________________________________________
gnokii-users mailing list
[hidden email]
https://lists.nongnu.org/mailman/listinfo/gnokii-users
Reply | Threaded
Open this post in threaded view
|

Re: Re: smsd + libmysqlclient charset changes after some time

p.wassi
Hello,

> Do I understand right, that there is no explicit reconnecting from smsd side?
smsd opens the mysql-connection with the MYSQL_OPT_RECONNECT option set,
so when the connection is lost, libmysqlclient tries to re-open that connection
itself (completely invisible for smsd) and re-sends the failed SQL-statement.
So, yes, there is no explicit reconnection from smsd side.

> From the first look it seem to me like something internal in mysql client
> library but I don't know for sure. Please continue testing and let us know
> the results.
My tests showed, that setting the character encoding just once when the
connection is opened, is sufficient.

Meanwhile, I had to migrate to a new server (from i386 to x64) and did a
clean install of Debian 7 + MySQL-Server + gnokii (both from Debian's repo),
and can now summarize: this setup will not work out-of-the-box when special
characters are needed. Altough all locales installed are UTF8-ones (en_GB.UTF8
being the default one), the connection to the database is opened as latin1
(the encoding is not specified by gnokii-smsd at all), which causes much trouble
when dealing with non-ASCII chars.

What I did to get these chars working: get the source code from Debian's repos,
and manually set the encoding to UTF8 in the DB_ConnectInbox() and DB_ConnectOutbox()
functions.

Just for reference (or to conclude): a smsd-mysql-setup as it is provided in Debian 7
will not work with non-ASCII characters, since the DB-connection's encoding does not
match on both ends, and/or the encoding changes by itself after some time.

An important notice: I've looked up the mysql.c file in gnokii's git, and have seen that
in 0.6.31 the Connect()-function utilizes a MYSQL_SET_CHARSET_NAME option (which was not done
before), so it seems that the whole issue was resolved in that version.
(Debian 7 is still on 0.6.30)
I'll give 0.6.31 a try (compiled manually) as soon as there is some free time (there are
still many things in the queue to get moved to the new server :) )

Best regards,
Paul

_______________________________________________
gnokii-users mailing list
[hidden email]
https://lists.nongnu.org/mailman/listinfo/gnokii-users