MySQL

phpMyAdmin is one of my favorite tools and I use it all the time. But man, the 30-minute session timeout is a huge PITA. A short cookie lifetime is all well and good on your production server, but what about when you’re just hacking away on the computer in your bedroom?

If you do any database work, chances are you've run into the following situation: you've made some structural changes to a few tables on the local or staging copy of your database, and now you need to replicate those changes on your staging or production database. The problem is, how can you be sure that you make the same changes to each copy of the database, other than by comparing each table one-by-one (which is tedious and error-prone)?

The obvious solution is to run a diff on your schemas; and after looking at all the options out there, I've found that running a diff is indeed one of your best options. I also found another solution that has some real advantages over running diffs – a free web app called MySQLDiff.

Ever find yourself doing something like this?

$q = "SELECT * FROM person WHERE id=$id";
$r = mysql_query($q);
$row = mysql_fetch_array($r);

$this->id = $row['id'];
$this->firstName = $row['firstName'];
$this->lastName = $row['lastName']; 
...
$this->pantSizeAfterThanksgiving = $row['pantSizeAfterThanksgiving'];

Not sure how this escaped my notice until now, but today I realized the default collation for MySQL columns is not case-sensitive. Among other things, this means that if you're searching for distinct values on a column with varchar, text, or any other non-binary data type, values that differ only in their cases (such as "apple" and "APpLE") are considered equal, and you won't get both values back. MySQL will return "apple" or "APpLE", but not both.

One guess as to how I discovered this. Heh.