MySQL errors after last update

freepbx
asterisk
Tags: #<Tag:0x00007fafc2a2f070> #<Tag:0x00007fafc2a2ef08>
(Azidhaka) #1

Hi,

The problem began after some updates were installed on my FreePBX 13.0.197.22, Asterisk 13.29.2 system last week. My extension names contain cyrillic, where the problem probably is.

First, all CEL insert queries started to fail:

[2020-02-09 07:42:51] WARNING[20290] res_odbc.c: SQL Execute returned an error: HY000: [MySQL][ODBC 5.1 Driver][mysqld-5.7.28-0ubuntu0.18.04.4]Data too long for column ‘appdata’ at row 1
[2020-02-09 07:42:51] WARNING[20290] res_odbc.c: SQL Execute error -1!
[2020-02-09 07:42:51] WARNING[20290] cel_odbc.c: 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 (‘APP_START’,{ts ‘2020-02-09 07:42:51.471548’},‘0892296793’,‘0892296793’,‘0892296793’,’’,‘046994001’,‘recordcheck’,‘sub-record-check’,‘SIP/Bulgaria-00058f78’,‘MixMonitor’,’/data/recordings/2020/02/09/in-046994001-0892296793-20200209-074251-1581226971.598369.wav,abi(LOCAL_MIXMON_ID),’,3,’’,‘1581226971.598369’,‘1581226971.598369’,’’,’’,’’)

I’ve extended the column ‘appdata’ to VARCHAR(255) to alleviate that.

After that, all the CDR insert queries started failing:

[2020-02-14 15:24:50] WARNING[26840] res_odbc.c: SQL Execute returned an error: HY000: [MySQL][ODBC 5.1 Driver][mysqld-5.7.28-0ubuntu0.18.04.4]Incorrect string value: ‘\xD0’ for column ‘lastdata’ at row 1
[2020-02-14 15:24:50] WARNING[26840] res_odbc.c: SQL Execute error -1!
[2020-02-14 15:24:50] WARNING[26840] cdr_adaptive_odbc.c: cdr_adaptive_odbc: Insert failed on ‘asteriskcdrdb:cdr’. CDR failed: INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,recordingfile,cnum,cnam,outbound_cnum) VALUES ({ ts ‘2020-02-14 15:23:59’ },’"" <0040316301139>’,‘0040316301139’,‘0040722727289’,‘qlog-queuedial’,‘SIP/229-00000462’,‘SIP/International-00000463’,‘Dial’,‘SIP/International/0040722727289,300,gM(queuedial-answer^1581686639.1487^229_С�’,51,38,‘ANSWERED’,3,‘Outbound’,‘1581686639.1487’,‘out-0040722727289-229-20200214-152359-1581686639.1487.wav’,‘229’,‘229 Склад Русе’,‘0040316301139’)

/etc/odbc.ini contains Charset=utf8
Both tables (cdr, cel) collation is utf8_unicode_ci

Please help me solve that.

(Franck Danard) #2

Hi
Maybe use mariadb-connector-odbc-3.0.8-ga-rhel7-x86_64
https://issues.freepbx.org/browse/FREEPBX-15268

#3

Possibly you are using a “supplementary character”

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html

(Azidhaka) #4

Looking at my question, i was not clear. The problems started after FreePBX modules got updated. Last week there were Core, Endpoint and some other. The problems started after updating them. The core OS has not had updates in months.

@danardf this system is FreePBX 13, based on CentOS 6 and that package is for 7. I found it for 6 and will do tests.

@dicko i already did check that. The database is as it has been for a couple of months, utf8. No changes there.
For the problem with Incorrect string value , on other systems (not FreePBX) people are converting their utf8 tables to utf8mb4 in order to hold the 4-byte UTF8 strings.
Maybe i should try that?
One other option is to rename all my extensions back to English, which would be ugly for my users.
Other option is to go back to older Core, before last week’s updates, if that’s possible.

(Jared Busch) #5

You can always downgrade modules.

But things have dependencies. Downgrading only core may cause other issues.