efa-project.org

Posted: **16 May 2024 09:20**

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?

Posted: **16 May 2024 09:46**

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.

Posted: **16 May 2024 10:38**

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

Posted: **16 May 2024 10:43**

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)

Posted: **16 May 2024 11:42**

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.

Posted: **16 May 2024 11:47**

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

Posted: **16 May 2024 12:20**

shawniverson wrote: 16 May 2024 11:47 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?

Posted: **16 May 2024 12:37**

Posted: **17 May 2024 08:52**

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

Posted: **17 May 2024 10:38**

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

Posted: **17 May 2024 10:42**

shawniverson wrote: 17 May 2024 10:38 Oh my bad, missing a command, I'll edit the post

Thanks my man.

Posted: **17 May 2024 12:47**

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

Posted: **17 May 2024 13:05**

Fixed the code above, try it now.

Posted: **17 May 2024 13:26**

ran amended command

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

ran it twice, same result.

Posted: **17 May 2024 17:44**

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.

Posted: **17 May 2024 17:55**

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

And potentially run it multiple times?

Thanks for this I really appreciate the help

Posted: **18 May 2024 12:07**

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

Posted: **21 May 2024 12:50**

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.

Posted: **06 Jun 2024 09:24**

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

efa-project.org

efa stops updating 'recent messages'

efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'

Re: efa stops updating 'recent messages'