[ejabberd] mysql erlang driver fails with mysql 5 stored procedures with prepared statments

Oleg Palij o.palij at dp.uz.gov.ua
Mon Aug 20 11:50:44 MSD 2007


Test case:
create procedure in mysql:
mysql> DELIMITER $$
mysql> CREATE PROCEDURE `dummy`()
BEGIN
   PREPARE stmt FROM 'CREATE TABLE IF NOT EXISTS temp (t TEXT)';
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
END;$$
mysql> DELIMITER ;

try to execute it from erlang:
2> {ok, DBRef}=mysql_conn:start("localhost", 3306, "user", "pass", "db", fun(Level, Format, Argument) -> ok end).
3> mysql_conn:fetch(DBRef, ["CALL dummy();"], self()).
{error,{mysql_result,[],
                     [],
                     0,
                     "#0A000PROCEDURE db.dummy can't return a result set in the given context"}}

here is a quick hack for this:
--- mysql_auth.erl      Fri Aug 17 17:59:09 2007
+++ mysql_auth_new.erl  Fri Aug 17 17:59:01 2007
@@ -121,7 +121,7 @@
                     ?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 65536 bor 131072 bor DBCaps,
     Maxsize = ?MAX_PACKET_SIZE,
     UserB = list_to_binary(User),
     PasswordL = size(Password),

But this changes the default behaviour.
Maybe it is better to give user ability to specify flags while connect. Here is example patch for standalone connection:
http://www.dp.uz.gov.ua/o.palij/mod_logdb/userflags.diff
Together with old usage (w/o flags) it allows following:
2> {ok, DBRef}=mysql_conn:start("localhost", 3306, "user", "pass", "db", [65536, 131072], fun(Level, Format, Argument) -> ok end).
3> mysql_conn:fetch(DBRef, ["CALL dummy();"], self()).
{updated,{mysql_result,[],[],0,[]}}

It is also not so good, as erlang mysql driver (correct me if i wrong) does not support multi results in c api manner (http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html), but at least it allows to use prepared statments in stored procedures...

P.S. from mysql.h:
	65536 is CLIENT_MULTI_STATEMENTS
	131072 is CLIENT_MULTI_RESULTS

-- 
ISC Pridn railway, System Administrator
e-mail: o.palij at dp.uz.gov.ua
xmpp://malik@jabber.te.ua


More information about the ejabberd mailing list