Bug #78490 MySQL Prepared Statements Sometimes return invalid number of rows (Critical)

• Submitted: 20 Sep 2015 21:06
• Modified: 20 Sep 2015 22:39
• Reporter: Kevin B
• Status: Not a Bug
• Category: MySQL Server: C API (client library)
• Severity: S1 (Critical)
• Version: 6.1.6
• OS: Windows
• CPU Architecture: Any

[20 Sep 2015 21:06] Kevin B

Description:
MySQL STMT will sometimes return 1 row sometimes for the following query. It should return 2 rows. Sometimes it returns 2, sometimes 1.

Code:
--------------
std::vector<holdplex::string> list_of_services;

list_of_services.push_back("test");
list_of_services.push_back("test2");

std::string select_service_sql = "SELECT * FROM services WHERE name_id=? OR name_id=?";

...(STMT Functions)
--------------

(First Parameter = 'test')
(Second Parameter = 'test2')

How to repeat:
MYSQL* mysql = mysql_init(NULL);

try
{

if(mysql == NULL)
{
     throw (int)1;
}

if(mysql_real_connect(mysql, DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, "databasename", 0, NULL, 0) == NULL)
{
	throw (int)2;
}

MYSQL_STMT* stmt = mysql_stmt_init(mysql);

if(stmt == NULL)
{
	throw (int)3;
}

unsigned long pref = std::numeric_limits<unsigned long>::max();
mysql_stmt_attr_set(stmt, STMT_ATTR_PREFETCH_ROWS, (void*)&pref);

std::vector<holdplex::string> list_of_services;
list_of_services.push_back("test");
list_of_services.push_back("test2");

std::string select_service_sql = "SELECT * FROM services WHERE name_id=?";
for(size_t i = 1; i < list_of_services.size(); i++)
{
	select_service_sql += " OR name_id=?";
}

mysql_stmt_prepare(stmt, select_service_sql.c_str(), select_service_sql.size());

std::vector<MYSQL_BIND> param;
param.resize(list_of_services.size());
memset(&param[0], 0, sizeof(param[0]) * param.size());

for(size_t i = 0; i < list_of_services.size(); i++)
{
	std::string str_key = list_of_services[i].conststring();
	param[i].buffer_type = MYSQL_TYPE_STRING;
	param[i].buffer = (char*)str_key.c_str();
	param[i].buffer_length = str_key.size();
	param[i].is_null = 0;
	param[i].length = &param[i].buffer_length;
}

mysql_stmt_bind_param(stmt, &param[0]);

MYSQL_RES* result = mysql_stmt_result_metadata(stmt);
if (result == NULL) 
{
   throw (int)0;   
}

size_t fieldnum = mysql_num_fields(result);

std::vector<MYSQL_FIELD*> field;
field.resize(fieldnum);
memset(&field[0], 0, sizeof(field[0]) * field.size());

for(size_t i = 0; i < fieldnum; i++)
{
	field[i] = mysql_fetch_field(result);
}

if(mysql_stmt_execute(stmt))
{
	throw (int)0;
}

param.resize(fieldnum);
memset(&param[0], 0, sizeof(param[0]) * param.size());

for(size_t i = 0; i < fieldnum; i++)
{
	param[i].buffer_type = MYSQL_TYPE_STRING;
	param[i].buffer = new char[field[i]->length];
	param[i].buffer_length = field[i]->length;
	param[i].is_null = new my_bool(0);
	param[i].length = new unsigned long(0);
	param[i].error = new my_bool(0);
}

if(mysql_stmt_bind_result(stmt, &param[0]))
{
	for(size_t i = 0; i < fieldnum; i++)
	{
		delete[] param[i].buffer;
		delete param[i].is_null;
		delete param[i].length;
		delete param[i].error;
	}
	throw (int)0;
}

if (mysql_stmt_store_result(stmt))
{
	for(size_t i = 0; i < fieldnum; i++)
	{
		delete[] param[i].buffer;
		delete param[i].is_null;
		delete param[i].length;
		delete param[i].error;
	}
	throw (int)0;
}

size_t totalrows = 0;
while(!mysql_stmt_fetch(stmt))
{
  totalrows++;
}

for(size_t i = 0; i < fieldnum; i++)
{
	delete[] param[i].buffer;
	delete param[i].is_null;
	delete param[i].length;
	delete param[i].error;
}

mysql_free_result(result);
mysql_stmt_close(stmt);
mysql_close(mysql);

std::cout<<totalrows<<std::endl;

}
catch(int a)
{
	if(a == 404)
	{
	        //404
		return 0;
	}
	//Internal Error
	return 0;
}

[20 Sep 2015 22:39] Kevin B

It was a programming mistake. Not a bug in the mysql c api library.