[Devel] MySQL and stored procedures

[Stefan Prelle]

Hi all,

I am trying to use the database layer's "raw_query" method in
conjunction with the MySQL module and stored procedures. To be more
specific: I am trying to substitute the LCR modules DB command with a
stored procedure.
I have to admit, that I am not very familiar with the MySQL C-API.

I had to add the CLIENT_MULTI_STATEMENTS option to "mysql_real_connect"
in my_con.c, to support stored procedures.

Now, I have a stored procedure, which when called from the shell
returns e.g. such an output:

mysql> CALL search_front_gws("4940668610111");
+-----------+------+------------+-----------+-------+--------+---------------+--------+
| ip_addr   | port | uri_scheme | transport | strip | prefix | gw_name
| grp_id |
+-----------+------+------------+-----------+-------+--------+---------------+--------+
|  93507134 | 5060 |          1 |         1 |     0 |        |
SBC01_MNet61b |      1 | 
| 705089086 | 5060 |          1 |         1 |     0 |        |
Freenet_01    |      1 | 
|  40029758 | 5060 |          1 |         1 |     0 |        |
SBC01_MNet60  |      1 | 
|  45862462 | 5060 |          1 |         1 |     0 |        |
SBC01_MNet63  |      1 | 
|  43175486 | 5060 |          1 |         1 |     0 |        |
SBC01_MNet61a |      1 | 
+-----------+------+------------+-----------+-------+--------+---------------+--------+
5 rows in set (0.00 sec)

Called from within the LCR module, the first request is working fine.,
the results are processed as expected. When a second call is processed,
the query command fails:
submit_query: Commands out of sync; you can't run this command now

Digging a bit into it, I found this
http://dev.mysql.com/doc/refman/5.1/en/commands-out-of-sync.html

As far as I understand the database layer, "mysql_store_result()" is
called any time a query is issued successfully, so this effect must be
related especially to stored procedures. Any ideas?

As a workaround, I tried to hardcode the MySQL C-API commands in the
module - with limited success. Basically a code structured like this:

mysql_query()
result = mysql_use_result()
while ((row = mysql_fetch_row(result))) {
 ...
}
mysql_free_result(result);

produced the same result (command out of sync). I had to put the
following code between the while-loop and the free_result:

while (mysql_next_result(mysql)) {
  LOG(L_DBG, "load_front_gws: Ignore next result\n");
}
mysql_next_result(mysql);

That got it working, but I don't really know what I am doing there. 

Unfortunately this code looses it's database connection if put to some
load. I can't really figure out why, except for this reason:
>From http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
"You can also encounter this error with applications that fork child
processes, all of which try to use the same connection to the MySQL
server. This can be avoided by using a separate connection for each
child process."

But as far as I can see, the normal database layer also uses only one
database connection. So what do I miss?

Any help/idea is appreciated.

Regards,
  Stefan