New MySQL Sandbox tutorial

John Goulah wrote a nice quick tutorial to MySQL Sandbox.
Thanks, John!

MySQL Proxy is back

MySQL Proxy is back. After a long hiatus due to many deadlines in the Proxy team, the GPL version of MySQL Proxy has been updated. The most important news is the location. Proxy is now hosted on Launchpad, like many more MySQL related projects, and MySQL code itself.

Kay and Jan announced and commented the changes. The repository is now on Bazaar, and there are rules for contributions.
On the technical side, the differences are quite a lot, although the changes are not documented yet. So I am exploring and reporting my findings while I meet them.
The architecture has changed. The Proxy has been split into plugins, to make it more manageable.
The admin interface, as explained in the manual, is gone. Instead, there is a plugin, and you can design your own or expand the existing one (which does very little.) For example, from the code directory, after compiling, you could try this one:

./src/mysql-proxy --plugin-dir=$PWD/plugins/ \
 --admin-lua-script=$PWD/lib/admin.lua

The only command enabled is SELECT * FROM backends (case sensitive). To use the admin interface now you need to provide a username and a password (default: root/secret).

$ mysql -h 127.0.0.1 -P 4041 -u root -psecret
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

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

mysql> SELECT * FROM backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 127.0.0.1:3306 | 0     | 1    |
+-------------+----------------+-------+------+
1 row in set (0.00 sec)

Another important change is an option governing the amount of information from the Proxy. By default, the Proxy is silent. If you want to see warnings and errors, you must use the option
--log-level=(error|warning|info|message|debug)
Performance has been increased as well, by skipping result sets by default. If you really need a result set to be used in a Lua script, now you need to say it explicitly. When you append a query to the query queue, you must add {resultset_is_needed = true } as third parameter. If you don't the resultset is not made available to read_query_result().
There are more news, related to load balancing and query splitting, but I will report on that some other time.

Seven quiet facts

I didn't want to get involved, but Jan caught me in the tell-seven-things-about-you game. The rules:

• Link your original tagger(s), and list these rules on your blog.
• Share seven facts about yourself in the post - some random, some weird.
• Tag seven people at the end of your post by leaving their names and the links to their blogs.
• Let them know they’ve been tagged by leaving a comment on their blogs and/or Twitter.

Here are the seven facts about me:

1. Nobody among my parents and siblings speaks any foreign language. On the other hand, my wife speaks four languages, my sisters-in-law at least three each, and my father-in-law speaks seven languages fluently.
2. English is my fourth language and I learned it at the age of 33. (Previous ones: Italian, Sardinian, French, and Spanish as my fifth one)
3. I never had typing lessons. I type with only two (occasionally four) fingers.
4. I learned SQL before English. I attended my first Oracle course five years before my first English lesson.
5. I wrote a database query language in 1991. One of the application that I created with that interpreter is till running today.
6. My first operating system was VMS, running on a Digital VAX 11-750. I still fondly remember that file system with integrated revision control.
7. My favorite pastime is reading (mostly in English, but also in Italian and French). I have always a book with me, in case I get stuck in traffic, at the supermarket queue, or at the airport. During working days, I read one book per week. During weekends and vacation, one per day is quite common.

The seven invited ones:

• Kaj Arnö, my boss, who offered me the job of my dreams in MySQL, writes in languages that he does not speak, and who will surely enjoy this new form of social networking.(done)
• Barton George, one of the finest minds in the open source arena, who always enjoys a good challenge.(done)
• Edona Nahi, my wife's third sister, who's attending a master in California.
• Ivan Zoratti, my Italian colleague who enjoys his life abroad.
• Monty Taylor, a fine open source enthusiast with an extensive culture that I would like to unveil.
• Ronald Bradford, because he has a lot to say now, and I am curious to hear it.
• Stefano Rodighiero, who shares my passion for Perl and has recently made me envious by writing a book that I would have liked to. (done)

Q&A on MySQL 5.1

Listening to Sheeri's presentation on MySQL 5.1, I saw that there are a few questions left unanswered. I am listing here some of the questions that I found interesting, plus a few from an early webinar on the same topic.

Q: does Partitioning physically split data?

A: No. Some engines (MyISAM, Archive) do a physical split, but this is not necessary, as you see if you apply partitioning to a InnoDB table. Partitioning is a logical split of data, for easy retrieval. It is completely transparent to the user.

Q: Can you set partitions to different servers?

A: No. Partitions are logical parts of one table within one server. Partitioning through the Federated engine is not supported.

Q: How efficient are Row-Based Replication operations compared to Statement based ones?

A: RBR is faster when the insert or update is the result of an expensive operation. Otherwise, the efficiency for insertion and deletion is roughly equivalent. Updates on multiple records are usually more expensive with Row-Based Replication.

Q: Is the event scheduler polluting the Error Log?

A: yes, unfortunately. But it has been fixed in 5.1.31 (See also Bug#38066. As you can see from the discussion in the bug report page, it was object of a long and intense discussion.

Q: Can you send email through the Event Scheduler?

A: No. But you can integrate it with a hack using MySQL Proxy through Federated tables.

Q: Is there an equivalent to SHOW FULL PROCESSLIST from the INFORMATION SCHEMA?

A: Yes. Actually, SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST is equivalent to SHOW FULL PROCESSLIST. See the manual

Q: Are partitions supported in replication?

A: Yes. Partitions are fully supported in replication. The only problem you may have is when using the "DATA DIRECTORY" and "INDEX DIRECTORY" clauses, if the slave does not have the same directory structure and OS privileges as the master.

Q: Is the event scheduler supported in replication?

A: Yes. The event definition is replicated but left inactive, and the effects of the event scheduler are replicated as any other statement. When promoting a slave to master, it is necessary to manually activate the events.

Q: What happened to RENAME DATABASE? Why is not available anymore?

A: RENAME DATABASE was a command implemented in the early stages of MySQL 5.1, for the specific purpose of helping the upgrade script to set the database name with the appropriate charset. It had a deadly side effect, though. It removed all the objects associated with the database. Thus, it was removed and renamed in such a way that nobody would use it for simply renaming a database.

Q: How does the slow query log work with microseconds?

A: To catch slow queries with duration measured in less than 1 second, you can set the long_query_time global variable to a fractional value. E.g. set global long_query_time=0.5; will enable queries taking more than 1/2 second to be logged to the slow queries log.