MariaDB 10 vicidial_live_agents crashes

[Vince-0]

Hi!

I have a server who's vicidial_live_agents table crashes every few minutes with just a few agents logged in and taking calls.
This results in agent error message "your session has been disabled" and it seems they get kicked out their conferences and lose their current calls.

This database was crashing all the time on the previous server.

I backed it up, installed Vicibox 6.0.3 (along with the Asterisk servers) with mariadb 10 from mariadb's Opensuse repo
Imported the database, upgraded schemas from astguiclient 2.8 to 2.12.

I've tried repairing the entire database many times with repair table and offline myisamchk but just this table keeps crashing.
I've tried dumping the database, recreating schemas and importing.
I usually to blame hardware for crashed tables but HP says it's all good and RAM tests check out. I will next try moving the database to another server.

Does anyone else run Vici on MariaDB 10.0.21 like this?

Database log spams this:
[ERROR] mysqld: Table './asterisk/vicidial_live_agents' is marked as crashed and last (automatic?) repair failed

Apache logs spams this:
PHP Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /srv/www/htdocs/agc/conf_exten_check.php on line 570, referer: http://10.0.0.60/agc/vicidial.php

conf_exten_check.php does some DB queries on vicidial_live_agents from line 570.

/my.cnf.d/server.conf;

Code: Select all

[mysqld]
skip-external-locking
key_buffer_size = 900M
max_allowed_packet = 16M
table_open_cache = 8192
sort_buffer_size = 6M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 128M
query_cache_size = 0M
thread_cache_size = 16
thread_concurrency = 16
max_connections = 1000
open_files_limit = 24567
max_heap_table_size = 2G
tmp_table_size = 2G
concurrent_insert = 2
myisam-recover = OFF
performance_schema = OFF

# Replication Master Server (default)
log-bin=mysql-bin
binlog_format=statement
server-id       = 1
expire_logs_days = 9


--
Version: 2.12-515a BUILD: 151007-2224 from Vicibox 6.0.3 ISO, zypper update, mariadb 10 repo
SVN Version: 2401 DB Schema Version: 1432
HW Specs:
Master DB/WEB: HP Dual Xeon Quads, raid10
Slave DB: HP DUAL Xeon Quads, raid10
Telephony: 2x Dual Xeon Quads, raid1

[nandotech]

I've never seen this happen to this particular table...

I'm sorry to say though, I have had this happen to me in the past. We never truthfully tracked down the issue, but because we work in virtual environments we spun up a new database and cloned over.

Long story short, anytime I've seen the error from mysqld: that Table 'x' is marked as crashed and last (automatic?) repair failed
Check the log output of your --auto-repair, but I venture to guess somehow the table got corrupted. I had this happen to me with a server where vicidial_log and vicidial_list plus some others became corrupt.

The reason these tables actually corrupted in the first place was due to MySQL crashing. Just recently we had a high load DB that would repeatedly crash throughout the day. Again, since we do virtual--we spun up a new server and backed up all the data. Everything ran fine.

edit:
Also, I think we still run MariaDB 5.5 everywhere--just realized you asked specifically.

[Vince-0]

It's starting to look like a hardware issue. I swaped to the slave DB and no crashed tables yet.

"That's weird"

[nandotech]

It's starting to look like a hardware issue. I swaped to the slave DB and no crashed tables yet.

"That's weird"

Wouldn't be the first time.

Don't completely write off that hardware just yet either--I had the exact same issue with ViciDIAL Databases running on Virtual Machines.

Spin up new machine, back up data--all crashes stop.

*shrug*

[okli]

Was the slave also running MariaDB 10.x ?

Have you isolated if the issue was hardware or related to MariaDB version?

[sunnyday]

*EDIT*: MEMORY seems fine, don't use INNODB.

I've been fighting this issue for days now and finally have a solution. In general, MyISAM tables can be problematic under heavy/multi-thread usage.I converted vicidial_live_agents into a MEMORY table and the system is now stable again.

*BEFORE* doing this, make sure your database has been checked and repaired. Also, make sure you have a bit of additional ram available... say ~250KB per simultaneously logged-in agent (just a guess). MySQL ram settings may need to be adjusted for this as well if you have a lot of agents.

I first made a back-up of the table and then recreated it while replacing the TEXT fields with varchar(1000). This is simply because MEMORY tables don't support TEXT/BLOB field types. My system doesn't seem to be using these fields at all and I'm not sure how much space they need. If you run into issues with this try increasing the length to something larger, say 4000, which will require more ram.

Here's the MySQL code to do it:

ALTER TABLE `asterisk`.`vicidial_live_agents` RENAME TO `asterisk`.`vicidial_live_agentsBAK`;

