Introducing the Tungsten-toolbox

After the public home for Tungsten Replicator, we needed another place where to host complementary tools. We discussed the pros and cons of hosting these tools in the same place where we publish Tungsten, but in the end we decided that it's more practical to have a separate project, where we can publish tools related to database replication, no matter if they are dedicated to Tungsten or if they can work with other replication systems. So, here it is. We have now Tungsten Toolbox, a site where we will collect our tools and accept contributions from others.

We have already a few tools that, after being cleaned up, will find their way to this repository. The one that was more or less ready for prime time is the Tungsten Sandbox, a tool that installs more than one Tungsten instance with a backend database in the same host. It is, as you can imagine, based on the MySQL Sandbox and it works reasonably well. All the tools in this toolbox are released under the New BSD License. What can you expect to see in the near future? We have a few ideas already:

• A binary log analyzer
• A tool that changes properties on-the-fly
• A JSON API for Tungsten
• Sandboxes and deployers for complex topologies (multiple masters, fan-in)
• Sandboxes and deployers for direct slaves
• Deployers for a mix of MySQL native and Tungsten replication
• A PostgreSQL sandbox

We are, of course, open to contributions. If you have a tool that is useful for database replication and want to release it under a BSD license, feel free to propose it in the Google Group discussion on Tungsten Replicator.

Automating MySQL Librarian tasks

The MySQL Librarian is a collection of community generated content, easy to search and to extend. If you have missed the announcement, you may look at Introducing the MySQL Librarian.. To add a new link, you have several ways. You can just use the Add A Link feature inside the Librarian. If the link to be added comes from Planet MySQL, every post has a link to facilitate the insertion to the Librarian. For everything else, adding a link is a manual task. Until today.

Adding easily to the Librarian

Diego Medina, who should be well known in the community as a very active promoter of MySQL Proxy, and one of the most creative bug finders, has made two additions that have already been incorporated into the Librarian. If you go to the MySQL Librarian now, you will find a "bookmarklet" that you can drag and drop to your browser toolbar (if your browser is either FireFox or Safari, that is). After that, you have a button in your toolbar, which you can use to quickly add a new link.

For example, let's consider an article that we may want to add to the Librarian. Something like MockLoad on Launchpad - MySQL Proxy.

If you have already dragged and dropped the bookmarklet, you can click on your new button, and get to the Librarian page, with almost all the detailed already filled in for you.

Search engines

Diego didn't limit his contribution to adding links. He has also created a new search engine plugin for FireFox, which lets you use the MySQ Librarian as your search engine.

Adding the Librarian engine	Using the Librarian engine

If your default search engine is the Librarian, your search will be sent to MySQL Librarian, as easily as you search in Google or in any other engine.

Thanks, Diego!

Introducing the MySQL community-driven Replication Monitoring Tools

If you are using MySQL replication, you know how hard is to monitor it properly. You have a wide choice of commercial and free tools, all of which check the health of your replication system from the outside. A few years ago, I wrote an article advocating a self-monitoring and self-healing replication system, using new features in MySQL 5.1. At the time, there were some missing technology pieces to make this project feasible. Now the pieces exist, and you can create your own self monitoring replication system.

Hartmut rules!

It started during FrOSCon, when I discussed with Hartmut Holzgraefe the practical application of a plugin that he wrote some time ago. One of the missing pieces for the internal replication monitoring is the ability of reading replication values into variables. Currently, you can see the replication status with SHOW MASTER STATUS and SHOW SLAVE STATUS. Unfortunately, you can't manipulate the values from these commands inside a stored routines. And thus there is no way of measuring the health of a replication system without the help of external programming languages.
This means that, even if you manage to detect a master failure, you can't create a CHANGE MASTER STATUS with the right parameters.
During our FrOSCon talks, Hartmut released a small Launchpad project, the MySQL replication status INFORMATION_SCHEMA plugin, which implements two INFORMATION_SCHEMA tables for MASTER and SLAVE STATUS. Using these extensions, you can get single values inside a stored routine.

The Replication Monitor is born

From this starting point, the Replication Monitoring Tools become possible.
The project is young, and it only contains a proof-of-concept monitoring tool (see below), but given enough time and help, it can be expanded into a full fledged system.
The roadmap has a long list of possible features, some of which are quite ambitious

The project is open. I need ideas, manpower, testers, to implement all the intended features. This is a project from the community to the community.
The principle that you have seen several times when downloading MySQL server fully applies. If you have time, you can save money and help yourself and the community at the same time.

Replication Monitor in action

