efa-project.org

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)");

Have you tried clearing the lock by running mysql recovery?

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

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

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 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...

Okay so here is my "solution":

first I cleared the mtalog_ids table without using a join: (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: (my time keep interval is 14 days)

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

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?

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