Fix Mysql “128 rollback segment(s) are active” and “Waiting for purge to start”

The main problem is not the fact that you are receiving the error when issuing service mysql status, but the fact that the database is stuck and responding very slowly.

You can get the specified error after updating Mysql server to 10.0.x and this happens due to innodb storage formats. Probably the previous Mysql server version used Barracuda file format and after the update you use the Antelope storage format.

What you want to do is change the format to Barracuda and make this setting global.

The first thing to do is to backup all your databases !!

Edit your Mysql configuration file (/etc/mysql/my.cnf or other- depending on distro) and add:

innodb-rollback-segments=256
innodb_file_format=Barracuda

Save and restart mysql process.

The login to the Mysql server and issue :

mysql -u admin -p`cat /etc/psa/.psa.shadow`         //for Plesk

mysql -u admin -p         // without Plesk

show variables like “%innodb_file%”;

If you see Antelope, then do this:

SET GLOBAL innodb_file_per_table=1;

SET GLOBAL innodb_file_format=Barracuda;

GRANT ALL PRIVILEGES ON yourdb.* TO ‘youruser’@’localhost’;

FLUSH PRIVILEGES;

Restart mysql (and do a reboot?).

In the end you should have something like this:

If this doesn’t work, you might want to try THIS first and restart the process described above.

Compress all Mysql tables from a database

db-table-list.txt contains the list of tables from a specific db, exported with:

use somedb;

SELECT * FROM somedb;

And filtered with:

cut -d’|’ -f 2 ./db-table-list.txt

Here’s the script:

#!/bin/bash

for tbl in $(cat ./db-table-list.txt); do
mysql –user=root \
–password=”suchsecure” \
–execute=”use somedb;” \
–execute=”ALTER TABLE $tbl ROW_FORMAT=Compressed;”
done <<< “$tbl”

Fixing Plesk Postix sending emails locally

I had this issue too. Any email being sent to my company domain was being sent locally. This is (I believe) because in Plesk (yes it’s a Plesk issue).

So basically it’s going oh soandso@company.com is the registered user lets send any @company.com emails locally or something like that.

Anyway, I have had to fix this twice now and I did it by editing the /etc/postfix/main.cf file and commenting out the lines that started with “virtual”.

Block failure notice emails in qmail

I have Plesk on my Linux server and I receive lots of spam emails from the root account on my server.
Here’s what happened. My forum used to send emails to non-existent email accounts around the web and I used to receive the failure notices for them.
To block failure notice emails in qmail that were forwarded to my main email I had to modify the aliases from

/var/qmail/alias

Here’s a sample email I received:

MAILER-DAEMON@nixware.net
Mar 30 (7 days ago)

to postmaster 
Hi. This is the qmail-send program at nixware.net.
I tried to deliver a bounce message to this address, but the bounce bounced!

:
2a00:1450:4013:0c01:0000:0000:0000:001a does not like recipient.
Remote host said: 550-5.1.1 The email account that you tried to reach does not exist. Please try
550-5.1.1 double-checking the recipient's email address for typos or
550-5.1.1 unnecessary spaces. Learn more at
550 5.1.1 http://support.google.com/mail/bin/answer.py?answer=6596 q5si14669450wjx.9 - gsmtp
Giving up on 2a00:1450:4013:0c01:0000:0000:0000:001a.

--- Below this line is the original bounce.

Return-Path: &lt;&gt;
Received: (qmail 8634 invoked for bounce); 29 Mar 2015 23:50:02 +0200
Date: 29 Mar 2015 23:50:02 +0200
From: MAILER-DAEMON@nixware.net
To: root@nixware.net
Subject: failure notice

As you can see, the postmaster account was receiving the spam from MAILER-DAEMON and MAILER-DAEMON was forwarding them to my gmail.com account. Pretty nasty…

The fix:
Go to

/var/qmail/alias

do a

ls -alh

and you will see a couple of hidden config files.

Inside each of those file I had my @gmail account. With the next script I overwrote the gmail account with a non-existent email:

 

 

for FILE in ./.qmail-*;do echo "nonexistent@nodomain.tld" > $FILE;done

Restart/reload qmail:

service qmail reload

That’s it! You should not receive any spam from any of the root, mailman or postmaster accounts.

How to fix Plesk mysql Fatal Exception

Today I received the following error when I was trying to use one of my forums and the server load was huge: 192.

It seems that there were too many connections towards the mysql server.

"ERROR: PleskFatalException Unable to connect to database: mysql_connect() [function.mysql-connect]: Too many connections"

 

The number of connections can be checked with this command:

[root@nyx ~]# mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` extended-status | grep Max_used_connections
| Max_used_connections | 127 |
[root@nyx ~]#

I checked the Plesk Mysqld configuration and I had:

set-variable=max_connections=100
set-variable=max_user_connections=0 ##this is a bad idea because there won't be any limit to the sql resources a connection can access

I modified the /etc/my.cnf file with a new limit to both max_connections and max_user_connections:

mysql connections

And the server load started decreasing dramatically from 190 to about 18. Still too high but it was a step forward.
Checked the /var/log/mysqld.log and found some errors, so yeah, the database needed some repairing to be done.

PS: the mysql process list can be viewed like this:

 

mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` processlist | more