ODBC Locks Warning In CLI – Asterisk 1.8.32.3

Home » Asterisk Users » ODBC Locks Warning In CLI – Asterisk 1.8.32.3
Asterisk Users 2 Comments

Hi all

I get this warning in the Asterisk CLI about once every ten minutes or so:

[Nov 23 14:47:36] WARNING[2544]: res_odbc.c:647
ast_odbc_prepare_and_execute: SQL Execute returned an error -1: HY000:
[MySQL][ODBC 5.1 Driver][mysqld-5.1.73]Deadlock found when trying to get lock; try restarting transaction (105)
[Nov 23 14:47:36] WARNING[2544]: res_odbc.c:659
ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying connection to cdr [asterisk-cdr]…
[Nov 23 14:47:36] WARNING[2544]: res_odbc.c:763 ast_odbc_sanity_check:
Connection is down attempting to reconnect…
[Nov 23 14:47:36] NOTICE[2544]: res_odbc.c:1541 odbc_obj_connect: Connecting cdr
[Nov 23 14:47:36] NOTICE[2544]: res_odbc.c:1573 odbc_obj_connect: res_odbc:
Connected to cdr [asterisk-cdr]

Does this imply that I’m missing the ODBC CELs and / or CDRs that were trying to write to MySQL over ODBC when the above occurred?

Or will the ODBC module in Asterisk (or ODBC itself?) recover gracefully and re-emit the CEL or CDR insert that hit the lock and were therefore NOT
written to MySQL?

Thanks,

Stefan

2 thoughts on - ODBC Locks Warning In CLI – Asterisk 1.8.32.3

  • It might be worth pointing out that 1.8x was released 6 years ago, went into security fix only over 2 years ago, and reached “end of life/no further fixes” over a year ago.

    11.x went into “security fix only” last month – 13 and 14 are the current versions – can you try with them?

  • Hi all

    Just to report back if it is of interest to anybody, I managed to solve this.

    Had nothing to do with Asterisk, rather with Percona 5.6 / MySQL which is the database used via ODBC from Asterisk to store CELs and CDRs.

    I added the following in the /etc/my.cnf file for the Percona instance I am running:

    innodb_lock_wait_timeout0
    transaction-isolation=READ-COMMITTED

    and restarted the Percona instance.

    This appears to have definitively removed the below warning from my Asterisk instance’s CLI.

    Using READ-COMMITTED (instead of the default REPEATABLE-READ) transaction isolation level apparently has optimised InnoDB table row locking (at least in my use case, Percona / MySQL version and load) to such a degree that I no longer see the warning below come up.

    Everything is still reliably logging as far as I can determine, and all my MySQL stored procedures which process my CDRs and CELs into useful form for my dialing back-end are still working fine.

    Just in case somebody can be helped by this.

    ast_odbc_prepare_and_execute: SQL Execute returned an error -1: HY000:
    [MySQL][ODBC 5.1 Driver][mysqld-5.1.73]Deadlock found when trying to get lock; try restarting transaction (105) [Nov >23 14:47:36] WARNING[2544]:
    res_odbc.c:659 ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying connection to cdr [asterisk-cdr]… Connection is down attempting to reconnect… Connecting cdr [Nov 23 14:47:36] NOTICE[2544]: res_odbc.c:1573
    odbc_obj_connect: res_odbc: Connected to cdr [asterisk-cdr]

    trying to write to MySQL over ODBC when the above occurred?

    and re-emit the CEL or CDR insert that hit the lock and were therefore NOT
    written to MySQL?