Database issue not assigning token.

Report bugs and workarounds
ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Database issue not assigning token.

Post by ethandee178 » 27 Aug 2018 12:39

Often time when I come in on a monday, my customers will tell me they can't release their mail.
Aside from some other possibly unrelated issues, the most common issue is that the 'token=' is blank in the link.
I've written a script to easily release these. But I know it is annoying to my customers.
Any one had this issue? Dealt with it?
Seems like it could be database related. Is there a database clean/tuning command I could run that might help?
Thanks.

BTW this is that easy release script:

#!/bin/bash

while true; do
until [[ $msgID == ??????????.????? ]]; do
if [[ $msgID == "exit" ]]; then
exit
fi
read -p "Message ID?: " msgID
done
fullpath=`find /var/spool/MailScanner/quarantine -iname *"$msgID"*`
echo $fullpath
/usr/sbin/sendmail.postfix -t <$fullpath
msgID=""
done

User avatar
shawniverson
Posts: 2610
Joined: 13 Jan 2014 23:30
Location: Rushville, Indiana, USA
Contact:

Re: Database issue not assigning token.

Post by shawniverson » 27 Aug 2018 21:47

Never, ever, use -t!

You could be releasing mail to everybody! Not just your own recipients!

Is the token really gone, or are the tokens expiring? Monday seems peculiar....
Version 3.0.2.6 released! Update now to keep your eFa secure!

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 17 Sep 2018 13:31

Another Monday with the same issue.
When I say the token is 'gone' I mean in the release link, it says 'token='
If I assign a token manually it releases no problem.
I also cloned and edited a page which I call release-msg-bak.cgi which doesn't check for the token.
It also releases it no problem.
The original (release-msg.cgi) is where I got the '-t' from.
I just copy and pasted.

User avatar
shawniverson
Posts: 2610
Joined: 13 Jan 2014 23:30
Location: Rushville, Indiana, USA
Contact:

Re: Database issue not assigning token.

Post by shawniverson » 18 Sep 2018 21:46

Yeah, that's why that script is deprecated and going away...

So there is a token for the email IDs in the SQL maillog table, just not in the report?

Just Mondays? Are you on 3.0.2.6? I want to try to reproduce this problem.

The token is important to prevent someone from exploiting the message release code.
Version 3.0.2.6 released! Update now to keep your eFa secure!

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 24 Sep 2018 18:11

Is there a better script I should use for release? Or an edit to make? I have no problem with that.
Or if there is already a KB on it just link it.
It seems (based on the the release script) that it is just requesting a valid token, not a specific token. It seems like any token can release any email, but the the trade off is it can only be used once as the release script removes it, thus verifying that it was released validly already. So next time it happens I will check the maillog table and see if anything related to the token is recorded there.

I realize the issue I cause by removing that validation. But I gotta make people happy in a hurry sometimes. Such is the world of I.T.

Just Mondays, yes, however, I don't check on the weekends so theoretically, it could happen F 6PM - M 7AM.

my motd says "You are currently running EFA-3.0.2.6."
And you are welcome to take a look into my system next time it happens if you think it'll help.

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 27 Sep 2018 15:08

Some new info, it happened again today on a Thursday! Gathered some info.
Web interface says:
Error: Database connection failed

It is possible that the database is overloaded or otherwise not running properly

Contact the system administrator if the problem persists

Webmin mysql says:
DBI connect failed : Too many connections

And I ran `show status like 'Conn%';`
and got this:
MariaDB [(none)]> show status like 'Conn%';
+-----------------------------------+--------+
| Variable_name | Value |
+-----------------------------------+--------+
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 20 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 179878 |
+-----------------------------------+--------+
7 rows in set (0.23 sec)

And that's what I've got. Any thoughts?

User avatar
pdwalker
Posts: 1135
Joined: 18 Mar 2015 09:16

Re: Database issue not assigning token.

Post by pdwalker » 02 Oct 2018 04:01

So, you are clearly having some kind of database problem. What that means exactly, I don't know. You'll have to investigate your system to find out what is happening.

The connection failed error message is a symptom. Now you need to find the root cause.

Can you share your mysql configuration files?

