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?