CREATE TABLE `vicidial_live_agents` (
`live_agent_id` int(9) unsigned NOT NULL AUTO_INCREMENT,
`user` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`server_ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`conf_exten` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`extension` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`status` enum('READY','QUEUE','INCALL','PAUSED','CLOSER','MQUEUE') COLLATE utf8_unicode_ci DEFAULT 'PAUSED',
`lead_id` int(9) unsigned NOT NULL,
`campaign_id` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`uniqueid` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`callerid` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`channel` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`random_id` int(8) unsigned DEFAULT NULL,
`last_call_time` datetime DEFAULT NULL,
`last_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_call_finish` datetime DEFAULT NULL,
`closer_campaigns` varchar(1000) COLLATE utf8_unicode_ci,
`call_server_ip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`user_level` tinyint(3) unsigned DEFAULT '0',
`comments` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`campaign_weight` tinyint(1) DEFAULT '0',
`calls_today` smallint(5) unsigned DEFAULT '0',
`external_hangup` varchar(1) COLLATE utf8_unicode_ci DEFAULT '',
`external_status` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
`external_pause` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',
`external_dial` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',
`external_ingroups` varchar(1000) COLLATE utf8_unicode_ci,
`external_blended` enum('0','1') COLLATE utf8_unicode_ci DEFAULT '0',
`external_igb_set_user` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',
`external_update_fields` enum('0','1') COLLATE utf8_unicode_ci DEFAULT '0',
`external_update_fields_data` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
`external_timer_action` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',
`external_timer_action_message` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
`external_timer_action_seconds` mediumint(7) DEFAULT '-1',
`agent_log_id` int(9) unsigned DEFAULT '0',
`last_state_change` datetime DEFAULT NULL,
`agent_territories` varchar(1000) COLLATE utf8_unicode_ci,
`outbound_autodial` enum('Y','N') COLLATE utf8_unicode_ci DEFAULT 'N',
`manager_ingroup_set` enum('Y','N','SET') COLLATE utf8_unicode_ci DEFAULT 'N',
`ra_user` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',
`ra_extension` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',
`external_dtmf` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',
`external_transferconf` varchar(120) COLLATE utf8_unicode_ci DEFAULT '',
`external_park` varchar(40) COLLATE utf8_unicode_ci DEFAULT '',
`external_timer_action_destination` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',
`on_hook_agent` enum('Y','N') COLLATE utf8_unicode_ci DEFAULT 'N',
`on_hook_ring_time` smallint(5) DEFAULT '15',
`ring_callerid` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',
`last_inbound_call_time` datetime DEFAULT NULL,
`last_inbound_call_finish` datetime DEFAULT NULL,
`campaign_grade` tinyint(2) unsigned DEFAULT '1',
`external_recording` varchar(20) COLLATE utf8_unicode_ci DEFAULT '',
`external_pause_code` varchar(6) COLLATE utf8_unicode_ci DEFAULT '',
`pause_code` varchar(6) COLLATE utf8_unicode_ci DEFAULT '',
`preview_lead_id` int(9) unsigned DEFAULT '0',
PRIMARY KEY (`live_agent_id`),
KEY `random_id` (`random_id`),
KEY `last_call_time` (`last_call_time`),
KEY `last_update_time` (`last_update_time`),
KEY `last_call_finish` (`last_call_finish`),
KEY `vlali` (`lead_id`),
KEY `vlaus` (`user`)
) ENGINE=MEMORY AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

[mflorell]

Don't even think about using InnoDB engine tables with VICIdial. We've gone over the reasons here on the forums many times, but rest assured it will mess everything up.

We use MyISAM because it is an engine that preserves query queue, something that InnoDB does not. As for MEMORY tables, they can be useful on some larger systems, but you have to be careful with the size of the fields that you convert from TEXT to CHAR(because you can't use TEXT in MEMORY tables) because you can end up using all of your memory on the machine and actually make performance worse.

Also, you can convert to MEMORY tables on the fly, without restarting MySQL, just make sure you've altered the TEXT fields first.

[sunnyday]

Ok, that clears things up. Do you have a recommendation for the field size when replacing TEXT fields?

[mflorell]

It really depends on how many in-groups your agents have selected. We have some clients with less than a dozen in-groups, all with IDs 10 characters or less. For them we set it to CHAR(200). But we have other clients that have agents selecting over 30 in-groups at once, so the fields need to be longer. I would suggest seeing how long the entries are now for your logged-in agents and using that as a way to measure what you should set the maximum amount to. As for the agent_territories field, if you don't use territories, then just set this to 10.