Summary:	ASTERISK-25938: res_odbc: MySQL/MariaDB statement LAST_INSERT_ID() always returns zero.
Reporter:	Edwin Vandamme (pay123)	Labels:
Date Opened:	2016-04-19 07:49:19	Date Closed:	2016-04-25 05:16:32
Priority:	Minor	Regression?	Yes
Status:	Closed/Complete	Components:	Functions/func_odbc Resources/res_odbc
Versions:	13.8.0 13.8.1	Frequency of Occurrence
Related Issues:	causes ASTERISK-25955 Make single-connection per dns for func_odbc optional
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	Attachments:	( 0) func_odbc_dsn.diff
Description:	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}
Comments:	By: Asterisk Team (asteriskteam) 2016-04-19 07:49:20.283-0500 Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution. A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report. Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process]. By: Mark Michelson (mmichelson) 2016-04-21 10:29:16.573-0500 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. By: Mark Michelson (mmichelson) 2016-04-21 15:15:17.156-0500 I'm attaching func_odbc_dsn.diff . This patch contains a proposed fix for this problem. It changes func_odbc back to using a single connection per DSN. Please give this a try and verify that it fixes your problem. By: Edwin Vandamme (pay123) 2016-04-22 02:30:42.558-0500 I will give it a go later today. I haven't looked at the patch yet, but if you wish to use multiple connections, you might want to establish a connection per thread/call and that would fix the issue as well. It might be me, but where is the attachement located ? I compared func_odbc.c from 13.7.0 and 13.8.1, the only difference is an additional line in 13.8.1 #include "asterisk/res_odbc_transaction.h", so I would say that is not the source of the problem. By: Mark Michelson (mmichelson) 2016-04-22 09:55:05.799-0500 Re-attaching the patch now. I'm not sure what happened there. The problem isn't with func_odbc.c itself, but rather the behavior of the API  it depends on in res_odbc.c. The suggestion of a connection per call is something that would allow nicely for parallelism and it would fix your particular case. My only concern here is if there are unreported problems that also stem from this behavior change. Maybe for Asterisk 14 we could go with a connection per call since upgrading to a major version allows for such behavior changes. For Asterisk 13, though, I'm going to try to revert to the behavior of 13.7.0 for func_odbc just so that behavior remains consistent and we don't have the possibility of causing subtle issues for people trying to perform a minor version upgrade. By: Carlos Oliva (coliva) 2016-04-22 10:56:49.424-0500 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: {noformat} /* * 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) * */ {noformat} By: Edwin Vandamme (pay123) 2016-04-22 13:01:43.373-0500 @Mark I applied the patch to 13.8.1 and made 5 calls, each time the problem was fixed. @Carlos I tried your aproach as well with the unmodified 13.8.1 as follows : - Modify odbcinst.ini by adding Threading = 3 - Restart Asterisk - Test call Unfortunately that did not fix the problem. In any case for logical reasons I must agree with Mark's arguments as this would avoid unexpected behaviour for all Asterisk 13.?.? users. Thank you for all the effort. By: Edwin Vandamme (pay123) 2016-04-25 03:50:18.417-0500 For those following the problem, it still exists in 13.8.2, so the patch needs to be applied. (this comment was not intended to reopen the issue.) By: Asterisk Team (asteriskteam) 2016-04-25 03:50:18.616-0500 This issue has been reopened as a result of your commenting on it as the reporter. It will be triaged once again as applicable.