How db4free.net helps to improve MySQL

Today I had a perfect example to show you how db4free.net helps to find bugs - which are of course reported and consequently fixed. And sometimes, like this one, the bugs even result from a (although in this case not serious) bug in db4free.

A few days ago, a db4free.net user contacted me and told me that he could not connect to his database. He only saw information_schema and on the MySQL 5.1 server, he could see the show_log database, in which I provide access to a view where every user can see his individual slow queries (thanks to the new table logging feature).

I looked up his user and found out that he has entered an empty string for the host information (that's the db4free.net bug, because this should be prevented). I fixed the host info manually in the mysql schema and changed it to a % sign, but I forgot to update the tables_priv table where access to the show_log database was granted.

So he contacted me again and reported that he couldn't connect to the show_log database anymore. I issued a GRANT USER for his_username@'%' command - and the server crashed. I soon found out that this was because the host information was inconsistant between the user, db and tables_priv tables.

Here's how to repeat this bug:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE USER testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE test2.tt (
    ->   id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)

mysql> GRANT ALL ON test1.* TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test2.tt TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.tables_priv
    ->   SET host=''
    ->   WHERE user='testuser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR testuser@'%';
ERROR 2013 (HY000): Lost connection to MySQL server during query

You can also look up for this in the bug report.

This story is very typical for two reasons. One is that db4free.net hosts a huge variety of different users. They do things in different ways and sometimes (and this should not be considered bad) in unexpected ways. And you find most of the bugs, if you do unexpected things.

The second reason is that the bug itself was typical. Most of the bugs that I found so far can be split into two categories. The first category are of course bugs in new features (mainly in development releases) - that's just natural. Then I found quite a few bugs that result from complex privilege settings, including database or even table specific privileges (and probably even more if you define privileges on the column level, which I - to be honest - never did except for educational purposes when I learnt for my MySQL exams).

There would even be a third reason, why this bug is typical - but you can easily avoid this. I manually modified the mysql schema tables and flushed the privileges. If you do this and make a mistake so that you create inconsistant values (like I did), it's not unlikely that you stumble across a bug. However, if you can avoid to edit the mysql schema tables manually (and in almost all cases you can avoid it using the GRANT and REVOKE commands as well as CREATE USER and DROP USER), I'd highly recommand you to avoid it. It's a bad habit from me that I often do it anyway ;-).

I haven't counted the number of bugs that I found through db4free.net, but it were quite a few. Most of them of course in the development releases - in MySQL 5.1 and in MySQL 5.0 before it became GA and most of the bugs that I reported recently refer to the Event Scheduler - but that's probably because it's one of my favourite new MySQL 5.1 features that I played with more than with the other nice new things.