efa stops updating 'recent messages'

Bugs in eFa 4
Post Reply
1an3
Posts: 40
Joined: 07 May 2021 13:05

efa stops updating 'recent messages'

Post by 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
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 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.
User avatar
shawniverson
Posts: 3706
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: efa stops updating 'recent messages'

Post by 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
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 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)
User avatar
shawniverson
Posts: 3706
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: efa stops updating 'recent messages'

Post by 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.
User avatar
shawniverson
Posts: 3706
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: efa stops updating 'recent messages'

Post by 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
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 1an3 »

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?
User avatar
shawniverson
Posts: 3706
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: efa stops updating 'recent messages'

Post by shawniverson »

yes
1an3
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 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
User avatar
shawniverson
Posts: 3706
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: efa stops updating 'recent messages'

Post by shawniverson »

Oh my bad, missing a command, I'll edit the post
1an3
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 1an3 »

shawniverson wrote: 17 May 2024 10:38 Oh my bad, missing a command, I'll edit the post
Thanks my man. :mrgreen:
1an3
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 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
User avatar
shawniverson
Posts: 3706
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: efa stops updating 'recent messages'

Post by shawniverson »

Fixed the code above, try it now.
1an3
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 1an3 »

ran amended command


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

ran it twice, same result.
User avatar
shawniverson
Posts: 3706
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: efa stops updating 'recent messages'

Post by shawniverson »

Well, I think you found the problem table :lol:

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
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 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
User avatar
shawniverson
Posts: 3706
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: efa stops updating 'recent messages'

Post by shawniverson »

Yes, but if it doesn't time out, increase the number so that you can delete more entries at once.
1an3
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 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
Posts: 40
Joined: 07 May 2021 13:05

Re: efa stops updating 'recent messages'

Post by 1an3 »

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