rlm_sql_mysql: SP support extended patch
Stanislav Schukin
tifssoft at gmail.com
Thu May 17 01:58:55 CEST 2007
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);
}
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-devel/attachments/20070517/f19234b9/attachment.html>
More information about the Freeradius-Devel
mailing list