The proof of concept implements a table on the master, where the slaves write their status, by means of federated tables.
Each slave uses a federated table to the master INFORMATION_SCHEMA.MASTER_STATUS to compare its status with the master,a nd a second federated table to write its status to the master table. The result is that you can read the global status of a replication system in the master, and in each slave (because the table is replicated).

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |      82321 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |      82530 | IO+,SQL+,P+,E+ |
|       103 | mysql-bin.000001 |      82739 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |      82948 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

The status reads as follows:
IO+ means that the slave IO_thread is working (the opposite is io-.
SQL+ means that the SQL thread is working.
P+ means that the slave is reading from the latest binlog and that the position is equal or greater to the one shown by the master.
E+ means that the slave has executed all the statements received from the master.
Let's try a small experiment. We stop slave 2 and see what happens.

slave2> stop slave;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |      91517 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |      91517 | io-,sql-,p-    |
|       103 | mysql-bin.000001 |      91932 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |      92141 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

The monitor shows that slave 2 is not working. If we restart the slave, the situation is restored.

slave2> start slave;

select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |     114894 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |     115104 | IO+,SQL+,P+,E+ |
|       103 | mysql-bin.000001 |     115314 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |     115524 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

We can do the same experiment while loading some heavy data, like the employee sample database.

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |     134158 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000001 |     134975 | IO+,SQL+,p-    |
|       103 | mysql-bin.000001 |     133777 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000001 |    1165155 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |    7343962 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000001 |    8374099 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000001 |    7343751 | IO+,SQL+,P+,e- |
|       104 | mysql-bin.000001 |    8374310 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

Very rapidly (the experiment happens in a MySQL Sandbox, so all servers use the same disk and CPU), the slaves are left behind in execution.
If we stop a slave while loading, the situation is even more explicit.

slave3> stop slave;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000001 |   45764491 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000001 |   45764703 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000001 |   42685103 | io-,sql-,p-    |
|       104 | mysql-bin.000001 |   42685103 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

Let's introduce a new element of difference, and flush logs while still loading data.

master> flush logs;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000002 |    2044673 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000002 |    3066965 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000001 |   42685103 | io-,sql-,p-    |
|       104 | mysql-bin.000002 |    3067176 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

Now the stopped slave is really far behind. Let's see what happens when we put it back online.

slave3> start slave;

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000002 |   27604369 | IO+,SQL+,P+,e- |
|       102 | mysql-bin.000002 |   27603945 | IO+,SQL+,P+,e- |
|       103 | mysql-bin.000002 |   27604157 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000002 |   25558385 | IO+,SQL+,P+,e- |
+-----------+------------------+------------+----------------+

The restored slave was the first one to catch up in execution. This would not probably happen in a situation with separate hosts for each slave, but it's interesting to test our system.
And finally, after a few seconds more, all slaves have caught up with the master, with both data fetching and execution.

master> select * from slaves_status ;
+-----------+------------------+------------+----------------+
| server_id | binlog_file      | binlog_pos | slave_status   |
+-----------+------------------+------------+----------------+
|       101 | mysql-bin.000002 |  104641288 | IO+,SQL+,P+,E+ |
|       102 | mysql-bin.000002 |  104641501 | IO+,SQL+,P+,E+ |
|       103 | mysql-bin.000002 |  104641714 | IO+,SQL+,P+,E+ |
|       104 | mysql-bin.000002 |  104641927 | IO+,SQL+,P+,E+ |
+-----------+------------------+------------+----------------+

Getting started

There is a step-by-step set of instructions in MySQL Forge Wiki that explain how to build and install the necessary plugin.
After that, you should get the code from the bazaar repository and make the changes in the option files of master and slaves (check the sql/master_scripts and sql/slave_scripts directories). Then, you should load the initializing script in the master and in each slave, and you are in business.
Notice that this first version relies on a MySQL Sandbox running with MySQL 5.1.28, compiled from source. Further versions will lift this limitation, but for now, if you want to try it out, you need to follow my steps closely.

Next steps

What's left? Ah, yes. A totally internal monitoring system is not much helpful. If the DBA needs to run a query to know what's happening, then the system is almost worthless.
This is another challenge, because MySQL architecture forbids connection to operating system services such as mail applications. There is already a solution to this problem. We only need to integrate it with the rest of the replication monitoring system. More challenges are outlined in the roadmap.
If you think this is interesting, check the roadmap, and comment on it. Even better, if you want to implement parts of it, join the sandbox developers group and start coding!

MySQL Test Creator - wrapping up a Summer of Code experience

I talked about this matter already, but now it's time to wrap up.
The Summer of code is over, and the project I have mentored is finished, with a new tool as its outcome, the MySQL test creator.

The student who developed this tool, Charlie Cahoon, did a decent job. All in all, considering that it was his first serious developing experience, he got an excellent result. The maturity stage is still alpha, but he did the breakthrough work to get the development of this tool in the right track.

What do we have in hour hands? A tool that will speed up and make easy the writing of test scripts, during a normal session with the MySQL client. It means that you will fiddle with the database in your usual way, and the test creator will work in the background, writing a test script with its corresponding result file.
Its features include

• starting, stopping and resetting the background script creation;
• using loops;
• removing errors from the script;
• undoing the last command;
• showing the test script.

Let's see an example.

mysql> start;
+------------------------+
| TEST CREATOR           |
+------------------------+
| Testing has started... |
+------------------------+
1 row in set (0.00 sec)

mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> set @counter = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> start_loop myvar 100;
+-------------------------+
| TEST CREATOR            |
+-------------------------+
| Beginning loop creation |
+-------------------------+
1 row in set (0.00 sec)

mysql> set @counter = @counter + 1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (@counter);
Query OK, 1 row affected (0.00 sec)

mysql> stop_loop;
+--------------------------------+
| TEST CREATOR                   |
+--------------------------------+
| Executed 2 queries X 100 times |
+--------------------------------+
1 row in set (0.02 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

mysql> stop;
+---------------------+
| TEST CREATOR        |
+---------------------+
| Testing has stopped |
+---------------------+
1 row in set (0.00 sec)

mysql> view test;
+-----------------------------------+
| Test File                         |
+-----------------------------------+
| --disable_warnings                |
| DROP TABLE if exists t1;          |
| --enable_warnings                 |
| create table t1 (id int);         |
| set @counter = 0;                 |
| let $myvar = 100;                 |
| while ($myvar)                    |
| {                                 |
| set @counter = @counter + 1;      |
| insert into t1 values (@counter); |
| dec $myvar;                       |
| }                                 |
| select count(*) from t1;          |
+-----------------------------------+
13 rows in set (0.00 sec)

If you have ever tried to create a loop in mysqltest language, you know what a time saver this tool will be.
Another example. Getting rid of errors:

mysql> start;
+------------------------+
| TEST CREATOR           |
+------------------------+
| Testing has started... |
+------------------------+
1 row in set (0.00 sec)

mysql> select 1 as info;
+------+
| info |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select 2 as info;
+------+
| info |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select this is wrong;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wrong' at line 1
mysql> view test;
+-----------------------+
| Test File             |
+-----------------------+
| connection con55;     |
| select 1 as info;     |
| select 2 as info;     |
| --error 1064          |
| select this is wrong; |
+-----------------------+
5 rows in set (0.00 sec)

mysql> discard_last;
+-------------------------------+
| TEST CREATOR                  |
+-------------------------------+
| Last query has been discarded |
+-------------------------------+
1 row in set (0.00 sec)

mysql> view test;
+-------------------+
| Test File         |
+-------------------+
| connection con55; |
| select 1 as info; |
| select 2 as info; |
+-------------------+
3 rows in set (0.00 sec)

mysql> select 3 as info;
+------+
| info |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> wrong;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wrong' at line 1
mysql> discard_last;
+-------------------------------+
| TEST CREATOR                  |
+-------------------------------+
| Last query has been discarded |
+-------------------------------+
1 row in set (0.00 sec)

mysql> view test;
+-------------------+
| Test File         |
+-------------------+
| connection con55; |
| select 1 as info; |
| select 2 as info; |
| select 3 as info; |
+-------------------+
4 rows in set (0.00 sec)

mysql> stop;
+---------------------+
| TEST CREATOR        |
+---------------------+
| Testing has stopped |
+---------------------+
1 row in set (0.00 sec)

There are still a few glitches to take care of, but the results so far are really great!

Test suites for the masses

Overview

Test suites are hard, but your business needs to have one.
They are hard because modern applications are complex.
Technology can help you. There are tools that can simplify the task of creating a thorough test suite.

Does your main business have a regression test?

The likely answers to the above questions are:

• Yes. - Good chap. Keep up the good work. You may stop reading, but just out of curiosity, read on.
  
• No. I don't need one. - You may stop reading. Most likely your business is already doomed. :)
• No. I don't have time (don't know how) to make one. - Keep reading. There is something for you.

What's a regression test? Is a useful watchdog that tells you if your software is working the way it was intended to.
If you don't have one, you have no quick and reliable way of telling if your application is safe.
If you think that you don't need a regression test suite, either you have found the formula of making perfect software, or your optimism is way off mark.

Tests are hard

If you have a test suite, you know that it requires hard work, dedication, cunning ways of thinking, technical abilities.
If your business includes a MySQL database, you may consider using its specific test framework, which introduces yet another level of difficulty.
MySQL testing framework is designed to allow testing any database scheme (stand alone, replicated, clusters) with just one box, at the price of some language addition that you would need to learn.
Writing your own regression test that covers your application means replaying the data structure and the queries, to make sure that, if you alter the working environment, your application continues working as required.

The most likely scenario occurs when you need to upgrade. Your current server has a bug that was just fixed in a more recent one. You would like to upgrade, but before doing that you would like to be sure that the new environment is capable of running your queries without side effects.
A regression test would solve the problem. You set up a temporary server, install the upgraded software and run the test suite. If you don't get errors, you may confidently upgrade. It depends on how thorough is your test, of course. The more you cover, the more you can trust it.
If you don't have a regression test and you need to upgrade, you are in trouble. If there are inconsistencies between your application and the new version, you will find out when a user complains about a failure. Which is not the most desirable course of action.

Understanding the problem

Why making a test suite for an application is so hard? Mostly because you don't usually have a list of all queries running against your server. Even if you planned a modular application and all your database calls are grouped in one easy accessible part of your code, that won't solve your problem. Modern applications don't use static queries. Most queries are created on the fly by your application server, twisting a template and adding parameters from users input.
And even if your templates are really well planned, so that they leave nothing to imagination, you will have trouble figuring out what happens when many requests access your database concurrently.
Smarter applications can log all database requests and leave a trail that you can use to create your test. But more complex systems count more than one application, and taking trace of all their queries is a real headache.

Making tests easier

Fortunately, there is a central place where all queries go. Your server.
Unfortunately, the server does not create a test case for you. It just records all calls in the general log, and it that is not suitable for further consumption.
Here comes the technology I mentioned earlier. You can make a test script out of your general log.

1. activate the general log;
2. Download the test maker;
3. Inspect the general log and identify the likely period when the most important queries occur;
4. tell the test maker to read the above traffic (which you can identify by line number or by timestamp)
5. edit the resulting test and see if covers your needs.
   

The test maker is a collection of two tools. One that can get a snapshot of your data (like mysqldump, but with a LIMIT clause, so that you can take only a sample of your data) and the test maker itself, which reads the general log and creates a script. You can tune this tool with several options.

 Log Analyzer and test maker,  version 1.0.2 [2007-06-10]
 (C) 2007 Giuseppe Maxia, MySQL AB
 Syntax: ./testing_tools/test_maker.pl [options] log_file test_file
 --add_disable_warnings|w        Add disable warnings before DROP commands. (default: 0))
 --from_timestamp|t = name       Starts analyzing from given timestamp. (default: undefined))
 --to_timestamp|T = name         Stops analyzing at given timestamp. (default: undefined))
 --from_line|l = number          Starts analyzing at given line. (default: first))
 --to_line|L = number            Stops analyzing at given line. (default: last))
 --from_statement|s = name       Starts analyzing at given statement. (default: first))
 --to_statement|S = name         Stops analyzing at given statement. (default: last))
 --dbs = name                    Comma separate list of databases to parse. (default: all))
 --users = name                  Comma separate list of users to parse. (default: all))
 --connections = name            Comma separate list of connection IDs to parse. (default: all))
 --verbose                       Adds more info to the test . (default: 0 ))
 --help                          Display this help . (default: 0 ))

