Bug #86291 MYSQL_DATA_TRUNCATED reported when buffer large enough
Submitted: 12 May 2017 3:38 Modified: 7 Jul 2017 4:46
Reporter: Francois Lamoureux Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:5.7.18 OS:Ubuntu (16.04)
Assigned to: Assigned Account CPU Architecture:Any

[12 May 2017 3:38] Francois Lamoureux
Description:
MYSQL_DATA_TRUNCATED is returned after mysql_stmt_fetch() even though all bind_results parameters have been allocated enough memory.

Output from code below:
-----------------------
allocating 51 for FIELD1
allocating 37 for FIELD2
allocating 46 for FIELD3
allocating 65 for FIELD4
allocating 1 for F5
allocating 8 for F6
allocating 8 for F7
allocating 8 for F8

Data truncated
error: no, length: 8, buffer_length: 51,
error: true, length: 36, buffer_length: 37,
error: no, length: 15, buffer_length: 46,
error: no, length: 9, buffer_length: 65,
error: no, length: 1, buffer_length: 1,
error: no, length: 4, buffer_length: 8,
error: no, length: 4, buffer_length: 8,
error: true, length: 4, buffer_length: 8,

------------------------
We can clearly see above that the two fields where error is set to true (which triggered the MYSQL_DATA_TRUNCATED return code) have been allocated large enough.

How to repeat:
Using a View descibed as such:
===========================================
Field     type           NULL    Default
FIELD1    varchar(50)    NO        
FIELD2    varchar(36)    NO        uuid()
FIELD3    varchar(45)    YES        
FIELD4    varchar(64)    YES        
F5        tinyint(1)     YES        1
F6        int(11)        YES        -1
F7        int(11)        YES        -1
F8        int(11)        YES        -1

And with this single row returned from the view:
=================================================
    FIELD1        FIELD2                                    FIELD3              FIELD4        F5    F6    F8    F9
    aaaaaaaa    cc2361a2-34f4-42b7-8b5c-1a59e9dc423b    aaaaaaaaaaaaaaa        aaaaaaaaa    1    -1    -1    -1

==================================================
Then calling the do_something() method below will reproduce the output from the defect description:
--------------------
apr_hash_t *prepare_bind_results(MYSQL_RES *metadata, MYSQL_BIND bind_result[]) {
    apr_hash_t *ret = apr_hash_make(r->pool);
    MYSQL_FIELD *field;
    
    for(int i = 0; i < mysql_num_fields(metadata); i++) {
        field = mysql_fetch_field_direct(metadata, i);
        bind_result[i].buffer_type = field->type;
        bind_result[i].length = apr_pcalloc(r->pool, sizeof(unsigned long)); 
        *bind_result[i].length = 0;
        
        switch (field->type) {
            case (MYSQL_TYPE_TINY):
                bind_result[i].buffer = (signed char *) apr_pcalloc(r->pool, sizeof(signed char));
                bind_result[i].buffer_length = sizeof(signed char);                
                break;
            case (MYSQL_TYPE_LONG):
                bind_result[i].buffer = (long *) apr_pcalloc(r->pool, sizeof(long));
                bind_result[i].buffer_length = sizeof(long);
                break;
            case (MYSQL_TYPE_LONGLONG):
                bind_result[i].buffer = (long long*) apr_pcalloc(r->pool, sizeof(long long));
                bind_result[i].buffer_length = sizeof(long long);
                break;
            case (MYSQL_TYPE_STRING):
            case (MYSQL_TYPE_VARCHAR):
            case (MYSQL_TYPE_VAR_STRING):
                bind_result[i].buffer = apr_pcalloc(r->pool, sizeof(char) * (field->length + 1));
                bind_result[i].buffer_length = sizeof(char) * (field->length + 1);
                break;
            case (MYSQL_TYPE_TIME):
            case (MYSQL_TYPE_DATE):
            case (MYSQL_TYPE_DATETIME):
            case (MYSQL_TYPE_TIMESTAMP):
                bind_result[i].buffer = (MYSQL_TIME *) apr_pcalloc(r->pool, sizeof(MYSQL_TIME));
                bind_result[i].buffer_length = sizeof(MYSQL_TIME);
                break;
            default:
                ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "Database column type for column [%s] is not handled (enum value: %d)!!! (%s:%d)", field->name, field->type, __FILE__, __LINE__);
        }
        ap_log_rerror(APLOG_MARK, APLOG_TRACE2, 0, r, "allocating %lu for %s", bind_result[i].buffer_length, field->name );
        
        bind_result[i].is_null = apr_pcalloc(r->pool, sizeof(my_bool));
        bind_result[i].error = apr_pcalloc(r->pool, sizeof(my_bool));
        apr_hash_set(ret, apr_pstrdup(r->pool, field->name), APR_HASH_KEY_STRING, &bind_result[i]);
    }
    
    return ret;
}

bool do_something() {
    bool ret = false;
    MYSQL_STMT *stmt = NULL;
    MYSQL_BIND bind_select[1];
    MYSQL_RES *metadata = NULL;
    char *sql = apr_pstrdup(r->pool, "select * from my_view where FIELD1 = ?");

    memset(bind_select, 0, sizeof(bind_select));

    bind_select[0].buffer_type= MYSQL_TYPE_STRING;
    bind_select[0].buffer= (char *) "aaaaaaaa";
    bind_select[0].buffer_length=strlen("aaaaaaaa");

    ap_log_rerror(APLOG_MARK, APLOG_TRACE2, 0, r, "SQL: %s", sql);
    if (!(stmt = mysql_stmt_init(&mysql))) {
        ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "mysql_stmt_init(), out of memory");
    }else if (mysql_stmt_prepare(stmt, sql, strlen(sql))) {
        ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "mysql_stmt_prepare() failed with %s", mysql_stmt_error(stmt));    
    }else if (mysql_stmt_bind_param(stmt, bind_select)) {
        ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "mysql_stmt_bind_param() failed with %s", mysql_stmt_error(stmt));
    } else if (!(metadata = mysql_stmt_result_metadata(stmt))){
        ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "mysql_stmt_execute() failed with %s", mysql_stmt_error(stmt));
    } else {
        int field_count = mysql_num_fields(metadata);
        MYSQL_BIND bind_results[field_count];
        apr_hash_t *hash_bind_results = prepare_bind_results(metadata, bind_results);
        int db_ret = 0;
        
        if (mysql_stmt_bind_result(stmt, bind_results)) {
            ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "mysql_stmt_bind_result() failed with %s", mysql_stmt_error(stmt));
        }else if (mysql_stmt_execute(stmt)) {
            ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "mysql_stmt_execute() failed with %s", mysql_stmt_error(stmt));
        } else if (mysql_stmt_store_result(stmt)) { // This will force fetching all rows from the server in one shot, preventing back and forth
            ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "mysql_stmt_store_result() failed with %s", mysql_stmt_error(stmt));
        }else if ((db_ret = mysql_stmt_fetch(stmt)) == 0) {
        
            ret = true;
            // whatever...

        }else if (db_ret == MYSQL_DATA_TRUNCATED) {
            ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "Data truncated!!!");
            for (int x=0; x<field_count; x++) {
                ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "error: %s, length: %lu, buffer_length: %lu, ", 
                        bind_results[x].error_value ? "true" : "no", 
                        (unsigned long) *bind_results[x].length,
                        bind_results[x].buffer_length);
            }
        }else if (db_ret == MYSQL_NO_DATA) {
            ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "No rows returned");
        }else{
            ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "Error: %s", mysql_stmt_error(stmt));
        }
    }
    if (stmt) {
        mysql_stmt_close(stmt);
    }
    return ret;
}
[14 May 2017 20:04] Francois Lamoureux
On a different table I also get the same sort of error. I even went as far as allocating double the column lenght.

Given this FOR statement:
-------
for (int x=0; x<mysql_num_fields(metadata); x++) {
    MYSQL_FIELD *field = mysql_fetch_field_direct(metadata, x);
    ap_log_rerror(APLOG_MARK, APLOG_ERR, 0, r, "  error: %4s, length: %5lu, buffer_length: %5lu, column size: %5lu",
            bind_results[x].error_value ? "true" : "no", 
            (unsigned long) *bind_results[x].length,
            bind_results[x].buffer_length,
            field->length);
}
-------
This is what I got
-------
  error:   no, length:     4, buffer_length:    22, column size:    11
  error:   no, length:    24, buffer_length:    25, column size:    24
  error:   no, length:    44, buffer_length:    45, column size:    44
  error:   no, length:    44, buffer_length: 10001, column size: 10000
  error: true, length:    44, buffer_length:   101, column size:   100
  error: true, length:     4, buffer_length:    22, column size:    11
  error:   no, length:    40, buffer_length:    40, column size:    23
  error: true, length:     0, buffer_length:    37, column size:    36
  error: true, length:     4, buffer_length:    22, column size:    11
  error: true, length:     9, buffer_length:    46, column size:    45
  error: true, length:    28, buffer_length:   257, column size:   256
  error: true, length:    36, buffer_length:    37, column size:    36
  error: true, length:    10, buffer_length:    51, column size:    50
  error: true, length:     3, buffer_length:    46, column size:    45
  error: true, length:     4, buffer_length:    22, column size:    11
  error: true, length:     3, buffer_length:    46, column size:    45
  error: true, length:    10, buffer_length:    16, column size:    15
  error:   no, length:    40, buffer_length:    40, column size:    23
  error:   no, length:    40, buffer_length:    40, column size:    23
  error: true, length:    40, buffer_length:    40, column size:    23
  error: true, length:    40, buffer_length:    40, column size:    23
  error:   no, length:    40, buffer_length:    40, column size:    23
  error: true, length:    40, buffer_length:    40, column size:    23
  error:   no, length:     8, buffer_length:    30, column size:    15
  error:   no, length:     8, buffer_length:    30, column size:    15
  error:   no, length:     8, buffer_length:    30, column size:    15
  error:   no, length:     8, buffer_length:    30, column size:    15
  error:   no, length:     8, buffer_length:    30, column size:    15
  error: true, length:     8, buffer_length:    30, column size:    15
-------
[19 May 2017 11:35] Chiranjeevi Battula
Hello Francois,

Thank you for the bug report.
Could you please provide repeatable test case (exact steps/sample code, create table statement/database details etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[7 Jun 2017 0:25] Francois Lamoureux
Hi, sorry, I haven't forgotten about you nor this bug. I'll see if I can strip down my DB and program to the bare minimum.
[8 Jul 2017 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".