Question on stability of results returned by C api mysql_fetch_row()

[Lew Pitcher]

I have written a C program that summarizes the contents of a table. It
only "SELECT"s from the table, and retrieves rows using mysql_fetch_row
(). As the program must interpret the row data, it may need to work with
as many as five (5) rows as a time.

I'm chasing down a memory corruption problem with this program, that
seems to overwrite the stored row data, and I want to eliminate my use
(or possibly, misuse) of the C api as the source.

My question is: Once I've retrieved a row using mysql_fetch_row(), does
that returned row (the MYSQL row, and the strings it's elements point to)
remain stable and unaltered by subsequent mysql_fetch_row() calls? If so,
then my problem lies outside of my use of the API, otherwise, I suspect
that I've used the API incorrectly, and will have to refactor that
portion of the code.

Here's a brief, naive example of the sort of processing I'm doing. This
is NOT the code I'm debugging; this code doesn't seem to suffer the
memory corruption my more complex program does. However, this code /does/
illustrate the mysql_fetch_row() assumption that I use in my bigger
project.

##### Table definition #####
Field Type Null Key Default Extra
t1ID int(10) unsigned NO PRI NULL auto_increment
t1Key varchar(20) NO NULL
t1Valu varchar(80) YES NULL

##### Table contents #####
t1ID t1Key t1Valu
1 HOME /home/lpitcher
2 PWD /home/lpitcher
3 LOGNAME lpitcher
4 TERM xterm

##### Program source #####
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <mysql/my_global.h>
#include <mysql/mysql.h>

/*
** USER macro definition supplied by compile commandline
** PSWD macro definition supplied by compile commandline
*/

int main(void)
{
MYSQL *dbm;
MYSQL_RES *results;
MYSQL_ROW row1,row2,row3;

dbm = mysql_init(NULL);
mysql_real_connect(dbm,"localhost",USER,PSWD,"lptest",0,NULL,0);

mysql_query(dbm,"SELECT t1Key, t1Valu FROM lptest.t1;");
results = mysql_store_result(dbm);

row1 = mysql_fetch_row(results);

/*
** Do either of these calls to mysql_fetch_row() somehow
** alter the results accessable from the row returned by
** the prior call(s) to mysql_fetch_row()?
*/
row2 = mysql_fetch_row(results);
row3 = mysql_fetch_row(results);

/*
** At this point, can I be certain that row1, row2, and row3
** all access different table rows?
*/

if (strcmp(row1[0],row2[0]) == 0)
printf("First and second rows have the same key [%s]\n",row1[0]);
if (strcmp(row1[1],row2[1]) == 0)
printf("First and second rows have the same value [%s]\n",row1[1]);

if (strcmp(row1[0],row3[0]) == 0)
printf("First and third rows have the same key [%s]\n",row1[0]);
if (strcmp(row1[1],row3[1]) == 0)
printf("First and third rows have the same value [%s]\n",row1[1]);

if (strcmp(row2[0],row3[0]) == 0)
printf("Second and third rows have the same key [%s]\n",row2[0]);
if (strcmp(row2[1],row3[1]) == 0)
printf("Second and third rows have the same value [%s]\n",row2[1]);

mysql_free_result(results);
mysql_close(dbm);

return 0;
}

##### Program execution #####
First and second rows have the same value [/home/lpitcher]


I appreciate any guidance or advice you can give me.
Thanks,

[Tony Mountifield]

Post by Lew Pitcher
I have written a C program that summarizes the contents of a table. It
only "SELECT"s from the table, and retrieves rows using mysql_fetch_row
(). As the program must interpret the row data, it may need to work with
as many as five (5) rows as a time.
I'm chasing down a memory corruption problem with this program, that
seems to overwrite the stored row data, and I want to eliminate my use
(or possibly, misuse) of the C api as the source.
My question is: Once I've retrieved a row using mysql_fetch_row(), does
that returned row (the MYSQL row, and the strings it's elements point to)
remain stable and unaltered by subsequent mysql_fetch_row() calls? If so,
then my problem lies outside of my use of the API, otherwise, I suspect
that I've used the API incorrectly, and will have to refactor that
portion of the code.

Your example looks ok, so you must be doing something subtly different in
the other program.

If you do mysql_store_result(), it allocates memory for all the rows in the
result set, so you can seek around them and fetch multiple rows independently,
as per your example.

If the expected result set is very large, mysql_store_result() can use a lot
of memory, and mysql_use_result() can be used instead. But in this case,
rows are fetched from the server one at a time, you cannot seek around the
data set, and I would think you can only have one live row at a time.

If that doesn't explain it, then you may need to share the appropriate portion
of the real code that doesn't work.

Cheers
Tony

Post by Lew Pitcher
Here's a brief, naive example of the sort of processing I'm doing. This
is NOT the code I'm debugging; this code doesn't seem to suffer the
memory corruption my more complex program does. However, this code /does/
illustrate the mysql_fetch_row() assumption that I use in my bigger
project.
##### Table definition #####
Field Type Null Key Default Extra
t1ID int(10) unsigned NO PRI NULL auto_increment
t1Key varchar(20) NO NULL
t1Valu varchar(80) YES NULL
##### Table contents #####
t1ID t1Key t1Valu
1 HOME /home/lpitcher
2 PWD /home/lpitcher
3 LOGNAME lpitcher
4 TERM xterm
##### Program source #####
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/my_global.h>
#include <mysql/mysql.h>
/*
** USER macro definition supplied by compile commandline
** PSWD macro definition supplied by compile commandline
*/
int main(void)
{
MYSQL *dbm;
MYSQL_RES *results;
MYSQL_ROW row1,row2,row3;
dbm = mysql_init(NULL);
mysql_real_connect(dbm,"localhost",USER,PSWD,"lptest",0,NULL,0);
mysql_query(dbm,"SELECT t1Key, t1Valu FROM lptest.t1;");
results = mysql_store_result(dbm);
row1 = mysql_fetch_row(results);
/*
** Do either of these calls to mysql_fetch_row() somehow
** alter the results accessable from the row returned by
** the prior call(s) to mysql_fetch_row()?
*/
row2 = mysql_fetch_row(results);
row3 = mysql_fetch_row(results);
/*
** At this point, can I be certain that row1, row2, and row3
** all access different table rows?
*/
if (strcmp(row1[0],row2[0]) == 0)
printf("First and second rows have the same key [%s]\n",row1[0]);
if (strcmp(row1[1],row2[1]) == 0)
printf("First and second rows have the same value [%s]\n",row1[1]);
if (strcmp(row1[0],row3[0]) == 0)
printf("First and third rows have the same key [%s]\n",row1[0]);
if (strcmp(row1[1],row3[1]) == 0)
printf("First and third rows have the same value [%s]\n",row1[1]);
if (strcmp(row2[0],row3[0]) == 0)
printf("Second and third rows have the same key [%s]\n",row2[0]);
if (strcmp(row2[1],row3[1]) == 0)
printf("Second and third rows have the same value [%s]\n",row2[1]);
mysql_free_result(results);
mysql_close(dbm);
return 0;
}
##### Program execution #####
First and second rows have the same value [/home/lpitcher]
I appreciate any guidance or advice you can give me.
Thanks,
--
Lew Pitcher
"In Skills, We Trust"

[Lew Pitcher]

Post by Tony Mountifield

Post by Lew Pitcher
I have written a C program that summarizes the contents of a table. It
only "SELECT"s from the table, and retrieves rows using mysql_fetch_row
(). As the program must interpret the row data, it may need to work
with as many as five (5) rows as a time.
I'm chasing down a memory corruption problem with this program, that
seems to overwrite the stored row data, and I want to eliminate my use
(or possibly, misuse) of the C api as the source.
My question is: Once I've retrieved a row using mysql_fetch_row(), does
that returned row (the MYSQL row, and the strings it's elements point to)
remain stable and unaltered by subsequent mysql_fetch_row() calls? If so,
then my problem lies outside of my use of the API, otherwise, I suspect
that I've used the API incorrectly, and will have to refactor that
portion of the code.

Your example looks ok, so you must be doing something subtly different
in the other program.

This is what I hoped.

Post by Tony Mountifield
If you do mysql_store_result(), it allocates memory for all the rows in
the result set, so you can seek around them and fetch multiple rows
independently, as per your example.

Again, exactly what I had hoped. It looks like I didn't misunderstand the
API after all. The documentation concentrates on the results of a single
row mysql_fetch_row(), and says nothing on how multiple calls interact.
My assumption was that multiple calls /don't/ interact, but the buggy
code made me question that assumption.

Post by Tony Mountifield
If the expected result set is very large, mysql_store_result() can use a
lot of memory, and mysql_use_result() can be used instead. But in this
case, rows are fetched from the server one at a time, you cannot seek
around the data set, and I would think you can only have one live row
at a time.

I use mysql_store_result() rather than mysql_use_result() for a several
reasons.

First off, (for reasons :-) ) I need to "seek around" the resultset in
order to properly parse the rows, which rules out mysql_use_result().

Secondly, I have additional queries to make, in conjunction with each
row. I can't wrap these queries into the main query as JOINs, as they
have their own limitations that preclude that. So, as mysql_use_result()
requires that I issue no other queries until I've mysql_free_result(), I
cannot use mysql_use_result() in this processing.

Finally, I've got the memory space to store the entire resultset, and if
I exceed that space, I can introduce limits to the size of the resultset.

Post by Tony Mountifield
If that doesn't explain it, then you may need to share the appropriate
portion of the real code that doesn't work.

Perhaps, but not yet. The real code is in such a raw state that it isn't
worth sharing yet.

FWIW, it attempts to produce a printed, interpreted log of telephone
calls from raw "Call Detail Record" data (as generated by my Asterisk
18.1.0 PBX) stored in a MySql table. The "interpretation" includes
grouping related call detail records into a single "telephone call"
instance for reporting purposes, determining the caller's name (from a
couple of "telephone book" tables), and which telephone (or internal
service) answered the call (with data derived from a "channel alias"
table and/or an "extension alias" table, as appropriate for the call).

And, this is just a hobby ;-)

