Erlang MySQL Stored Procedure Support

Posted by jake | Filed under Programming Ducks

github.com/JacobVorreuter/erlang_mysql

If you’re getting the following exception trying to call a stored procedure or prepared statement with the Erlang MySQL library you must apply this patch to mysql_auth.erl inorder to add multi-results and multi-statements to the caps list:

{error,{mysql_result,[],[],0,<<"#0A000PROCEDURE
dbname.procname can't return a result set in the given context">>}}




@@ -27,6 +27,8 @@
 -define(LONG_PASSWORD, 1).
 -define(LONG_FLAG, 4).
 -define(PROTOCOL_41, 512).
+-define(CLIENT_MULTI_STATEMENTS, 65536).
+-define(CLIENT_MULTI_RESULTS, 131072).
 -define(TRANSACTIONS, 8192).
 -define(SECURE_CONNECTION, 32768).
 -define(CONNECT_WITH_DB, 8).
@@ -121,7 +123,8 @@ make_new_auth(User, Password, Database) ->
          ?CONNECT_WITH_DB
        end,
     Caps = ?LONG_PASSWORD bor ?LONG_FLAG bor ?TRANSACTIONS bor
-	?PROTOCOL_41 bor ?SECURE_CONNECTION bor DBCaps,
+	?PROTOCOL_41 bor ?SECURE_CONNECTION bor
+	?CLIENT_MULTI_STATEMENTS bor ?CLIENT_MULTI_RESULTS bor DBCaps,
     Maxsize = ?MAX_PACKET_SIZE,
     UserB = list_to_binary(User),
     PasswordL = size(Password),

Permalink | Comments (5) | Jan 14, 2009

5 Responses to “Erlang MySQL Stored Procedure Support”

  1. takeshi Says:
    September 22nd, 2009 at 1:20 am

    Hello,
    nice work with the mysql driver.

    However, I noticed something strange when trying to install erlang_mysql (make install).
    It is about this line from the Makefile:
    mkdir -p /`erl -eval ‘io:format(”~s~n”, [code:lib_dir()])’ -s init stop -noshell`/erlang_mysql-1/{ebin,include}

    When the above is executed on CentOS5.3, all is well, but when I try it with Ubuntu9.04, somehow {ebin,include} is not expanded and a folder named {ebin,include} is created, causing the subsequent steps to fail.
    May I suggest to replace the above line with two different mkdir commands to avoid this problem in different platforms?

  2. takeshi Says:
    October 14th, 2009 at 7:04 am

    Hello, it seems the above patch is not enough to permit using Stored Procedures with the Erlang-mysql library. In my tests, it works most of the time. However, from time to time, I get an ‘update’ response instead of ‘data’ response to a query like this:

    Query:”select * from `Group` where Id=1234″
    Result:{updated,{mysql_result,[],[],1,0,512,768,[],[]}}

    So it seems the patch will instruct the MySQL to accept sending stored procedure results to the client. However, it seems this causes the server to send some extra messages and so, I suspect some extra coding is necessary otherwise, responses and fetches will be wrongly paired as there will be some remaining messages concerning previous requests. Also, if the SP returns Result Sets as those flags will allow, I believe there is no code in the lib to handle them: it would be able to treat a single Result Set.

    Detail: usually I set a large connection pool, which was concealing the above problem somehow. The problem became clear when I started to use a single connection to the server (unit testing).

    regard,
    takeshi

  3. takeshi Says:
    October 15th, 2009 at 4:06 am

    Actually I believe it is not a good idea to enable CLIENT_MULTI_STATEMENTS. This would permit someone to issue more than one query (select , call storedproc) in a single fetch and this will have disastrous consequences as erlang_mysql will only pick the first response and the others will be wrongly matched with subsequent requests. I tested, and to permit to call SPs, just enabling CLIENT_MULTI_RESULTS is enough. And it is safe to use the patch to call SPs as long as the SP doesn’t send a Result Set back. My previous described problem happens because I was using the patch to call SPs containing SELECT. So, if in your SP you just call UPDATE/INSERT/DELETE, it would be fine as the server will send a single response back to you and this will be properly handled by erlang_mysql (mysql currently doesn’t handle multiple responses for a single query).

  4. takeshi Says:
    October 15th, 2009 at 4:07 am

    >> (mysql currently doesn’t handle multiple responses for a single query).

    I meant: erlang_mysql currently doesn’t handle multiple responses for a single query.

  5. DmitryMe Says:
    December 18th, 2009 at 6:27 pm

    Well,
    I’ve changed driver to multiple results support. It returns a list of results in this case.
    http://www.gtracker.ru/patch

Leave a Reply