CDR Reports or other external query to cdr table stop work calls!!!

This site is best viewed in a modern browser with JavaScript enabled.

CDR Reports or other external query to cdr table stop work calls!!!

Vlad23

I have a great problem with Issabel 4. I migrated from Elastix 4. At first day of using in production some times during the day we have crashed telephony - all active sip registration frozen, nobody can make calls. In operator panel we can see some frozen calls near extensions. After many tests and researches we find that problem in locks in MySQL. Our company is contact center and some of our managers create CDR Reports. Then they did this reports and we (admins) can see in mytop their queries to asterisk.cdrdb and lock in command "show open table" - the issabel is death. We made absolutely clear test. We Installed to VM absolutely new issabel from iso. Created only 2 extensions and registered only 2 softphones. In asteriskcdrdb database we imported big size table for longer time for query and start mysqldump asteriskcdrb. During this command all tables in DB were locked, CDR too. And issabel was frosen. We can't do calls from one extensions to second. Softphones showed dialing, operator panel showed calls but other softphone didn't ring! We made absolutely same test on old system Elastix 4. And all worked successfully !!! We are in shock!
Can you help me?

venturinog

Vlad23 Hello, can you please post commands used to import/export records an how you detected that tables were locked?

Vlad23

Hello!
"Show open tables from asteriskcdrdb" in mysql cli. Result "1" in column "In_Use".
I used System Backup|Restore from web interface Elastix and Issabel for migration all configuration's and DBs to production server.
For export big table (it's cdr table from another asterisk system) to asteriskcdrdb on new test's VM with Issabell I used dbForge Studio 2019 for MySQL.
On production server and test I used commands:
mysqldump -u r oot-pPASSWORD asteriskcdrdb > /root/dump_queue_log.sql and
mysqldump --single_transaction -u root -pPASSWORD asteriskcdrdb > /root/dump_queue_log.sql
In that moment when query was finished the calls start ringing and Issabel came back to life ...
On production server I also created CDR Report with big data interval. In all variants issabel was frozen during queries but in --single_transaction mode we had best result because tables were locked one by one. Without single_transaction the DB asteriskcdrdb was blocked fully.

venturinog

Vlad23 did you try mysqldump --quick --lock-tables=false --single-transaction?

Vlad23

mysqldump is only method for simulation problem, but its not the problem. The problem is not working calls then we make reports cdr in web interface or use external query. We will try your proposition on test VM but in Elastix mysqldump works always without --quick --lock-tables=false. Maybe the problem in isoliation level mysql in Issabel?