Bug #108006 recipe for BLOBs and mysql_stmt_fetch(), using mysql_stmt_fetch_column() broken
Submitted: 28 Jul 2022 3:57 Modified: 3 Aug 2022 2:31
Reporter: Danny Holstein Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:8.0.28 OS:SUSE
Assigned to: CPU Architecture:Any

[28 Jul 2022 3:57] Danny Holstein
Description:
The code snippet called out in https://dev.mysql.com/doc/c-api/8.0/en/mysql-stmt-fetch.html works when retrieving one unbound column.

When I bind fixed length numeric columns and leave the BLOB column unbound, mysql_stmt_fetch_column() retrieves no data for what was the unbound columns.

I'd also tried to "This can be used for fetching the data value in pieces.", per the documentation, but using the "offset" parameter yields no additional data.

How to repeat:
Start with the code snippet that uses mysql_stmt_fetch_column() for BLOBs, bind other columns.

Iteratively use mysql_stmt_fetch_column() with a bound column.

Suggested fix:
Implement the "offset" parameter in mysql_stmt_fetch_column().

I looked at the source for libmysql.cc and found these lines (2818):

  char *start = value + param->offset;
  char *end = value + length;
I suspect it should be:

  char *start = value + param->offset;
  char *end = start + length;

Though when I tricked it to continue and bring in the rest of the data, the algorithm still didn't retrieve more data.
[28 Jul 2022 12:05] MySQL Verification Team
Hi Mr. Holsten,

Thank you for your bug report.

However, you have not supplied us with necessary information .....

First of all, that part of code is with us from 5.1 onwards and practically had no bugs reported.

Hence, the problem is most likely in your code. Precisely in the use of prepared statements.

You can check this by using SQL statements for PREPARE etc ...... If it works, then the error is in your code.

If it does not work, send us a full test case with SQL statements like PREPARE, EXECUTE, USING etc ...
[28 Jul 2022 13:07] Danny Holstein
Okay, I will generate a test case.

I see you hadn't read what I wrote, no where do I mention "prepared statements".  I refer to the code called out here (in https://dev.mysql.com/doc/c-api/8.0/en/mysql-stmt-fetch.html):

    real_length= 0;

    bind[0].buffer= 0;
    bind[0].buffer_length= 0;
    bind[0].length= &real_length
    mysql_stmt_bind_result(stmt, bind);

    mysql_stmt_fetch(stmt);
    if (real_length > 0)
    {
    data= malloc(real_length);
    bind[0].buffer= data;
    bind[0].buffer_length= real_length;
    mysql_stmt_fetch_column(stmt, bind, 0, 0); // <-doesn't work if another column is added to the SELECT and is bound
    }

My example is embedded in over 1000 lines of C++.  Until I figured out a work around, the above code was the template I followed (and wasted a few weeks figuring out the problem is in the client/documentation).  It'll take a day to get you the example.

I find it odd that a company such as Oracle/MySQL would claim "bug-free" just because no one has reported any.  I'd like to see your test suite proving how this function (mysql_stmt_fetch_column) was tested.

I haven't found any examples of how to use "mysql_stmt_fetch_column()" other than this one, and no example of it being used with "offset" set to other than 0, which I believe is reason enough to modify/extend the documentation.
[28 Jul 2022 13:33] MySQL Verification Team
Hi Mr. Holstein,

We are patiently waiting on your feedback.
[3 Aug 2022 2:26] Danny Holstein
demo code

Attachment: BugChck.cpp (text/x-c++src), 1.46 KiB.

[3 Aug 2022 2:26] Danny Holstein
demo DB

Attachment: dump.csv (text/csv), 49.95 KiB.

[3 Aug 2022 2:29] Danny Holstein
When the bind buffer and buffer_length are set to zero, the bind length never collects the actual length of the field.
[3 Aug 2022 2:31] Danny Holstein
NOTE:  I compiled with:
g++ --std=c++17 -g -m32 BugChck.cpp /home/danny/src/mysql-connector-c-6.1.11-linux-glibc2.12-i686/lib/libmysqlclient.a -ldl -pthread -o BugChck
[3 Aug 2022 12:30] MySQL Verification Team
Hi Mr. Holstein,

Thank you for your test case. 

First of all, you did not send us a test case that we have asked for.

Second, your program is flawed and we can not accept it ......

Please look at the directory testclients/ in our source code tree and inspect all files there, but especially the file named mysql_client_test.cc. You will notice that you made lots of errors in your small test case.

Can't repeat.