Bug #22001 Prepared Statements unusable with unbuffered queries, mysql_stmt_refetch needed

• Submitted: 4 Sep 2006 16:07
• Reporter: Domas Mituzas
• Status: Verified
• Category: MySQL Server: Prepared statements
• Severity: S4 (Feature request)
• Version: 5.0, 5.1, etc
• CPU Architecture: Any

[4 Sep 2006 16:07] Domas Mituzas

Description:
When using PS without buffering the result set, the server does not report the exact maximal length of a column but gives a hint about the value. However, this hint is not usable when not buffering because when the data does not fit into the buffer libmysql will report truncation error, but even if one resizes the buffer, because at this time the real length can be obtained from the BIND structure, it's impossible.

That's because during the next mysql_stmt_fetch() call new data will be fetched from the socket and the old data will be gone. For, this reason one does need a function like mysql_stmt_refetch() to be able to retry the fetch

This is especially important when implementing connectors for dynamic languages (PHP, Perl, etc), where users do not preallocate any buffers. 

How to repeat:
-

Suggested fix:
-

[4 Sep 2006 16:51] Andrey Hristov

I propose adding a function - mysql_stmt_refetch
===== libmysql.c 1.258 vs edited =====
--- 1.258/libmysql/libmysql.c   2006-09-04 18:49:29 +02:00
+++ edited/libmysql.c   2006-09-04 18:18:10 +02:00
@@ -4556,6 +4556,36 @@ int STDCALL mysql_stmt_fetch(MYSQL_STMT
   }
   else
   {
+    stmt->last_row= row;
+    /* This is to know in mysql_stmt_fetch_column that data was fetched */
+    stmt->state= MYSQL_STMT_FETCH_DONE;
+  }
+  DBUG_RETURN(rc);
+}
+
+
+/*
+  Fetch and return row data to bound buffers, if any
+*/
+
+int STDCALL mysql_stmt_refetch(MYSQL_STMT *stmt)
+{
+  int rc;
+  uchar *row;
+  DBUG_ENTER("mysql_stmt_refetch");
+
+  if (!stmt->last_row)
+    DBUG_RETURN(0);
+
+  if (((rc= stmt_fetch_row(stmt, stmt->last_row)) &&
+       rc != MYSQL_DATA_TRUNCATED))
+  {
+    stmt->state= MYSQL_STMT_PREPARE_DONE;       /* XXX: this is buggy */
+    stmt->read_row_func= (rc == MYSQL_NO_DATA) ?
+      stmt_read_row_no_data : stmt_read_row_no_result_set;
+  }
+  else
+  {
     /* This is to know in mysql_stmt_fetch_column that data was fetched */
     stmt->state= MYSQL_STMT_FETCH_DONE;
   }
===== mysql.h 1.164 vs edited =====
--- 1.164/include/mysql.h       2006-09-04 18:50:11 +02:00
+++ edited/mysql.h      2006-09-04 15:54:58 +02:00
@@ -718,6 +718,7 @@ typedef struct st_mysql_stmt
     metadata fields when doing mysql_stmt_store_result.
   */
   my_bool       update_max_length;
+  unsigned char *last_row;
 } MYSQL_STMT;

 enum enum_stmt_attr_type
@@ -780,6 +781,7 @@ int STDCALL mysql_stmt_prepare(MYSQL_STM
                                unsigned long length);
 int STDCALL mysql_stmt_execute(MYSQL_STMT *stmt);
 int STDCALL mysql_stmt_fetch(MYSQL_STMT *stmt);
+int STDCALL mysql_stmt_refetch(MYSQL_STMT *stmt);
 int STDCALL mysql_stmt_fetch_column(MYSQL_STMT *stmt, MYSQL_BIND *bind,
                                     unsigned int column,
                                     unsigned long offset);

[4 Sep 2006 16:54] Andrey Hristov

After the call to mysql_stmt_fetch() which had failed with truncation error, the length of the column has been updated in the BIND structure. Hence, one can realloc the buffer and call mysql_stmt_refetch() like:
	if (ret == MYSQL_DATA_TRUNCATED) {
		for (i = 0; i < stmt->result.var_cnt; i++) {
			if (stmt->stmt->bind[i].error && stmt->stmt->bind[i].buffer_length) {
				stmt->result.buf[i].val= erealloc(stmt->result.buf[i].val, stmt->result.buf[i].buflen);
			}
		}

		if (MYSQL_DATA_TRUNCATED == mysql_stmt_refetch(stmt->stmt)) {
			php_error_docref(NULL TSRMLS_CC, E_ERROR, "Serious extension error. Please contact the developers");
			RETURN_FALSE;
		}
	}