Incorrect handling utf8mb4 with Cyrillic in SNG7-FPBX (14.0.1rc1.11)

Incorrect handling utf8mb4 with Cyrillic symbols in MariaDB, module CDR Reports.

Please show us the inside of /etc/odbc.ini

cat /etc/odbc.ini

[MySQL-asteriskcdrdb]
Description=MySQL connection to ‘asteriskcdrdb’ database
driver=MySQL
server=localhost
database=asteriskcdrdb
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8

This is actually related to this:

https://issues.freepbx.org/browse/FD7-23

I’ll have a look at upgrading the ODBC connector in SNG7 next week, and we may be able to fix this properly

In FreePBX 13, there is no such problem.
The problem is only in the SNG7 … With the transition to CentOS 7

Cool! Thank you.

OK, it seems to be working - can you please run this on your SNG7 machine:

yum install sangoma-devel

and then

yum update

That will activate the testing repo, and then yum update should pull down the new mysql-connector RPM

Resolving Dependencies
--> Running transaction check
---> Package mysql-connector-odbc.x86_64 0:5.2.5-6.el7 will be updated
---> Package mysql-connector-odbc.x86_64 0:5.3.6-1.el7 will be an update
--> Finished Dependency Resolution

After you’ve installed that, you’ll need to restart Asterisk, and hopefully the CDRs should then be correct!

Alas, the problem is again.

cat /etc/odbc.ini

[MySQL-asteriskcdrdb]
Description=MySQL connection to 'asteriskcdrdb' database
driver=MySQL
server=localhost
database=asteriskcdrdb
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8

cat /etc/odbcinst.ini

