When the _mail table corrupts, there are logs in vrx-servlet container saying that the table is corrupted.
Command to check vrx-servlet log:
sudo docker logs vrx-servlet -f
Message in vrx-servlet log
Feb 10 06:10:29 trsapp01 ed639a33c193[1900]: org.springframework.dao.TransientDataAccessResourceException: StatementCallback; SQL [ select i.id as id from 2_evitem i inner join 2_mail m on m.id = i.id and m.vox in (1,6) inner join import_item ii on ii.item_id = i.id and ii.error_message is null inner join import_metadata im on im.id = ii.metadata_id inner join import imp on imp.id = im.import_id and imp.client = 2 and imp.sentiment_processing_options is not null where i.id not in ( select p.evitem_id from 2_evitem_processing p where p.evitem_id = i.id and p.code = 'sent_to_sentiment' ) and i.id in ( select p.evitem_id from 2_evitem_processing p where p.evitem_id = i.id and p.code = 'processed_by_tcu_builder' ) and ifnull(json_extract(imp.sentiment_processing_options, '$.deconvolutionEnabled'), 'false') = 'false' order by i.id ]; (conn=14587) Index 2_mail is corrupted; nested exception is java.sql.SQLTransientConnectionException: (conn=14587) Index 2_mail is corrupted
Initial log when _mail table corrupts:
Feb 10 06:10:29 trsapp01 9fa00617d5ba[1900]: 2024-02-10 6:10:29 0 [ERROR] InnoDB: tried to purge non-delete-marked record in index `vox_index` of table `obsilon`.`2_mail`: tuple: TUPLE (info_bits=0, 2 fields): {[4] (0x80000001),[8] CR(0x00000000001F4352)}, recor
d: COMPACT RECORD(info_bits=0, 2 fields): {[4] (0x80000001),[8] CR(0x00000000001F4352)}
Feb 10 06:10:29 trsapp01 9fa00617d5ba[1900]: 2024-02-10 6:10:29 0 [ERROR] InnoDB: Flagged corruption of `vox_index` in table `obsilon`.`2_mail` in purge
Feb 10 06:10:29 trsapp01 ed639a33c193[1900]: 2024-02-10 06:10:29.963 [ERROR] [sentimentScheduler-1]: Exception Occurred
Solution
Stop all services except mariadb.service
sudo systemctl stop vad_worker asr_worker diarization_worker tagger_worker sentiment_worker sudo systemctl stop gearman.service vrx_servlet.service jumptoweb.service jumptoweb_api.service sphinxsearch.service
Login to the database
sudo docker exec -it mariadb mysql -uroot -p******** obsilon
Database password can be found in
/opt/intelligent-voice/.envasTOMCAT_SCRIPT_PASSWORD.Run the query with the updated group number (in the example group number is 1)
check table 1_mail; optimize table 1_mail; check table 1_mail;
Expected output:
MariaDB [obsilon]> check table 1_mail; +----------------+-------+----------+------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+-------+----------+------------------------------------------------+ | obsilon.1_mail | check | Warning | InnoDB: Index vox_index is marked as corrupted | | obsilon.1_mail | check | error | Corrupt | +----------------+-------+----------+------------------------------------------------+ 2 rows in set (0.009 sec) MariaDB [obsilon]> optimize table 1_mail; +----------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+----------+----------+-------------------------------------------------------------------+ | obsilon.1_mail | optimize | note | Table does not support optimize, doing recreate + analyze instead | | obsilon.1_mail | optimize | status | OK | +----------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.104 sec) MariaDB [obsilon]> check table 1_mail; +----------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+-------+----------+----------+ | obsilon.1_mail | check | status | OK | +----------------+-------+----------+----------+ 1 row in set (0.011 sec)
Resume the services
sudo systemctl start gearman.service vrx_servlet.service jumptoweb.service jumptoweb_api.service sphinxsearch.service sudo systemctl start vad_worker asr_worker diarization_worker tagger_worker sentiment_worker
Check vrx-servlet logs