Bug #16118 mysql_next_result always returns 0 after a call to mysql_use_result.

• Submitted: 2 Jan 2006 0:06
• Modified: 2 Jan 2006 22:43
• Reporter: Hasani Blackwell
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.18
• OS: Windows (Windows 2000 SP4)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[2 Jan 2006 0:06] Hasani Blackwell

Description:
When executing multiple queries, mysql_next_result always returns 0 when mysql_use_result is used to retrieve the result set. If mysql_store_result is used, mysql_next_result will function as expected.

NOTE: The server is v5.0.18, but the client is compiled against the headers and libraries shipped with v4.1.13a. At runtime, the client is also using the libmysql.dll shipped with v4.1.13a.

How to repeat:
#include "stdafx.h"
#include <stdio.h>
#include <winsock.h>
#include <mysql.h>

int main(int argc, char* argv[])
{
	MYSQL handle;
	mysql_init(&handle);
	
	if(&handle == NULL)
		return -1;
	
	if(!mysql_real_connect(&handle,
		"127.0.0.1",
		"root",
		argv[1],
		"mysql",
		0,
		NULL,
		CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS))
	{
		mysql_close(&handle);
		return -1;
	}

	const char* multiSQL = "DROP TABLE IF EXISTS test_table;"
                   "CREATE TABLE test_table(id INT);"
                   "INSERT INTO test_table VALUES(10);"
                   "UPDATE test_table SET id=20 WHERE id=10;"
                   "SELECT * FROM test_table;"
                   "INSER INTO non_existent_table VALUES(11);";
	if(mysql_real_query(&handle, multiSQL, (unsigned long) strlen(multiSQL)))
	{
		mysql_close(&handle);
		return -1;
	}
	
	MYSQL_RES* result = NULL;
	result=mysql_use_result(&handle); //Doesn't work... says only 1 statement was executed.
	//result = mysql_store_result(&handle); //Works... says three statements were executed.
	if(mysql_field_count(&handle) > 0)
		printf("OK\n");
	if(result != NULL)
		mysql_free_result(result);
	while(true)
	{
		int res = mysql_next_result(&handle);
		if(res != 0)
		{
			mysql_free_result(result);
			mysql_close(&handle);
			return 0;
		}
		if(mysql_field_count(&handle) > 0)
			printf("OK\n");
		result = mysql_use_result(&handle); //Doesn't work... says only 1 statement was executed.
		//result = mysql_store_result(&handle); //Works... says three statements were executed.
		if(result != NULL)
		mysql_free_result(result);
	}
	mysql_close(&handle);
	return 0;
}

[2 Jan 2006 0:07] Hasani Blackwell

reproduceable c code

Attachment: main.cpp (text/plain), 1.64 KiB.

[2 Jan 2006 22:43] MySQL Verification Team

Please read:

http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html

" mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result() does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row(). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result(). The client allocates memory only for the current row and a communication buffer that may grow up to max_allowed_packet  bytes.

On the other hand, you shouldn't use mysql_use_result() if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This ties up the server and prevent other threads from updating any tables from which the data is being fetched.

When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows are returned as part of the result set for your next query. The C API gives the error Commands out of sync; you can't run this command now if you forget to do this! "