Database issue not assigning token.
-
- Posts: 42
- Joined: 26 May 2015 16:28
Database issue not assigning token.
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
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
- shawniverson
- Posts: 3649
- Joined: 13 Jan 2014 23:30
- Location: Indianapolis, Indiana USA
- Contact:
Re: Database issue not assigning token.
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....
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....
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
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.
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.
- shawniverson
- Posts: 3649
- Joined: 13 Jan 2014 23:30
- Location: Indianapolis, Indiana USA
- Contact:
Re: Database issue not assigning token.
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.
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.
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
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.
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.
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
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?
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.
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.
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.
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
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.
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 769 times
Re: Database issue not assigning token.
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.
Can you run the command “mysqladmin processlist”?
I’ll look through the logs shortly to see what you’ve sent.
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
I attached my proccesslist in the zip file.
Thanks
Thanks
Re: Database issue not assigning token.
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?
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?
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
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
#
# 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
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
It is happening again right now.
Any thoughts? Ideas?
Any thoughts? Ideas?
Re: Database issue not assigning token.
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)
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
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
Looks like a high cpu %.
Re: Database issue not assigning token.
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?
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?
- shawniverson
- Posts: 3649
- Joined: 13 Jan 2014 23:30
- Location: Indianapolis, Indiana USA
- Contact:
Re: Database issue not assigning token.
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
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
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.
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.
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?
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.
In your /etc/my.cnf.d/server.cnf
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:
create passwordfile for root
[client]
user=root
password=<< your MYSQLROOTPWD: from /etc/EFA-Config >>>
exec:
And please read : viewtopic.php?t=2974
Hint: mention the E.F.A.version
Some checks to perform: viewtopic.php?p=11633#p11633
Code: Select all
[mysqld]
tmpdir = /var/lib/mysql/temp
tmpdir = /var/lib/mysql/temp
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
Code: Select all
vi /root/.my.cnf
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
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
Re: Database issue not assigning token.
ooooh! that's useful. I didn't know that one.
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
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.
If I need to run again tomorrow let me know.
- Attachments
-
- Mysql tuner.zip
- (3.46 KiB) Downloaded 722 times
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
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
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
Re: Database issue not assigning token.
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:
Stop mail flow
# Stop services ( I have serveral things triggered by cron that use mysql)
Somehow you have a massive log file in /var/lib/mysql/Filter.globalvision.net.err(5G)
Empty the logfile.
Now change server config.
in the mysqld section
# this is only for the mysqld standalone daemon
[mysqld]
/etc/my.cnf.d/server.cnf
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
and check the /var/lib/mysql/Filter.globalvision.net.err file
If everything looks ok you can analyze mysql
Now optimize
Start services and allow mail
Recheck diskspace:
Run mysqltuner again to check ( and again after 24 hour)
Make snapshot or hope for the best
before you start check diskspace:
Code: Select all
df -h
# Stop services ( I have serveral things triggered by cron that use mysql)
Code: Select all
service crond stop
service mailscanner stop
service mysqld stop
Empty the logfile.
Code: Select all
echo clearedthefile > /var/lib/mysql/Filter.globalvision.net.err
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
(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
If everything looks ok you can analyze mysql
Code: Select all
mysqlcheck --all-databases -a
Code: Select all
mysqlcheck --all-databases -o
Code: Select all
service crond start
service mailscanner start
Run mysqltuner again to check ( and again after 24 hour)
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
-
- Posts: 42
- Joined: 26 May 2015 16:28
Re: Database issue not assigning token.
It just happened again.
Attached is the tuner file.
THoughts?
Attached is the tuner file.
THoughts?
- Attachments
-
- tuner.zip
- (2.72 KiB) Downloaded 732 times