Lock database mailscanner - mtalog and mtalog_ids

[mayconplanet]

Hello, we have a lock problem in the database mailscanner.

informations:
-Database size - 3.3GB
- EFA SQL 5.1 SGDB
- EFA-Version - EFA-3.0.1.5

After the purge activation in the EFA was made troubleshooting in SQL 5.1, the EFA mailscanner database has been identified "Locked" with the "mtalog" and "mtalog_ids" tables for DELETE in the smtp_id and msg_i columns.

Script execution - /etc/cron.daily/mailwatch = /usr/local/bin/mailwatch/tools/Cron_jobs/db_clean.php >> /dev/null 2>&1

Query in the Script cause lock database:
// Cleaning the mta_log and optionally the mta_log_id table
$sqlcheck = "SHOW TABLES LIKE 'mtalog_ids'";
$tablecheck = dbquery($sqlcheck);
$mta = get_conf_var('mta');
$optimize_mtalog_id = '';
if ($mta == 'postfix' && mysql_num_rows($tablecheck) > 0) {
//version for postfix with mtalog_ids enabled
dbquery(
"DELETE i.*, m.* FROM mtalog AS m
LEFT OUTER JOIN mtalog_ids AS i ON i.smtp_id = m.msg_id
WHERE m.timestamp < (NOW() - INTERVAL " . RECORD_DAYS_TO_KEEP . " DAY)"
);
$optimize_mtalog_id = ', mtalog_ids';
} else {
dbquery("DELETE FROM mtalog WHERE timestamp < (NOW() - INTERVAL " . RECORD_DAYS_TO_KEEP . " DAY)");

[shawniverson]

Have you tried clearing the lock by running mysql recovery?

[mayconplanet]

Hello!

After running MYSQL-RECOVERY the table continues with "Lock" mtalog and mtalog_ids when you run the mailwatch script in the CRON.Daily routine.
What impact will be caused if you do not choose to execute this Query, as already commented in Script - That execution is optional

// Cleaning the mta_log and optionally the mta_log_id table
$sqlcheck = "SHOW TABLES LIKE 'mtalog_ids'";
$tablecheck = dbquery($sqlcheck);
$mta = get_conf_var('mta');
$optimize_mtalog_id = '';
if ($mta == 'postfix' && mysql_num_rows($tablecheck) > 0) {
//version for postfix with mtalog_ids enabled
dbquery(
"DELETE i.*, m.* FROM mtalog AS m
LEFT OUTER JOIN mtalog_ids AS i ON i.smtp_id = m.msg_id
WHERE m.timestamp < (NOW() - INTERVAL " . RECORD_DAYS_TO_KEEP . " DAY)"

Output MySQL Recovery -
MYSQLCHECK


efa.tokens OK
mailscanner.audit_log OK
mailscanner.autorelease OK
mailscanner.blacklist OK
mailscanner.inq OK
mailscanner.maillog OK
mailscanner.mcp_rules OK
mailscanner.mtalog OK
mailscanner.mtalog_ids OK
mailscanner.outq OK
mailscanner.sa_rules OK
mailscanner.saved_filters OK
mailscanner.spamscores OK
mailscanner.user_filters OK
mailscanner.users OK
mailscanner.whitelist OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
sa_bayes.awl
note : The storage engine for the table doesn't support repair
sa_bayes.bayes_expire
note : The storage engine for the table doesn't support repair
sa_bayes.bayes_global_vars
note : The storage engine for the table doesn't support repair
sa_bayes.bayes_seen
note : The storage engine for the table doesn't support repair
sa_bayes.bayes_token
note : The storage engine for the table doesn't support repair
sa_bayes.bayes_vars
note : The storage engine for the table doesn't support repair
sa_bayes.txrep
note : The storage engine for the table doesn't support repair
sqlgrey.config OK
sqlgrey.connect OK
sqlgrey.domain_awl OK
sqlgrey.from_awl OK
sqlgrey.optin_domain OK
sqlgrey.optin_email OK
sqlgrey.optout_domain OK
sqlgrey.optout_email OK

efa.tokens OK
mailscanner.audit_log OK
mailscanner.autorelease OK
mailscanner.blacklist OK
mailscanner.inq OK
mailscanner.maillog OK
mailscanner.mcp_rules OK
mailscanner.mtalog OK
mailscanner.mtalog_ids OK
mailscanner.outq OK
mailscanner.sa_rules OK
mailscanner.saved_filters OK
mailscanner.spamscores OK
mailscanner.user_filters OK
mailscanner.users OK
mailscanner.whitelist OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log
note : The storage engine for the table doesn't support optimize
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.servers OK
mysql.slow_log
note : The storage engine for the table doesn't support optimize
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
sa_bayes.awl
note : Table does not support optimize, doing recreate + analyze instead
status : OK
sa_bayes.bayes_expire
note : Table does not support optimize, doing recreate + analyze instead
status : OK
sa_bayes.bayes_global_vars
note : Table does not support optimize, doing recreate + analyze instead
status : OK
sa_bayes.bayes_seen
note : Table does not support optimize, doing recreate + analyze instead
status : OK
sa_bayes.bayes_token
note : Table does not support optimize, doing recreate + analyze instead
status : OK
sa_bayes.bayes_vars
note : Table does not support optimize, doing recreate + analyze instead
status : OK
sa_bayes.txrep
note : Table does not support optimize, doing recreate + analyze instead
status : OK
sqlgrey.config OK
sqlgrey.connect OK
sqlgrey.domain_awl OK
sqlgrey.from_awl OK
sqlgrey.optin_domain OK
sqlgrey.optin_email OK
sqlgrey.optout_domain OK
sqlgrey.optout_email OK

[shawniverson]

Impact should be minimal as a workaround. Just be aware those dbs will continue to grow in size.

[tobiasp]

Hi,
out of the blue I'm also having this issue... every day my eFa install stops processing mails and if I check the mysql process list I see this "DELETE i.*, m.* FROM mtalog..." query with a very high runtime and it seems it blocks eFa from processing mails.

this issue appeared seemingly out of nowhere... there was no update or anything before it started... I updated to 4.0.4-42 and then ran

Code: Select all

run-parts /etc/cron.daily

but it did not solve the issue - this query seems to be running forever...

I ran mysqlcheck and it gave everything an OK.

how could I solve this? is there a way to just empty out both tables without breaking everything?

I just checked the mysql tables.. and maybe the cleanup of mtalog_ids never actually worked:
mtalog: 207329 entries 91MiB
mtalog_ids: 11995488 entries 2,5 GiB

so I guess the join operation is maybe overwhelming the server seeing the size of the mtalog_ids table?
seems to me that this table might never have been cleaned since the server started running...

[tobiasp]

Okay so here is my "solution":

first I cleared the mtalog_ids table without using a join:

Code: Select all

DELETE FROM mtalog_ids WHERE smtp_id not in (SELECT msg_id FROM mtalog) LIMIT 100000;

(I ran this multiple times with a bash script and pause in between until there where no rows affected anymore).

then I also had so cleanup the mtalog like this:

Code: Select all

DELETE FROM mtalog m WHERE m.timestamp < (NOW() - INTERVAL 14 DAY);

(my time keep interval is 14 days)

and then I ran:

Code: Select all

OPTIMIZE TABLE mtalog, mtalog_ids

and then I could run the original DELETE (with join) statement and it finished fairly quickly.

Code: Select all

DELETE i.*, m.* FROM mtalog AS m LEFT OUTER JOIN mtalog_ids AS i ON i.smtp_id = m.msg_id WHERE m.timestamp < (NOW() - INTERVAL 14 DAY);

[pdwalker]

I think we should be notified if the /usr/bin/mailwatch/tools/Cron_jobs/mailwatch_db_clean.php script runs into an error.

This script must have been failing for me for quite some time before the problem of an excessively large mtalog_ids table came to my attention.

Shawn, is this worth raising a mailwatch bug/feature request for?

[shawniverson]

Won't need to open a request to mailwatch. We just need to dump the >/dev/null 2>&1 in the cron job. I'll work on this. I'm also adding an extra step to the cron job to help keep the mtalog_ids clean.

https://github.com/E-F-A/eFa5/issues/15