Asterisk MariaDB with ODBC connector

Asterisk Asterisk Support
1

Hello,

I have installed asterisk 13.18.cert2 on a server. I have configured MariaDB locally and installed and configured the odbc connector. So far CDR seems to be working fine, my SIP user registrations are also stored on the database and those work fine.

When I am debugging asterisk I see this message spamming every 5 seconds or so

[Jan 15 15:00:44] WARNING[29307][C-000003dd]: res_config_odbc.c:120 custom_prepare: SQL Prepare failed! [SELECT * FROM sip_users WHERE host = ? AND callbackextension = ? AND port = ?]

I have tried taking a tcpdump and looked at the PCAP, I am not seeing any sql queries there. Any idea on where I should look to pinpoint the issue here?

Thanks in advance for any assistance.

2

whats in your extconfig.conf?

3

[settings]
sipusers => odbc,asterisk,sip_users
sippeers => odbc,asterisk,sip_users
voicemail => odbc,asterisk,voicemail
extensions => odbc,asterisk,extensions
did => odbc,asterisk,did

4

whats the schema on the sip_users table

5

Auto increment set to :789505
Default collation set to: latin1_swedish_ci
engine set to: MyISAM

CREATE TABLE dummy (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(80) NOT NULL DEFAULT '',
	`accountcode` VARCHAR(20) NOT NULL DEFAULT '0',
	`amaflags` VARCHAR(7) NULL DEFAULT NULL,
	`callgroup` VARCHAR(10) NULL DEFAULT NULL,
	`callerid` VARCHAR(80) NULL DEFAULT NULL,
	`canreinvite` CHAR(3) NULL DEFAULT 'yes',
	`context` VARCHAR(80) NULL DEFAULT NULL,
	`defaultip` VARCHAR(15) NULL DEFAULT NULL,
	`dtmfmode` VARCHAR(7) NULL DEFAULT NULL,
	`directmedia` ENUM('yes','no','nonat','update') NULL DEFAULT NULL,
	`fromuser` VARCHAR(80) NULL DEFAULT NULL,
	`fromdomain` VARCHAR(80) NULL DEFAULT NULL,
	`fullcontact` VARCHAR(80) NULL DEFAULT NULL,
	`host` VARCHAR(31) NOT NULL DEFAULT '',
	`insecure` VARCHAR(4) NULL DEFAULT NULL,
	`language` CHAR(2) NULL DEFAULT NULL,
	`mailbox` VARCHAR(50) NULL DEFAULT NULL,
	`md5secret` VARCHAR(80) NULL DEFAULT NULL,
	`nat` VARCHAR(5) NOT NULL DEFAULT 'no',
	`deny` VARCHAR(95) NULL DEFAULT NULL,
	`permit` VARCHAR(95) NULL DEFAULT NULL,
	`mask` VARCHAR(95) NULL DEFAULT NULL,
	`pickupgroup` VARCHAR(10) NULL DEFAULT NULL,
	`port` VARCHAR(5) NOT NULL DEFAULT '',
	`qualify` CHAR(3) NULL DEFAULT NULL,
	`restrictcid` CHAR(1) NULL DEFAULT NULL,
	`rtptimeout` CHAR(3) NULL DEFAULT NULL,
	`rtpholdtimeout` CHAR(3) NULL DEFAULT NULL,
	`secret` VARCHAR(80) NULL DEFAULT NULL,
	`type` VARCHAR(6) NOT NULL DEFAULT 'friend',
	`username` VARCHAR(80) NOT NULL DEFAULT '',
	`disallow` VARCHAR(100) NULL DEFAULT 'all',
	`allow` VARCHAR(100) NULL DEFAULT 'g729;ilbc;gsm;ulaw;alaw',
	`mohsuggest` VARCHAR(100) NULL DEFAULT NULL,
	`regseconds` INT(11) NOT NULL DEFAULT '0',
	`ipaddr` VARCHAR(15) NOT NULL DEFAULT '',
	`regexten` VARCHAR(80) NOT NULL DEFAULT '',
	`cancallforward` CHAR(3) NULL DEFAULT 'yes',
	`mohinterpret` VARCHAR(45) NULL DEFAULT NULL,
	`extension` INT(10) UNSIGNED NOT NULL,
	`progressinband` VARCHAR(15) NULL DEFAULT NULL,
	`callingpres` VARCHAR(100) NULL DEFAULT NULL,
	`useragent` VARCHAR(120) NULL DEFAULT NULL,
	`lastms` VARCHAR(40) NULL DEFAULT NULL,
	`regserver` VARCHAR(80) NULL DEFAULT NULL
6

If you check on the query system is looking for callback extension which is not present on the table sip_users structure, did you use Alembic to create the db structure?

7

I had asterisk 1.8 installed and running and moved over/updated my configs to asterisk 13. Do I need to have a callbackextension column? And if so can i just null it out?

8

Did you use Alembic for the deb schema ?

9

check asterisk source’s realtime schema and compare with your sip_user table.

[ippbx@asteriskcti tmp] cd asterisk-16.7.0/contrib/realtime/mysql/ [ippbx@asteriskcti mysql] ll
total 52
-rw-rw-r-- 1 ippbx ippbx 1059 Dec 24 01:43 mysql_cdr.sql
-rw-rw-r-- 1 ippbx ippbx 41928 Dec 24 01:43 mysql_config.sql
-rw-rw-r-- 1 ippbx ippbx 1021 Dec 24 01:43 mysql_voicemail.sql

10

It needs to have a callbackextension field.

callbackextension VARCHAR(40),

11

Thank you everyone, added the ‘callbackextension’ field in my database and that has solved the issue!

12

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