Post by Tony Mountifield
Cheers Tony

[snip my example code]

Thanks

[Tony Mountifield]

Post by Lew Pitcher

Post by Tony Mountifield
If that doesn't explain it, then you may need to share the appropriate
portion of the real code that doesn't work.

Perhaps, but not yet. The real code is in such a raw state that it isn't
worth sharing yet.

Nevertheless, more eyes make bugs shallower.

Post by Lew Pitcher
FWIW, it attempts to produce a printed, interpreted log of telephone
calls from raw "Call Detail Record" data (as generated by my Asterisk
18.1.0 PBX) stored in a MySql table. The "interpretation" includes
grouping related call detail records into a single "telephone call"
instance for reporting purposes, determining the caller's name (from a
couple of "telephone book" tables), and which telephone (or internal
service) answered the call (with data derived from a "channel alias"
table and/or an "extension alias" table, as appropriate for the call).

I used and developed with Asterisk for many years, including CDR processing.
So happy to offer any insight if needed. Privately if you prefer.

Post by Lew Pitcher
And, this is just a hobby ;-)

Yes, for me too, having retired from real work! :)

Cheers
Tony

[The Natural Philosopher]

Post by Lew Pitcher
row1 = mysql_fetch_row(results);
/*
** Do either of these calls to mysql_fetch_row() somehow
** alter the results accessable from the row returned by
** the prior call(s) to mysql_fetch_row()?
*/
row2 = mysql_fetch_row(results);
row3 = mysql_fetch_row(results);

