FR with MySQL - Stored Procedures

[Gunther]

Despite that several people reported that their FreeRadius 1.x installation
is working fine with MySQL Stored Procedures, I run into quite some
problems.

Here my environment:
- CentOS 4.4 on Xen Server 3.1
- FreeRadius 1.1.6
- MySQL 5.0.37 Community Edition with INNODB Tables

I used a very simple stored procedure to track down the problem:
---------------
DELIMITER //
DROP PROCEDURE IF EXISTS CheckIt //
CREATE PROCEDURE CheckIt ()
BEGIN
SELECT 12345;
END//
---------------
This routine will always return the value 12345.

Anyhow, when I called this procedure from FreeRadius I always go an error:
"PROCEDURE myDB.CheckIt can't return a result set in the given context"
-----
Sun May  6 07:23:10 2007 : Debug: rlm_sql_mysql: query:   CALL CheckIt()
Sun May  6 07:23:10 2007 : Debug: rlm_sql_mysql: MYSQL check_error: 1312
received
Sun May  6 07:23:10 2007 : Error: rlm_sql (sql): database query error,  CALL
CheckIt(): PROCEDURE myDB.CheckIt can't return a result set in the given
context
-----

I then tried to call the same function via a PHP script (w/o FreeRadius
involved) and run into the same problem.

Then I found the information that it is required for MySQL Stored Procedures
to function 
the client_flag 'CLIENT_MULTI_STATEMENTS' (refer to mysql.h) has to be added
to the mysql_real_connect call.
After adding it to the call within PHP all worked fine.

Then I added the flag to
freeradius-1.1.6/src/modules/rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c :
-----
if (!(mysql_sock->sock = mysql_real_connect(&(mysql_sock->conn),
                                                    config->sql_server,
                                                    config->sql_login,
                                                    config->sql_password,
                                                    config->sql_db,
                                                    atoi(config->sql_port),
                                                    NULL,
 
CLIENT_FOUND_ROWS|CLIENT_MULTI_STATEMENTS))) {
-----
./configure; make; make install

Note: CLIENT_MULTI_STATEMENTS automatically also sets CLIENT_MULTI_RESULTS
within MySQL 

Started FreeRadius and procedure calls were accepted and results were
correct.
-----
Sun May  6 21:29:08 2007 : Debug: rlm_sql_mysql: query:   CALL CheckIt()
Sun May  6 21:29:08 2007 : Debug: rlm_sql (sql): - sql_xlat finished
Sun May  6 21:29:08 2007 : Debug: rlm_sql (sql): Released sql socket id: 0
Sun May  6 21:29:08 2007 : Debug: radius_xlat:  '12345'
-----

A user can now login and things seem to work fine. But then consecutive
MySQL queries started
showing new error results:
-----
Sun May  6 21:41:42 2007 : Debug: rlm_sql_mysql: MYSQL check_error: 2014
received
Sun May  6 21:41:42 2007 : Error: rlm_sql (sql): database query error,
<SELECT ... my sql query> : Commands out of sync; you can't run this command
now
-----

MySQL seems to track the state of each call and when the order of this state
is incorrect,
MySQL responds with 'CR_COMMANDS_OUT_OF_SYNC' = 'Commands out of sync; you
can't run this command now'.

>From what I found on the net ... "When the result of a statement isn't freed
MySQL gives an error when
trying to process a new query"

Could it be that there is somewhere a 'mysql_free_result' missing?

This is what the MySQL documentation is saying:
--------------------
B.1.2.13. Commands out of sync
If you get Commands out of sync; you can't run this command now in your
client code, you are calling client functions in the wrong order.
This can happen, for example, if you are using mysql_use_result() and try to
execute a new query before you have called mysql_free_result(). It can also
happen if you try to execute two queries that return data without calling
mysql_use_result() or mysql_store_result() in between.
--------------------
2.4.16. Upgrading MySQL
If, after an upgrade, you experience problems with recompiled client
programs, such as Commands out of sync  or unexpected core dumps, you
probably have used old header or library files when compiling your programs.
In this case, you should check the date for your mysql.h file and
libmysqlclient.a library to verify that they are from the new MySQL
distribution. If not, recompile your programs with the new headers and
libraries.
-------------------- ... Did that ...

When I leave some time between a login/logout/login it works ... Looks like
the MySQL status information
times out after a short while.

Any hints on getting this up and running without changing O/S ? Thanks!