As you can see from the options, you can filter the log by several combinations of connection ID, users list, timestamps, line numbers.
The resulting script is suitable for the MySQL test framework.

Advantages

The test maker is not a panacea, but it helps reducing your test suite development time. Running this tool you will get a passable starting test, which you can then refine to make it closer to your needs.
The main advantage is that you can have a set of instructions that were run against your production server, and you can replay against a testing server when you need to upgrade.
If some features of MySQL 5.1 look appealing and you plan to upgrade, then a test suite is the best way of making sure that the switch will be painless.

More than that, once you have a test suite, consider joining the Community Testing and Benchmarking Program, a project to share test suites from the Community, so that your test case becomes part of MySQL AB suite. Thus, you won't be alone with your upgrade problems. If there are any regression failures, they will be addressed before the next version is released, for the mutual benefit of company and users.

Known issues

There are problems, of course.
The general log is not turned on by default. To switch it on, you need to restart the server, and to switch it off you will do that again (until you upgrade to MySQL 5.1, which has logs on demand).
Moreover, the general log can become huge, so you need additional maintenance, to avoid filling the whole disk with logs.
Furthermore, the general log will record everything sent to the server, even wrong queries. While this is desirable for a test case, because testing failures is as important as testing compliance, you will need to adjust manually the test script, adding "--error" directives.
Again, the test maker records only the test script, but not the result, which you will need to create using the testing framework.
All in all, having a tool writing a big chunk of the test script is much better than doing it all manually. So even if this is not a complete solution, it's a hell of a good start.
Comments welcome.