Search This Blog

24 October 2007

Proposed a talk for UC2008

Well, we did it. In between his SCRUM meeting and my compiling MySQL on Windows, Eric and I bashed out a proposal for the MySQL Users Conference 2008 and have submitted it. The content of the talk, well... It is entitled "Tour of External language stored procedures for MySQL" and we're going to talk about our little project.
On Kaj's blog, he suggests that submissions should be creative, descriptive and specific. I think we have all three bases covered.
Fingers crossed!

23 October 2007

The context of external language procedure's queries

Perhaps it wasn't entirely clear what the last posting meant by "reentrant". It means that the external language stored procedure can issue a query within the same thread context that executed the stored procedure.

Thanks to the question posed by Bill Karwin, I have tested this scenarios to demonstrate:
mysql> lock table mysql.proc write;
Query OK, 0 rows affected (0.00 sec)

mysql> call test2('select distinct name from mysql.proc');
+--------------------+
| name |
+--------------------+
| test1 |
| test2 |
| xml_current_time |
| xml_get_state |
| xml_list_methods |
| xml_list_signature |
| xml_method_help |
| xml_sum_and_diff |
+--------------------+
8 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
As you can see, the exclusive write lock on the PROC table has not prevented the inline query from being able to read the table as it truly is executed within the same thread.
A second example to demonstrate that the stored procedure is executed from within the same transaction context:
mysql> create table foo (a int primary key) engine=innodb;
Query OK, 0 rows affected (0.19 sec)

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

mysql> insert into foo values (1),(2),(4);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> call test2('commit');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo values (8),(12),(14);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> call test2('rollback');
Query OK, 0 rows affected (0.00 sec)

mysql> select * from foo;
+---+
| a |
+---+
| 1 |
| 2 |
| 4 |
+---+
3 rows in set (0.00 sec)

I hope that clarifies things a little.

Reentrant MySQL with External Stored Procedures

Ok, this is a late late blog post because the code was done and pushed into the work tree not last weekend, but the weekend before. I think it is a pretty significant feature to blog about but for some strange reason, it completely slipped my mind.

Check this out:
mysql> call test2('select count(*) from mysql.user; select count(*) from mysql.proc');
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Not impressed yet? How about this:
mysql> set max_sp_recursion_depth=2;
Query OK, 0 rows affected (0.00 sec)

mysql> call test2('select count(*) from mysql.proc;call test2("select count(*) from mysql.db;call test2(\'select count(*) from mysql.user\')")');
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
And here was how the procedure was declared:

CREATE PROCEDURE test2(query text)
READS SQL DATA
LANGUAGE deep_thought
DYNAMIC RESULT SETS 5
EXTERNAL NAME 'loopback';
Maybe now you're curious how the language plugin performs this magic? Well, the psm_example.so plugin is actually linked against the MySQL client libraries, albeit a craftily modified version of it: Less than 10 lines of code within the client library was added. Then the stored routine was able to reenter the mysql server simply by using the mysql client library and execute statements within the same context by having code somewhat like the following:

mysql_options(&mysql, MYSQL_OPT_USE_INLINE_CONNECTION, NULL);
if (!mysql_real_connect(&mysql, 0, 0, 0, 0, 0, 0, CLIENT_MULTI_STATEMENTS))
{ ... }

Pretty nifty, innit?