Summary:	ASTERISK-26633: res_odbc: SQL Execute returned "Deadlock found when trying to get lock; try restarting transaction"
Reporter:	wushumasters (wushumasters)	Labels:
Date Opened:	2016-12-01 08:05:14.000-0600	Date Closed:	2017-01-25 11:52:15.000-0600
Priority:	Major	Regression?
Status:	Closed/Complete	Components:	Resources/res_odbc
Versions:	13.13.0	Frequency of Occurrence
Related Issues:
Environment:	Debian 8 64 bits + Mysql	Attachments:	( 0) debug ( 1) debug_complete ( 2) debug_odbc2.3.4 ( 3) extconfig.conf ( 4) extensions.conf ( 5) full.log ( 6) odbc.ini ( 7) odbcinst.ini ( 8) queues.conf ( 9) res_odbc.conf (10) sqlerror.log
Description:	Hello, If i settings my queues to "ringall" and receive many calls i start to had this message from mysql {noformat} [2016-11-30 15:50:26] WARNING[12027][C-00001731] res_odbc.c: SQL Execute returned an error: HY000: [MySQL][ODBC 5.1 Driver][mysqld-5.5.52-0+deb8u1-log]Deadlock found when trying to get lock; try restarting transaction [2016-11-30 15:50:26] WARNING[12027][C-00001731] res_odbc.c: SQL Execute error -1! {noformat} I attached a log where this only happens when a agent not answer a call and the app write in the queue_log RINGNOANSWER Sorry my english Thanks Now i can reproduce the issue (Set a Queue with ringall strategy, put 4 peers and set autofill = yes, after this call to the queue 4 times and them when the peers was ringing execute "hangup request all")
Comments:	By: Asterisk Team (asteriskteam) 2016-12-01 08:05:14.729-0600 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: Rusty Newton (rnewton) 2016-12-06 16:55:35.935-0600 We require additional debug to continue with triage of your issue. Please follow the instructions on the wiki [1] for how to collect debugging information from Asterisk. For expediency, where possible, attach the debug with a '.txt' file extension so that the debug will be usable for further analysis. Thanks! [1] https://wiki.asterisk.org/wiki/display/AST/Collecting+Debug+Information By: Rusty Newton (rnewton) 2016-12-06 16:57:20.348-0600 In addition to the debug log, please provide the configuration of the queue, agent and dialplan.  DB dumps or CLI config output where appropriate. By: wushumasters (wushumasters) 2016-12-06 18:20:58.361-0600 Rusty, The files are attached In the same call two erros on odbc Thanks By: Rusty Newton (rnewton) 2016-12-08 17:48:53.889-0600 You forgot to make sure the "DEBUG" log channel type was included in your log. Be sure to turn up the levels from 0 to 5. {noformat} *CLI> core set verbose 5 *CLI> core set debug 5 {noformat} You can use "logger show channels" to see which channels are configured to output to various destinations. https://wiki.asterisk.org/wiki/display/AST/Basic+Logging+Commands Make sure the log has  warning,error,notice,verbose and debug channels. By: wushumasters (wushumasters) 2016-12-10 08:54:00.162-0600 Rusty, This log full has 99 verbose, i tried put core set debug 5 but cli returned the same informations Thanks By: Rusty Newton (rnewton) 2016-12-13 09:12:34.856-0600 There is no problem with the verbose output. You don't have a single "DEBUG" message in the log which indicates you didn't follow the instructions and include the DEBUG log channel. Please follow the instructions closely: https://wiki.asterisk.org/wiki/display/AST/Collecting+Debug+Information As I mentioned in the previous comment, please use the available CLI commands to verify your settings in logger.conf are loaded. By: wushumasters (wushumasters) 2016-12-13 09:31:19.925-0600 Rusty, Sorry I get now the log with debug but is too big I attached a file only with the error and debug, is enoutgh? Sorry my english Thanks By: Rusty Newton (rnewton) 2017-01-05 09:03:58.577-0600 Hmm, we might need to see more than that. Try getting 500 - 1000 lines before the lock and after it. Can you also provide the extconfig.conf and res_odbc.conf?  Of course, remember to sanitize sensitive information. By: wushumasters (wushumasters) 2017-01-05 09:13:47.314-0600 i will do other debug to attach here By: Rusty Newton (rnewton) 2017-01-09 18:03:00.057-0600 What exact versions of your ODBC and MySQL libraries are you using? By: wushumasters (wushumasters) 2017-01-09 18:18:08.980-0600 Rusty, Mysql: 5.5.53-0+deb8u1 ODBC: 2.3.1-3 Both are the native packages for Debian 8 Thanks By: Rusty Newton (rnewton) 2017-01-10 14:13:59.914-0600 I'm not sure the issue is within Asterisk, but we can try something. Can you try two things? 1. Build the latest UnixODBC 2.3.4 2. After trying that, then with that new version try upping "max_connections" (res_odbc.conf) to something above 1. Provide debug from the new attempt. Thanks! By: wushumasters (wushumasters) 2017-01-13 13:52:56.487-0600 Rusty, Apparently, after change the mysql engine to MyISAM the problem was solved I think the issue is happening with Innodb I'll check for another few days. Thanks By: Rusty Newton (rnewton) 2017-01-14 15:32:46.242-0600 Okay. Did you get a chance to try it with InnoDB with the latest UnixODBC and/or max connections pushed up? By: wushumasters (wushumasters) 2017-01-16 05:33:24.651-0600 Rusty, i will try With ODBC 2.3.1 and max connections = 20 nothing change By: Joshua C. Colp (jcolp) 2017-01-17 10:37:37.482-0600 Waiting for feedback in regards to using InnoDB. By: wushumasters (wushumasters) 2017-01-23 06:11:27.338-0600 Only to report, with MyISAM the error not occured anymore. Now i will try with ODBC 2.3.4 and Innodb By: Rusty Newton (rnewton) 2017-01-23 09:37:55.969-0600 Thanks, let us know. By: wushumasters (wushumasters) 2017-01-24 17:21:11.686-0600 Rusty with 2.3.4 same problem I attached the debug I think this is a issue in mysql with innodb and not in asterisk or odbc Now i can reproduce the issue (Set a Queue with ringall strategy, put 4 peers and set autofill = yes, after this call to the queue 4 times and them when the peers was ringing execute "hangup request all") Sorry my english Thanks By: Rusty Newton (rnewton) 2017-01-25 11:52:15.514-0600 Thanks for the follow up. I'm going to close this out and recommend that you pursue the issue with the MySQL/innodb team then and see what they recommend. If they don't think it is a bug and point out some problem with Asterisk behavior then hopefully with their specific recommendation we could track something down.