Asterisk 13.14 with ODBC - cannot modify database

Asterisk Asterisk Support
1

Hi everyone, I have asterisk 13.14 with odbc driver and MariaDB. ODBC driver is connected:
Database (MariaDB) is running and ok:

```root@asterisk:/etc/asterisk# echo “INSERT INTO `last_listener (tel_cislo) VALUES (‘1234’);” | isql -v asterisk-connector

root@asterisk:/etc/asterisk# echo "SELECT tel_cislo FROM last_listener LIMIT 1;"  | isql -v asterisk-connector
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT tel_cislo FROM last_listener LIMIT 1;
+----------+
| tel_cislo|
+----------+
| 1234     |
+----------+
SQLRowCount returns 1
1 rows fetched
SQL> root@asterisk:/etc/asterisk#

Asterisk is “satisfied”:

asterisk*CLI> odbc show
ODBC DSN Settings
-----------------
Name:   asterisk
DSN:    asterisk-connector
Last connection attempt: 1969-12-31 18:00:00
Number of active connections: 1 (out of 1)

But if i run command from Asterisk…

asterisk*CLI> odbc write ODBC_DUMMY  123 exec
INSERT INTO last_listener (tel_cislo) VALUES ('123')
asterisk*CLI>

Database is not modify …
By read - nothing…

asterisk*CLI> odbc read ODBC_DUMMY  exec
SELECT tel_cislo FROM last_listener LIMIT 1
asterisk*CLI>

My test files:

root@asterisk:/etc/asterisk# tail func_odbc.conf
[DUMMY]
dsn=asterisk
readsql=SELECT tel_cislo FROM last_listener LIMIT 1
writesql=INSERT INTO last_listener (tel_cislo) VALUES ('${ARG1}')
root@asterisk:/etc/asterisk#

root@asterisk:/etc/asterisk# tail res_odbc.conf
[asterisk]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => replace_with_strong_password
pre-connect => yes
root@asterisk:/etc/asterisk#

Have You any idea for me - stupid?

2

The usual suspect is selinux.

3

Unfortunately.
Selinux is disabled but problem remains. ( Debian 4.9.88-1+deb9u1 (2018-05-07))

4

Any log message on the Asterisk cli when you try to write the DB ?

5

I had a db problem: after working perfectly for many weeks, Asterisk would suddenly be unable to write to the database, even though it was connected through the odbc driver. Restarting Asterisk temporarily fixed the problem. I didn’t see the problem again after an upgrade, I’m currently on 13.22.

Regards

6

I recompiled Asterisk to version 15. The same with mariadb odbc. But no working.
Database is running ok. (remote connect)
image
From linux machine with odbc - no problem.

root@asterisk:~# echo SELECT tel_cislo FROM last_listener |isql -v asterisk-connector asterisk replace_with_strong_password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT tel_cislo FROM last_listener
+----------+
| tel_cislo|
+----------+
| 1234     |
+----------+
SQLRowCount returns 1
1 rows fetched

But from console … nothing.

asterisk*CLI> odbc show

ODBC DSN Settings
-----------------

  Name:   asterisk
  DSN:    asterisk-connector
    Number of active connections: 1 (out of 1)

asterisk*CLI>

asterisk*CLI> odbc read ODBC_POKUS  1 exec
Failed to execute query. [SELECT tel_cislo FROM last_listener LIMIT 1]
[Sep  9 11:33:45] WARNING[4024]: func_odbc.c:476 execute: SQL Execute returned an error -1: 42000: [ma-3.0.6][10.1.26-MariaDB-0+deb9u1]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT tel_cislo FROM last_listener LIMIT 1' at line 1 (230)
[Sep  9 11:33:45] WARNING[4024]: func_odbc.c:485 execute: SQL Exec Direct failed (-1)![SELECT tel_cislo FROM last_listener LIMIT 1]
asterisk*CLI>

if write - nothing…no debug message…

asterisk*CLI> odbc write ODBC_POKUS 123 exec
INSERT INTO last_listener (tel_cislo) VALUES ('123')
asterisk*CLI>

I must probably use last version with normal MYSQL connection… :sweat:

7

In the meantime while debug this issue you can insert data on the DB using System() app

8

Yes - its no problem. This solution is running. But this is not “clean”:

  1. Asterisk extension script
  2. Shell script for database
  3. Return to asterisk extension script
    … weeery slow.