MySQL Bugs: #13776: Invalid string or buffer length error

Bug #13776	Invalid string or buffer length error
Submitted:	5 Oct 2005 17:49	Modified:	13 Nov 2007 8:12
Reporter:	Michael Pryor (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.12	OS:	Windows (Windows 2003)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
See Bug #3855
MyODBC generates a "Invalid string or buffer length" error whenever the following valid ADO syntax is used:

This is the same problem and it appears again using MySQL 4-1-14 and MyODBC 3.51.11-2 on Windows 2003.

The problem is not reproducible on Windows XP Pro.

ODBC Data Source is set to "Don't Optimize Column Width" and "Return Matching Rows"

Also tried the connection string
DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=root;PASSWORD=;OPTION=3;

Either way, this syntax consistently generates an error.

How to repeat:
In MySQL 4.1.14

> create database TestDB;
> use TestDB;
> create table Test ( s longtext );

Save the following as a test.asp file to your webserver and load in a web page.
<%
Dim db: Set db = Server.CreateObject("ADODB.Connection")
db.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TestDB;USER=root;PASSWORD=password;OPTION=3;"
Dim rs: Set rs = "SELECT s FROM Test", db, 1, 3
rs.AddNew
rs("s") = "Value"
rs.Update
%>

Expected: Blank page (success)
Observed: Error message:

---------------------
Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

[Microsoft][ODBC Driver Manager] Invalid string or buffer length 

/test.asp, line 11 

Suggested fix:
The above script and MySQL 4.0 work fine (with MyODBC 3.51.11-2).  I categorized as S1 because the only workaround is to not use MySQL 4.1 currently.

This bug does not occur on MySQL 4.1.9.
(also updated typo in MyODBC Version #)

We've experienced the same behavior using mysql-1.4.14 static binaries for i686 on Linux.

I've got the follow message:
Microsoft VBScript compilation  error '800a0401'

Expected end of statement

/test.asp, line 4

Dim rs: Set rs = "SELECT s FROM Test", db, 1, 3

Could you please check and correct your ASP code ?

Sorry for the typo.  The correct ASP code for repro is below:

<%

Dim db: Set db = Server.CreateObject("ADODB.Connection")
db.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TestDB;USER=root;PASSWORD=password;OPTION=3;"

Dim rs: Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT s FROM Test", db, 1,3

rs.AddNew
rs("s") = "test"
rs.Update

%>

Thanks for your bug report.  I was able to reproduce this using WinXP/SP2 and the following code:

<%

Dim db: Set db = Server.CreateObject("ADODB.Connection")
db.Open "DRIVER={MySQL ODBC 3.51
Driver};SERVER=localhost;DATABASE=TestDB;USER=root;PASSWORD=password;OPTION=3;"

Dim rs: Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT s FROM Test", db, 1,3

rs.AddNew
rs("s") = "test"
rs.Update

%>

Please try c/odbc 3.51.12 and report success or failure.

Failure.

Problem persists using 3.51.12 and windows 2003 (I have updated the version number to reflect this).

I was able to reproduce the bug on 3.51.12 and Windows 2003. 

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Invalid string or buffer length

/13776.asp, line 13

Since this bug also exists in MySQL 5, are there any plans to fix it?

I am having this same problem running MySQL 4.1.14 on Windows 2000 SP4 and ODBC 3.51.12.  I am trying to use the code below to upload a new record with a BLOB field containing a document.  Note that if the APPENDCHUNK line is commented out the objRS.Update works perfectly....

strConn = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=publishing;Uid=docs;Pwd=docs;Options=3"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM Docs WHERE 1=0", strConn,2,3,1
objRS.AddNew
objRS.Fields("Department").Value = MyDepartment
objRS.Fields("Type").Value = MyDocsType
objRS.Fields("Title").Value = MyTitle
objRS.Fields("Publisher").Value = MyPublisher
objRS.Fields("Keywords").Value = MyKeywords
objRS.Fields("Date").Value = MyDate
objRS.Fields("MIMEType").Value = MyMIMEType
objRS.Fields("FileName").Value = MyFileName
objRS.Fields("Document").AppendChunk = objUpload.Fields("FileName").BLOB & ChrB(0)
objRS.Update
objRS.Close

The same code worked previously but I am not sure if it was the upgrade to 4.1.14 OR the latest ODBC 3.51.12 that caused it to stop working?

Has any one any suggestions for a solution?
Thanks in advance

Having now Googled to see if there are other similar problems (with solutions) I have discovered that my code previously posted will work by using a client side cursor for the record set i.e.

strConn = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=publishing;Uid=docs;Pwd=docs;Options=3"
Set objRS = Server.CreateObject("ADODB.Recordset")
'// EXTRA LINE OF CODE
objRS.CursorLocation = 3 '// adUseClient

objRS.Open "SELECT * FROM Docs WHERE 1=0", strConn,2,3
objRS.AddNew

Hope this helps.

I am experiencing the same problem when using MyODBC 3.51.12 to connect to MySQL 4.1.14.

Is there a fix around the corner?  If not, what are the alternative solutions to pull data from MySQL to Oracle?

../mk

I've had this happen to me for the last few days and I've tracked the problem to specifically to the inclussion of an auto increment field.

My table structure, for simplicity,

Table1 (
Field1 Integer Auto-inc
Field2 Text)

Now, 

If you do 

strSQL = "select * from Table1 Where 1=0"
oRec.Open strSQL, oConn, 1, 2 (lock the table properly but I use 1 and 2, they have real variable names though)
oRec.AddNew
oRec("Field2") = ClientVariable
oRec.Update
oRec.Close

An error occurs right? It would seem that the ODBC driver is trying to provide a value for Field1 which us developers usually avoid as we all know we can't as it's Auto-Inc but the behaviour is exactly that, I've tried providing a value for the auto-inc field too but that didn't work either, the only way was to not even reference the auto-inc field in the insert. I've change my code around so that inserts use a stored procedure and I'll be looking at doing that to my update functions soon too. 

What this meant for me was not being able to use AddNew when there's an auto-inc field.

Hope this helps.

I'm getting this error, but not getting it on an ASP page. I have a custom program I use that inserts data into our MySQL server and it works just fine thousands of times per hour.  I'm now getting this error on a new project, the only difference between this project and previous projects is that we are trying to insert a hyperlink (very long, includes data items) into a "longtext" field, my first thought is that this bug is related to the "longtext" field?

If I can help in any way, please let me know.

Hi Mike. Try: server.htmlencode(rs.fields(i).value)

Have the same problem.
Trying to set a longtext field with ADO DB
Server: MySql Version 4.1.19
Client: MySql ODBC 3.51 / Window 2000 Pro SP4 
Creating connection with code not in DNS in Control Panel.

Fields('Info1') = 'any information'

The same program works fine if a conect to a Server with MySql 4.0.27

I'm going to try to use WinXP in Client Side.

Ignacio Gutierrez
http://www.sait.com.mx

Problem persists using a Client in WinXP.

EUREKA:

Find a solution:

I add these options to the conection string: 
1:The client can't handle that MyODBC returns the real width of a column. 
2048:Use the compressed client/server protocol. 
131072:Add some extra safety checks (should not be needed but...).

1+2048+131072 = 133121

ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"
                    "SERVER=localhost;"
                    "DATABASE=test;"
                    "USER=venu;"
                    "PASSWORD=venu;"
                    "OPTION=133121;"

Works OK !

Ignacio Gutierrez
Software Administrativo Integral
http://www.sait.com.mx

The problem is the CursorLocation, the solution is that sentence

rs.CursorLocation = 3 or rs.CursorLocation = adUseClient

That finish the bug, bye

"rs.CursorLocation = adUseClient" is mandatory for updates. Check http://dev.mysql.com/doc/refman/5.0/en/cursor-restrictions.html

Closing the report.

Sorry, I was wrong as server and ADO cursors are not the same... We are aware of this problem and actively working on it.

This seems a bug not in MyODBC, but in ADODB.
SQLDescribeCol() returns the length of LONGTEXT as 4294967295 which is greater than 0 for unsigned types such as SQLULEN. However, ADODB passes this value as the buffer length parameter for SQLBindParameter (as signed SQLLEN). Consequently it results in a negative buffer length that causes the driver manager error.
I suppose the root of the problem lies in MS SQL Server data types that have maximum length of 2^31 (2147483648) bytes, which is still ok for signed long. Therefore MS programmers have not performed any boundary checks for this parameter in ADO libraries.

In the followup to my original submission I mentioned:
"This bug does not occur on MySQL 4.1.9."

Isn't it possible that even if the "bug" is technically MSFT's fault, that there is something MySQL can do to work around it (since this wasn't happening in MySQL 4.1.9).  Classifying the bug as "not a bug" because MSFT screwed something up doesn't really help us the users ;)

Older versions of MyODBC did not cause the problem just because of the bug that prevented MyODBC from returning the correct length of LONGTEXT columns. 
What we can do from our side is to "emulate" MS length of LONGTEXT as 2147483648.
This suggestion is not something the developers will accept without hesitation. 
I am going to do two things:

1. Test MyODBC 3.51.16 with MySQL Server 4.1.9
2. Propose the workaround with the reporting the LONGTEXT columns as 2147483648 bytes to our developers.

When I had the problem, I figure the problem was retriving field lenght, so I change the form I was trying to insert the register. 

Instead I use this code:

cSql = 'select * from eventos where 0'
oRS = CreateObject('ADODB.RecordSet')
oRS.CursorLocation = adUseClient
oRS.Open(cSql, oCon, adOpenForwardOnly, adLockOptimistic, adCmdText)
With oRS
	.AddNew()
	.Fields('suc')		= 20
	.Fields('numuser')	= 'NACHO'
	.Fields('longfield')	= 'Very long information LONG LONG'
	.Update()        &&  <=== HERE WAS THE PROBLEM
Endwith

It look like, when I open the Recordset using: "select * from .. where 0", 
It retrives the column types without problems.

With this change, never have this problem anymore

I hope this help you.

Best Regards

Ignacio Gutierrez

The reason why mysql 4.1.9 worked well is that it reported that LONGTEXT column is MYSQL_TYPE_BLOB with the length of 16 Mbytes whereas newer versions such as 5.0 set the length parameter to 4Gb.

The patch for this problem will be either an option for DSN (and connection string) or some routine that detects using of ADODB library and sets the length of LONGTEXT to 2G instead of 4G

Patch

Attachment: patch13776.diff (application/octet-stream, text), 3.57 KiB.

This should be done by adding a new driver flag (OPTION) instead of another field of the DBC struct. We can still do the auto-detection, but this would also allow a user to force it on if the auto-detection fails.

Add FLAG_COLUMN_SIZE_S32 option for working around ADO bug

Attachment: bug13776.patch (text/plain), 8.16 KiB.

Add FLAG_COLUMN_SIZE_S32 option for working around ADO bug (full patch)

Attachment: bug13776.patch (text/plain), 9.78 KiB.

Microsoft Visual FoxPro has the same problem, see Bug #30890.

The data transformation services in Microsoft SQL Server 2000 may have the same problem. See Bug #27100.

The fix for this bug has been committed to the source repository, and will be included in the next release (3.51.22).

An entry has been added to the 3.51.22 changelog. 

Added the FLAG_COLUMN_SIZE_S32 option to limit the reported column size to a signed 32-bit integer. This option is automatically enabled for ADO applications to provide a work around for a bug in ADO.

The table for configuration options has also been updated.

I am using MySQL ODBC v5 to connect to MYSQL database. It connects fine to my database, fetches fine and nice from database but cannot insert cause of this .UPDATE function
Isueing recordset update function, i get error.
Here is my codes

========my connection string==========
Public connectStr = "Driver={MySQL Connector/ODBC v5};SERVER=localhost;port=3306;DATABASE=nicres;UID=root;OPTION=133121;"
==========connection open sub=============
 Public Sub OpenDB()
        Try
            If Conn.State = ConnectionState.Open Then
                Conn.Close()
            End If
            Conn.ConnectionString = connectStr
            Conn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
===============Her is the code that does the inertion into  the database========
openDB
Try
            ' adding to db
            Dim Recordset As New ADODB.Recordset()
            With Recordset
                .CursorLocation = ADODB.CursorLocationEnum.adUseClient
                .Open(SQLadd, Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)

                .AddNew()
                .Fields("BioDataID").Value = r.RecordID
                .Fields("Features").Value = r.Features
                .Fields("FingerID").Value = r.FingerID
                .Fields("Created").Value = Now.Date
                .Fields("Edited").Value = Now.Date

                .Update()                    <========Error arises here
                r.ID = .Fields("ID").Value
                .Close()
            End With
            Recordset = Nothing
            Conn = Nothing
===========Error generated=================
"Multiple-Step operation generated error. Chech each status value"

I think the problem is with ODBC Recordset cause in every bug reported, they specify the problem arrising from this recordset.update function.

Adewumi, please don't post such long comments and twice... If you want a code review, you'll need support contract. Otherwise, post *complete* test case as I do not see your database field definitions here and I suspect your problem is trying to stuff BLOB (or any other large field) into database in non-appropriate way. Not related to this discussion.

Please note that "MySQL Connector/ODBC v5" is completely unsupported. The current recommended driver is version 5.1, identified as "MySQL ODBC 5.1 Driver".