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

[Carlos Oliva (JIRA)]

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

Carlos Oliva commented on ASTERISK-25938:
-----------------------------------------

Sorry for the newbie question but the desired behaviour can not be simulated in odbc?  I think use threading = 3 in obcinst.ini maybe a workaround for this issue, because do only one thing at time. Am I wrong?

In /unixODBC-2.3.4/DriverManager/__handles.c I can read:

/*
 * use just one mutex for all the lists, this avoids any issues
 * with deadlocks, the performance issue should be minimal, if it
 * turns out to be a problem, we can readdress this
 *
 * We also have a mutex to protect the connection pooling code
 *
 * If compiled with thread support the DM allows four different
 * thread strategies:
 *
 * Level 0 - Only the DM internal structures are protected.
 * The driver is assumed to take care of itself
 *
 * Level 1 - The driver is protected down to the statement level.
 * Each statement will be protected, and the same for the connect 
 * level for connect functions. Note that descriptors are considered
 * equal to statements when it comes to thread protection.
 *
 * Level 2 - The driver is protected at the connection level. Only
 * one thread can be in a particular driver at one time.
 *
 * Level 3 - The driver is protected at the env level, only one thing
 * at a time.
 *
 * By default the driver opens connections with lock level 0; drivers
 * are expected to be thread safe now. This can be changed by adding
 * the line
 *
 * Threading = N
 *
 * to the driver entry in odbcinst.ini, where N is the locking level 
 * (0-3)
 * 
 */


> 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
>         Attachments: func_odbc_dsn.diff
>
>
> 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)