| Bug #59541 | Incorrect results when using SQLBindCol with row binding and indicator variables | ||
|---|---|---|---|
| Submitted: | 17 Jan 2011 4:00 | Modified: | 3 Jan 2013 22:18 |
| Reporter: | amrith kumar | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | mysql 5.1 | OS: | Linux (x86_64) |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
[17 Jan 2011 4:01]
amrith kumar
Results from running the sample program. amrith@amrith-desktop64:~/virtualdb/tests$ ./row-binding sizeof(row) = 16 ROW_ARRAY_SIZE = 500 Binding 0x166dd80, 4, 0x166dd88 nrows = 2 0x166dd80 | 00000006 68676665 00000004 00000000 | 0x166dd90 | 00000014 78777675 00000004 00000000 | 0x166dda0 | 6A696867 6E6D6C6B 7271706F 76757473 | 0x166ddb0 | 7A797877 64636261 68676665 6C6B6A69 | 0x166ddc0 | 706F6E6D 74737271 78777675 62617A79 | 0x166ddd0 | 66656463 6A696867 6E6D6C6B 7271706F | 0x166dde0 | 76757473 7A797877 64636261 68676665 | 0x166ddf0 | 6C6B6A69 706F6E6D 74737271 78777675 | 0x166de00 | 62617A79 66656463 6A696867 6E6D6C6B | 0x166de10 | 7271706F 76757473 7A797877 64636261 | setting rowsize to 12 Binding 0x166dd80, 4, 0x166dd84 nrows = 2 0x166dd80 | 00000006 00000004 00000000 00000004 | // This is the error 0x166dd90 | 00000000 78777675 62617A79 66656463 | // The value for row 2, 0x166dda0 | 6A696867 6E6D6C6B 7271706F 76757473 | // a = 20 is nowhere to be 0x166ddb0 | 7A797877 64636261 68676665 6C6B6A69 | // seen. 0x166ddc0 | 706F6E6D 74737271 78777675 62617A79 | 0x166ddd0 | 66656463 6A696867 6E6D6C6B 7271706F | 0x166dde0 | 76757473 7A797877 64636261 68676665 | 0x166ddf0 | 6C6B6A69 706F6E6D 74737271 78777675 | 0x166de00 | 62617A79 66656463 6A696867 6E6D6C6B | 0x166de10 | 7271706F 76757473 7A797877 64636261 |
[17 Jan 2011 9:45]
Valeriy Kravchuk
What exact Connector/ODBC version do you use?
[17 Jan 2011 13:36]
amrith kumar
Here is the information you requested. If you'd like some other information about the driver, please let me know. Thanks! SQL_DRIVER_VER: 05.01.0006 SQL_DRIVER_ODBC_VER: 03.51 SQL_DRIVER_NAME: libmyodbc5.so SQL_DM_VER: 03.52.0002.0002 SQL_DBMS_VER: 5.1.49-1ubuntu8.1 SQL_DBMS_NAME: MySQL
[20 Jan 2011 7:09]
Bogdan Degtyariov
Hi Amrith, Have you tried MySQL Connector/ODBC 5.1.8? This version has several fixes related to row binding since your current driver release 5.1.6. One more question, which driver manager you are using? Is that UnixODBC, iODBC, DataDirect or something else? Which version? Thanks.
[20 Jan 2011 9:14]
amrith kumar
Bogdan, No, I have not been able to successfully run my test program with 5.1.8. I have installed the driver by downloading mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz, unpacking it, and pointing my .odbc.ini to the new libmyodbc5.so. I am unable to connect to the database (i.e. SQLConnect() fails) and any attempt to get more error information causes SQLGetDiagRec() to fail. So, I'm not sure how to use just the new ODBC driver and not upgrade a whole bunch of other things. If you could help me with that upgrade, I'm happy to retry with 5.1.8. As for DM, I'm using unixODBC and the previous update has the DM Version. -amrith
[20 Jan 2011 11:52]
Bogdan Degtyariov
Amrith, Thanks for answering my questions. Analyzing your last reply I noticed one thing: if SQLConnect() fails the driver (libmyodbc5.so) does not get loaded and therefore the diagnostic call SQLGetDiagRec() is completely handled by the driver manager (UnixODBC). This brings us to the conclusion that SQLGetDiagRec() failure has nothing to do with MySQL ODBC driver. It actually points to the problem between your application and the driver manager. It is important to make sure that 64-bit client application is always loading 64-bit driver manager library (libodbc.so). SQLConnect() could fail if libodbc.so is attempted to be loaded dynamically, but the library contains 32-bit code. Also, .odbc.ini is not the standard configuration file name for UnixODBC. In order to be able to find this file you have to set the environment variable ODBCINI=path_to_odbc_ini/.odbc.ini I hope this will help to establish the connection from MyODBC driver 5.1.8
[20 Jan 2011 14:14]
amrith kumar
Bogdan, I'm trying to understand your reply. You write: "Analyzing your last reply I noticed one thing: if SQLConnect() fails the driver (libmyodbc5.so) does not get loaded and therefore the diagnostic call SQLGetDiagRec() is completely handled by the driver manager (UnixODBC). This brings us to the conclusion that SQLGetDiagRec() failure has nothing to do with MySQL ODBC driver. It actually points to the problem between your application and the driver manager." All I did was change the driver path in my .odbc.ini as below: amrith@amrith-desktop64:~$ diff .odbc.ini.1 .odbc.ini.2 11c11 < Driver = /usr/lib/odbc/libmyodbc.so --- > Driver = /usr/lib/odbc/libmyodbc5.so amrith@amrith-desktop64:~$ And I'm unable to get connected to the database. My existing libmyodbc.so is 64 bit, just as the new one appears to be. amrith@amrith-desktop64:~$ objdump -f /usr/lib/odbc/libmyodbc.so /usr/lib/odbc/libmyodbc.so: file format elf64-x86-64 architecture: i386:x86-64, flags 0x00000150: HAS_SYMS, DYNAMIC, D_PAGED start address 0x000000000000ecb0 amrith@amrith-desktop64:~$ objdump -f /usr/lib/odbc/libmyodbc5.so /usr/lib/odbc/libmyodbc5.so: file format elf64-x86-64 architecture: i386:x86-64, flags 0x00000150: HAS_SYMS, DYNAMIC, D_PAGED start address 0x000000000005d850 amrith@amrith-desktop64:~$ So, there's more to switching ODBC drivers than just putting the new driver in the right place and pointing to it. And, the fact that changing the path in .odbc.ini makes the program stop (or start) working, means the DM is loading it. With that said, I am sure you have a system with the latest driver. Would you be able to take the simple sample program I provided and run it on your system? It is important to make sure that 64-bit client application is always loading 64-bit driver manager library (libodbc.so). SQLConnect() could fail if libodbc.so is attempted to be loaded dynamically, but the library contains 32-bit code. Also, .odbc.ini is not the standard configuration file name for UnixODBC. In order to be able to find this file you have to set the environment variable ODBCINI=path_to_odbc_ini/.odbc.ini I hope this will help to establish the connection from MyODBC driver 5.1.8
[21 Jan 2011 8:43]
Bogdan Degtyariov
Repeated the problem using Connector/ODBC 5.1.8. Setting the report status to "Verified".
[3 Jan 2013 22:18]
John Russell
Added to changelog for 5.2.3: On a 64-bit system, calls to the SQLBindCol function using indicator variables (through the last parameter) could return incorrect results.
Description: On a 64 bit system, when using indicator variables (SQLBindCol's last parameter), you could receive incorrect results. Simple repro instructions are provided in the "How to repeat" section. A synopsis follows. The system on which I was reproducing this problem is a 64 bit linux system running ubuntu 10.10. The table t1 used in the reproduction below has the following schema. CREATE TABLE T1 ( A INT ); In this table were two rows INSERT INTO T1 VALUES ( 6 ); INSERT INTO T1 VALUES ( 20 ); The query being executed is SELECT * FROM T1; This returns one column, of type integer. The program below attempts to retrieve 500 rows at a time. The interesting ODBC calls involved are ... SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, ... ); SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, ... ); SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, ... ); SQLBindCol (hstmt, ... ); SQLExecDirect (hstmt, ... ); SQLFetch (hstmt, ... ); OPTION 1: Assume that you used the following simple structure to retrieve data. typedef struct { SQLINTEGER a; SQLLEN ind; } row_t; Then, the bind would look something like this. retcode = SQLBindCol(hstmt, (SQLUSMALLINT) 1, (SQLSMALLINT) SQL_C_ULONG, (SQLPOINTER) (&row[0].a), (SQLLEN) sizeof(row[0].a), (SQLLEN *) (&row[0].ind)); You would get two rows of data, and a dump of the memory for the buffer used would look something like this. Note that the buffer was initialized with ascii characters from 'a' through 'z'. 0x248fd80 | 00000006 68676665 00000004 00000000 | 0x248fd90 | 00000014 78777675 00000004 00000000 | 0x248fda0 | 6A696867 6E6D6C6B 7271706F 76757473 | 0x248fdb0 | 7A797877 64636261 68676665 6C6B6A69 | 0x248fdc0 | 706F6E6D 74737271 78777675 62617A79 | The structure used above aligned the indicator on an 8 byte boundary. a = 0x248fd80, indicator = 0x248fd88 The size of the structure is 16 bytes and therefore the first row was retrieved at 0x248fd80 and the second at 0x248fd90. Option 2: Now, assume that data was retrieved this way. The SQLINTEGER is 4 bytes and the SQLLEN (for the indicator) is 8 bytes for a total of 12 bytes. Allocate a buffer of 500*12 bytes and bind data as below. retcode = SQLBindCol(hstmt, (SQLUSMALLINT) 1, (SQLSMALLINT) SQL_C_ULONG, (SQLPOINTER) (row), (SQLLEN) sizeof(int), (SQLLEN *) (row+sizeof(int))); With this, data is not correctly retrieved. a = 0x16fed80, indicator = 0x16fed84 0x16fed80 | 00000006 00000004 00000000 00000004 | 0x16fed90 | 00000000 78777675 62617A79 66656463 | 0x16feda0 | 6A696867 6E6D6C6B 7271706F 76757473 | 0x16fedb0 | 7A797877 64636261 68676665 6C6B6A69 | 0x16fedc0 | 706F6E6D 74737271 78777675 62617A79 | How to repeat: /* Sample program: row-binding.c */ #include <sql.h> #include <sqlext.h> #include <stdio.h> #include <stdlib.h> void test1 (SQLHSTMT); void test2 (SQLHSTMT); int main() { SQLHENV henv; SQLHDBC hdbc; SQLHSTMT hstmt; SQLRETURN retcode; retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLAllocHandle()\n", __LINE__ ); return 0; } retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLSetEnvAttr()\n", __LINE__ ); return 0; } retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLAllocHandle()\n", __LINE__ ); return 0; } retcode = SQLConnect(hdbc, (SQLCHAR*) "node0", SQL_NTS, (SQLCHAR*) "root", SQL_NTS, (SQLCHAR *) "password", SQL_NTS); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLConnect()\n", __LINE__ ); return 0; } retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLAllocHandle()\n", __LINE__ ); return 0; } test1 (hstmt); SQLCloseCursor(hstmt); test2 (hstmt); SQLCloseCursor(hstmt); SQLFreeHandle(SQL_HANDLE_STMT, hstmt); SQLDisconnect(hdbc); SQLFreeHandle(SQL_HANDLE_DBC, hdbc); SQLFreeHandle(SQL_HANDLE_ENV, henv); return 0; } #define ROW_ARRAY_SIZE (500) typedef struct { SQLINTEGER a; SQLLEN ind; } row_t; void * allocate ( int size ) { char * p = malloc (size); int ix; for (ix = 0; ix < size; ix ++) { p[ix] = 'a' + (ix % 26); } return p; } void test1(SQLHSTMT hstmt) { row_t *row; SQLLEN nrows; SQLRETURN retcode; int i; row = allocate (ROW_ARRAY_SIZE * sizeof(row_t)); printf ( "sizeof(row) = %d\n", (int) sizeof(row_t)); printf ( "ROW_ARRAY_SIZE = %d\n", ROW_ARRAY_SIZE ); retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER) sizeof(row_t), 0); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLSetStmtAttr()\n", __LINE__ ); return; } retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) ROW_ARRAY_SIZE, 0); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLSetStmtAttr()\n", __LINE__ ); return; } retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &nrows, 0); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLSetStmtAttr()\n", __LINE__ ); return; } printf ( "Binding %p, %d, %p\n", (SQLPOINTER) (&row[0].a), (int) sizeof(row[0].a), (SQLLEN *) (&row[0].ind)); retcode = SQLBindCol(hstmt, (SQLUSMALLINT) 1, (SQLSMALLINT) SQL_C_ULONG, (SQLPOINTER) (&row[0].a), (SQLLEN) sizeof(row[0].a), (SQLLEN *) (&row[0].ind)); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLBindCol()\n", __LINE__ ); return; } retcode = SQLExecDirect(hstmt, "SELECT * from t1;", SQL_NTS); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLExecDirect()\n", __LINE__ ); return; } retcode = SQLFetch(hstmt); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLFetch()\n", __LINE__ ); return; } printf ( "nrows = %d\n", (int) nrows ); for (i = 0; i < 10; i ++) { printf ( "%p | %08X %08X %08X %08X |\n", ((char *) row) + 16*i, ((int *) row)[0 + 4*i], ((int *) row)[1 + 4*i], ((int *) row)[2 + 4*i], ((int *) row)[3 + 4*i] ); } free (row); return; } void test2 (SQLHSTMT hstmt) { char * row; SQLLEN rowsize = sizeof(int) + sizeof(SQLLEN); SQLLEN nrows; SQLRETURN retcode; int i; row = allocate (ROW_ARRAY_SIZE* rowsize); printf ( "setting rowsize to %d\n", (int) rowsize ); retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER) rowsize, 0); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLSetStmtAttr()\n", __LINE__ ); return; } retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) ROW_ARRAY_SIZE, 0); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLSetStmtAttr()\n", __LINE__ ); return; } retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &nrows, 0); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLSetStmtAttr()\n", __LINE__ ); return; } printf ( "Binding %p, %d, %p\n", row, (int) sizeof(int), (row + sizeof(int))); retcode = SQLBindCol(hstmt, (SQLUSMALLINT) 1, (SQLSMALLINT) SQL_C_ULONG, (SQLPOINTER) (row), (SQLLEN) sizeof(int), (SQLLEN *) (row+sizeof(int))); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLBindCol()\n", __LINE__ ); return; } retcode = SQLExecDirect(hstmt, "SELECT * from t1;", SQL_NTS); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLExecDirect()\n", __LINE__ ); return; } retcode = SQLFetch(hstmt); if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) { printf ( "%d: Error in SQLFetch()\n", __LINE__ ); return; } printf ( "nrows = %d\n", (int) nrows ); for (i = 0; i < 10; i ++) { printf ( "%p | %08X %08X %08X %08X |\n", ((char *) row) + 16*i, ((int *) row)[0 + 4*i], ((int *) row)[1 + 4*i], ((int *) row)[2 + 4*i], ((int *) row)[3 + 4*i] ); } free (row); return; } /* Makefile: CC=gcc CFLAGS=-g LDFLAGS=-g -lrt -lodbc all: row-binding row-binding: row-binding.o row-binding.o: row-binding.c */