Mailscanner web login

Report bugs and workarounds
Post Reply
wilbourne
Posts: 52
Joined: 22 Sep 2016 09:04

Mailscanner web login

Post by wilbourne »

Hello All,

This morning When we run mailscanner web. https://efa.domain.com we recieve an error:

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



My version is 3.0.2.3


and when i try to restart mailscanner , i have this :

Code: Select all

Restarting MailScanner ...


Could not use Custom Function code MailScanner::CustomConfig::InitSQLBlacklist, it could not be "eval"ed. Make sure the module is correct with perl -wc (Error: DBD::mysql::st execute failed: Lost connection to MySQL server during query at /usr/share/MailScanner/perl/custom/SQLBlackWhiteList.pm line 164.
) at /usr/share/MailScanner/perl/MailScanner/Config.pm line 1053
Could not use Custom Function code MailScanner::CustomConfig::InitSQLWhitelist, it could not be "eval"ed. Make sure the module is correct with perl -wc (Error: DBI connect('database=mailscanner;host=localhost','mailwatch',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) at /usr/share/MailScanner/perl/custom/SQLSpamSettings.pm line 69
) at /usr/share/MailScanner/perl/MailScanner/Config.pm line 1053


and when i try to restart mysql :

Code: Select all

mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
. SUCCESS!
error_log httpd :

Code: Select all

[Wed Nov 22 11:05:52 2017] [error] [client ] PHP Fatal error:  Uncaught exception 'mysqli_sql_exception' with message 'Unknown column 'released' in 'field list'' in /var/www/html/mailscanner/functions.php:975\nStack trace:\n#0 /var/www/html/mailscanner/functions.php(975): mysqli->query('?SELECT? id AS ...')\n#1 /var/www/html/mailscanner/functions.php(1998): dbquery('?SELECT? id AS ...')\n#2 /var/www/html/mailscanner/status.php(81): db_colorised_table('?SELECT? id AS ...', 'last 50 mes...')\n#3 {main}\n  thrown in /var/www/html/mailscanner/functions.php on line 975, referer: http://ip_address/mailscanner/quarantine.php

I also try to update to 3.0.25 but the problem still persist

Any help would be more then appreticiated.
With best regards
Last edited by wilbourne on 22 Nov 2017 13:19, edited 1 time in total.
wilbourne
Posts: 52
Joined: 22 Sep 2016 09:04

Re: Mailscanner web login

Post by wilbourne »

some log :

Code: Select all

2017-11-22 13:15:37 139659218286624 [Note] /usr/sbin/mysqld (mysqld 10.1.29-MariaDB) starting as process 18782 ...
2017-11-22 13:15:37 139659218286624 [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.

Code: Select all

2017-11-22 13:15:37 7fa6753fb700 InnoDB: uncompressed page, stored checksum in field1 1380143684, calculated checksums for field1: crc32 4010322890, innodb 2313287087, none 3735928559, stored checkum in field2 0, calculated checksums for field2: crc32 4010322890, innodb 1254598370, none 3735928559, page LSN 16777216 352323584, low 4 bytes of LSN at page end 54580, page number (if stored to pge already) 671090944, space id (if created with >= MySQL-4.1.1 and stored already) 1573120
InnoDB: page type 34831 meaning PAGE TYPE CORRUPTED

Code: Select all

2017-11-22 13:15:37 140352908408576 [Note] InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt pageis an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dv.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html for information about forcing recovery.

and when Itry to connect the database sometimes it works or I have this message :
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111 "Connection refused")

or if I try to test a query during the query I have this :
'Lost connection to MySQL server during query'



any idea?
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Mailscanner web login

Post by pdwalker »

I see two problems that need further investigation:

[Wed Nov 22 11:05:52 2017] [error] [client ] PHP Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Unknown column 'released' in 'field list'' in /var/www/html/mailscanner/functions.php:975\n

and
2017-11-22 13:15:37 7fa6753fb700 InnoDB: uncompressed page, stored checksum in field1 1380143684, calculated checksums for field1: crc32 4010322890, innodb 2313287087, none 3735928559, stored checkum in field2 0, calculated checksums for field2: crc32 4010322890, innodb 1254598370, none 3735928559, page LSN 16777216 352323584, low 4 bytes of LSN at page end 54580, page number (if stored to pge already) 671090944, space id (if created with >= MySQL-4.1.1 and stored already) 1573120
InnoDB: page type 34831 meaning PAGE TYPE CORRUPTED

The problem is, how do we investigate given the limited information we have?

1/ Can you tell me what changed between when it was working and when it stopped working?

2/ Do you have your efa ugprade logs from 3023 to 3025? Do you have the logs previous to that?

3/ How about the disk space on your server? can you post the results of 'df -H' here so we can see if disk space might be an issue?

4/ Have you tried repairing the innodb tables?
wilbourne
Posts: 52
Joined: 22 Sep 2016 09:04

