Educating the world

Our blog has over 10,000 readers a month

Fixing problems caused by updating Plesk

August 16th, 2011

Permanently deleting MediaWiki pages and revisions

August 3rd, 2011

Spammers target wiki’s because many of them are mis-configured. They use robot’s to submit page updates and page additions. I could understand it if they were adding advertising, redirecting to other sites or adding Trojans but they’re not. The page updates are nonsense like:

AQqGJP , [url=http://cqvbgfwzwypk.com/]cqvbgfwzwypk[/url], [link=http://pfjbydaloorn.com/]pfjbydaloorn[/link], http://bkxerafkfcgr.com/

or random sentences like:

Thank God! Someone with barnis speaks!

It serves no purpose other than giving me more work to do. Mind you on this occasion I got a blog article out of it, so it’s not all bad!

In spite of having my configuration set the way I want it they have some how managed to by pass them; there is probably a bug in MediaWiki, so the next job is to install the latest version.

The central table inside MediaWiki is mw_revision. It holds all the versions of each page. The history of a document is a timestamped ordered list of revision records.

MediaWiki uses hard coded numbers to represent the namespace column value in the mw_page table, so we will create a new table to hold the values. These will make it easier to see which namespace our articles are in:

DROP TABLE IF EXISTS cats;
CREATE TABLE cats
(
id INT PRIMARY KEY
,name VARCHAR(20)
);
INSERT INTO cats(id, name) VALUES
(’0′,'’)
,(’-1′,’Special:’)
,(’-2′,’Medi:’)
,(’1′,’Talk:’)
,(’2′,’User:’)
,(’3′,’User_talk:’)
,(’4′,’Project:’)
,(’5′,’Project_talk:’)
,(’6′,’File:’)
,(’7′,’File_talk:’)
,(’8′,’MediaWiki:’)
,(’9′,’MediaWiki_talk:’)
,(’10′,’Template:’)
,(’11′,’Template_talk:’)
,(’12′,’Help:’)
,(’13′,’Help_talk:’)
,(’14′,’Category:’)
,(’15′,’Category_talk:’)
;

If we look at one of our pages:

SELECT page_id, CONCAT(name,page_title) full_name
FROM mw_page, cats
WHERE id = page_namespace
AND page_title like ‘%How_to_re%’;

We can see that there are 2 pages: the original and an extra page with the same name but in a different namespace.

+---------+-----------------------------------------+
| page_id | full_name                               |
+---------+-----------------------------------------+
|       9 | How_to_report_problems_effectively      |
|      79 | Talk:How_to_report_problems_effectively |
+---------+-----------------------------------------+

We will investigate each page in turn.

First page 9 “How to report problems effectively". Here is the SQL to look at the revision history of the page:

SELECT @page:=9;
SELECT rev_id, rev_page, rev_text_id,
SUBSTR(rev_comment,1,15) comment, rev_user,
rev_user_text, rev_timestamp, rev_len, rev_parent_id
FROM mw_revision
WHERE rev_page=@page
ORDER BY rev_timestamp;

We can see from the results below that the last 3 entries are spam.

+--------+----------+-------------+-----------------+----------+-----------------+----------------+---------+---------------+
| rev_id | rev_page | rev_text_id | comment         | rev_user | rev_user_text   | rev_timestamp  | rev_len | rev_parent_id |
+--------+----------+-------------+-----------------+----------+-----------------+----------------+---------+---------------+
|     10 |        9 |          10 | New page:  In a |        1 | Davidnewcomb    | 20071210142814 |    4057 |             0 |
|     11 |        9 |          11 |                 |        1 | Davidnewcomb    | 20071210142854 |    4022 |            10 |
|     13 |        9 |          13 |                 |        1 | Davidnewcomb    | 20071211110321 |    4584 |            11 |
|     25 |        9 |          25 |                 |        1 | Davidnewcomb    | 20071216172547 |    4603 |            13 |
|     79 |        9 |          79 |                 |        1 | Davidnewcomb    | 20071220185710 |    6291 |            25 |
|     80 |        9 |          80 |                 |        1 | Davidnewcomb    | 20071220192557 |    6938 |            79 |
|    770 |        9 |         770 |                 |        1 | Davidnewcomb    | 20100322103154 |    6939 |            80 |
|    833 |        9 |         833 | wIojbsxYCEMAiHX |        0 | 195.251.148.31  | 20110608224510 |      45 |           770 |
|    841 |        9 |         841 | FAjVBqBMj       |        0 | 50.19.100.18    | 20110609091850 |      59 |           833 |
|    854 |        9 |         854 | yrZXYJobVab     |        0 | 201.248.150.100 | 20110609141813 |     135 |           841 |
+--------+----------+-------------+-----------------+----------+-----------------+----------------+---------+---------------+

We must delete the text records associated with those revisions:

DELETE FROM mw_text
WHERE old_id IN (833, 841, 854);

Next we need to delete the revision entries.

DELETE FROM mw_revision
WHERE rev_id IN (833, 841, 854);

Now that the page is clean we must solder up the pages table to make that page point to the latest version. Firstly record the timestamp of the most recent revision. Using the timestamp and the page number as an index collect the latest revision number and the latest revision’s length.

SELECT @most_recent := MAX(rev_timestamp)
FROM mw_revision
WHERE rev_page = @page;

SELECT @latest := rev_id, @latest_len := rev_len
FROM mw_revision
WHERE rev_timestamp = @most_recent;

When we have everything we need, update the page table:

UPDATE mw_page SET
page_latest = @latest,
page_len = @latest_len
WHERE page_id = @page;

Next we’ll check out the other page with the same name:

SELECT @page:=79;
SELECT rev_id, rev_page, rev_text_id, substr(rev_comment,1,15) comment,
rev_user, rev_user_text, rev_timestamp, rev_len, rev_parent_id
FROM mw_revision
WHERE rev_page=@page
ORDER BY rev_timestamp DESC;

We can see there is only one entry and that is a spam entry.

+--------+----------+-------------+---------------+----------+---------------+----------------+---------+---------------+
| rev_id | rev_page | rev_text_id | comment       | rev_user | rev_user_text | rev_timestamp  | rev_len | rev_parent_id |
+--------+----------+-------------+---------------+----------+---------------+----------------+---------+---------------+
|    825 |       79 |         825 | QorQCGouPrXjN |        0 | 74.119.98.5   | 20110608164848 |      26 |             0 |
+--------+----------+-------------+---------------+----------+---------------+----------------+---------+---------------+

We must delete the text records associated with those revisions:

DELETE FROM mw_text WHERE old_id IN (825);
DELETE FROM mw_revision WHERE rev_id IN (825);

This is a page we want to get rid of so we will just delete the entry from the page table.

DELETE FROM mw_page WHERE page_id = @page;

If you are going to be doing lots of this then here are some procedures to help. This SQL will produce a report to help you identify pages that have been attacked.

SELECT rev_id, rev_page, SUBSTR(CONCAT(name, page_title), 1, 10) full_name, substr(rev_comment, 1, 10) comment, rev_user_text, replace(substr(old_text, 1, 50), “\n","") txt
FROM mw_revision, mw_text, mw_page, cats
WHERE old_id = rev_text_id
AND rev_page = page_id
AND id = page_namespace
ORDER BY rev_page, rev_timestamp;

This procedure helps to delete all the artefacts for a page.

DELIMITER //
CREATE PROCEDURE delete_page (IN p_id INT)
BEGIN
DELETE FROM mw_text WHERE old_id IN
(SELECT rev_text_id
FROM mw_revision
WHERE rev_page = p_id);

DELETE FROM mw_revision WHERE rev_page = p_id;
DELETE FROM mw_page WHERE page_id = p_id;
END //
DELIMITER ;

It can be called by:

CALL delete_page(123);

Where as this SQL deletes all the revisions after a point. To find the identifier of the last good version, use the web interface and go to the page that has been altered. Click the history link for the page. Hover over the revision’s “addition time” link. The number you want is in the oldid part of the URL. In this case, we don’t need the page id because a revision can only be part of one page. You can compare the selected revisions to make sure you have the correct one.

DELIMITER //
CREATE PROCEDURE delete_after_rev
(IN p_last_good_rev INT)
BEGIN

SELECT @page:= rev_page
FROM mw_revision
WHERE rev_id = p_last_good_rev;

DELETE FROM mw_text WHERE old_id IN
(SELECT rev_text_id FROM mw_revision
WHERE rev_page = @page AND rev_id > p_last_good_rev);

DELETE FROM mw_revision
WHERE rev_page = @page AND rev_id > p_last_good_rev;

SELECT @most_recent := MAX(rev_timestamp)
FROM mw_revision WHERE rev_page = @page;

SELECT @latest := rev_id, @latest_len := rev_len
FROM mw_revision
WHERE rev_timestamp = @most_recent;

UPDATE mw_page SET
page_latest = @latest,
page_len = @latest_len
WHERE page_id = @page;

END //
DELIMITER ;

It can be called by:

CALL delete_after_rev(1234);

When the page is over written by the spammer it severs all the Category and Section links. A new page is generated and cached. The side effects of this will manifest themselves as incomplete Category pages, pages not appearing and your searches not returning the correct results. Click the Special:LonelyPages page to display the Orphan pages; this special page can act as a guide to help you track down all the pages that have been tampered with.

To remove the page cache run this SQL:

TRUNCATE mw_objectcache;

As pages are requested they will be rebuilt and cached so there will be a short lag as this happens.

To rebuild the search indexes drop to the command line and run the rebuildall command. There is an .htaccess to prevent you from running this file via the web. I tried removing this but the program wouldn’t run, so it must be run from the command line.

cd <wiki-root>
php maintenance/rebuildall.php

My wiki is less that a hundred pages so the rebuild only took a second. The output is here for your reference:

PHP Warning:  xxx
** Rebuilding fulltext search index (if you abort this will break searching; run this script again to fix):
Dropping index...
Rebuilding index fields for 83 pages...
0

Rebuild the index...
Done.


** Rebuilding recentchanges table:
Loading from page and revision tables...
$wgRCMaxAge=7862400 (91 days)
Updating links and size differences...
Loading from user, page, and logging tables...
Flagging bot account edits...
Flagging auto-patrolled edits...
Deleting feed timestamps.
Done.


** Rebuilding links tables -- this can take a long time. It should be safe to abort via ctrl+C if you get bored.
Refreshing redirects table.
Starting from page_id 1 of 83.
Refreshing links table.
Starting from page_id 1 of 83.
Retrieving illegal entries from pagelinks... 0..0
Retrieving illegal entries from imagelinks... 0..0
Retrieving illegal entries from categorylinks... 0..0
Retrieving illegal entries from templatelinks... 0..0
Retrieving illegal entries from externallinks... 0..1
Done.

If you don’t have access to the command line then running a web spider across your site would force the pages to rebuild and update their search caches but it would take much longer.

When you are all done, you ought to clean up the procedures and tables that we have created because they are no longer needed:

DROP TABLE cats;
DROP PROCEDURE delete_page;
DROP PROCEDURE delete_after_rev;

Happy repairing !

Thanks to David Aldridge for giving me a starting point.

Want to remove a file, but something is locking it?

August 1st, 2011

Tired of not being able to delete a file or folder because something is locking it? Want to move a folder but for some reason you can’t? Did you close down an application and for some reason you still can’t delete/move/rename files it was using? Do you miss your Unix box? Well LockHunter is the application for you!

It works as an application or via the context menu to tell you which processes are locking files that are preventing you from being happy. You can explicitly remove the locks owned by the application or delete the file which would be really handy for trying to clean up a machine infected with malware.

I gave it a go and it was pretty good.
http://www.lockhunter.com/

Upgrading Plesk from PSA_10.2.0 to PSA_10.3.0

July 9th, 2011

I’ve just had a bit of an “oh shit” moment! So thought I’d blog how I got out of it.

Parallels is the company that produces Plesk. On the whole it’s a pretty good bit of server software which helps to manage a single machine that supports many virtual hosts without using virtualisation. It uses the idea of Customers, Subscriptions, Clients and Domains in order to allow the owner to basically sub-let their server to other resellers. The main criticism is that its update cycle is unpredictable and really slow. They tend to upgrade the integral software as new (chargeable) major version releases with no upgrade path. It’s fine for them but business moves a bit quicker than they do. We need new versions of PHP and MySQL a bit quicker because the latest application software often relies on them.

I wanted to install the latest Lime Survey but couldn’t so I had to upgrade the whole server from Plesk 9 to Plesk 10 and transfer all the domains with their set up over to the new appliance. Not a particularly nice job!

A month ago I was updating the mysql client libraries with yum and it updated the Plesk’s PHP as a dependency. The update took PHP from version 5.0.2 to 5.3. I had been wanting to update PHP for a while because the version that came with Plesk was too old for most of the applications I wanted to upgrade to. I was holding back because I couldn’t find enough information on what effect a newer version of PHP would have on the Plesk system software and interfaces; needless the say the Plesk website and forums were useless as usual. I don’t have a development machine to play with so I wanted to be sure before attempting anything. Anyway the surprise upgrade seemed to have worked, but when I tried to install the Plesk 10.3 maintenance upgrade things went a bit pear shaped.

Plesk updates are usually pretty reliable. I say pretty reliable; there are always problems but nothing I can’t fix, or fix, or fix, or fix, or fix, or fix, or fix.

This time was a little different though. The maintenance release failed half way through with the message:

===> Installing /usr/sbin/suexec replacement
Trying to backup original /usr/sbin/suexec… done
`/usr/local/psa/suexec/psa-suexec’ -> `/usr/sbin/suexec’
Trying to set up APS controller database… Attempting to check APS db connection via ODBC, using /usr/bin/isql (empty output is good)
[ISQL]ERROR: Could not SQLConnect
Failed, rc = 1.
Failed to establish test connection. Cleaning up.

ERROR while trying to establish test connection to APS database via ODBC
Check the error reason(see log file: /tmp/plesk_10.3.0_installation.log), fix and try again

***** problem report *****
ERROR while trying to establish test connection to APS database via ODBC
Check the error reason(see log file: /tmp/plesk_10.3.0_installation.log), fix and try again
Execute command rm -f /tmp/pp-bootstrapper-mode.flag
Execute command rm -f /var/lock/parallels-panel-maintenance-mode.flag
Execute command touch /var/lock/parallels-panel-upgrade-failure.flag
Execute command /usr/sbin/getenforce
Disabled
Error: An error occurred during performing of installation POST actions (see log for details).
Warning! Not all packages were installed.
Please check if your system services are operable.
Please resolve this issue and try installing the packages again.
If you cannot resolve the issue on your own, contact product technical support for assistance.

All my virtual domains were gone! Check the psa database and the configuration was still in-tacked - phew!!! Going to each web address just displayed the default Apache starting page. Checked the logs and they were full of 404 errors. Did an strace to watch which configuration files where being read and none of them were. Rebuilding Plesk’s virtual domain’s specific conf files would have been a nightmare. Plesk used to just Include the conf file from each virtual domain’s conf folder but now it’s all about the generation of the file i.e. all the files have names like 13099743180.74277400_httpd.include, so it’s a lot more complicated!

I needed a way of rebuilding all of Plesk’s Apache configuration to include the httpd.conf files specific to each domain. Did a bit of trawling and came up with this which did the trick.

/usr/local/psa/admin/sbin/httpdmng −−reconfigure−all

Thank you Server God!

Logged into the the Plesk administration via the web interface and it shows the version is still 10.2 (instead of 10.3). I feel like I’ve had a narrow escape so I’m not going to do any more upgrades until the next major release - where I’ll have to update the server &amp;#58;&amp;#40; but at least it will be clean.

Thanks to PleskGUY for the knowledge base article on Parallels web site. The article says that in order for the patch to work you must be able to access the database via the localhost IP address:

mysql -h 127.0.0.1 -P 3306 -uadmin -p`cat /etc/psa/.psa.shadow`

I checked my my.cnf and I had set bind-address=<my-external-ipaddress> in order to do a server update. I can understand why it wasn’t picking up 127.0.0.1 but strangely when I substituted the -h address for localhost it worked. Pinging localhost showed it resolved to 127.0.0.1. I think this is one of those pain in the ass permission peculiarities from MySQL’s grant/revote access control system.

I removed the bind-address from /etc/my.cnf and restarted mysqld with:

/etc/init.d/mysqld restart

Rerun the patch update and it… (doing out-of-hours)

Problem 2: I found that the FTP password to one of the sites had been cancelled. I logged back into the Plesk interface and changed it back to what it was and it seemed to take it without complaining.

Problem 3: (5 August 2011) It looks like the AWStats is screwed again. I was checking my stats and found there is a big gap in the monthly reports and they are out of order again: 2010-01, 2010-02, 2010-03, 2010-04, 2010-05, 2010-06, 2010-07, 2010-08, 2011-08, 2011-07, 2011-05, 2011-06. Honestly Plesk are bloody useless.
In previous versions you used to be able to tweak the number of months you kept on a per domain bases, but now it looks like there is one value per server. I logged into the control panel clicked through to Settings -< Settings of Server Statistics. Then changed the Retain web and traffic statistics for to 12 (months). I could only find that this was held in the misc table of the psa database in the record with the key stat_ttl. I couldn’t find where this links to awstats. I’ve probably have dig out my old awstat-stats-fixer program.

**UPDATE**

Problem 4: (16 August 2011): I wanted to edit a user’s email options through the domain’s control panel. Plesk directed me to the page https://example.com/smb/user/overview/id/<num> which threw the following error:

Internal error: Specified column “imNumber” is not in the row
Message Specified column “imNumber” is not in the row
File Abstract.php
Line 182
Type Zend_Db_Table_Row_Exception

The Abstract.php refers to /usr/local/psa/admin/plib/Zend/Db/Table/Row/Abstract.php which I got from the Plesk error log located at /var/log/sw-cp-server/error_log.

To correct this problem log in to the psa database and run the following SQL:

ALTER TABLE smb_users ADD imNumber VARCHAR(255);
ALTER TABLE smb_users ADD imType INT(11);
ALTER TABLE smb_users ADD isLegacyUser INT(11);
ALTER TABLE smb_users ADD additionalInfo TEXT;

There’s no need to restart anything it should just start working.

Installing Python 2.7 with MySQL support on Plesk CentOS 5.5

July 7th, 2011

CentOS 5.5 is dependant on Python 2.4. Everything will break if you change it, so we must install a newer version of Python in a different place.

Surprisingly my 1&1 CentOS didn’t come with gcc installed so I had to install it myself.

yum -y install gcc gdbm-devel readline-devel ncurses-devel zlib-devel bzip2-devel sqlite-devel db4-devel openssl-devel tk-devel bluez-libs-devel make

Important: Before you go any further please read Upgrading Plesk from PSA_10.2.0 to PSA_10.3.0

I’m going to need the MySQL development libraries so install them next:

yum -y install mysql-devel

The big surprise here was that it installed PHP 5.3 as a dependency! which I was not expecting, but everything still seems to be working!

Now that we have the platform set up, we’ll download all the sources we need and unpack them.

  1. Create a folder for us to work in:

    mkdir /home/mrn/dev/python
    cd /home/mrn/dev/python

  2. Go to the Python download page:
    http://www.python.org/download/releases/
  3. Download and uncompress:

    wget http://www.python.org/ftp/python/2.7.1/Python-2.7.1.tar.bz2
    tar -xjvf Python-2.7.1.tar.bz2

  4. Go to the Python MySQL module page:
    http://sourceforge.net/projects/mysql-python/
  5. Download and uncompress:

    wget http://sourceforge.net/projects/mysql-python/files/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz
    tar -xzvf MySQL-python-1.2.3.tar.gz

  6. Download the Easy Installer set up:

    wget http://peak.telecommunity.com/dist/ez_setup.py

Build Python

  1. Go to Python folder:

    cd Python-2.7.1

  2. Check there is nothing already living in /usr/local/python27 because that is where we are going to install out copy of Python.
  3. Run the source configuration program:

    ./configure –prefix=/usr/local/python27

  4. Start the build:

    make

  5. Install (as root):

    make install

If you don’t have root privileges then you can change the −−prefix to a location where you can write.

Ok, to test it we’ll try to start the interactive prompt:

# /usr/local/python27/bin/python
Python 2.7.1 (r271:86832, Jun 2 2011, 16:02:46)
[GCC x.x.x xxxxxxxxx (xxxxx x.x.x-xx)] on xxxxx
Type “help", “copyright", “credits” or “license” for more information.

We must add python to our PATH so that the next part will pick up our new python and not the old one.

# export PATH=/usr/local/python27/bin:$PATH

As a prerequisite for the Python MySQL’s module we need to install setuptools. There is a really handy video on ShowMeDo here on how to do it. Don’t forget to run this as root (if you installed it as root) because it will modify the Python installation directory. The import should return without an error.

# cd ..
# python ez_setup.py

To test it, try to import setuptools.

# /usr/local/python27/bin/python
Python 2.7.1 (r271:86832, Jun 2 2011, 16:02:46)
[GCC x.x.x xxxxxxxxx (xxxxx x.x.x-xx)] on xxxxx
Type “help", “copyright", “credits” or “license” for more information.
>>> import setuptools
>>>

Compile the MySQL module

Now that we have sorted out the Python installation go to the MySQL Python directory. The site.py default file doesn’t need changing so we can use it as is. There is the option to change the MySQL Python module name from _mysql to something else but there’s not much point because every example you’ll find on the Internet has it as the default; you’ll spend the rest of your life searching and replacing! Again you’ll have to run this as root if you did earlier.

# cd MySQL-python-1.2.3
# python setup.py build
# python setup.py install

To test it, try to import _mysql, it should return without an error.

# /usr/local/python27/bin/python
Python 2.7.1 (r271:86832, Jun 2 2011, 16:02:46)
[GCC x.x.x xxxxxxxxx (xxxxx x.x.x-xx)] on xxxxx
Type “help", “copyright", “credits” or “license” for more information.
>>> import setuptools
>>>

The first time I did this I got the following error message, but after I’d logged out and logged back in again the error message went away and I couldn’t reproduce it.

/usr/local/python27/lib/python2.7/site-packages/MySQL_python-1.2.3-py2.7-linux-x86_64.egg/_mysql.py:3: UserWarning: Module _mysql was already imported from /usr/local/python27/lib/python2.7/site-packages/MySQL_python-1.2.3-py2.7-linux-x86_64.egg/_mysql.pyc, but /home/djn/dev/python/MySQL-python-1.2.3 is being added to sys.path

The unexpected upgrade of PHP from version 5.1 to 5.3 threw up the following error message on all my .php pages:

SYSTEM WARNING: date_default_timezone_get() [function.date-default-timezone-get]: It is not safe to rely on the system’s timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected ‘Europe/London’ for ‘BST/1.0/DST’ instead

Fixed it by adding the following line to /etc/php.ini and restarting the web server.

date.timezone = ‘Europe/London’