rlm_sql_mysql: SP support extended patch

[Peter Nixon]

Heh. This is rather amusing in that the postgresql shim (rlm_sql_postgresql) 
and its wrapper module (rlm_sql) started life as MySQL specific. This means 
that the design doesnt't actually exactly fit the way Postgresql does things 
as MySQL had no concept of transactions  or stored procedures etc at the 
time of writing, and rlm_sql_postgresql has to do some "funny things" in 
order to stay in that model (as do the other sql driver modules). The fact 
that youhave now taken some "hints" from the Postgresql driver module means 
that we probably should sit down and redesign rlm_sql's interface to the 
shims as MySQL now resembles a "real database" and does things pretty 
similar to other "real" databases.

Any takers? (I will provide what support I can on Postgresql)

Peter

On Thu 17 May 2007, Stanislav Schukin wrote:
> 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);
> }



-- 

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc