Thursday, August 23, 2007

Heading to FrOSCon

I mentioned this event a few weeks ago, but it's worth a reminder.

The Free and Open Source Software Conference is held in Sankt Augustin, a cheerful place near Bonn.
FrOSCon header

Some reasons to attend:
  • the organizers are friendly and efficient
  • the program is exciting
  • the food is good,
  • it's full of open source enthusiasts
  • I am meeting with a bunch of friends and colleagues
Do you need more? Ah, yes, my session :)
I will present a topic for database administrators, Monitoring and logging a database server.
But there is also Lenz Grimmer with a talk about Opening the doors and windows of the Cathedral, and Susanne Ebrecht, who will bravely cover the PostgreSQL vs. MySQL, or Venus vs. Mars debate.

See you there!

Tuesday, August 21, 2007

Another step forward for MySQL Proxy


After a few months of documentation uncertainty, MySQL Proxy has finally hit
the official manual.
The delay between the release and the manual is not a sign of neglect. The documentation engineer had to catch up while the author kept releasing revision after revision (we are now at revision 156. When the Proxy was announced, it was at revision 9).
The Proxy is growing, and so is the interest about this feature. A few days ago, an Oracle user was asking for a Proxy-like feature!

And the surprises are not over. More is coming. Stay tuned.

Go Proxy! Go Jan!

Monday, August 13, 2007

How MySQL 5.1 is going to change your life

Are you getting ready for MySQL 5.1? You should. I know that it's taken a long way from alpha to beta, but now it is really close to GA. Really. There is no way it's going to stay beta forever. Very soon someone in the high management will realize that all the outstanding bugs were fixed, and MySQL 5.1 is ready for prime time.
And then, you will have to get used to it. Meaning that there a lot of stuff that is so attractive, that you will want to explore the manual in search of guidance.
Let me give you a preview of what's there for you.
Partitioning

Better start reading some theory on this stuff. If you have performance problems with huge loads of data, this is really what you should be doing. Partitioning is a chunk of pure performance hooked to a database. It's not in any way related to relational theory, and you don't need to redesign your database, but surely you need to rethink it in terms of partitioning.
Rush to read the manual and Robin Schumacher introductory article. If you really want to get the most about partitioning, though, you should check The Art of SQL (Note: I am not affiliated with the author or the publisher. This advice is out of my goodness only)

Row based replication

This is heavy stuff. For demanding application. If you have been dreaming of a more robust replication mechanism for MySQL, here it is.
Row based replication will send to the slave the data, rather than the statement to reproduce it. All edge cases where normal replication leave you in doubt are now handled efficiently by row-based replication. And if you want to get the best of the two worlds, you can still choose the mixed format. Check it out.

Log tables on demand

Get ready for a new experience. Logs whenever you need them, without scheduling a server reboot.
The new logs can materialize in your server in several shapes. You can have them as files, the same as you have seen them in previous versions, or you can have them as CVS tables (which you can convert to MyISAM or to something else), or you can have both at once, tables and files.
And your logs are dynamic. You can tell the server to start and stop logging, to change the log file name, to log on tables and files or only on one of them, you'll have more options than some video games.
And your life will change when you realize that you will need to plan for backups that include the logs. Yes. This is the downside. Get used to it.

Event scheduler

Someone said that the event scheduler is the coolest new feature in MySQL 5.1. Perhaps it was my reaction as well when I saw it for the first time. I can't tell you if it's the best one, but surely it will change your way of working. Say goodbye to OS dependent schedulers. Your database will take care of its own jobs. Setting up events is ridiculously easy. Once you try, you will be hooked forever.

XML functions
To tell you the truth, this is my least favorite feature in 5.1, because for theoretical reasons I am not fond of XML, but I have to admit that it's going to be damn useful, and I will have to get acquainted to it. It will save time and allow for flexible integration schemes with applications that only talk XML.
What else? MySQL 5.1 will change your life, sure. To see the other reasons for my prediction, check the list of wonders for the upcoming version.

