Error odbc and cdr_adaptative Incorrect string value

Asterisk Asterisk Support
1

Hello guys.
I have an error when saving the cdr after disconnecting the Agent channel that is logged in.
I discovered that the error occurs when running dialplan gosub to unpause the agent if it hangs up while paused.
In the UnpauseQueueMember function exactly. But it is written according to the documentation.
In other call situations, the error does not occur.

Dialplan:
same => n,UnpauseQueueMember(,Local/${AgentNumber}@queue_agents)
same => n,Return

I found that if I remove the comma in the “UnpauseQueueMember(Local…” it doesn’t generate an error.

But I need to inform the queue in the function and I don’t want that.
UnpauseQueueMember([queuename,interface,[options,[reason]]])

The comma is causing the error in cdr_adaptative see the error example and what the AMI returns in the CDR event.

AMI Event:
Event: Cdr
Privilege: cdr,all
AccountCode: 8000
Source: 8000
Destination: h
DestinationContext: APPLICATION-20
CallerID: “8000 pjsip” <8000>
Channel: PJSIP/8000-000000a8
DestinationChannel:
LastApplication: xïùéb
LastData: pïùéb
StartTime: 2023-01-27 10:38:31
AnswerTime: 2023-01-27 10:38:39
EndTime: 2023-01-27 10:38:47
Duration: 16
BillableSeconds: 8
Disposition: ANSWERED
AMAFlags: DOCUMENTATION
UniqueID: 1674826711.608
UserField:
gateway_id:
carrier:
callrec: 1
callfile:
hangupcause:

Log Dialplan:
Executing [s@gosub-endagentlogin:7] UnpauseQueueMember(“PJSIP/8000-000000b0”, “,Local/1999@queue_agents”) in new stack
– Executing [s@gosub-endagentlogin:8] Return(“PJSIP/8000-000000b0”, “”) in new stack
[2023-01-27 11:07:01] WARNING[60842]: res_odbc.c:529 ast_odbc_print_errors: SQL Execute returned an error: 22007: [ma-3.1.9][10.5.15-MariaDB-0+deb11u1-log]Incorrect string value: ‘\xEF\xF9\xE9b\007F’ for column asterisk.cdr.lastapp at row 1
[2023-01-27 11:07:01] WARNING[60842]: res_odbc.c:424 ast_odbc_prepare_and_execute: SQL Execute error -1!
[2023-01-27 11:07:01] WARNING[60842]: cdr_adaptive_odbc.c:766 odbc_log: cdr_adaptive_odbc: Insert failed on ‘asterisk:cdr’. CDR failed: INSERT INTO cdr (calldate, answer, end, src, dst, dcontext, channel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, uniqueid, linkedid, sequence, callrec) VALUES ({ ts ‘2023-01-27 11:06:53’ }, { ts ‘2023-01-27 11:06:55’ }, { ts ‘2023-01-27 11:07:01’ }, ‘8000’, ‘h’, ‘APPLICATION-20’, ‘PJSIP/8000-000000b0’, ‘x���b’, ‘p���b’, 8, 6, ‘ANSWERED’, 3, ‘8000’, ‘1674828413.632’, ‘1674828413.632’, 269, 1)

Has anyone gone through this and could help me?

2

You need to raise this on issues.asterisk.org to stand a chance of getting it fixed.

There is also an error in the wiki, and as that is automatically generated from the markup in the comments, also an error in the documentation generator.

Putting a mandatory parameter after an optional one is not very clever, but it would probably break too much to fix that and it could only be done in the next major release, as it is a change of existing function.

3

Is there any other way to deal with this problem?

Ok I’ll report the problem on issues.asterisk.org.

Thank you for your help!

4

It could be your table collation or charset.

You can share more information using the command in the MySQL/MariaDB CLI.

SHOW CREATE TABLE asterisk.cdr;

On the other hand, from where come the strings below?
xïùéb
pïùéb

5

I assumed that the parameters were out of phase, and binary values had been treated as characters. Given that FreePBX is normally run on little-ending machines, these two valued are likely quite close, either time values, or possibly related pointers.

6

Previously I had already tried some things about this problem. I tried the command below,
ALTER TABLE cdr MODIFY lastapp varchar(80) CHARSET utf8mb4 COLLATE=‘utf8_general_ci’;
But it didn’t solve it.

Here is the return of SHOW CREATE TABLE :

src varchar(80) NOT NULL DEFAULT ‘’,
dst varchar(80) NOT NULL DEFAULT ‘’,
dcontext varchar(80) NOT NULL DEFAULT ‘’,
channel varchar(80) NOT NULL DEFAULT ‘’,
dstchannel varchar(80) NOT NULL DEFAULT ‘’,
**lastapp varchar(80) CHARACTER SET utf8mb4 DEFAULT NULL,**
lastdata varchar(80) NOT NULL DEFAULT ‘’,
duration int(10) unsigned NOT NULL DEFAULT 0,
billsec int(10) unsigned NOT NULL DEFAULT 0,
disposition varchar(45) NOT NULL DEFAULT ‘’,
amaflags int(4) unsigned NOT NULL DEFAULT 0,
accountcode varchar(80) DEFAULT NULL,
userfield varchar(255) NOT NULL DEFAULT ‘’,
uniqueid varchar(32) NOT NULL DEFAULT ‘’,
linkedid varchar(32) NOT NULL,
sequence int(10) unsigned NOT NULL,
peeraccount varchar(80) DEFAULT ‘’,
callednum varchar(80) DEFAULT NULL,
gateway_id int(10) unsigned DEFAULT NULL,
gateway_group int(10) unsigned DEFAULT NULL,
carrier varchar(25) DEFAULT NULL,
callrec tinyint(1) DEFAULT NULL,
callfile varchar(150) DEFAULT NULL,
calltype enum(‘in’,‘out’,‘ext’,‘inApp’,‘outApp’,‘transit’) DEFAULT NULL,
sip_out_ip varchar(15) DEFAULT ‘’,
sip_out_useragent varchar(255) DEFAULT ‘’,
hangupcause int(10) unsigned DEFAULT 0,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

7

I made several changes to test in the odbc driver and also in the database for utf8mb4 and for utf8, I didn’t get any results, I did a log collection, the problem always occurs when I finish the camal call with a logged in agent, thus generating the variable lastdata and lastapp with strange characters.

>>>>>>. Debug full log asterisk <<<<<<

[2023-02-01 14:59:26] DEBUG[292578] res_odbc.c: Reusing ODBC handle 0x7f75f8149bb0 from class ‘asterisk’
[2023-02-01 14:59:26] DEBUG[292578] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE id = ?
[2023-02-01 14:59:26] WARNING[292586] res_odbc.c: SQL Execute returned an error: 22007: [ma-3.1.9][10.5.18-MariaDB-0+deb11u1-log]Incorrect string value: ‘\xDFv0v\007F’ for column asterisk.cdr.lastapp at row 1
[2023-02-01 14:59:26] WARNING[292586] res_odbc.c: SQL Execute error -1!
[2023-02-01 14:59:26] WARNING[292586] cdr_adaptive_odbc.c: cdr_adaptive_odbc: Insert failed on ‘asterisk:cdr’. CDR failed: INSERT INTO cdr (calldate, answer, end, src, dst, dcontext, channel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, uniqueid, linkedid, sequence, callrec) VALUES ({ ts ‘2023-02-01 14:57:43’ }, { ts ‘2023-02-01 14:57:47’ }, { ts ‘2023-02-01 14:59:26’ }, ‘8000’, ‘h’, ‘APPLICATION-20’, ‘PJSIP/8000-00000006’, ‘x�v0v’, ‘p�v0v’, 102, 99, ‘ANSWERED’, 3, ‘8000’, ‘1675274263.18’, ‘1675274263.18’, 6, 1)
[2023-02-01 14:59:26] DEBUG[292586] res_odbc.c: Releasing ODBC handle 0x7f75f8168940 into pool
[2023-02-01 14:59:26] DEBUG[292586] stasis.c: Creating topic. name: channel:1675274366.19, detail:
[2023-02-01 14:59:26] DEBUG[292586] stasis.c: Topic ‘channel:1675274366.19’: 0x7f75d80154e0 created
[2023-02-01 14:59:26] DEBUG[292578] res_config_odbc.c: Parameter 1 (‘id’) = ‘8000’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(start) result is ‘2023-02-01 14:57:43’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(clid) result is ‘“8000 pjsip” <8000>’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(dcontext) result is ‘APPLICATION-20’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(channel) result is ‘PJSIP/8000-00000006’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(dstchannel) result is ‘’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(lastapp) result is ‘x�v0v’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(lastdata) result is ‘p�v0v’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(duration,f) result is ‘102.940045’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(billsec,f) result is ‘99.203213’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(disposition) result is ‘ANSWERED’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(amaflags) result is ‘DOCUMENTATION’
[2023-02-01 14:59:26] DEBUG[292586] pbx_variables.c: Function CDR(accountcode) result is ‘8000’

<<<<<< Debug verbose CLI >>>>>

Executing [s@gosub-endagentlogin:5] MYSQL(“PJSIP/8000-00000007”, “Disconnect 1”) in new stack
– Executing [s@gosub-endagentlogin:6] UnpauseQueueMember(“PJSIP/8000-00000007”, “,Local/1999@queue_agents”) in new stack
– Executing [s@gosub-endagentlogin:7] Return(“PJSIP/8000-00000007”, “”) in new stack
0x7f75d80214d0 - Transitioning CDR for PJSIP/8000-00000007 from state Single to Finalized
0x7f75d80214d0 - Beginning finalize/dispatch for PJSIP/8000-00000007
0x7f75d80214d0 - Dispatching CDR for Party A PJSIP/8000-00000007, Party B
[2023-02-01 15:05:48] WARNING[292586]: res_odbc.c:529 ast_odbc_print_errors: SQL Execute returned an error: 22007: [ma-3.1.9][10.5.18-MariaDB-0+deb11u1-log]Incorrect string value: ‘\xDFv0v\007F’ for column asterisk.cdr.lastapp at row 1
[2023-02-01 15:05:48] WARNING[292586]: res_odbc.c:424 ast_odbc_prepare_and_execute: SQL Execute error -1!
[2023-02-01 15:05:48] WARNING[292586]: cdr_adaptive_odbc.c:766 odbc_log: cdr_adaptive_odbc: Insert failed on ‘asterisk:cdr’. CDR failed: INSERT INTO cdr (calldate, answer, end, src, dst, dcontext, channel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, uniqueid, linkedid, sequence, callrec) VALUES ({ ts ‘2023-02-01 15:03:21’ }, { ts ‘2023-02-01 15:03:26’ }, { ts ‘2023-02-01 15:05:48’ }, ‘8000’, ‘h’, ‘APPLICATION-20’, ‘PJSIP/8000-00000007’, ‘x�v0v’, ‘p�v0v’, 146, 141, ‘ANSWERED’, 3, ‘8000’, ‘1675274601.21’, ‘1675274601.21’, 7, 1)

<<<< File /etc/odbc.ini >>>>

[asterisk]
Driver = MariaDB
Description = MariaDB connection to database
Server = 127.0.0.1
Port = 3306
Database = asterisk
UserName = asterisk
Password = xxxxxxxx
Socket = /var/run/mysqld/mysqld.sock
CharSet = UTF8

<<<<< File /etc/odbcinst.ini >>>>>

[MariaDB]
Driver=libmaodbc.so
Description=MariaDB Connector/ODBC(Unicode)
Threading=0
UsageCount=2

<<<< Mariadb Server version: 10.5.18-MariaDB-0+deb11u1-log Debian 11 >>>
show variables like ‘%coll%’;
±---------------------±----------------+
| Variable_name | Value |
±---------------------±----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
±---------------------±----------------+

show variables like ‘%cha%’;
±------------------------------±---------------------------+
| Variable_name | Value |
±------------------------------±---------------------------+
| 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/ |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | none |
| session_track_state_change | OFF |

show create database asterisk;
±---------±----------------------------------------------------------------------------------------+
| Database | Create Database |
±---------±----------------------------------------------------------------------------------------+
| asterisk | CREATE DATABASE asterisk /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci */ |

show create table cdr;

| cdr | CREATE TABLE cdr (
calldate datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
answer datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
end datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
clid varchar(80) NOT NULL DEFAULT ‘’,
src varchar(80) NOT NULL DEFAULT ‘’,
dst varchar(80) NOT NULL DEFAULT ‘’,
dcontext varchar(80) NOT NULL DEFAULT ‘’,
channel varchar(80) NOT NULL DEFAULT ‘’,
dstchannel varchar(80) NOT NULL DEFAULT ‘’,
lastapp varchar(80) DEFAULT NULL,
lastdata varchar(80) NOT NULL DEFAULT ‘’,
duration int(10) unsigned NOT NULL DEFAULT 0,
billsec int(10) unsigned NOT NULL DEFAULT 0,
disposition varchar(45) NOT NULL DEFAULT ‘’,
amaflags int(4) unsigned NOT NULL DEFAULT 0,
accountcode varchar(80) DEFAULT NULL,
userfield varchar(255) NOT NULL DEFAULT ‘’,
uniqueid varchar(32) NOT NULL DEFAULT ‘’,
linkedid varchar(32) NOT NULL,
sequence int(10) unsigned NOT NULL,
peeraccount varchar(80) DEFAULT ‘’,
callednum varchar(80) DEFAULT NULL,
carrier varchar(25) DEFAULT NULL,
callrec tinyint(1) DEFAULT NULL,
callfile varchar(150) DEFAULT NULL,
calltype enum(‘in’,‘out’,‘ext’,‘inApp’,‘outApp’,‘transit’) DEFAULT NULL,
sip_out_ip varchar(15) DEFAULT ‘’,
sip_out_useragent varchar(255) DEFAULT ‘’,
hangupcause int(10) unsigned DEFAULT 0,
CONSTRAINT CONSTRAINT_1 CHECK (callrec in (0,1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci |
±---------±----------------------------------------------------------------------------------------+

8

Has anyone gone through this and could help me?

I’m still looking for a solution, I found that it is when dialplan executes any exten h ( hangup ).

9

I found the problem.
After the gosub() context, the Return() function is executed. So far ok, but I had a context after that “h” that didn’t end with the line same => n,Hangup(), so the dialplan generated some problem that when cdr_adptative_odbc would save the lastapp and appdata variables, it would give an error because sent strange characters.

After ending the context with “same => n,Hangup()” the problem was eliminated.
Thanks to everyone who helped =D

1 Like
10

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.