Re: Mailscanner web login

Post by wilbourne »

THank you for your help :clap:
here is The disk space :

Code: Select all

[root@computer ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_00-lv_root
                      7.8G  2.9G  4.6G  39% /
tmpfs                 1.9G     0  1.9G   0% /dev/shm
/dev/sda1             485M  162M  298M  36% /boot
/dev/mapper/vg_00-lv_tmp
                      976M  231M  695M  25% /tmp
/dev/mapper/vg_00-lv_var
                       26G  6.4G   19G  26% /var
none                  1.9G  404K  1.9G   1% /var/spool/MailScanner/incoming

The problem come from mysql, I cannot dump or restore my database or repairing and my appliance is on autoupdate mode. So I think an kernel update are broken mysql or something like this. I cannot find a trace of error on log about mysql or database corrup or a failed update kernel.

All services work postfix/spamassasin/clamav etc... but I cannot open mailwatch and the spam are not registred on the quarantine database.

So, I have install an other appliance with the release 3.0.2.5 and I copy a backup locate at /var/EFA/backup and the quarantine folder to the new appliance and I change the traffic flow to the new .
Last edited by wilbourne on 29 Nov 2017 10:05, edited 1 time in total.
wilbourne
Posts: 52
Joined: 22 Sep 2016 09:04

Re: Mailscanner web login

Post by wilbourne »

and here is the update log
LOG_EFA.7z
log update
(16.1 KiB) Downloaded 428 times
henk
Posts: 517
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Mailscanner web login

Post by henk »

Hi Wilbourne,

In update-2017-11-22-10%3A05%3A37.log there are multiple serious errors (connect to Mysql).

Starts with the backup on line 80:

Code: Select all

Beginning System Backup at 11222017 100543
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111 "Connection refused")" when trying to connect
Was Mysql running??

See line 2663

Code: Select all

Fix schema for username field in `audit_log` table.................[0;31m ERROR[0m
Database error: Lost connection to MySQL server during query - SQL = 'ALTER TABLE `audit_log` CHANGE `user` `user` VARCHAR(191) NOT NULL DEFAULT''
Somehow you missed the ugrade of Mysql. It should look something like this:

Code: Select all

Testing connectivity to the database .................................^[[1;32m OK^[[0m

Updating database schema:
 - Convert database to utf8...........................................^[[0;32m ALREADY DONE^[[0m

 - Drop `geoip_country` table.........................................^[[0;32m ALREADY DROPPED^[[0m
 - Drop `spamscores` table............................................^[[0;32m ALREADY DROPPED^[[0m
 - Add autorelease table to `mailscanner` database....................^[[0;32m ALREADY EXIST^[[0m
 - Add mtalog_ids table to `mailscanner` database.....................^[[0;32m ALREADY EXIST^[[0m
 - Add resetid, resetexpire and lastreset fields in `users` table.....^[[0;32m ALREADY EXIST^[[0m
 - Add login_expiry and login_timeout fields in `users` table.........^[[0;32m ALREADY EXIST^[[0m

 - Fix schema for username field in `audit_log` table.................^[[1;32m OK^[[0m
 - Fix schema for id field in `blacklist` table.......................^[[0;32m ALREADY DONE^[[0m
 - Fix schema for id field in `whitelist` table.......................^[[0;32m ALREADY DONE^[[0m
 - Fix schema for username field in `users` table.....................^[[0;32m ALREADY DONE^[[0m
 - Fix schema for username field in `user_filters` table..............^[[0;32m ALREADY DONE^[[0m
 - Fix schema for spamscore field in `users` table....................^[[0;32m ALREADY DONE^[[0m
 - Fix schema for highspamscore field in `users` table................^[[0;32m ALREADY DONE^[[0m
 - Fix schema for timestamp field in `maillog` table..................^[[1;32m OK^[[0m
 - Fix schema for password field in `users` table.....................^[[0;32m ALREADY DONE^[[0m
 - Fix schema for fullname field in `users` table.....................^[[0;32m ALREADY DONE^[[0m
 - Fix schema for rule_desc field in `mcp_rules` table................^[[0;32m ALREADY DONE^[[0m
 etc,etc
I would restore EFA and stop processes like: mailscanner/crond/webmin and make sure there is plenty of diskspace/memory for Mysql.

Before the upgrade make sure you can login to Mysql:

Code: Select all

 mysql -uroot
Run Mysqltuner to check Mysql config. https://github.com/major/MySQLTuner-perl

Analyze db's :

Code: Select all

mysqlcheck --all-databases --analyze
Check disk space used:

Code: Select all

find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
917M    /var/lib/mysql/
872K    /var/lib/mysql/sqlgrey
76M     /var/lib/mysql/mailscanner
1.2M    /var/lib/mysql/mysql
8.0K    /var/lib/mysql/performance_schema
24K     /var/lib/mysql/efa
69M     /var/lib/mysql/sa_bayes
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
Post Reply