[HELP] MySQL problem with commands that include NOW()

Asterisk Asterisk Support
1

I only started Asterisk a couple of days ago, so I might be missing something.
For the past 15 years I have been using Dialogic soft- and hardware, so it’s a bit of a culture shock as how easy most stuff is implemented.
Unfortunately I have been pondering over the following all day so I hope someone might point me in the right direction.

I am using CentOS 6.5 and Asterisk 11.12.0.
Currently no packages are updated, just the RPM’s on the CentOS DVD’s.

There are 2 issues :

  1. INSERTING a new record and getting the LAST_INSERT_ID() without the possibility that another call inserts a new record while I still need to get the ID. In C I used to lock the MySQL thread, but I don’t think that can be done. Or does each call has it’s own MySQL connection ?
    I have a specific asterisk_insert in func_odbc.conf for that, but I am not sure if that is the correct way to do this.

  2. When using commands containing (), such as now and LAST_INSERT_ID() I get starnge errors.
    I tried all kinds of permutations to get it working using double quotes, Q_r and Q_w, escaping ( and ) but to no avail.

[Sep 19 17:32:36] VERBOSE[2901][C-00000048] pbx.c: -- Executing [9991@from-internal:46] Set("SIP/6002-00000046", "Q="INSERT INTO callsystem.history SET IServer=1,Project='LOOKUP',CallerId='6002',Status='ONLINE'") in new stack [Sep 19 17:32:36] WARNING[2901][C-00000048] func_odbc.c: SQL Execute returned an error -1: 42000: [MySQL][ODBC 5.1 Driver][mysqld-5.1.71]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Q' at line 1 (186) [Sep 19 17:32:36] WARNING[2901][C-00000048] func_odbc.c: SQL Exec Direct failed (-1)![Q] [Sep 19 17:32:36] WARNING[2901][C-00000048] res_odbc.c: SQL Execute error! Verifying connection to asterisk [MySQL-Asterisk]... [Sep 19 17:32:36] WARNING[2901][C-00000048] func_odbc.c: SQL Execute returned an error -1: 42000: [MySQL][ODBC 5.1 Driver][mysqld-5.1.71]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Q' at line 1 (186) [Sep 19 17:32:36] WARNING[2901][C-00000048] func_odbc.c: SQL Exec Direct failed (-1)![Q] [Sep 19 17:32:36] ERROR[2901][C-00000048] func_odbc.c: Unable to execute query [Q] [Sep 19 17:32:36] VERBOSE[2901][C-00000048] pbx.c: -- Executing [9991@from-internal:47] Set("SIP/6002-00000046", "R=") in new stack

When not using commands containing () everything is OK.

[Sep 19 17:33:35] VERBOSE[2907][C-00000049] pbx.c: -- Executing [9991@from-internal:46] Set("SIP/6002-00000047", "Q="INSERT INTO callsystem.history SET IServer=1,Project='LOOKUP',CallerId='6002',Status='ONLINE'") in new stack [Sep 19 17:33:35] VERBOSE[2907][C-00000049] pbx.c: -- Executing [9991@from-internal:47] Set("SIP/6002-00000047", "Q_w()="INSERT INTO callsystem.history SET IServer=1,Project='LOOKUP',CallerId='6002',Status='ONLINE'") in new stack [Sep 19 17:33:35] VERBOSE[2907][C-00000049] pbx.c: -- Executing [9991@from-internal:48] NoOp("SIP/6002-00000047", "") in new stack

This is my query in extensions.conf for the working part.

same = n,Set(Q="INSERT INTO callsystem.history SET IServer=${IServer},Project='${Project}',CallerId='${ANI}',Status='ONLINE';") same = n,Set(Q_w()=${Q})

This is my query in extensions.conf when it is showing the errors.

same = n,Set(Q="INSERT INTO callsystem.history SET IServer=${IServer},SIVR=NOW(),EIVR=NOW(),Project='${Project}',CallerId='${ANI}',Status='ONLINE';")
same = n,Set(Q_w()=${Q})

This is my func_odbc.conf

[code][r]
prefix=Q
dsn=asterisk
readsql=${ARG1}

[w]
prefix=Q
dsn=asterisk
writesql=${VAL1}

[history]
prefix=insert
dsn=asterisk
writesql=INSERT INTO callsystem.history SET IServer=${ARG1},SIVR=NOW(),EIVR=NOW(),Project=’${ARG2}’,CallerId=’${ARG3}’,Status=‘ONLINE’;
readsql=SELECT LAST_INSERT_ID() FROM callsystem.history LIMIT 1;[/code]

2

The project is comming along nicely, a large portion of the Dialogic functionality has already been ported to Asterisk.

To get along I realy need to get the above problems fixed, so I experimented a little more with the following results :

  1. Does not seem to work to have both a readsql= and writesql= statement in the same section of func_odbc.conf
    Splitting both commands up in a seperate section works.
    In the supplied func_odbc.conf history_h1 and history_h2 work when called from the dialplan.
    history_h3 does not work.

[code][h1]
prefix=history
dsn=asterisk
writesql=INSERT INTO callsystem.history SET IServer=${ARG1},SIVR=NOW(),EIVR=NOW(),Project=’${ARG2}’,CallerId=${ARG3},Status=‘ONLINE’;

[h2]
prefix=history
dsn=asterisk
readsql=SELECT LAST_INSERT_ID() FROM callsystem.history LIMIT 1;

[h3]
prefix=history
dsn=asterisk
writesql=INSERT INTO callsystem.history SET IServer=${ARG1},SIVR=NOW(),EIVR=NOW(),Project=’${ARG2}’,CallerId=${ARG3},Status=‘ONLINE’;
readsql=SELECT LAST_INSERT_ID() FROM callsystem.history LIMIT 1;
[/code]
HOWEVER: Then you need to LOCK() UNLOCK() the calling lines and then I get the error :
No application ‘lock’ for extension …
Even though in the log file I can see the following :
pbx.c: == Registered custom function 'LOCK’
pbx.c: == Registered custom function 'TRYLOCK’
pbx.c: == Registered custom function 'UNLOCK’
loader.c func_lock.so => (Dialplan mutexes)

What am I configuring incorrectly?

  1. I can get my queries working defining sections in func_odbc.conf, but personally I find that rather restrictive and less readable, but that is each to their own I guess.
    Annyhow I do believe that the assignment of the string same =n,Set(Q="…") is interpreted incorrectly due to the usage of ( and ).
    So if anyone can see what I did wrong, I would be very grateful.
3
  1. I got the LOCK/UNLOCK fixed, my bad I misunderstood the syntaxis so this is the solution to the locking isue to get a unique call id.
    Remember to lock for any table with an AUTO_INCREMENT field where you insert a record.
    Be carefull not to mess up the while loop as this can cause a deadlock.
same = n,While($[${L} <= 0])
   same = n,Set(L=${LOCK(MySQL)})
same = n,EndWhile()

same =n,NoOp(YOUR CODE to INSERT a record...)
same =n,NoOp(YOUR CODE to SELECT LAST_INSERTED_ID...)

same = n,Set(L=${UNLOCK(MySQL)})

I hope my mistakes are helpfull to the next person stepping in the same trap.

All that remains now is to get issue 2) fixed, anyone ?

4

Hat off to the community and Digium.
So 10 days into using Asterisk I managed to convert my entire Dialogic setup on a test server where everything is working just fine. It needs some heavy load testing now, but the hard work is done.

All that remains is my initial issue, it is not the end of the world, just not according to my way of thinking.
I do feel it is something that might point at other issues in the string interpreter.

  1. As per the original post : When using commands containing (), such as now and LAST_INSERT_ID() I get starnge errors.
    I tried all kinds of permutations to get it working using double quotes, Q_r and Q_w, escaping ( and ) but to no avail.