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

8 views
Skip to first unread message

Lew Pitcher

unread,
Feb 11, 2021, 11:27:23 AMFeb 11
to
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,
--
Lew Pitcher
"In Skills, We Trust"

Tony Mountifield

unread,
Feb 11, 2021, 11:58:21 AMFeb 11
to
In article <s03lt7$54i$1...@dont-email.me>,
Lew Pitcher <lew.p...@digitalfreehold.ca> wrote:
> 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
--
Tony Mountifield
Work: to...@softins.co.uk - http://www.softins.co.uk
Play: to...@mountifield.org - http://tony.mountifield.org

Lew Pitcher

unread,
Feb 11, 2021, 12:35:27 PMFeb 11
to
On Thu, 11 Feb 2021 16:56:29 +0000, Tony Mountifield wrote:

> In article <s03lt7$54i$1...@dont-email.me>,
> Lew Pitcher <lew.p...@digitalfreehold.ca> wrote:
>> 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.

> 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.


> 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.

> 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 ;-)

> Cheers Tony
[snip my example code]

Thanks

The Natural Philosopher

unread,
Feb 11, 2021, 1:34:38 PMFeb 11
to

> 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 :-(


--
WOKE is an acronym... Without Originality, Knowledge or Education.

Lew Pitcher

unread,
Feb 11, 2021, 2:24:30 PMFeb 11
to
On Thu, 11 Feb 2021 18:34:36 +0000, The Natural Philosopher wrote:

>> 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

unread,
Feb 11, 2021, 4:10:23 PMFeb 11
to
Another possibility is buffer overflow.
C doesn't wipe your bottom for you. plenty of scope for shitty code

--
"I guess a rattlesnake ain't risponsible fer bein' a rattlesnake, but ah
puts mah heel on um jess the same if'n I catches him around mah chillun".

Tony Mountifield

unread,
Feb 11, 2021, 6:53:16 PMFeb 11
to
In article <s03pst$54i$2...@dont-email.me>,
Lew Pitcher <lew.p...@digitalfreehold.ca> wrote:
> > 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.

> 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.

> And, this is just a hobby ;-)

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

Cheers
Tony

Lew Pitcher

unread,
Feb 13, 2021, 6:13:38 PMFeb 13
to
I think I found my problem, and I'm kicking myself for it

On Thu, 11 Feb 2021 16:27:19 +0000, Lew Pitcher wrote:

> 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

unread,
Feb 13, 2021, 10:50:22 PMFeb 13
to
On 13/02/2021 23:13, Lew Pitcher wrote:
> I think I found my problem, and I'm kicking myself for it
>
> On Thu, 11 Feb 2021 16:27:19 +0000, Lew Pitcher wrote:
>
>> 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

> 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!



--
“when things get difficult you just have to lie”

― Jean Claud Jüncker
Reply all
Reply to author
Forward
0 new messages