Are there any interesting messages in the mysql error log? Do you have an error log enabled? Look in /var/lib/mysql. There may be a .err file present. (this depends on your version of efa and what you've installed it on)

Do you have any cpu/disk/memory monitoring tools running at the time you are getting the error?

Without more information, beyond saying "your db appears to have an issue", there is not much else I can suggest at this time.

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 11 Oct 2018 13:12

Error happened again today so I took some time to look into what you requested.
One additional thing, the processlist in mysql seems to be hung up. I'm not a mysql wiz so it'd be hard for me to figure out what's going on there.
Any ideas?
Thanks for you help.

See attachments.
Attachments
myemailfilter.zip
Some relevant text files and a HTOP screenshot
(162.55 KiB) Downloaded 14 times

User avatar
pdwalker
Posts: 1135
Joined: 18 Mar 2015 09:16

Re: Database issue not assigning token.

Post by pdwalker » 12 Oct 2018 04:05

What do you mean, “the processlist in MySQL appears to be hung up”?

Can you run the command “mysqladmin processlist”?

I’ll look through the logs shortly to see what you’ve sent.

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 15 Oct 2018 19:28

I attached my proccesslist in the zip file.
Thanks

User avatar
pdwalker
Posts: 1135
Joined: 18 Mar 2015 09:16

Re: Database issue not assigning token.

Post by pdwalker » 16 Oct 2018 03:04

ok, so what i can see is there is a delete query running on the mtalog table that's taking a long time to complete. And because that query locks the table, everything else stalls.

So, why is there a delete query, and why is it taking so long?

I'll need to check the mailwatch code to see if I can figure out what it is doing.

Can you also send me /etc/my.cnf?

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 17 Oct 2018 19:52

This is all that the /etc/my.cnf file has in it:

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 24 Oct 2018 12:41

It is happening again right now.
Any thoughts? Ideas?

User avatar
pdwalker
Posts: 1135
Joined: 18 Mar 2015 09:16

Re: Database issue not assigning token.

Post by pdwalker » 25 Oct 2018 03:10

Apologies, been sick, so I forgot to follow up.

I suspect your sql instance isn't configured to use enough memory.

Can you give me a "top" screenshot showing your mysqld.

For a reference, here is my top (sorted by % memory)

Code: Select all

top - 11:10:13 up 50 days, 1 min,  1 user,  load average: 0.23, 0.17, 0.13
Tasks: 241 total,   2 running, 239 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  1.2%sy, 34.3%ni, 64.5%id,  0.0%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:   8060924k total,  7575156k used,   485768k free,   391772k buffers
Swap:        0k total,        0k used,        0k free,  4642604k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
29561 clam      20   0 1259m 828m 2408 S  0.0 10.5 135:57.91 clamd
17305 mysql     20   0  858m 297m 6192 S  0.0  3.8 311:39.43 mysqld
19733 root      30  10  308m 182m  956 S  0.0  2.3  15:37.15 sendmailanalyze
 8735 root      30  10  245m 119m  948 S  0.0  1.5   7:18.58 sendmailanalyze

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 26 Oct 2018 13:02

top.PNG
top.PNG (55.12 KiB) Viewed 725 times
Looks like a high cpu %.

User avatar
pdwalker
Posts: 1135
Joined: 18 Mar 2015 09:16

Re: Database issue not assigning token.

Post by pdwalker » 29 Oct 2018 04:13

So, there is something funny going on with your mysql. What, I'm not sure.

One good thing is your IO Wait is low (0.2%wa) which would normally indicate a problem with writing to the disks if it were high.

And your memory allocation seems reasonable, so that shouldn't be it.

And your system has enough ram, so that's not it...

Do you have the mysql-slow.log available to you? For me, the file is in /var/log/mysqld-slow.log. It might be worth examining this file to find out if mysql is getting caught up on something. On my system, I have the tools pt-query-digest and mysqldumpslow that can examine that can examine this log and tell me something more about what's happening.

At this point, I don't know what else to suggest without logging into your system and watching what happens.

What version of Linux do you run your EFA instance under?

User avatar
shawniverson
Posts: 2610
Joined: 13 Jan 2014 23:30
Location: Rushville, Indiana, USA
Contact:

Re: Database issue not assigning token.

Post by shawniverson » 29 Oct 2018 07:49

I would be curious as to whether any of these are running at the time this is occurring:

Code: Select all

/usr/local/bin/mailwatch/tools/Cron_jobs/mailwatch_db_clean.php
/usr/local/bin/mailwatch/tools/Cron_jobs/mailwatch_quarantine_maint.php --clean 
/usr/local/bin/mailwatch/tools/Cron_jobs/mailwatch_quarantine_report.php 
/usr/bin/php /usr/local/bin/mailwatch/tools/Postfix_relay/mailwatch_postfix_relay.php 
/usr/bin/php /usr/local/bin/mailwatch/tools/Postfix_relay/mailwatch_mailscanner_relay.php 
Version 3.0.2.6 released! Update now to keep your eFa secure!

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 29 Oct 2018 18:52

I am actually just using the appliance for VMware that I downloaded a few years ago. (Which is CentOS)
Is it easy enough to redeploy? Or migrate? I have 40+ domains. And obviously lot's of historic information.
I can look to see if those processes are running next time.

User avatar
pdwalker
Posts: 1135
Joined: 18 Mar 2015 09:16

Re: Database issue not assigning token.

Post by pdwalker » 30 Oct 2018 06:11

I previously had performance issues running the vmware image under esxi server - and somehow I resolved the problem.

I think I had a problem with my disk controller configuration on my esxi server so disk io performance was terribly slow.

Are you running your vm image under esxi server? If so, what version?

henk
Posts: 184
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Database issue not assigning token.

Post by henk » 31 Oct 2018 12:20

In your /etc/my.cnf.d/server.cnf

Code: Select all

[mysqld]
tmpdir = /var/lib/mysql/temp
tmpdir = /var/lib/mysql/temp
Did you changed that? Should be: /tmp and remove the duplicate line.

In your mysql_err.txt there is a clear indication about your config:

2018-09-27 11:46:10 139696984684576 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.


So, can you just post the output of mysqltuner?

install mysqltuner to get info on your Mysql db 'health'. (https://github.com/major/MySQLTuner-perl)
In your root directory:

Code: Select all

wget http://mysqltuner.pl/ -O mysqltuner.pl
create passwordfile for root

Code: Select all

vi /root/.my.cnf
[client]
user=root
password=<< your MYSQLROOTPWD: from /etc/EFA-Config >>>

exec:

Code: Select all

 perl mysqltuner.pl

And please read : viewtopic.php?t=2974
Hint: mention the E.F.A.version

Some checks to perform: viewtopic.php?p=11633#p11633

User avatar
pdwalker
Posts: 1135
Joined: 18 Mar 2015 09:16

Re: Database issue not assigning token.

Post by pdwalker » 02 Nov 2018 06:42

ooooh! that's useful. I didn't know that one.

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 05 Nov 2018 20:35

Thank you for the info. Mysql tuner info attached. However, it does not have a 24 hour uptime because I had to reboot this morning.
If I need to run again tomorrow let me know.
Attachments
Mysql tuner.zip
(3.46 KiB) Downloaded 5 times

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 05 Nov 2018 20:45

These were running at the time:
root 5289 0.0 0.1 322420 16644 ? SN Nov04 0:00 /usr/bin/php -q /usr/local/bin/mailwatch/tools/Cron_jobs/mailwatch_db_clean.php
root 7215 0.0 0.1 322416 16708 ? S Nov04 0:00 /usr/bin/php /usr/local/bin/mailwatch/tools/Postfix_relay/mailwatch_postfix_relay.php --refresh
root 1937 0.0 0.1 322416 16528 ? S Nov04 0:00 /usr/bin/php /usr/local/bin/mailwatch/tools/Postfix_relay/mailwatch_mailscanner_relay.php --refresh

EFA version has been mentioned but it is EFA-3.0.2.6

I am running under ESXI 5.5

henk
Posts: 184
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Database issue not assigning token.

Post by henk » 06 Nov 2018 10:57

I missed the EFA-3.0.2.6 remark, sorry for that. Mysqltuner shows several issues,

Make snapshot or hope for the best

before you start check diskspace:

Code: Select all

df -h
Stop mail flow

# Stop services ( I have serveral things triggered by cron that use mysql)

Code: Select all

service crond stop
service mailscanner stop
service mysqld stop
Somehow you have a massive log file in /var/lib/mysql/Filter.globalvision.net.err(5G)

Empty the logfile.

Code: Select all

echo clearedthefile > /var/lib/mysql/Filter.globalvision.net.err
Now change server config.
in the mysqld section
# this is only for the mysqld standalone daemon
[mysqld]

/etc/my.cnf.d/server.cnf

Code: Select all

innodb-defragment = 1
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 1G
innodb_file_per_table = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 125M
join_buffer_size = 512K
key_buffer_size = 48M
key_cache_segments = 4
max_heap_table_size = 32M
tmp_table_size = 32M
thread_cache_size = 16
query_cache_size = 0M
query_cache_type = OFF
tmpdir = /tmp
skip-name-resolve
As I'm not sure this Mysql version supports the automatic resize , you need to remove the /var/lib/mysql/ib_logfile files to be sure.
(ib_logfile0 and ib_logfile1)

!!!To get some free space you could remove some of the older backup files in /var/EFA/backup

Now start mysql

Code: Select all

service mysqld start
and check the /var/lib/mysql/Filter.globalvision.net.err file

If everything looks ok you can analyze mysql

Code: Select all

mysqlcheck --all-databases -a
Now optimize

Code: Select all

mysqlcheck --all-databases -o
Start services and allow mail

Code: Select all

service crond start
service mailscanner start
Recheck diskspace:

Run mysqltuner again to check ( and again after 24 hour)

ethandee178
Posts: 25
Joined: 26 May 2015 16:28

Re: Database issue not assigning token.

Post by ethandee178 » 20 Nov 2018 15:14

It just happened again.
Attached is the tuner file.
THoughts? :think:
Attachments
tuner.zip
(2.72 KiB) Downloaded 2 times

Post Reply