Volatile Yard 挥洒自如 Write anything I want to write...

Disaster

In the morning of Christmas 2006, I visited this site and encountered a 'disaster'... the Drupal engine complained that it failed to connect to the MySQL database server! Did somebody attack my site? How should I repair it?

I logged in via SSH remotely, and found that the MySQL server had been shut down abnormally. I restarted it and the site was alive again as usual! But when I wanted to check the Drupal logs, the page didn't load... no, I waited and found that it could load but took more than 10 minutes! What happened?

Diagnosis and Repair

I suspected that there were some problems in the Drupal database. First I checked the number of entries in each table with: (Masked off some critical things here. You think I will show you my password huh?)

\$ mysqlshow -uxxxx -pyyyy --count drupal

And I got it --- the watchdog table contained more than 1 million rows! I used mysqlbinlog to find out all recent queries. Well, Drupal had logged more than 1 million entries of PHP errors concerning the failure of opening some ancestor directory that never exists (which involves something like opendir('drutex/../../../../../../../.. (repeated many times) '). To restore the database performance, I did

mysql> DELETE FROM watchdog WHERE message like 'opendir%';

and I had only about 1,000 rows left in the watchdog table. Surfed the logs page again, but it still took more than 10 minutes! Something still went wrong but I suspected that it's still related to the database. So I modified the function _db_query() in the drupal file includes/database.mysqli.inc to record all queries and the time taken, and added something in index.php to store the results:

if (\$_SERVER['REMOTE_ADDR'] == 'ip.of.my.computer') {
  file_put_contents('/somepath/result.txt', print_r($queries, 1));
}

Surfed the logs page again, and examined the output file. There were 3 queries where each took about 280 seconds, and all of them involved the watchdog table! To confirm the problem, I performed a simple SELECT COUNT(*) FROM watchdog; and it took about 280 seconds. So the table must have been corrupted somehow.

I did a CHECK TABLE watchdog MEDIUM. After 15 minutes, MySQL told me that the table was basically ok. I think that it was not and would get a different result if I had use the EXTENDED option to make a full scan. In fact the problem could be so subtle that it escaped the scan with the MEDIUM option. But I did not want to waste the time and so I just asked MySQL to repair it for me:

mysql> REPAIR TABLE watchdog EXTENDED;

The EXTENDED option rebuilds the table somehow from scratch, and is not generally used. But I believed that the problem was severe, and I was desperate too! There was no harm using the option but it's expected to take a long time to repair. Luckily, it only took about 6 minutes. I tried

mysql> SELECT COUNT(*) FROM watchdog;

again and I got the result in less than a second!

Wrap Up

What had happened here? I haven't delved into it much (yet). But even that it's an attack, it's not severe as no data is lost. Besides, I did regular backup of the site. For security, I upgraded all software involved to the their latest versions, including MySQL, drutex, etc. The binary log of MySQL and the access log of the Apache server recording the disaster are still on the server. So if I want, I can do some tedious forensic work here, and use it against the cracker in the court if there's any... Dare you crack me!