Sunday, August 12, 2007

Boost your SQL with Proxy loops

By now, everybody knows that I am quite fond of MySQL Proxy and with reason. Using a Proxy you can add features without need of delving into the intricacies of MySQL server code.
One of the features that you can create with the Proxy, once you are familiar with its basic architecture (if you still aren't, you may check Getting started with MySQL Proxy) is a loop.
So many times I catch myself doing things like
$ for N in `seq 1 10` ; do mysql -e "CREATE TABLE t$N (id int)"; done
or something like that. Why, oh why there is no FOR command in MySQL syntax?

Some time ago, I created a working solution using stored routines, but it is less than perfect. It requires installation, and the routines are limited to one database, thus requiring an unnecessary amount of detail to make it work.

Proxy loops with a counter.

Enter the Proxy, and the solution is surprisingly slim.
What you need to do is start the proxy with the loop module from MySQL Forge. Then, from your client you can do loops:
mysql> set @x =0;
Query OK, 0 rows affected (0.02 sec)

mysql> for 1 10 select @x := @x + 1 as x;
+------+
| x |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.42 sec)
This is the simplest syntax. It requires a command (FOR) a start counter (1) a stop counter (10) and a query (select @x := @x + 1 as x).
The Proxy does the rest, sending the query as many times you required, and collecting the results in practical tabular form. You can actually refer to a counter in the query itself.
mysql> for 1 5 create table t$N (id int);
+--------+--------+---------------+
| inj_id | result | affected rows |
+--------+--------+---------------+
| 1 | 0 | 0 |
| 2 | 0 | 0 |
| 3 | 0 | 0 |
| 4 | 0 | 0 |
| 5 | 0 | 0 |
+--------+--------+---------------+
5 rows in set (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
| t3 |
| t4 |
| t5 |
+----------------+
5 rows in set (0.00 sec)
The $N variables refers to the current counter. The Proxy script will do the change before sending the query to the server. The result is shown in a dataset. A '0' (zero) means no error. When an actual error occurs, the loop is stopped, and the results up to that point are reported.
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> for 1 5 create table t$N (id int);
+--------+--------+---------------+
| inj_id | result | affected rows |
+--------+--------+---------------+
| 1 | 0 | 0 |
| 2 | -1 | 0 |
+--------+--------+---------------+
2 rows in set (0.01 sec)

mysql> show errors;
+-------+------+---------------------------+
| Level | Code | Message |
+-------+------+---------------------------+
| Error | 1050 | Table 't2' already exists |
+-------+------+---------------------------+
1 row in set (0.00 sec)
I removed one table (so the first command in the loop succeds) and when the second query is executed, an error is reported. The loop is immediately interrupted and the result is relayed to the client.

Proxy loops with a list.

Counters are useful, but they are not always what you need. Sometimes, you need to loop though a list. The above mentioned module supports a second syntax, using a list of comma separated items instead of a counter.
FOR user_var (items, in, a, list) your query
Easy to use. Really. Want to create three tables with the same structure but different names? Presto!
mysql> FOR mytable (the_first, the_second, the_third, something_else) CREATE TABLE $mytable (id int);
+--------+--------+---------------+
| inj_id | result | affected rows |
+--------+--------+---------------+
| 1 | 0 | 0 |
| 2 | 0 | 0 |
| 3 | 0 | 0 |
| 4 | 0 | 0 |
+--------+--------+---------------+
4 rows in set (0.05 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| something_else |
| the_first |
| the_second |
| the_third |
+----------------+
4 rows in set (0.00 sec)
Even if you don't declare a counter, nonetheless you can use one in your query, if needed. Suppose you want to create the tables in such a way that the first one has a CHAR(1) column, the second one a CHAR(2), and so on.
It's a lame example, but you can do it, using the now familiar $N variable.
mysql> FOR mytable (the_first, the_second, the_third, something_else) CREATE TABLE $mytable (name char($N ) );
+--------+--------+---------------+
| inj_id | result | affected rows |
+--------+--------+---------------+
| 1 | 0 | 0 |
| 2 | 0 | 0 |
| 3 | 0 | 0 |
| 4 | 0 | 0 |
+--------+--------+---------------+
4 rows in set (0.05 sec)

mysql> desc the_first;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc the_second;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name | char(2) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
As usual, enjoy, and give it a try!

mysqldump unusual error

A friend of mine called me today, asking for advice on a baffling error.
He has just finished upgrading a database server from 4.1.20 to 5.0.45but, when attempting a backup to all databases with mysqldump, he gets a discomforting message:
mysqldump: Got error: 1044:
Access denied for user 'root'@'localhost' to database 'information_schema'
when using LOCK TABLES
Of course, the error is doubly strange.
  • First, because mysqldump is not supposed to attempt dumping the information schema. I know for sure, since I had to write a special script to dump the I_S when I really needed to export its data.
  • Second, because there was actually a bug affecting this behavior, but it was fixed over one year ago.
Looking at the bug report, though, I saw that someone was complaining about this bug again recently.
I don't believe without action, so I rapidly installed a 5.0.45 and I tried to repeat the offending behavior. No luck. Everything worked as expected.
Then it dawned on me that perhaps he was not using the right mysqldump. I asked my friend to report the version of the dump, and indeed, it was a version that comes with MySQL 4.1.
Apparently, an old version of the client tools were still stuck in the OS execution path, before the directory where the new ones were installed.
A couple of minutes later, having cleaned the installation, all went well.
Lesson learned: before crying "bug", try "--version"!

Monday, August 06, 2007

Summer of Code pleasures

Summer of Code image

As a professional tester, I have a dream about a tool that will let me use my favorite SQL manager, and at the same time create tests ready to be plugged to the regression test suite.
I have been dreaming and planning about it, and finally the magic tool is shaping up.
I am mentoring a Summer of Code project, assigned to Charlie Cahoon. After a long preparatory work and much planning, the MySQL test creator is finally out!
Initially, it was planned as a separated tool, a replacement for the MySQL command line. The idea is that you write queries and eye the results, while the tool in background is writing a polished test script. That would have been a good tool, multi platform, easy to use, but limited to the command line.
Then the MySQL Proxy came along, and we decided to use it as a backend. Thus the tool, instead of being written in C++, it ended up in Lua. Kudos to Charlie for learning the language very quickly and for coding it.
The tool is not ready yet. It has just the basic features, but it has now reached the stage when it can actually produce a simple test script+result pair.
For example, This is what I would type to check that the CREATE, INSERT, and SELECT statements are supported (not exhaustive test, of course!)
mysql> start;  # This is a macro from the magic tool
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 (id int, c char(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1,'a'), (2,'b');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+------+------+
| id | c |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.01 sec)

mysql> stop; # another macro. The tool stops recording now
Query OK, 0 rows affected (0.01 sec)

mysql> exit
What I see on the screen is not what I need to get in a test script. If I had found a bug here and I wanted to submit it, I would have to do some cut-and-pasting and clean-up. Instead, the mysql-test-creator is doing that for me.
$ cat creator.test
--disable_warnings
DROP TABLES if exists t1;
--enable_warnings
create table t1 (id int, c char(10));
insert into t1 values (1), (2);
insert into t1 values (1,'a'), (2,'b');
select * from t1;

$ cat creator.result
DROP TABLES if exists t1;
create table t1 (id int, c char(10));
insert into t1 values (1,'a'), (2,'b');
select * from t1;
id c
1 a
2 b
Isn't it sweet?
Bravo Charlie!

More is coming. The tool will support multiple sessions at once, with the creation of a unified test script from several sessions. Stay tuned!