MySQL Bugs: #68628: Can't update record with BIGINT field with 15 char value (ODBC error '80004005')

Bug #68628	Can't update record with BIGINT field with 15 char value (ODBC error '80004005')
Submitted:	10 Mar 2013 21:20	Modified:	27 Mar 2013 4:14
Reporter:	Jesper Ahlers	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Windows (Win 7 32 BIT)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	BIGINIT, FACEBOOK, ODBC Error, record structure

Description:
On my pc (localhost) I have restored a database from my external homepage. 
One of the tables have a BIGINT field holding Facebook values. 
When these values are longer than 10 characters, I get the following error when writing to the row.

"Microsoft OLE DB Provider for ODBC Drivers error '80004005' 
Query-based update failed because the row to update could not be found. "

The code is like:
SQLstmt = "SELECT * FROM tblslLogindb WHERE sl_key = 1411"
rs69("Create_date") =	now()							rs69.Update	  

There is no problem on the external homepage running MYsql 5.5.25a

How to repeat:
Same error everythime

Suggested fix:
no idea

Thank you for the bug report. Are you able to run that query with the command client mysql?. If yes please provide the ODBC connector version and change the category. Thanks.

I can update the row with the statement 
"Update tblsllogindb set Password_not_kryp = 'kaj2'  where sl_key = 2141;"
using either the command client or the MySQL Query Browser or in a my asp program.

But when I change the update to using rows, I get the problem if the row contains bitinit values with more than 10 chars. 
(if the value is 10 chars or less no problem)

I am using the MySQL ODBC 3.51 Driver, the same as on the external web server.

My code:

Dim strSQLServerName, strSQLDBUserName, strSQLDBPassword, strSQLDBName, strSQLDriver
Dim strSQLoption, strSQLPort

Const locktyperead 			= 1
Const locktypeupdate 		= 2
Const cursortypeforward 	= 0
Const cursortypeOpenKeyset = 1  
Const adParamInput = &H0001
Const adWChar = 130

Dim strDataBaseConnectionTKD
Dim strSQLstmt64
dim strNbDatabasePathCD64, objNbConn64, rs64

strSQLServerName 	= "localhost" 
strSQLDBUserName 	= "root" 
strSQLDBPassword 	= "mitpw" 
strSQLDBName 		= "tenere_dk_db" 
strSQLDriver 		= "MySQL ODBC 3.51 Driver"
strSQLoption 		= "16834"
strSQLPort	 			= ""

strDataBaseConnectionTKD = " Driver={" & strSQLDriver & "};Server=" & strSQLServerName & ";Database=" & strSQLDBName & "; User=" & strSQLDBUserName & ";Password=" & strSQLDBPassword & ";Option=" & StrSQLOption & ";" &	strSQLPort   

' The sql statement
strSQLstmt64 = "SELECT * FROM tblslLogindb WHERE sl_key = 2145; "

Set objNbConn64 = Server.CreateObject("ADODB.Connection")
Set rs64=Server.CreateObject("ADODB.Recordset")			
objNbConn64.Open = strDataBaseConnectionTKD					

rs64.Open strSQLstmt64, objNbConn64, cursortypeforward , locktypeupdate 

rs64("Password_not_kryp") = "kaj123"
rs64.Update

Hi Jesper,

MySQL Connector/ODBC 3.51 has been deprecated and it will not receive any patches or bug fixes. Can you try MyODBC driver 5.2a (a is for ANSI)? 
Version 5.2a is compatible with 3.51 as it was a non-UNICODE driver too.

Hi 
I have now tried both ODBC driver "MyQL ODBC 5.2w Driver" and
 "MySQL ODBC 5.2(a) Driver". Still same problem.
Any other suggestion?
/Jesper Kick

I still cannot repeat the problem.
Please check the vbs script uploaded below.
Try running it from the command line. You will need to replace my Data Source Name test52w with the DSN that exists in your environment.

VBS test case

Attachment: test68628.vbs (application/octet-stream, text), 806 bytes.

Hi Bogdan
Thanks for working on my problem, and sorry for first testing now, but I have been sick.
I have now tested with your example and no problem. 
But if bigvalue = 2222 is changed to bigvalue = 123456789012345, then it dumps.
(conn.Execute ("insert into bug68268 (id, bigvalue, vc) values (1, 123456789012345, 'abcde')")
Regards
/Jesper Kick

The test case fails with such big number because of integer overflow.

123456789012345 is far bigger than the standard 32-bit integer range  -2147483648 to 2147483647.

Your connection option (OPTION=16834 = 2 + 64 + 128 + 256 + 16384) consists of the following:

 2     FLAG_FOUND_ROWS          /* Return matched rows instead of affected_rows */
 64    FLAG_NO_SCHEMA           /* Ignore schema in column specifications */
 128   FLAG_NO_DEFAULT_CURSOR	/* No default cursor */
 256   FLAG_NO_LOCALE           /* Don't use setlocale() */
 16384 FLAG_NO_BIGINT           /* Treat BIGINT columns as INT columns */

The last option breaks the update because it makes the driver to use INT type for BIGINT column, which results in the overflow. Removing that option worked for me perfectly (OPTION=450), no update errors.

Hi Bogdan 
Great, you solved it. Thanks a lot.
Please close the case
Kind regards
Jesper Kick

Not a bug. Closed.