[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[MySQL ODBC 5.3 Unicode Driver]
Driver=/usr/lib64/libmyodbc5w.so
UsageCount=1

[MySQL ODBC 5.3 ANSI Driver]
Driver=/usr/lib64/libmyodbc5a.so
UsageCount=1

You’ll need to change your Driver in /etc/odbc.ini to use the ANSI driver (or the Unicode driver?)

Enabled ANSI driver, CDR Reports works correctly, but the CEL table:

[2017-05-16 21:08:20] WARNING[2655]: cel_odbc.c:773 odbc_log: Insert failed on 'asteriskcdrdb:cel'.  CEL failed: INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('CHAN_START',{ts '2017-05-16 21:08:20.943455'},'Алексей Иванович','202','','','','*65','from-internal','SIP/202-00000000','','',3,'','1494968900.0','1494968900.0','','','')
[2017-05-16 21:08:20] WARNING[2655]: cel_odbc.c:773 odbc_log: Insert failed on 'asteriskcdrdb:cel'.  CEL failed: INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('ANSWER',{ts '2017-05-16 21:08:20.945029'},'Алексей Иванович','202','202','','*65','*65','from-internal','SIP/202-00000000','Answer','',3,'','1494968900.0','1494968900.0','','','')

Driver Unicode not working ;(

cat /etc/odbc.ini

[MySQL-asteriskcdrdb]
Description=MySQL connection to 'asteriskcdrdb' database
driver=MySQL-ANSI
server=localhost
database=asteriskcdrdb
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8

cat /etc/odbcinst.ini

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[MySQL-Unicode]
Driver=/usr/lib64/libmyodbc5w.so
UsageCount=1

[MySQL-ANSI]
Driver=/usr/lib64/libmyodbc5a.so
UsageCount=1

It looks like a problem in the driver libmyodbc5w in CentOS 7 :confused:

Can you see WHY it’s failing? You may need to turn on odbc debugging (and possibly look in the mariadb logs)

I had a similar problem last week and it was unrelated to Unicode settings. Check to see if your version of MariaDB has ‘context’ listed as a reserved word. If it does, you may need to include the field names in back ticks (` next to the 1 key) to escape them and get the insert to work.

As a test, log into the MariaDB CLI and try inserting the query from there. You should get a better feel for the error on the insert.

I’m finding that newer versions of MySQL and MariaDB both work better with the field names consistently ensconced in back ticks. It’s something I’ve started doing on all of my other database projects as a prophylactic against wasting another 9 hours trying to figure it out again.

I ran the failed commands manually and they worked. Asterisk gives the full query so it’s easy enough to run.

MariaDB [asteriskcdrdb]> INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('ANSWER',{ts '2017-05-16 21:08:20.945029'},'Алексей Иванович','202','202','','*65','*65','from-internal','SIP/202-00000000','Answer','',3,'','1494968900.0','1494968900.0','','','');
Query OK, 1 row affected (0.00 sec)

MariaDB [asteriskcdrdb]> INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('CHAN_START',{ts '2017-05-16 21:08:20.943455'},'Алексей Иванович','202','','','','*65','from-internal','SIP/202-00000000','','',3,'','1494968900.0','1494968900.0','','','');
Query OK, 1 row affected (0.00 sec)

That’s just strange. Unless @klisrod has something strange going on, it should fail for both or work for both.

How to enable debugging odbc?

The records are all in the CEL table, but Asterisk writes Warning.

WARNING[2536]: cel_odbc.c:773 odbc_log: Insert failed on 'asteriskcdrdb:cel'.  CEL failed: INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('CHAN_START',{ts '2017-05-17 17:30:55.536305'},'Алексей Иванович','202','','','','*65','from-internal','SIP/202-00000001','','',3,'','1495042255.1','1495042255.1','','','')
WARNING[2536]: cel_odbc.c:773 odbc_log: Insert failed on 'asteriskcdrdb:cel'.  CEL failed: INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('ANSWER',{ts '2017-05-17 17:30:55.537168'},'Алексей Иванович','202','202','','*65','*65','from-internal','SIP/202-00000001','Answer','',3,'','1495042255.1','1495042255.1','','','')

WARNING[2532]: cdr_adaptive_odbc.c:748 odbc_log: cdr_adaptive_odbc: Insert failed on 'asteriskcdrdb:cdr'.  CDR failed: INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,lastapp,lastdata,duration,billsec,disposition,amaflags,uniqueid,cnum,cnam) VALUES ({ ts '2017-05-17 17:30:55' },'"Алексей Иванович" <202>','202','en','app-speakextennum','SIP/202-00000001','Playback','extension',1,1,'ANSWERED',3,'1495042255.1','202','Алексей Иванович')
WARNING[2536]: cel_odbc.c:773 odbc_log: Insert failed on 'asteriskcdrdb:cel'.  CEL failed: INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('HANGUP',{ts '2017-05-17 17:30:57.462223'},'Алексей Иванович','202','202','','*65','en','app-speakextennum','SIP/202-00000001','','',3,'','1495042255.1','1495042255.1','','','{"hangupcause":0,"hangupsource":"SIP/202-00000001","dialstatus":""}')
WARNING[2536]: cel_odbc.c:773 odbc_log: Insert failed on 'asteriskcdrdb:cel'.  CEL failed: INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('CHAN_END',{ts '2017-05-17 17:30:57.463269'},'Алексей Иванович','202','202','','*65','en','app-speakextennum','SIP/202-00000001','','',3,'','1495042255.1','1495042255.1','','','')
WARNING[2536]: cel_odbc.c:773 odbc_log: Insert failed on 'asteriskcdrdb:cel'.  CEL failed: INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('LINKEDID_END',{ts '2017-05-17 17:30:57.463526'},'Алексей Иванович','202','202','','*65','en','app-speakextennum','SIP/202-00000001','','',3,'','1495042255.1','1495042255.1','','','')


MariaDB [asteriskcdrdb]> INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('CHAN_START',{ts '2017-05-17 17:30:55.536305'},'Алексей Иванович','202','','','','*65','from-internal','SIP/202-00000001','','',3,'','1495042255.1','1495042255.1','','','');
Query OK, 1 row affected (0.00 sec)

MariaDB [asteriskcdrdb]> INSERT INTO cel (eventtype,eventtime,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,uniqueid,linkedid,peer,userdeftype,extra) VALUES ('ANSWER',{ts '2017-05-17 17:30:55.537168'},'Алексей Иванович','202','202','','*65','*65','from-internal','SIP/202-00000001','Answer','',3,'','1495042255.1','1495042255.1','','','');
Query OK, 1 row affected (0.00 sec)

The problem in the ODCB driver.

What can I do with ODCB or Asterisk?

I had a thought on this.

This is an “INSERT”, which means the record cannot be present in the database. We know the syntax of the record is correct and when tried from the command line, it works.

What if you are using the same database for two different connections? Suppose, for a second, that you are successfully writing to the same database and table with all three interfaces. If the record exists from the first connection, it will fail in the second.

There are two ways to solve this. The first is the dreaded “INSERT IGNORE” which most true believers despise, but would work well in this scenario. The second is to use the “REPLACE” command instead of the “INSERT”. Since the three inserts would be the same, the “REPLACE” would work and sufficiently avoid the duplicate INSERT.

Since we now know that the record is actually getting written to the database, this scenario is suddenly a lot more plausible.