Team exhibition - Baby talking MySQL

At the MySQL Developers Meeting in Heidelberg there were show off sessions where each team had a chance of showing their best to others.
As part of this shut out presentation, I demonstrated some MySQL Proxy magic.

This is a sample session from that presentation.

mysql> select 1 + 1;
+-------------------------------+
| ERROR                         |
+-------------------------------+
| Don't understand these digits |
+-------------------------------+
1 row in set (0.00 sec)

# Hmmm!
# Let's try to make it understand ...

mysql> set sql_mode='english_digits'; # You did not know we could do that. Did you?
Query OK, 42 rows affected (0.01 sec)

mysql> select 1 + 1;
+--------+
| result |
+--------+
| two    |
+--------+
1 row in set (0.00 sec)

# Clever!
# perhaps it understands something more

mysql> select 1 + two;
+--------+
| result |
+--------+
| three  |
+--------+
1 row in set (0.00 sec)

# Ok. What about two numbers?

mysql> select seven + two;
+--------+
| result |
+--------+
| nine   |
+--------+
1 row in set (0.00 sec)

# Very good. Now how about something other than adding numbers?

mysql> select seven * two;
+----------+
| result   |
+----------+
| fourteen |
+----------+
1 row in set (0.00 sec)

# Pushing it a bit ?

mysql> select seven * three;
+-----------------------------------------------+
| result                                        |
+-----------------------------------------------+
| Can't count beyond my fingers and toes. (21?) |
+-----------------------------------------------+
1 row in set (0.00 sec)

# Well, it has some limitations!

As an exercise for the reader, you can figure out the Lua script that made the above exchange possible.

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!

How to hijack a Linux + MySQL server

I hope I got your attention with the title. The politically correct term should be "how to redirect MySQL traffic on Linux", but you probably wouldn't have noticed.
The scenario is this. You want to switch servers without need for the clients to change connection parameters.
Why would you want to do this? Some reasons:

• You want to test a new server version, which you installed to listen to port 13306, and you want to do it without modifying the applications parameters;
• You ant to use MySQL Proxy on port 4040 to log the traffic for a while without restarting the server, in order to debug an inexplicable problem;

Whatever the reason, you can do it.

Here is how you can proceed on Linux.

Set a iptables rule to redirect port 3306 to port 13306 or to port 4040:

sudo iptables -t nat -I PREROUTING \
-s ! 127.0.0.1 -p tcp \
--dport 3306 -j \
REDIRECT --to-ports 13306

Caveat: This will only work if your clients are connecting from a separate machine. On the same server, this rule spares the local IP address 127.0.0.1.

To undo the redirection, repeat the above command with -D instead of -I.

Working for MySQL is COOL!

A new style for MySQL jobs page.
Haven't you heard? MySQL is hiring!


If the adventurous type doesn't appeal to you, you can opt for the quiet home sweet home version, with the beach sweet beach variation. That's what happened during the recent Summer of Code. I know it's a different story, but when I saw the picture of the career climber in the desert I couldn't resist associating it with Sakila typing away in the beach.