Bug #33086 mysql_stmt_fetch_column does not update bind buffer on blob data
Submitted: 8 Dec 2007 15:25 Modified: 26 Jun 2009 16:06
Reporter: John Millaway Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S2 (Serious)
Version:5.0.51 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysql_stmt_fetch_column MYSQL_DATA_TRUNCATED MYSQL_TYPE_BLOB

[8 Dec 2007 15:25] John Millaway
Description:
The goal is to fetch rows containing blob columns without knowing the maximum length of the blobs, and without buffering the entire result set on the client. (Imagine that each single row fits in memory or disk on the client, but not all rows.)

The online documentation for mysql_stmt_fetch() says there are two options. Option #1 is to buffer all the results on the client by calling mysql_stmt_store_results(). This obviously won't work for many large blobs. Option #2 says that we can bind a zero-length buffer to the blob column, and expect mysql_stmt_fetch() to fail with an error of MYSQL_DATA_TRUNCATED. The actual `length` will be reported to us. Using this, we can allocate a buffer of the correct size and call mysql_stmt_fetch_column() to fetch the complete data. 

The bug is that mysql_stmt_fetch_column() reports success, but does not update the bind `buffer` data. There is no way to fetch blobs without buffering all blobs.

How to repeat:
The following code attempts to fetch a blob column using the strategy from the docs. Everything works as expected, right down to the final assert(), which fails -- showing that the buffer was not updated with the blob data. 

void example (MYSQL* m)
{
    MYSQL_STMT* stmt;
    int rc;
    const char* sql;
    MYSQL_BIND  bind[2];
    int  int_data=0;
    unsigned long blob_length =0;
    my_bool blob_error = 0;

    /* Create a table with a blob column and insert a single row.
     */
    sql = "DROP TABLE IF EXISTS example";
    rc = mysql_real_query (m, sql, strlen(sql));
    assert(rc==0);

    sql = "CREATE TABLE example (int_data int primary key, blob_data TEXT) ENGINE=MyISAM";
    rc = mysql_real_query (m, sql, strlen(sql));
    assert(rc==0);

    sql = "INSERT INTO example (int_data,blob_data) values (77,'xxxx')";
    rc = mysql_real_query (m, sql, strlen(sql));
    assert(rc==0);
    
    stmt = mysql_stmt_init (m);
    assert(stmt);

    /* Try to fetch the blob without knowing its length in advance,
     * and without storing all rows on the client.
     */
    sql = "SELECT int_data, blob_data FROM example";
    rc = mysql_stmt_prepare (stmt, sql, strlen(sql));
    assert(rc == 0);

    rc = mysql_stmt_execute (stmt);
    assert(rc==0);

    memset (bind, 0, sizeof bind);
    bind[0].buffer_type = MYSQL_TYPE_LONG;
    bind[0].buffer = &int_data;
    bind[0].buffer_length = sizeof int_data;

    bind[1].buffer_type = MYSQL_TYPE_BLOB;
    bind[1].buffer = NULL;
    bind[1].buffer_length =  0;
    bind[1].length =  & blob_length;
    bind[1].error =  & blob_error;

    rc = mysql_stmt_bind_result (stmt, bind);
    assert(rc==0);

    /* We expect MYSQL_DATA_TRUNCATED since bind[1].buffer_length == 0. */
    rc = mysql_stmt_fetch (stmt);
    assert (rc == MYSQL_DATA_TRUNCATED);

    if (rc == MYSQL_DATA_TRUNCATED){
        char blob_data[4];

        /* Expect error bit to be set, and the actual length to be reported.*/
        assert (blob_error);
        assert (blob_length == 4);
        
        memset (blob_data, 0, sizeof(blob_data));
        bind[1].buffer = blob_data;
        bind[1].buffer_length =  blob_length;

        assert (blob_data[0] == 0);
        assert (bind[1].buffer_length == 4);

        /* re-fetch the column.*/
        rc = mysql_stmt_fetch_column (stmt, bind, 1, 0);
        assert (rc == 0);

        /* Expect blob data to be there.*/
        assert (blob_length == 4);
        assert (blob_data[0] == 'x'); /* FAIL!! blob_data is not set.*/
    }
}

Suggested fix:
Provide a mechanism to fetch blob columns without buffering all rows on the client. That may mean patching mysql_stmt_fetch_column to work as expected, or perhaps by some other mechanism.
[8 Dec 2007 18:10] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45 or 5.0.51, and inform about the results.
[8 Dec 2007 21:38] John Millaway
Fails on 5.0.51.
[1 Feb 2008 13:54] Stefan Bähring
Hello, I discover the same problem. Is there any progress? Is it really a bug?
[1 Feb 2008 18:17] John Millaway
Yes, this is a serious bug. It's MySQL's dirty little secret: MySQL can't handle BLOBs in a scalable manner. If you look into the PHP source code, you'll see that PHP does the worst of all possible things: calls mysql_store_results(). The take-away message is: don't use BLOBs in MySQL unless you can fit the entire result set in memory (possibly multiple times over, allowing for any copies that your platform layer introduces.)
[1 May 2008 0:47] Sovande Ulv
I got the same bug in version 5.1.22 on OS X for both MYSQL_TYPE_STRING and MYSQL_TYPE_BLOB. Glad to hear if someone has a workaround other than storing the full result set on the client which just isn't feasible if one want this to scale.
[3 May 2008 18:15] Sovande Ulv
Actually the example program has a bug. Instead of 

        rc = mysql_stmt_fetch_column (stmt, bind, 1, 0);

it should say

        rc = mysql_stmt_fetch_column (stmt, &bind[1], 1, 0);

Though there is still some "funky" problems with mysql_stmt_fetch_column() but that is for another bug report
[26 Jun 2009 16:04] Valeriy Kravchuk
Complete test case that proves bug is NOT repeatable in 5.0.84

Attachment: bug33086.c (text/plain), 2.68 KiB.

[26 Jun 2009 16:06] Valeriy Kravchuk
With complete (and fixed, based on last comment) test case uploaded as a separate file I've got with 5.0.84 on Mac OS X:

valeriy-kravchuks-macbook-pro:5.0 openxs$ gcc -o bug33086 `$CFG --cflags` -DDEBUG bug33086.c `$CFG --libs`
bug33086.c: In function 'example':
bug33086.c:20: warning: incompatible implicit declaration of built-in function 'strlen'
bug33086.c:44: warning: incompatible implicit declaration of built-in function 'memset'
valeriy-kravchuks-macbook-pro:5.0 openxs$ ./bug33086
Server: 5.0.84, client: 5.0.84

Blob length: 4, data[0]: x

So, no assertion failures, length is correct and data were really fetched. Everything works as expected.