No, but beware of reusing 'results' again to make another database
access *in between*.

I've done that :-(

[Lew Pitcher]

Post by The Natural Philosopher

Post by Lew Pitcher
row1 = mysql_fetch_row(results);
/*
** Do either of these calls to mysql_fetch_row() somehow ** alter
the results accessable from the row returned by ** the prior
call(s) to mysql_fetch_row()?
*/
row2 = mysql_fetch_row(results);
row3 = mysql_fetch_row(results);

No, but beware of reusing 'results' again to make another database
access *in between*.
I've done that :-(

As have I. :-(

But not this time. :-)

Thanks, TNP, for the reminder

[The Natural Philosopher]

Post by Lew Pitcher

Post by The Natural Philosopher

Post by Lew Pitcher
row1 = mysql_fetch_row(results);
/*
** Do either of these calls to mysql_fetch_row() somehow ** alter
the results accessable from the row returned by ** the prior
call(s) to mysql_fetch_row()?
*/
row2 = mysql_fetch_row(results);
row3 = mysql_fetch_row(results);

No, but beware of reusing 'results' again to make another database
access *in between*.
I've done that :-(

As have I. :-(
But not this time. :-)
Thanks, TNP, for the reminder

Another possibility is buffer overflow.
C doesn't wipe your bottom for you. plenty of scope for shitty code

