Bug #48105 Cannot insert records with LONGTEXT columns
Submitted: 16 Oct 2009 7:38 Modified: 7 Oct 2010 8:39
Reporter: Miroslav Ambros Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.1 OS:Microsoft Windows (XP)
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: Duplicate entry, insert, last column, longtext, not stored

[16 Oct 2009 7:38] Miroslav Ambros
Description:
I have a problem inserting records containing LONGTEXT columns using AddNew.
The values are not stored in a database plus "Duplicate entry for key" error can be caused as a side effect.
Mirek

How to repeat:
I have a simple empty table with two columns:
     CREATE TABLE my_table (Id INT NOT NULL PRIMARY KEY, SomeText LONGTEXT)

I want to add new records using AddNew:
     rs.AddNew
     rs("Id") = 700000
     rs("SomeText") = "blah_blah_1"
     rs.Update

     rs.AddNew
     rs("Id") = 700001
     rs("SomeText") = "blah_blah_2"
     rs.Update

A problem is the first AddNew creates new record with "Id" set to "0" and "SomeText" set to "NULL". And the following AddNew fails with "Duplicate entry '0' for key 'PRIMARY'" error as (I suppose) it tries to create next record with "Id" equal to "0"...

But if I slightly adjust the table definition (I add a dummy column at the end):
     CREATE TABLE bug35641 (Id INT NOT NULL PRIMARY KEY, SomeText LONGTEXT, AnotherDummyText VARCHAR(10))

Then the "Id" fields are filled with proper values (so no "Duplicate entry for key" error occurs) but fields "SomeText" still contain "NULL" values...

So my "amateur conclusion" is:
There is a problem storing LONGTEXT values causing some internal error which prevents (in case the column is the last one) saving the primary key value... but if there are other columns following the LONGTEXT column (of non-LONGTEXT type) this error is catched or overridden so primary key value can be saved... but LONGTEXT column value is not stored at all...

See attached test case...
[16 Oct 2009 7:41] Miroslav Ambros
Test case

Attachment: bug48105.vbs (application/octet-stream, text), 1.96 KiB.

[16 Oct 2009 7:46] Tonci Grgin
Miroslav, LONGTEXT is far bigger than anything MS SW recognizes. What happens when you use option "Limit column size to signed 32-bit range"?
[16 Oct 2009 7:49] Miroslav Ambros
Forgot versions used:
     mysql-5.4.2-beta-win32.msi
     mysql-connector-odbc-5.1.5-win32.msi
[16 Oct 2009 7:52] Tonci Grgin
Miroslav, see my last comment.
[16 Oct 2009 7:58] Miroslav Ambros
I have changed my connection string now:
     Const DSN = "Driver={MySQL ODBC 5.1 Driver};Uid=***;Pwd=***;Server=***;Database=***;OPTION=134217728"

But with no luck - the same error occurs. I hope the number in "option" is correct... ;-)
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters....
[16 Oct 2009 8:42] Miroslav Ambros
New findings about LONGTEXT data type used in my test case when replaced:
    * with TEXT - the same error
    * with VARCHAR(4096) - the same error
    * with VARCHAR(4095) - no error, all values stored in database

So the value of 2^12 is the magical border... :-)
[24 Feb 2010 14:13] Tonci Grgin
See Bug#26165.
[7 Oct 2010 7:16] Bogdan Degtyariov
Verified with Connector/ODBC 5.1.7 rev 932
[7 Oct 2010 8:09] Bogdan Degtyariov
Looks like rs("Id") = 700001 is completely ignored by the driver:

INSERT INTO `test`.`bug48105`(`Id`,`SomeText`) VALUES (DEFAULT,'blah_blah_1')
INSERT INTO `test`.`bug48105`(`Id`,`SomeText`) VALUES (DEFAULT,'blah_blah_2')
[7 Oct 2010 8:39] Bogdan Degtyariov
Interesting that ADODB.Recordset uses SQLPutData() for text column #2, but never for column #1:
------------------------------------------------------
bug48105        170c-49c	ENTER SQLSetPos 
		HSTMT               0x001BE4C8
		SQLSETPOSIROW                1 
		UWORD                        4 <SQL_ADD>
		BOOL                         0 <SQL_LOCK_NO_CHANGE>

bug48105        170c-49c	EXIT  SQLSetPos  with return code 99 (SQL_NEED_DATA)
		HSTMT               0x001BE4C8
		SQLSETPOSIROW                1 
		UWORD                        4 <SQL_ADD>
		BOOL                         0 <SQL_LOCK_NO_CHANGE>

bug48105        170c-49c	ENTER SQLParamData 
		HSTMT               0x001BE4C8
		PTR *              0x0050DFA8

bug48105        170c-49c	EXIT  SQLParamData  with return code 99 (SQL_NEED_DATA)
		HSTMT               0x001BE4C8
		PTR *              0x0050DFA8

bug48105        170c-49c	ENTER SQLPutData 
		HSTMT               0x001BE4C8
		PTR                0x06220584
		SQLLEN                    22

bug48105        170c-49c	EXIT  SQLPutData  with return code 0 (SQL_SUCCESS)
		HSTMT               0x001BE4C8
		PTR                0x06220584
		SQLLEN                    22

bug48105        170c-49c	ENTER SQLParamData 
		HSTMT               0x001BE4C8
		PTR *              0x0050DFA8

bug48105        170c-49c	EXIT  SQLParamData  with return code -1 (SQL_ERROR)
		HSTMT               0x001BE4C8
		PTR *              0x0050DFA8

		DIAG [S1000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Duplicate entry '0' for key 'PRIMARY' (1062) 
------------------------------------------------------
That is the reason why the driver has no data for column #1 and therefore uses DEFAULT value for it.

I am setting the status as "not a bug" because it looks as a bug in ADODB. Information that directly proves the opposite is always welcome.