MySQL Bugs: #32171: ODBC Driver incorrectly parses large Unsigned Integers

Bug #32171	ODBC Driver incorrectly parses large Unsigned Integers
Submitted:	7 Nov 2007 20:38	Modified:	30 Nov 2007 22:30
Reporter:	Bob Hansen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.20	OS:	MacOS (10.4.10)
Assigned to:	Jim Winstead	CPU Architecture:	Any
Tags:	odbc driver mac unsigned integer long sqlbindcol

Description:
When reading in a large unsigned integer (such as 4255080020), the value retrieved back from the driver is always the same, 2147483647. Interestingly, that is exactly one less than half of what an unsigned integer can hold (2^32 - 1). Unsigned integers smaller than 2^32 work fine. It seems as though the driver treats unsigned integers as signed as they pass through.

How to repeat:
MySQL server version: 5.0.19-nt on Windows XP SP2
ODBC Driver: 3.51.21 on PowerPC iMac
and also 3.41.20 on x86 Mac Pro

Create a table with one column of data type Unsigned Integer. Create a row with 4255080020 in the field. Create a C project and connect to the database to Fetch data. Call SqlBindCol with targettype of SQL_C_ULONG, size 4, and send in a pointer to an unsigned long. After SqlFetch is called, the unsigned long is populated with the value 2147483647. One example of a "large" unsigned integer being used is 4255080020.

Using Xcode for development which is treating long, unsigned long, int, and unsigned int as 4 Bytes. This matches Integer data type in SQL. Connection is being made accross LAN with full access to database.

Note: Other ODBC functions for SQL are called in the process but are omitted above due to the strong doubt of their importance to the problem. (such as SqlAllocHandle and SqlExecDirect)

Suggested fix:
Seems to be possibly a bug in Mac versions of the ODBC driver that somehow converts outgoing unsigned integer to a signed integer, and concatinates it to be the largest possible signed integer (aka 2147483647).

I forgot to add I also tried the newest mysql server but still no dice.

mysql-5.0.45-win32

Server is using Windows XP SP2.
Client is using Intel MacOS 10.4.10 or PowerPC MacOS 10.4.10.

Hi Bob and thanks for your report. It looks rather important so I'll give it a priority. But before that I would need you to follow on your line: "Create a C project and connect to the database to Fetch data" and attach simple but complete test case demonstrating this error. Something like:
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>

void main()
{
  SQLHENV env;
  HSTMT stmt, hstmt_prep;
  HSTMT stmt_ins;
  SQLHDBC dbc;
   
   SQLRETURN rc;
   SQLCHAR     connOut[255], Val[255];/* buffer for connection output */
   SQLSMALLINT szConnOut; /* num bytes returned in connOut */

   SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,(SQLHANDLE*)&env);
   SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
   SQLAllocHandle(SQL_HANDLE_DBC,env,(SQLHANDLE*)&dbc);
  
   rc = SQLDriverConnect(dbc, NULL, (SQLCHAR *)"DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3306;UID=root;PWD=*****;Database=test;OPTION=3",
	   _countof("DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3306;UID=root;PWD=*****;Database=test;OPTION=3"), connOut, 255, &szConnOut, SQL_DRIVER_NOPROMPT);

  SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
  SQLAllocHandle(SQL_HANDLE_STMT, dbc, &hstmt_prep);
/*************************************************************************************/
    SQLExecDirect(hstmt_prep, (SQLCHAR *)"USE test", SQL_NTS);
    SQLExecDirect(hstmt_prep, (SQLCHAR *)"DROP TABLE IF EXISTS `test`.`bug32171`", SQL_NTS);
    SQLExecDirect(hstmt_prep, (SQLCHAR *)"CREATE TABLE `test`.`bug32171` (`col1` INTEGER UNSIGNED NOT NULL)",SQL_NTS);
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt_prep);
etc...

Test case to show incorrect ODBC parsing

Attachment: main.c (text/plain), 4.18 KiB.

What happens if you fix the type of valueIntoServer and valueFromServer to be SQLUINTEGER?

Depending on the platform, sizeof(unsigned long) may not be the same as sizeof(SQLUINTEGER), and ODBC expects to be passed a SQLUINTEGER for values identified as SQL_C_ULONG.

Never mind. Found the problem.

Don't use atol() on an unsigned value

Attachment: bug32171.diff (text/plain), 2.04 KiB.

Yeah, same behavior with SQLINTEGER. Both SQLINTEGER and unsigned long show up as 4 bytes using sizeof.

Thanks for the verification. How soon do you expect the change to be made into a release driver? Also, make sure to fix code for both Intel and PowerPC Mac versions. We haven't tried the test on Windows.

The fix for this bug has been committed, and will be in 3.51.22.

Wonderful, thanks. Make sure to also add to 3.41.22.

On a side note, this code example broke on SqlBindCol. I had same trouble with SqlGetData. Does that call the same underlying code?

There's no such thing as 3.41.22. And yes, this bug would have shown up in SQLGetData as well as SQLBindCol.

By 3.41.22 I mean the Intel Mac version not the PowerPC Mac version. You have two different downloads depending on your CPU architecture. We had the same problem with both versions.

A note has been added to the 3.51.22 changelog: 

Unsigned integer values greater than the maximum value of a signed integer would be handled incorrectly.

Just wanted to let you know I tried the new driver today (22) and it works perfectly with unsigned integers. Thanks!

Bob, thanks for your feedback and your interest in MySQL.

Due to Bug#41800 asked Eric to recheck on this.