efa stops updating 'recent messages'

[1an3]

Weird issue since updating all packages last week, eFA has stopped showing messages a couple of times. Email is being passed, but not logged in the appliance.

For example right now the last message logged is 2315 last night, so anything thats in quarantine etc I need to look at since then I have no idea how to get to. I also have no idea what to restart to just get the eFa running again, apart from rebooting the whole box, which is relatively disruptive

Just me?

[1an3]

MailWatch Version: 1.2.23

Operating System Version: CentOS Linux 7 (Core)

Postfix Version: 3.5.9

MailScanner Version: 5.5.1

ClamAV Version: 0.103.11

SpamAssassin Version: 3.4.6

PHP Version: 7.4.33

MySQL Version: 5.5.5-10.4.25-MariaDB

GeoIP Database Version: GeoLite2 Country database 2024-04-23 14:21:46


I've had a look in var log messages and I seem to see this quite frequently including around the time things started to die

Code: Select all

May 15 23:18:09 Hyena php: PHP Fatal error:  Uncaught mysqli_sql_exception: Lock wait timeout exceeded; try restarting transaction in /var/www/html/mailscanner/functions.php:1066
May 15 23:18:09 Hyena php: Stack trace:
May 15 23:18:09 Hyena php: Fatal error: Uncaught mysqli_sql_exception: Lock wait timeout exceeded; try restarting transaction in /var/www/html/mailscanner/functions.php:1066
May 15 23:18:09 Hyena php: Stack trace:
May 15 23:18:09 Hyena php: #0 /var/www/html/mailscanner/functions.php(1066): mysqli->query()
May 15 23:18:09 Hyena php: #1 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(168): dbquery()
May 15 23:18:09 Hyena php: #2 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(58): process_sql()
May 15 23:18:09 Hyena php: #3 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(210): doit()
May 15 23:18:09 Hyena php: #4 {main}
May 15 23:18:09 Hyena php: thrown in /var/www/html/mailscanner/functions.php on line 1066
May 15 23:18:09 Hyena php: #0 /var/www/html/mailscanner/functions.php(1066): mysqli->query()
May 15 23:18:09 Hyena php: #1 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(168): dbquery()
May 15 23:18:09 Hyena php: #2 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(58): process_sql()
May 15 23:18:09 Hyena php: #3 /usr/bin/mailwatch/tools/Postfix_relay/mailwatch_milter_relay.php(210): doit()
May 15 23:18:09 Hyena php: #4 {main}
May 15 23:18:09 Hyena php:  thrown in /var/www/html/mailscanner/functions.php on line 1066
May 15 23:18:09 Hyena systemd: milter_relay.service: main process exited, code=exited, status=255/n/a
May 15 23:18:09 Hyena systemd: Unit milter_relay.service entered failed state.
May 15 23:18:09 Hyena systemd: milter_relay.service failed.
May 15 23:18:09 Hyena systemd: milter_relay.service holdoff time over, scheduling restart.
May 15 23:18:09 Hyena systemd: Stopped Postfix relay service for MailWatch.
May 15 23:18:09 Hyena systemd: Started Postfix relay service for MailWatch.

but eFa doesnt die every time that PHP Fatal error is logged.

Incoming mail definitely continues to flow though, it's just logging nothing.

[shawniverson]

Your database is locked for too long for some reason. Take a look at this post and see if you are having the same problem.

viewtopic.php?p=19809#p19809

[1an3]

So two questions -

how tf do I run the mysqlrecovery thing that post refers to?
Will it Break Things?

Also - that sounds like it might account for the PHP fatal errors in var log messages (which happens multiple times a day) , but not for eFa not logging anything (which has happened twice in a week)

[shawniverson]

No need to run mysqlrecovery, that won't help here. Skip to the last post in the thread and follow its instructions.

Nothing will be logged either if the database is locked.

[shawniverson]

Here's what to do in detail:

Execute the following. Set the interval to however long you retain data. It is 14 days in this example. The first delete may take a while, so be patient.

Code: Select all

sudo mysql -u root

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

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

OPTIMIZE TABLE mtalog, mtalog_ids;

quit

[1an3]

Here's what to do in detail:

Execute the following. Set the interval to however long you retain data. It is 14 days in this example. The first delete may take a while, so be patient.

Code: Select all

sudo mysql -u root

USE mailscanner;

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

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

OPTIMIZE TABLE mtalog, mtalog_ids;

quit

Thanks for that you must have read my mind.... so in my eFa i keep for 21 days so I use 21 days in your commands here yeah?

[shawniverson]

yes

[1an3]

When I tried these commands on a test box I got " Error 1046 (3D000): No Database selected"
screenshot:
https://boltonuni-my.sharepoint.com/:i: ... w?e=qZQHaQ

[shawniverson]

Oh my bad, missing a command, I'll edit the post

[1an3]

Oh my bad, missing a command, I'll edit the post

Thanks my man.

[1an3]

Is there something wrong with the second command too?

Code: Select all

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

i get

Code: Select all

"you have an error in your syntax, check the manual that corresponds to your MariaDB server version for the right syntax to use near 'm WHERE m.timestamp < (NOW() - INTERVAL 21 DAY)' at line 1 

error screenshot https://boltonuni-my.sharepoint.com/:i: ... Q?e=5QcMd1

[shawniverson]

Fixed the code above, try it now.

[1an3]

ran amended command


ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ran it twice, same result.

[shawniverson]

Well, I think you found the problem table

Let's put a limit on it and try running it multiple times instead:

Code: Select all

DELETE FROM mtalog WHERE timestamp < (NOW() - INTERVAL 14 DAY) LIMIT 100000;

You may need to play with the limit number to make it smaller if it is still timing out, or bigger if it isn't timing out but need to delete more entries at once.

[1an3]

Ok - so if it times out I reduce that number.

And potentially run it multiple times?

Thanks for this I really appreciate the help

[shawniverson]

Yes, but if it doesn't time out, increase the number so that you can delete more entries at once.

[1an3]

OK Thanks mate - I ran it with LIMIT 10000 and it ran in 0.9s

So I ran it again, and it only affected 3997 rows, so I'm guessing thats it sorted.

I will make sure I'm sorted with this and then do it on live.

[1an3]

Thanks for the help with this shawniverson I havent had any repeat of these issues since I ran these commands on live.