-1

I use Asterisk 16.5.0, Mysql 5.7.28 and MySQL ODBC 8.0 Unicode Driver. I configured connection with MySql via ODBC connection. After some time Asterisk shows warning like that.

[2019-12-11 15:34:06] WARNING[1995]: res_odbc.c:538 ast_odbc_print_errors: SQL Prepare returned an error: 42S22: [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.28-log]Unknown column 'data' in 'field list'
[2019-12-11 15:34:06] WARNING[1995]: res_config_odbc.c:122 custom_prepare: SQL Prepare failed! [INSERT INTO queue_log (time, data, event, agent, queuename, callid) VALUES (?, ?, ?, ?, ?, ?)]

After restarting Asterisk, the problem does not occur. But after some time problem begins occur.

Ramin Darvishov
  • 1,043
  • 1
  • 15
  • 30

4 Answers4

0

Sounds like your queue_log table is missing the data field, It should look like :

CREATE TABLE `queue_log` (
  `recid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `origid` int(10) unsigned NOT NULL DEFAULT '0',
  `callid` varchar(32) NOT NULL DEFAULT '',
  `queuename` varchar(32) NOT NULL DEFAULT '',
  `agent` varchar(32) NOT NULL,
  `event` varchar(32) NOT NULL DEFAULT '',
  `data` varchar(100) NOT NULL DEFAULT '0',
  `data1` varchar(128) NOT NULL DEFAULT '0',
  `data2` varchar(128) NOT NULL DEFAULT '0',
  `data3` varchar(128) NOT NULL DEFAULT '0',
  `data4` varchar(128) DEFAULT NULL,
  `data5` varchar(128) DEFAULT NULL,
  `time` datetime DEFAULT '1970-01-01 00:00:01',
  `datetime` datetime DEFAULT '1970-01-01 00:00:01'
);
Nasir Iqbal
  • 909
  • 7
  • 24
Eddy_FL
  • 54
  • 1
  • 4
  • i mentioned that after restart warning does not occur. if problem is that queue_log table is missing the data field after restart problem must be again occur. – Ramin Darvishov Dec 12 '19 at 16:28
0

I still cannot understand that why command odbc show returns 

Number of active connections: 1 (out of 1)

but connection is broken. It looks from database (nothing inserts to tables like cdr, cel, queue_log) and also i looked connected users from mysql with command show processlist. Really which user Asterisk connects to Mysql not exist in connectected users. What does show command odbc show? Or may be exist some bug in odbc show?

Main question: How can i increase timeout time between Asterisk and Mysql (via ODBC connection)?

Ramin Darvishov
  • 1,043
  • 1
  • 15
  • 30
  • From the other forum Asterisk Technical Lead wrote this: ODBC is an abstraction layer so stuff can happen underneath in the ODBC part of the MySQL connector part, of which Asterisk is unaware. I think this is may right thought. https://community.asterisk.org/t/asterisk-mysql-odbc-unknown-column-data-in-field-list/82389/4 – Ramin Darvishov Jan 21 '20 at 15:38
0

I know some metod which solve this problem. Every 1 hour execute command with cron job.

asterisk -rx 'reload'

With this command Asterisk reload resumes connection or restore died connection. But I think that is not a professional approach.

Ramin Darvishov
  • 1,043
  • 1
  • 15
  • 30
0

I found the problem. Problem is that mysql starts before asterisk start. After some versions of Mysql service name renamed from mysql to mysqld. Because in startup script Asterisk (location on Centos is /etc/init.d/asterisk) we must change also.

# Should-Start:      dahdi misdn lcr wanrouter mysql postgresql

change to

# Should-Start:      dahdi misdn lcr wanrouter mysqld postgresql
Ramin Darvishov
  • 1,043
  • 1
  • 15
  • 30