Bug #77530 mysql_stmt_fetch_column() function not working as documented
Submitted: 29 Jun 2015 6:34 Modified: 20 Nov 2016 12:52
Reporter: Leo Carreon Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S1 (Critical)
Version:5.6.24 OS:Fedora
Assigned to: Assigned Account CPU Architecture:Any
Tags: mysql_stmt_fetch_column

[29 Jun 2015 6:34] Leo Carreon
Description:
Hi,

I'm developing some MySQL-related libraries on Fedora 22 Workstation.

My database server running on its own machine uses the following packages:

community-mysql.x86_64           5.6.24-3.fc22
community-mysql-common.x86_64    5.6.24-3.fc22
community-mysql-errmsg.x86_64    5.6.24-3.fc22
community-mysql-server.x86_64    5.6.24-3.fc22

My database client running on its own machine too uses the following packages:

community-mysql.x86_64           5.6.24-3.fc22
community-mysql-common.x86_64    5.6.24-3.fc22
community-mysql-devel.x86_64     5.6.24-3.fc22
community-mysql-libs.x86_64      5.6.24-3.fc22

The problem I'm encountering is in the use of the mysql_stmt_fetch_column() function.  

According to its documentation, this function should be able to fetch the value of a column 

in pieces.  Therefore, one of my library test cases is to make sure that the fetching of a 

column by chunks works correctly.  My test table contains two rows.  The value of the TEXT 

column in the first row is "The quick brown fox jumps over the lazy dog near the bank of the 

river." and the second row contains "ODBC C data types indicate the data type of C buffers 

used to store data in the application.".  When the mysql_stmt_fetch_column() function fetches 

the value of the TEXT column, the first chunk is OK but the succeeding chunks have the 

incorrect values.

I have also verified that if I fetch the whole column in one go, the resulting value is OK.

However, fetching in chunks is still desired because if the column so happens to be in the 

megabytes or even gigabytes in size, fetching in one go is not effective use of memory.

I'm supplying a test program (testprog.cpp) in how to repeat which illustrates this problem.

The test program was compiled using the following command line:

g++ -L/usr/lib64/mysql -lmysqlclient -m64 -o testprog -std=c++14 testprog.cpp

This is the contents of the output file (testprog.txt) of the test program:

File: testprog.txt
0000 : 54 68 65 20 71 75 69 63-6B 20 62 72 6F 77 6E 20 : The quick brown 
0010 : D8 CE 3E 7C FF 7F 00 00-D7 CE 3E 7C FF 7F 00 00 : ..>|......>|....
0020 : E0 CE 3E 7C FF 7F 00 00-54 CF 3E 7C FF 7F 00 00 : ..>|....T.>|....
0030 : 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 : ................
0040 : 00 00 00 00 00 00 00   -                        : .......         

Note from the above hex dump that the first 16 bytes are correct but the rest are not.

Regards,
Leo

How to repeat:
Compile and run this test program (testprog.cpp):

NOTE:  Replace the parameters of mysql_real_connect() function according to your test environment.

#include <cassert>
#include <cstring>
#include <fstream>
#include <mysql/mysql.h>

int main()
{
	auto res1 = mysql_library_init(0, nullptr, nullptr);
	assert(res1 == 0);

	MYSQL mysql;
	mysql_init(&mysql);

	auto res2 = mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8");
	assert(res2 == 0);

	auto res3 = mysql_real_connect(&mysql, "host/ip", "user", "password", "database", 0, nullptr, 0);
	assert(res3 != nullptr);

	auto mysql_stmt = mysql_stmt_init(&mysql);
	assert(mysql_stmt != nullptr);

	auto res4 = mysql_stmt_prepare(mysql_stmt, "SELECT aclob FROM testtbl LIMIT 1", 25);
	assert(res4 == 0);

	auto res5 = mysql_stmt_execute(mysql_stmt);
	assert(res5 == 0);

	MYSQL_BIND mysql_bind1[1];
	char buffer1[1];
	unsigned long length1;
	my_bool is_null1;
	std::memset(mysql_bind1, 0, sizeof(mysql_bind1));
	mysql_bind1[0].buffer_type = MYSQL_TYPE_STRING;
	mysql_bind1[0].buffer = buffer1;
	mysql_bind1[0].buffer_length = 0;
	mysql_bind1[0].length = &length1;
	mysql_bind1[0].is_null = &is_null1;
	mysql_bind1[0].is_unsigned = 0;
	mysql_bind1[0].error = nullptr;

	auto res6 = mysql_stmt_bind_result(mysql_stmt, mysql_bind1);
	assert(res6 == 0);

	auto res7 = mysql_stmt_fetch(mysql_stmt);
	assert(res7 == MYSQL_DATA_TRUNCATED);

	std::fstream file("testprog.txt", std::ios_base::out|std::ios_base::trunc|std::ios_base::binary);
	assert(file.is_open());

	MYSQL_BIND mysql_bind2[1];
	char buffer2[16];
	unsigned long length2;
	my_bool is_null2;
	std::memset(mysql_bind2, 0, sizeof(mysql_bind2));
	mysql_bind2[0].buffer_type = MYSQL_TYPE_STRING;
	mysql_bind2[0].buffer = buffer2;
	mysql_bind2[0].buffer_length = sizeof(buffer2);
	mysql_bind2[0].length = &length2;
	mysql_bind2[0].is_null = &is_null2;
	mysql_bind2[0].is_unsigned = 0;
	mysql_bind2[0].error = nullptr;
	for (unsigned long offset = 0; offset < length1; offset += length2)
	{
		auto res8 = mysql_stmt_fetch_column(mysql_stmt, mysql_bind2, 0, offset);
		assert(res8 == 0);

		file.write(buffer2, length2);
		assert(!file.fail());
	}

	file.close();

	mysql_stmt_free_result(mysql_stmt);

	mysql_stmt_close(mysql_stmt);

	mysql_close(&mysql);

	mysql_library_end();

	return 0;
}

Suggested fix:
Fix mysql_stmt_fetch_column() function such that the correct data is transferred to the receiving buffer on succeeding calls to the function.
[30 Jun 2015 3:05] Leo Carreon
Upon further investigation, I have discovered that the value of length in the MYSQL_BIND structure used with the mysql_stmt_fetch_column() function is being set to the total length of the column instead of the number of bytes stored in the buffer.

Because my assumption of the value of this length is incorrect, the logic of my test program is incorrect.  This meant I have to figure out the number of bytes stored in the buffer instead of being told how many where stored.

It would have been much better if the length field returned the number of bytes stored in the buffer because I already know the total size of the column based on the values returned by the mysql_stmt_fetch() function call.
[20 Oct 2016 12:52] Chiranjeevi Battula
Hello  Leo Carreon,

Thank you for the bug report.
I tried with latest version of MySQL C API versions as well and it worked without any issues.
Could you please provide repeatable test case (sample project, code, create table statements/database etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[21 Nov 2016 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".