Here is my work on the subject. First, I wrote a patch that iterates through multi-results in sql_store_result() and finally, takes the last result to store ( http://tifs.anarxi.st/freeradius-1.1.3-mysql5_sp.v1.patch) But later I understood that better way to do so is in sql_finish_select_query(). Besides, I wanted a possibility to use stored procedures in accounting calls. So I've also written some code to sql_finish_query(). Oher changes were made as well. While making the changes, I followed next rules: - don't break sql_mysql.c code-style; - add functionality only for MYSQL_VERSION_ID >= 50000 - use hints from rlm_sql_postgresql (like "rlm_sql_mysql: affected rows = %d" I took from there); - follow mysql version 5.0 reference; - final code should be able to handle all kinds of multi-results; Patch v2 (http://tifs.anarxi.st/freeradius-1.1.3-mysql5_sp.v2.patch) allows: - Use stored procedures in 'select' calls (used in authorization queries). First result that contains any fields is used. Any other results are ignored and properly freed, and debug message is printed:
rlm_sql_mysql: SQL query returned redundant result rlm_sql_mysql: redundant result (5 fields) ignored Any affected rows result is dumped as: rlm_sql_mysql: affected rows = 0
- Use stored procedures in 'update/insert query' calls (used in accounting). Results with fields are ignored (with correspondind message). First proper affected rows result is used for sql_affected_rows() if called. Any other affected rows result is dumped. However it seems that MySQL currently is unable to return more than one affected rows count and some code can be cutted out. But I left it for compatibility with MySQL v5.0 reference. I've performed some testing: - using SP as authorization reply query, trying to confuse rlm_sql_myql with multiple results (everything works ok, first result is used other ones ignored); - using SP as accounting start query (sql_query() -> sql_finish_query()), works ok, redundant results are ignored, affected rows dumped; - using SP as accounting update query (sql_query() -> sql_affected_rows() -> sql_finish_query()), works ok until we select any result set. affected rows = 0 comes instead of real affected rows count and freeradius tries alternative query. Though SP is still proceed ;) Hope this will be useful for Freeradius project! Please, use this code if you found it suitable. The patch itself comes below (you can also download it here: http://tifs.anarxi.st/freeradius-1.1.3-mysql5_sp.v2.patch) diff -ruN freeradius-1.1.3.orig/src/modules/rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c freeradius-1.1.3/src/modules/rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c --- freeradius-1.1.3.orig/src/modules/rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c 2005-12-09 18:10:08.000000000 +0200 +++ freeradius-1.1.3/src/modules/rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c 2007-05-17 02:10:13.000000000 +0300 @@ -47,9 +47,13 @@ MYSQL conn; MYSQL *sock; MYSQL_RES *result; + int num_fields; SQL_ROW row; } rlm_sql_mysql_sock; +/* Prototypes */ +static int sql_num_fields(SQLSOCK * sqlsocket, SQL_CONFIG *config); + /************************************************************************* * * Function: sql_create_socket @@ -82,7 +86,11 @@ config->sql_db, atoi(config->sql_port), NULL, +#if MYSQL_VERSION_ID < 50000 CLIENT_FOUND_ROWS))) { +#else + CLIENT_FOUND_ROWS|CLIENT_MULTI_STATEMENTS|CLIENT_MULTI_RESULTS))) { +#endif radlog(L_ERR, "rlm_sql_mysql: Couldn't connect socket to MySQL server %s@%s:%s", config->sql_login, config->sql_server, config->sql_db); radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'", mysql_error(&mysql_sock->conn)); mysql_sock->sock = NULL; @@ -173,18 +181,43 @@ *************************************************************************/ static int sql_store_result(SQLSOCK * sqlsocket, SQL_CONFIG *config) { +#if MYSQL_VERSION_ID >= 50000 + int status; +#endif rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn; if (mysql_sock->sock == NULL) { radlog(L_ERR, "rlm_sql_mysql: Socket not connected"); return SQL_DOWN; } +#if MYSQL_VERSION_ID >= 50000 + /* + * Get every affected row count prior to result + */ + while (!(mysql_field_count(mysql_sock->sock))) { + radlog(L_DBG, "rlm_sql_mysql: affected rows = %d", + mysql_affected_rows(mysql_sock->sock)); + + if ((status = mysql_next_result(mysql_sock->sock))) { + if (status > 0) { + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: Cannot get next result"); + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: %s", + mysql_error(mysql_sock->sock)); + return sql_check_error(mysql_errno(mysql_sock->sock)); + } + break; + } + } +#endif + if (!(mysql_sock->result = mysql_store_result(mysql_sock->sock))) { radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: Cannot get result"); radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: %s", mysql_error(mysql_sock->sock)); return sql_check_error(mysql_errno(mysql_sock->sock)); } + mysql_sock->num_fields = sql_num_fields(sqlsocket, config); + return 0; } @@ -235,12 +268,6 @@ return ret; } - /* Why? Per http://www.mysql.com/doc/n/o/node_591.html, - * this cannot return an error. Perhaps just to complain if no - * fields are found? - */ - sql_num_fields(sqlsocket, config); - return ret; } @@ -364,6 +391,59 @@ *************************************************************************/ static int sql_finish_query(SQLSOCK * sqlsocket, SQL_CONFIG *config) { +#if MYSQL_VERSION_ID >= 50000 + int status; + int field_count; + MYSQL_RES *result; + rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn; + + /* + * Free current result if we have one + */ + if ((field_count = mysql_field_count(mysql_sock->sock))) { + radlog(L_DBG, "rlm_sql_mysql: SQL query returned redundant result"); + if (!(result = mysql_store_result(mysql_sock->sock))) { + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: Cannot get result"); + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: %s", + mysql_error(mysql_sock->sock)); + return sql_check_error(mysql_errno(mysql_sock->sock)); + } + radlog(L_DBG, "rlm_sql_mysql: redundant result (%d fields) ignored", + field_count); + mysql_free_result(result); + } + + /* + * Get and free every next result or affected row count + */ + for (;;) { + if ((status = mysql_next_result(mysql_sock->sock))) { + if (status > 0) { + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: Cannot get next result"); + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: %s", + mysql_error(mysql_sock->sock)); + return sql_check_error(mysql_errno(mysql_sock->sock)); + } + break; + } + if ((field_count = mysql_field_count(mysql_sock->sock))) { + radlog(L_DBG, "rlm_sql_mysql: SQL query returned redundant result"); + if (!(result = mysql_store_result(mysql_sock->sock))) { + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: Cannot get result"); + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: %s", + mysql_error(mysql_sock->sock)); + return sql_check_error(mysql_errno(mysql_sock->sock)); + } + radlog(L_DBG, "rlm_sql_mysql: redundant result (%d fields) ignored", + field_count); + mysql_free_result(result); + } else { + radlog(L_DBG, "rlm_sql_mysql: affected rows = %d", + mysql_affected_rows(mysql_sock->sock)); + } + } +#endif + return 0; } @@ -378,7 +458,45 @@ *************************************************************************/ static int sql_finish_select_query(SQLSOCK * sqlsocket, SQL_CONFIG *config) { +#if MYSQL_VERSION_ID >= 50000 + int status; + int field_count; + MYSQL_RES *result; + rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn; +#endif + sql_free_result(sqlsocket, config); +#if MYSQL_VERSION_ID >= 50000 + /* + * Get and free every next result or affected row count + */ + for (;;) { + if ((status = mysql_next_result(mysql_sock->sock))) { + if (status > 0) { + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: Cannot get next result"); + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: %s", + mysql_error(mysql_sock->sock)); + return sql_check_error(mysql_errno(mysql_sock->sock)); + } + break; + } + if ((field_count = mysql_field_count(mysql_sock->sock))) { + radlog(L_DBG, "rlm_sql_mysql: SQL query returned redundant result"); + if (!(result = mysql_store_result(mysql_sock->sock))) { + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: Cannot get result"); + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: %s", + mysql_error(mysql_sock->sock)); + return sql_check_error(mysql_errno(mysql_sock->sock)); + } + radlog(L_DBG, "rlm_sql_mysql: redundant result (%d fields) ignored", + field_count); + mysql_free_result(result); + } else { + radlog(L_DBG, "rlm_sql_mysql: affected rows = %d", + mysql_affected_rows(mysql_sock->sock)); + } + } +#endif return 0; } @@ -393,8 +511,40 @@ *************************************************************************/ static int sql_affected_rows(SQLSOCK * sqlsocket, SQL_CONFIG *config) { +#if MYSQL_VERSION_ID >= 50000 + int status; + int field_count; + MYSQL_RES *result; +#endif rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn; +#if MYSQL_VERSION_ID >= 50000 + /* + * Get and free every result prior to affected row count + */ + while ((field_count = mysql_field_count(mysql_sock->sock))) { + radlog(L_DBG, "rlm_sql_mysql: SQL query returned redundant result"); + if (!(result = mysql_store_result(mysql_sock->sock))) { + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: Cannot get result"); + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: %s", + mysql_error(mysql_sock->sock)); + return -1; + } + radlog(L_DBG, "rlm_sql_mysql: redundant result (%d fields) ignored", + field_count); + mysql_free_result(result); + + if ((status = mysql_next_result(mysql_sock->sock))) { + if (status > 0) { + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: Cannot get next result"); + radlog(L_ERR, "rlm_sql_mysql: MYSQL Error: %s", + mysql_error(mysql_sock->sock)); + return -1; + } + return -1; + } + } +#endif return mysql_affected_rows(mysql_sock->sock); }