+1 The error is the actual MySQL error, so yes it's definitely a lock timeout. This means two different queries were trying to access the same data at the same time. Normally one'll wait until the other is finished, but if it waits too long you get the lock timeout error. It can be caused by a query taking too long, or a transaction modifying the data but then taking too long before commit. To avoid that make sure MySQL is tuned correctly. Good indexes on the tables and good sizes for the cache buffers. Make sure you're using a proper index for the delete operation, I'd also
avoid ORs if possible.
The query is part of mod_sofia, so isn't modifiable. Having a KEY(hostname,expires) on sip_dialogs may help though if it doesn't already exist (check with SHOW CREATE TABLE sip_dialogs). Avoid accessing sip_dialogs from any of your own code to minimise the number of things that might block each other. -Steve On 16 July 2013 06:48, Rafal Gwizdala <rafal.gwizdala@gmail.com> wrote:
I'd bet this is a lock timeout, caused by database operation taking too long. Make sure you're using a proper index for the delete operation, I'd also avoid ORs if possible. R
On Tue, Jul 16, 2013 at 4:21 AM, Lloyd Aloysius <lloyd.aloysius@gmail.com>wrote:
Hi,
I got the following error
2013-07-15 21:47:41.061450 [ERR] switch_odbc.c:514 ERR: [delete from sip_dialogs where (expires = -1 or (expires > 0 and expires <= 1373939087)) and hostname='mydomain.com'] [STATE: HY000 CODE 1205 ERROR: [unixODBC][MySQL][ODBC 5.1 Driver][mysqld-5.5.32]Lock wait timeout exceeded; try restarting transaction]
and the odbcinst.ini
[MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1 Threading = 0 UsageCount = 1
This is a mysql memory leak? How can fix this error?
DB Server running with 24 GB memory and 2 Quad Core Xeon
Thanks Lloyd
_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org