| 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: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Windows (XP) |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
| Tags: | Duplicate entry, insert, last column, longtext, not stored | ||
[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.
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...