[Lew Pitcher]

I think I found my problem, and I'm kicking myself for it

Post by Lew Pitcher
I have written a C program that summarizes the contents of a table. It
only "SELECT"s from the table, and retrieves rows using mysql_fetch_row
(). As the program must interpret the row data, it may need to work with
as many as five (5) rows as a time.
I'm chasing down a memory corruption problem with this program, that
seems to overwrite the stored row data, and I want to eliminate my use
(or possibly, misuse) of the C api as the source.

OK, here's the thing. While performing some subsequent processing on the
returned resultset (call this the "primary" results), I conditionally
execute a second query against a history table, using values from the
"primary" results row. I then INSERT selected values from "primary"
results, along with the results of that secondary query, into a temporary
table. Once I've processed all the "primary" results, I then run a query
against this temporary table, and generate a report from /those/ results.

Somewhere in this process, I sometimes run into a "segmentation
violation" (a Unix SIGSEGV), which generally indicates that I've tried to
access memory that I have no access to (outside of my address space, not
mapped, etc.). Initial debugging showed that the values in the "primary"
resultset had changed, in ways that would cause a SIGSEGV, hence my
original question regarding the stability of the resultset.

Further debugging traced my problem to the history table query. When I
designed the table, I knew that it was possible that a query would
retrieve NO rows from the table. I /thought/ that my program logic
handled that condition. I was wrong. :-(

So, I've remedied that oversight, and only process that secondary query's
returned row if the mysql_num_rows() is greater than zero. When I /did
not/ do this, sometimes the query would return an empty resultset, and
I'd grab a data-item pointer that wasn't initialized, and off I'd go into
SIGSEGV territory. Now, with the mysql_num_rows() test guarding the data,
I only grab a data-item pointer when I actually have one.

Thanks to all who made suggestions. You helped me see my code in a new
light and discover my oversight.

[The Natural Philosopher]

Post by Lew Pitcher
I think I found my problem, and I'm kicking myself for it

Post by Lew Pitcher
I have written a C program that summarizes the contents of a table. It
only "SELECT"s from the table, and retrieves rows using mysql_fetch_row
(). As the program must interpret the row data, it may need to work with
as many as five (5) rows as a time.
I'm chasing down a memory corruption problem with this program, that
seems to overwrite the stored row data, and I want to eliminate my use
(or possibly, misuse) of the C api as the source.

OK, here's the thing. While performing some subsequent processing on the
returned resultset (call this the "primary" results), I conditionally
execute a second query against a history table, using values from the
"primary" results row. I then INSERT selected values from "primary"
results, along with the results of that secondary query, into a temporary
table. Once I've processed all the "primary" results, I then run a query
against this temporary table, and generate a report from /those/ results.

Ok. I've done similar. It works. With caveats

Post by Lew Pitcher
Somewhere in this process, I sometimes run into a "segmentation
violation" (a Unix SIGSEGV), which generally indicates that I've tried to
access memory that I have no access to (outside of my address space, not
mapped, etc.). Initial debugging showed that the values in the "primary"
resultset had changed, in ways that would cause a SIGSEGV, hence my
original question regarding the stability of the resultset.
Further debugging traced my problem to the history table query. When I
designed the table, I knew that it was possible that a query would
retrieve NO rows from the table. I /thought/ that my program logic
handled that condition. I was wrong. :-(
So, I've remedied that oversight, and only process that secondary query's
returned row if the mysql_num_rows() is greater than zero. When I /did
not/ do this, sometimes the query would return an empty resultset, and
I'd grab a data-item pointer that wasn't initialized, and off I'd go into
SIGSEGV territory. Now, with the mysql_num_rows() test guarding the data,
I only grab a data-item pointer when I actually have one.
Thanks to all who made suggestions. You helped me see my code in a new
light and discover my oversight.

As I said C wont wipe your botty, But it will do exactly what you tell
it to!