[asterisk-bugs] [JIRA] (ASTERISK-25938) res_odbc: MySQL/MariaDB statement LAST_INSERT_ID() always returns zero.

[Mark Michelson (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-25938?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=230338#comment-230338 ] 

Mark Michelson commented on ASTERISK-25938:
-------------------------------------------

I think I know why this is happening. From http://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html:

{quote}
For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis.
{quote}

The key here is the phrase "on a per-connection basis". Prior to Asterisk 13.8.0, Asterisk used a single connection for all database access the entire time Asterisk was running. Starting in 13.8.0, connection management was removed and unixodbc does the heavy lifting for us. This means that it is likely that your two SQL statements are each using separate connections, meaning that LAST_INSERT_ID() for the new connection will always be 0.

I'm going to have some discussions and try to find the best way to address this.

> res_odbc: MySQL/MariaDB statement LAST_INSERT_ID() always returns zero.
> -----------------------------------------------------------------------
>
>                 Key: ASTERISK-25938
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-25938
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Functions/func_odbc, Resources/res_odbc
>    Affects Versions: 13.8.0, 13.8.1
>         Environment: CentOS	  	  	7.2		  	2015-11
> Asterisk	  		13.8.0/13.8.1
> MariaDB			5.5.44
> unixODBC		2.3.1
> jansson  	  		2.7	  		2014-10-02
> PJSIP (pjproject)	2.4.5		2015-08-12
>            Reporter: Edwin Vandamme
>            Severity: Minor
>
> After upgrading to Asterisk 13.8.0 the MySQL/MariaDB statement LAST_INSERT_ID() always returns zero.
> To double check that it is related to Asterisk, I recompiled and installed 13.7.0 without any other modifications and the LAST_INSERT_ID() was returned correctly.
> To be sure, I recompiled and installed 13.8.0 and the problem returned.
> So back to 13.7.0 and no problem whatsoever.
> I checked the latest version which at time of writing is 13.8.1 and the problem returned.
> So back to 13.7.0 and no problem whatsoever.
> In my extensions.conf I do the following : 
> {code}
> ; Lock MySQL/MariaDB access.
> same = n,While($[${L} <= 0])
> same = n,Set(L=${LOCK(MySQL)})
> same = n,EndWhile()
> ; Mark start of a call attempt.
> same = n,Set(history_h1(${nIServer},${IProject},${ANI})=)
> ; Get unique call id
> same = n,Set(CallId=${history_h2()})
> ; Unlock MySQL access as the correct CallId was found.
> same = n,Set(L=${UNLOCK(MySQL)})
> {code}
> In my func_odbc.conf I do the following : 
> {code}
> [h1]
> prefix=history
> dsn=asterisk
> writesql=INSERT INTO callsystem.history SET IServer=${ARG1},IProject='${ARG2}',CallerId='${ARG3}',Status='ONLINE';
> [h2]
> prefix=history
> dsn=asterisk
> readsql=SELECT LAST_INSERT_ID() FROM callsystem.history LIMIT 1;
> {code}
> I also tried
> {code}
> readsql=SELECT LAST_INSERT_ID(); to no avail.
> {code}



--
This message was sent by Atlassian JIRA
(v6.2#6252)