[HOWTO] Asterisk and LAST_INSERT_ID with MariaDB/MySQL

Asterisk Asterisk Support
1

@jcolp Feel free to add this to the Asterisk documentation if you find this usefull.

The way LAST_INSERT_ID works, requires you to perform the INSERT query and the LAST_INSERT_ID to be using the same connection.
In between the two queries, no other INSERT query on a table with an AUTO_INCREMENT field is allowed or you end up with an incorrect result for your LAST_INSERT_ID result.
This is an old age problem, which MariaDB/MySQL could have long solved by creating something like “INSERT RETURNID INTO db.table …”.

Originally, to serialize the queries correctly, I used to put a lock around them and everything was fine.
This linear thinking was inherited from various programming languages, such as PhP, so I would not be surprised that I am the only one with this logic.

Unfortunately when Asterisks changed from 13.7.0 to 13.8.0 this logic no longer worked.
As such I requested a regression in order for this to work again.
https://issues.asterisk.org/jira/browse/ASTERISK-25938

Although I stand by the logic that the regression was required to avoid breaking existing code (such as my own), a seed was planted in my mind that there should be a better solution.

Recently I watched a number of AstriCon 2016 video’s and there were some suggestions as to the maximum number of database connections.
This triggered me to check out the …/configs/samples/func_odbc.conf.sample, which suggests to use a stored procedure.
Some Googling did not turn up any suggestions, so I created my own, which does no longer require locking and simplifies the code significantly.

In the community spirit, I felt obliged to post my solution.
Feel free to modify and post optimizations.
At the time of writing, I used CentOS 7.3 1611, Asterisk 13.14.0 and MariaDB 10.1.20 (cluster).

What to do :

  1. Create a general stored procedure that performs an INSERT, followed by a LAST_INSERT_ID request.

  2. In extensions.conf read the return value from the CALL to the stored procedure.

  3. Define the proper query in func_odbc.conf, I found this tricky as the usage of quotes, brackets and semicolons needs to be correct.

  4. As I have all my realtime Asterisk tables in a database called asterisk, the logical place for me is to create the stored procedure in the asterisk database. Please note that the maximum length of the query is set to 1000. Adjust these values to reflect your own situation.

USE asterisk;
DROP procedure IF EXISTS getIDfromQuery;

DELIMITER $$
USE asterisk$$
CREATE DEFINER=root@% PROCEDURE getIDfromQuery(Q VARCHAR(1000))
BEGIN
SET @tQ=Q;
PREPARE xQ FROM @tQ;
EXECUTE xQ;
DEALLOCATE PREPARE xQ;

SELECT LAST_INSERT_ID();
END$$

DELIMITER ;

  1. In the extensions.conf, as an example, I set two column values when inserting a new record. Adjust to reflect your own situation.

same = n,Set(Id=${history_h1(aValue1, aValue2)=})

  1. In func_odbc.conf as an example, I set two column values when inserting a new record. The INSERT query is bogus, so adjust to reflect your own situation (beware of quotes, brackets and semicolon).

[h1]
prefix=history
dsn=asterisk
readsql=CALL asterisk.getIDfromQuery (“INSERT INTO yourdb.yourtable SET column1=‘${ARG1}’,column2=‘${ARG2}’”);

2 Likes
2

I’m not really sure where this would go for people to find it. If you find a wiki page you feel would be the best fit you can comment on it and it can be added.

3

Joshua,

Thanksfor the sugestion.
I will look in the wiki for a spot to comment.

At least Google will find this page and I hope it will be helpfull.