[OpenSER-Devel] presence load tests: 1.2.1 vs trunk with mysql

Dan Pascu dan at ag-projects.com
Fri Jul 20 13:05:02 CEST 2007


On Friday 20 July 2007, Victor Gamov wrote:
> Henning Westerholt wrote:
> >
> > Because of the side effects you mentioned above i think we have even
> > more reasons to stay with the old, known behavior.
>
> Let's explain my point of view.
>
> Now we have two functions making the same job by some different way:
> -- db_mysql_store_result() retrieves a complete result set and call
> many functions from res.c to process it
> -- db_mysql_fetsh_result() returns a partial result set and process it
> internally. It use nrows param to return nrows lines from this result
> set.
>
> Both of them use mysql_store_result() to get result set from DB-server
> to client so there is no problems on server-side (locking or some
> other) as I understand.

In this case indeed there is no problem. The issues I mentioned happen if 
the code uses a combination of mysql_use_result()+mysql_fecth_row()
However if they both use mysql_store_result() then the issues I mentioned 
do not apply. I haven't checked the code, so my comments were only 
touching general aspects and possible sources of problems. They're 
weren't meant as a critique of the actual code, but rather as pointers to 
where the issues could come from.

>
> For db_mysql_fetch result() when nrows==0 function do nothing and
> return 0.  But in this situation if user makes SELECT-like query and
> then call db_mysql_fetch_rows() with nrows=0 no mysql_store_result()
> called and it wrong because "you _must_ call mysql_store_result() or
> mysql_use_result() for every statement that successfully retrieves
> data" (http://dev.mysql.com/doc/refman/5.1/en/mysql-store-result.html)

Here I am not sure what happens if you do not retrieve the result set. It 
may be automatically released or it may prevent further queries from 
being processed correctly. I lean more towards the latter.
What I know for sure, is if there is a call procedure that returns 
multiple result sets, one must retrieve them all before moving to the 
next query. Else the next query will fail. By extrapolating this 
behavior, I expect that even with a single result set it must be 
retrieved before moving to the next query else the next query will fail.
I guess this is why in the docs they say you "must" retrieve result set.

Now from looking at the recent change made yesterday to return if the 
requested number of rows is 0, this will break the code by not retrieving 
the result set and preventing further queries from working properly.
At least a call to mysql_use_result(); mysql_free_result(); must be made 
even if the caller requests 0 rows.

Maybe this doesn't happen right now because no code actually asks for 0 
rows after a query, but it is a potential source of problems. Thus I 
believe the yesterday change to optimize for nrows==0 must be reverted or 
enhanced to call the above 2 functions to release the result set.

>
> And it's really strange don't get any data and call
> db_mysql_fetch_rows() with nrows=0 after SELECT-like query
>
> Also there are no problems or "any notable performance degradation if
> you call mysql_store_result()  in all cases".
>
> So we can use nrows to realize different behavior for
> db_mysql_fetch_rows():
>
> -- nrows == 0 -- get complete result set. If called after non
> SELECT-like query it will call mysql_store_result() and
> mysql_field_count() and return 0
>
> -- nrows > 0 -- get result set and return nrows lines only
>
> -- if we want to fetch zero rows then we can use nrows < 0 (now looks
> like wrong parameter value), call mysql_store_result() and
> mysql_free_result() and return 0.
>
> IMHO
>
> If I'm wrong in some basics please correct me. Thanks!



-- 
Dan



More information about the Devel mailing list