Testing and timing RDS/MySQL scheduled downtime with PHP

Something I was asked at work recently was “if we do this to the server, how long will it be unavailable for?” and usually the answer would be easy… however in certain circumstances you may not know. One of these is Amazon RDS, whereby you don’t really know what happens when you reboot or change the configuration of an instance, it just sort-of happens and eventually becomes available again.

For Amazon RDS an example would be when you modify the DB Instance Class of your main instance, which will cause the server to be “modified” which will cause some downtime; of course a reboot will cause downtime also.

Therefore I needed to time how long the website would go down for, at what point connections to the database would fail, or running a query would fail, etc.

I wrote a PHP CLI script to record how long it will take (in seconds) for the database to recover for any given operation that will cause downtime. Start the script, and it will keep outputting ‘+’ to the command line. Start the operation, and eventually the ‘+’ will turn into ‘-’, and once they turn into ‘+’ again it means the downtime is over and the database(s) will be available again, and it will tell you how long it took.

The output will look something like this…

user@server:~$ php test_upgrade.php
Starting...
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ + + + + + + + + + + + + + - - - - - - - - - - - - - - - -
- +

Database down for 262 seconds

The script is as follows…

<?php

error_reporting(0);

$host = 'myhost.us-east-1.rds.amazonaws.com';
$port = 3306;
$database = 'mydb';
$username = 'user';
$password = 'supersecretpassword';

$sql = "SELECT count(*) FROM users";

echo "Starting...\n";

$disconnectTime = 0;
$reconnectTime = 0;
$reconnected = null;

while ($reconnected !== true) {

	$link = mysql_connect($host.':'.$port, $username, $password);
	if (! $link) {
		flagConnection(false);
		$reconnected = false;
		continue;
	}

	$db_selected = mysql_select_db($database, $link);
	if (! $db_selected) {
    	flagConnection(false);
		$reconnected = false;
		continue;
	}

	$result = mysql_query($sql);
	if (! $result) {
		flagConnection(false);
		$reconnected = false;
		continue;
	}
	$row = mysql_fetch_row($result);
	if (! $row) {
		flagConnection(false);
		$reconnected = false;
		continue;
	}

	// If everything works, flag as connected
	flagConnection(true);

	// If was previously disconnected, exit the loop
	if ($reconnected === false) {
		$reconnected = true;
	}

	mysql_close($link);

}

$outageTime = $reconnectedTime - $disconnectedTime;

echo "\n\nDatabase down for $outageTime seconds\n";

function flagConnection($flag)
{
	global $disconnectedTime;
	global $reconnectedTime;
	global $reconnected;

	echo ($flag ? '+' : '-').' ';
	$log[] = $flag;

	if ($flag == false && is_null($reconnected)) {
		$disconnectedTime = time();
	}

	if ($flag == true && $reconnected === false) {
		$reconnectedTime = time();
	}

	sleep(1);

}

?>

Use phpMyAdmin with a remote MySQL server

Been doing relatively proprietary work recently, so not much to share other than this little tip. If you want to use phpMyAdmin but for whatever reason the MySQL server you’re using isn’t local, you can change the host by editing line 104 in config.inc.php in the root of phpMyAdmin:

$cfg['Servers'][$i]['host'] = 'localhost';

… and optionally the port if required

$cfg['Servers'][$i]['port'] = '';

Deleting rows from a MySQL database based on multiple LEFT JOIN with foreign key constraints

Something that I hadn’t attempted to do before, probably because I was trying to delete something from a medium-sized JOIN query that I hadn’t written using a WHERE clause, is realise that unlike deleting with INNER JOIN you cannot delete using a LEFT JOIN where the clause may or may not exist in various target tables in a SELECT query.

(put an example of original query here)

Thus if WHERE y IS NULL is actually null because the join doesn’t match any records in the other table(s) you can’t delete on that clause with a single query (which is better explained on this blog)

The way I figured to do it is create a master list of keys used for the JOIN operation (the keys you’re using for ON or USING) and export it as a comma-delimited list; you can use a sub-query if you’re not deleting from the table where the keys exist as primary. Example: if you’re deleting a list of products which have never been ordered using a cheque, you can’t get the product keys using a sub-query if you’re deleting the products themselves in the same batch query.

(put an example of key query here)

Then, get the list and DELETE FROM each target table using IN and the list of keys you previously generated. This way each table will be deleting from a list of keys that may or may not have matched in the original SELECT query; whether they did match or not doesn’t matter since it’s using IN, it’ll work without a sub-query so you can run it on all tables, and MySQL won’t complain since you’re targeting each table individually one at a time.

(put an example of delete batch query here)

You may need to disable foreign key checking to do this as the queries would be run in succession, not as a single query, so the dependencies would temporarily break.

Zend Framework, Zend_Db_Table ORDER BY sorting NULL as last instead of first

If you have a group of fields, some of which may be null (such as an address, in which the 1st line of the address may or may not be filled in) then you may want to sort them all so that if null appears in a field, it’s sorted to the bottom of the list as opposed to the top (which is the default behaviour)

$select = $table->select();

$rowset = $table->fetchAll($select->order('IF(ISNULL(line1),1,0), line1', 'line2', 'city', 'country'));

I grabbed the MySQL code from the MySQL Forums

Get column names from a Zend_Db_Table object

If you need to get access to the column names of a Zend_Db_Table object (filtering a list of column names so that you discard any that don’t match up, for example) then as found on StackOverflow you can do the following:

$table->info(Zend_Db_Table_Abstract::COLS);

Which of course works from within the object itself, so you could write a method such as:

public function getCols() {
        return $this->info(Zend_Db_Table_Abstract::COLS);
}

However unlike the documentation says, this does not appear to work…

//return $this->_cols;

Drupal migration SQL fixes

If you are migrating from a single instance of Drupal to a multi-site installation, you may encounter a problem or two along the way in terms of certain things breaking as they aren’t where they used to be. Since Drupal is almost solely reliant upon the database during it’s bootstrap process, more often than not if something is misconfigured you can end up with the white screen of death (which neither PHP or Apache will be able to pick up)

Theme fixing… (http://drupal.org/node/200774)

UPDATE system SET status=1 WHERE name = ‘garland’;

UPDATE variable SET value=’s:7:”garland”‘ WHERE name = ‘theme_default’;
TRUNCATE cache;

Files fixing… (http://flevour.net/blog/drupal-changing-files-directory-configuration-setting)

UPDATE `files` SET `filepath` = REPLACE(`filepath`, “files/”, “sites/SITEDOMAIN/files/”);

Duplicate records in SQL (duplicating user accounts/privilidges with unique keys and usernames)

INSERT
INTO
users
SELECT
‘newUsername’
,somecolumn
,someothercolumn
,yetanothercolumn
FROM
users
WHERE
username = ‘oldUsername’