rlm_sql_mysql - sql_finish_select_query - multiples results sets

[Jean DERAM]

Nicolas Baradakis a écrit :
> En réponse à Jean DERAM :
>
>   
>> I'm working on FR 1.1.6 and mysql 5.0
>>
>> I wan't to work with stored procedure in mysql.
>> So as I read on this list there is some tuning of mysql connection flags
>> to do.
>> So I back ported rlm_sql_mysql from today snapshot (19/07/07) to FR 1.1.6
>>     
>
> This is not needed. We're going to include support for MySQL stored
> procedure in 1.1.7. Therefore I'd suggest you get the sources like that:
>
> $ cvs -d :pserver:anoncvs at cvs.freeradius.org:/source login
> CVS password: anoncvs
> $ cvs -d :pserver:anoncvs at cvs.freeradius.org:/source checkout -r branch_1_1 radiusd
>
>   
>> I've duplicated the code of sql_finish_query in sql_finish_select_query
>> to care of multiple results set and then it works.
>>
>> What do you think about this?
>>     
>
> I think maybe you didn't backport the changes to sql_store_result().
> It should work if you use the branch 1.1 of the CVS. As always, any
> feedback is really welcome.
>
>   
I've backported all changed in relation with stored procedure from 
rlm_mysql.

I've done what you recommanded to me: get cvs version and make a try.

I've the same problem with cvs version.
This is reproducible like this.

In mysql create a test stored procedure:

DROP PROCEDURE IF EXISTS p_test;
DELIMITER $$
CREATE PROCEDURE p_test(
IN uname VARCHAR(64)
)
BEGIN
     SELECT id, UserName, Attribute, Value, op FROM radreply WHERE 
Username=uname ORDER BY id;
END $$
DELIMITER ;


in /etc/raddb/sql.conf:
    authorize_reply_query = "CALL p_test('%{SQL-User-Name}')"
    num_sql_socks = 1  
I limit num_sql_socks to force use of the same socket for this test.

In radreply, insert correct attributes:
INSERT INTO `radreply` VALUES ('', 'jean', 'Framed-Routing', ':=', 'None');
INSERT INTO `radreply` VALUES ('', 'jean', 'Framed-Protocol', ':=', 'PPP');
INSERT INTO `radreply` VALUES ('', 'jean', 'Service-Type', ':=', 
'Framed-User');

Now we can test with your favorite tools. All is ok, user can login, 
attributes are send in the reply.

Then we insert a bad attribute:
INSERT INTO `radreply` VALUES ('', 'jean', 'AA-TEST', ':=', 'xxxxxx');


Now if we try to login we get an Access-Reject
In debug we get:
 Error: rlm_sql: Failed to create the pair: Unknown attribute "AA-TEST"
 Error: rlm_sql (sql): Error getting data from database

This seems good but all next sql queries failed (even if user is 
different and has good attributes):
I always get this in log:
 Debug: rlm_sql_mysql: MYSQL check_error: 2014 received
 Error: rlm_sql (sql) in sql_postauth: Database query error - Commands 
out of sync; you can't run this comand now

This seems to be due to a bad free_result of a multiple result set.
The mysql connection get out of sync, a never recover for a normal state 
(all mysql queries are rejected).


I explain this by this part of code in rlm_sql.c:
int sql_getvpdata(SQL_INST * inst, SQLSOCK * sqlsocket, VALUE_PAIR 
**pair, char *query, int mode)
{
        SQL_ROW row;
        int     rows = 0;

        /*
         *      If there's no query, return an error.
         */
        if (!query || !*query) {
                return -1;
        }

        if (rlm_sql_select_query(sqlsocket, inst, query)) {
                radlog(L_ERR, "rlm_sql_getvpdata: database query error");
                return -1;
        }
        while (rlm_sql_fetch_row(sqlsocket, inst)==0) {
                row = sqlsocket->row;
                if (!row)
                        break;
                if (sql_userparse(pair, row, mode) != 0) {
                        radlog(L_ERR | L_CONS, "rlm_sql (%s): Error 
getting data from database", inst->config->xlat_name);
                        
(inst->module->sql_finish_select_query)(sqlsocket, inst->config);
                        return -1;
                }
                rows++;
        }
        (inst->module->sql_finish_select_query)(sqlsocket, inst->config);

        return rows;
}

=> If sql_userparse failed then FR call 
(inst->module->sql_finish_select_query)(sqlsocket, inst->config);

But sql_finish_select_query is only doing this:
static int sql_finish_select_query(SQLSOCK * sqlsocket, SQL_CONFIG *config)
{
        sql_free_result(sqlsocket, config);

        return 0;
}

I think sql_finish_select_query should be care if the result is a 
multiple result set.

I've made a patch for cvs version (joined in this mail).
With this patch I've got a better behaviour:

- FR says the attribut AA-TEST is unknown
- FR return good attibutes and send Access-Accept (without the patch, FR 
send a reject)
- FR doesn't make the connection out of sync

Have got the same behaviour with stored procedure and bad attributes?


Regards,
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: freeradius-1.1.7-mysql_stored_procedure.patch
URL: <http://lists.freeradius.org/pipermail/freeradius-devel/attachments/20070720/a77f2d28/attachment.ksh>