Page 1 of 2

Database issue not assigning token.

Posted: 27 Aug 2018 12:39
by ethandee178
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?

BTW this is that easy release script:


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

Re: Database issue not assigning token.

Posted: 27 Aug 2018 21:47
by shawniverson
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....

Re: Database issue not assigning token.

Posted: 17 Sep 2018 13:31
by ethandee178
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.

Re: Database issue not assigning token.

Posted: 18 Sep 2018 21:46
by shawniverson
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 I want to try to reproduce this problem.

The token is important to prevent someone from exploiting the message release code.

Re: Database issue not assigning token.

Posted: 24 Sep 2018 18:11
by ethandee178
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-"
And you are welcome to take a look into my system next time it happens if you think it'll help.

Re: Database issue not assigning token.

Posted: 27 Sep 2018 15:08
by ethandee178
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?

Re: Database issue not assigning token.

Posted: 02 Oct 2018 04:01
by pdwalker
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.

Re: Database issue not assigning token.

Posted: 11 Oct 2018 13:12
by ethandee178
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.

Re: Database issue not assigning token.

Posted: 12 Oct 2018 04:05
by pdwalker
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.

Re: Database issue not assigning token.

Posted: 15 Oct 2018 19:28
by ethandee178
I attached my proccesslist in the zip file.

Re: Database issue not assigning token.

Posted: 16 Oct 2018 03:04
by pdwalker
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?

Re: Database issue not assigning token.

Posted: 17 Oct 2018 19:52
by ethandee178
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

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

Re: Database issue not assigning token.

Posted: 24 Oct 2018 12:41
by ethandee178
It is happening again right now.
Any thoughts? Ideas?

Re: Database issue not assigning token.

Posted: 25 Oct 2018 03:10
by pdwalker
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

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

Re: Database issue not assigning token.

Posted: 26 Oct 2018 13:02
by ethandee178
top.PNG (55.12 KiB) Viewed 3398 times
Looks like a high cpu %.

Re: Database issue not assigning token.

Posted: 29 Oct 2018 04:13
by pdwalker
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?

Re: Database issue not assigning token.

Posted: 29 Oct 2018 07:49
by shawniverson
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_quarantine_maint.php --clean 
/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 

Re: Database issue not assigning token.

Posted: 29 Oct 2018 18:52
by ethandee178
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.

Re: Database issue not assigning token.

Posted: 30 Oct 2018 06:11
by pdwalker
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?

Re: Database issue not assigning token.

Posted: 31 Oct 2018 12:20
by henk
In your /etc/my.cnf.d/server.cnf

Code: Select all

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'. (
In your root directory:

Code: Select all

wget -O
create passwordfile for root

Code: Select all

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


Code: Select all


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

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

Re: Database issue not assigning token.

Posted: 02 Nov 2018 06:42
by pdwalker
ooooh! that's useful. I didn't know that one.

Re: Database issue not assigning token.

Posted: 05 Nov 2018 20:35
by ethandee178
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.

Re: Database issue not assigning token.

Posted: 05 Nov 2018 20:45
by ethandee178
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-

I am running under ESXI 5.5

Re: Database issue not assigning token.

Posted: 06 Nov 2018 10:57
by henk
I missed the EFA- 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/

Empty the logfile.

Code: Select all

echo clearedthefile > /var/lib/mysql/
Now change server config.
in the mysqld section
# this is only for the mysqld standalone daemon


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
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/ 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)

Re: Database issue not assigning token.

Posted: 20 Nov 2018 15:14
by ethandee178
It just happened again.
Attached is the tuner file.
THoughts? :think: