5

I am using prepared statements with the MYSQL C API and I am having trouble with truncation. mysql_stmt_fetch() always returns MYSQL_DATA_TRUNCATED and I don't understand why. Can someone give me reasons why the data would be truncated to help me troubleshoot this problem? The only reason I can think of is if my buffer variables were not of the right type, but I think they are the correct type. If you are interested, the code I am using is below. I also have shown a copy of my table and its structure, which I took directly from commands in the MYSQL client.

Some Notes about the code:

I have modified it to print out the values even when it returns truncated data. When I do so, the first field seems to print correctly, but the float field and datetime field are garbled. In addition, it seems to read the correct number of rows.

A majority of the code is error checking the various function calls. None of these error checks are triggered - or at least none of them print out messages as you would expect. The only indication of error is the MYSQL_DATA_TRUNCATED value returned by the fetch call.

Describe Measurements Table:

+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| nodeId     | int(11)  | NO   | PRI | NULL    |       |
| recordtime | datetime | NO   | PRI | NULL    |       |
| slevel     | float    | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+

Current Contents of Measurements Table:

+--------+---------------------+--------+
| nodeId | recordtime          | slevel |
+--------+---------------------+--------+
|      1 | 2015-09-22 19:33:50 |      2 |
|      1 | 2015-09-24 21:10:20 |      2 |
|      2 | 2015-09-22 19:33:53 |      5 |
|      3 | 2015-09-22 19:33:55 |      2 |
|      3 | 2015-09-22 19:45:42 |      4 |
|      3 | 2015-09-24 21:12:12 |      2 |
|      3 | 2015-09-24 21:13:30 |    3.4 |
|      4 | 2015-09-22 19:33:57 |      7 |
|      4 | 2015-09-24 21:05:53 |      5 |
|      4 | 2015-09-24 21:07:27 |      3 |
|      4 | 2015-09-24 21:22:52 |    9.9 |
|      4 | 2015-09-24 21:35:53 |      5 |
|      6 | 2015-09-24 21:26:01 |    2.2 |
|      6 | 2015-09-24 21:28:15 |    5.4 |
+--------+---------------------+--------+
14 rows in set (0.00 sec)

My Code:

static void select_rows (MYSQL_STMT *stmt){
char          *stmt_str = "SELECT nodeId, recordtime, slevel FROM Measurements";
MYSQL_BIND    param[3];
int           my_int;
float         my_float;
MYSQL_TIME    my_datetime;
my_bool       is_null[3];

  if (mysql_stmt_prepare (stmt, stmt_str, strlen (stmt_str)) != 0){
    print_stmt_error (stmt, "Could not prepare SELECT statement");
    return;}

  if (mysql_stmt_field_count (stmt) != 3){
    print_stmt_error (stmt, "Unexpected column count from SELECT");
    return;}

  memset ((void *) param, 0, sizeof (param)); /* zero the structures */

  /* set up INT parameter */

  param[0].buffer_type = MYSQL_TYPE_LONG;
  param[0].buffer = (void *) &my_int;
  param[0].is_unsigned = 0;
  param[0].is_null = &is_null[0];

  /* set up FLOAT parameter */

  param[1].buffer_type = MYSQL_TYPE_FLOAT;
  param[1].buffer = (void *) &my_float;
  param[1].buffer = (void *) &my_float;
  param[1].is_null = &is_null[1];

  /* set up DATETIME parameter */

  param[2].buffer_type = MYSQL_TYPE_DATETIME;
  param[2].buffer = (void *) &my_datetime;
  param[2].is_null = &is_null[2];

  if (mysql_stmt_bind_result (stmt, param) != 0){
    print_stmt_error (stmt, "Could not bind parameters for SELECT");
    return;}

  if (mysql_stmt_execute (stmt) != 0){
    print_stmt_error (stmt, "Could not execute SELECT");
    return;}

  if (mysql_stmt_store_result (stmt) != 0){
    print_stmt_error (stmt, "Could not buffer result set");
    return;}
  else{
    printf ("Number of rows retrieved: %lu\n",
            (unsigned long) mysql_stmt_num_rows (stmt));}

  int ii = mysql_stmt_fetch (stmt);
  while (ii == 0 || ii==MYSQL_DATA_TRUNCATED)  /* fetch each row */
{
    /* display row values */
    printf ("%d  ", my_int);
    printf ("%.2f  ", my_float);
    printf ("%04d-%02d-%02d %02d:%02d:%02d\n",
            my_datetime.year,
            my_datetime.month,
            my_datetime.day,
            my_datetime.hour,
            my_datetime.minute,
            my_datetime.second);
  ii=mysql_stmt_fetch (stmt);
  }
  mysql_stmt_free_result (stmt);      /* deallocate result set */
}
BenP1192
  • 221
  • 1
  • 6

1 Answers1

7

This turned out to be a frustratingly easy mistake. The order of my MYSQL_BIND structures was incorrect and there is no error returned by mysql_stmt_bind_param() to indicate a datatype mismatch. You can see that the float is in field 3 in my database but second in my param[] array. Switching the order of the MYSQL_BIND structures solves my issue.

I post this solution rather than deleting this question because very little information can be found on MYSQL truncation errors elsewhere online and others might be making this subtle, albeit simple mistake.

BenP1192
  • 221
  • 1
  • 6