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!