[PATCH] Allow mod_mysql_vhost to use stored procedures
In order to be able to use a stored procedure to get the document root for a given domain some changes need to be made to mod_mysql_vhost.
Firstly I have added a line to read from a section in my.cnf, called "lighttpd". An example of this section is:
[lighttpd] multi-results multi-statements
I have decided to use this method as it can be used for changing other options without needing to recompile the module, so it may be of use in the future. The other way of doing this would be to set the options explicitly, however this is less flexible.
Also I have added calls to mysql_next_result after each mysql_free_result.
I have tested this patch on Solaris 10 and it seems to work fine.
#4 Updated by benbrown over 8 years ago
- File mod_mysql_vhost_stored_proc.patch added
After looking at the MySQL documentation more (http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-queries.html) I have decided to change the patch slightly to pass the CLIENT_MULTI_STATEMENTS flag to mysql_real_connect rather than reading a group item from my.cnf, as this could be problematic with multiple instances that may require different options to be set. I have also added a check for at least MySQL version 4.1, as this is when the CLIENT_MULTI_STATEMENTS flag was added.
The need for the mysql_next_result() calls are also detailed in the page linked above, and also in this document (http://dev.mysql.com/doc/refman/5.1/en/mysql-next-result.html) which I will quote from:
If your program uses CALL statements to execute stored procedures, the CLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure. Because CALL can return multiple results, you should process them using a loop that calls mysql_next_result() to determine whether there are more results.
If you do not call mysql_next_result() after processing the query, subsequent queries will fail with an out of sequence error, and lighty returns a 500 error.
#5 Updated by benbrown over 8 years ago
Further information regarding mysql_next_result(). As we only care about the first result set returned, we just run it in a loop without storing the results. As the results are not stored, there is no need to call mysql_free_result in each iteration - in fact it would be impossible to do so. As mysql_free_result() has already been called from the previous mysql_store_result call, it is fine to loop over mysql_next_result() as I have done.
I have attached a new version of the patch "mod_mysql_vhost_2009062102.patch" which also checks for the correct mysql version before calling mysql_next_result().
Also available in: Atom