MySQL Bugs: #36071: Insert data in LONGTEXT does not work

Bug #36071	Insert data in LONGTEXT does not work
Submitted:	14 Apr 2008 22:32	Modified:	26 May 16:20
Reporter:	Louis Breda van
Status:	Closed
Category:	Connector/ODBC	Severity:	S1 (Critical)
Version:	5.1.3	OS:	Microsoft Windows (VISTA64)
Assigned to:	Bugs System	Target Version:	5.1
Tags:	windows, ODBC, longtext
Triage:	D2 (Serious)

Description:
Hello,

When I checked the content of a table, I noticed that all LONGTEXT fields where empty.

So I checked if the data was realy inserted by MsAccess via the ODBC-link. No problem
there, multiple routines, using both strings and memo fields.

So I wrote a small programm inserting a simple text "bla bla bla" in a table
containing three fields:
- thename  varchar and pk
- sometext varchar
- mytext longtext
When verifiing the result with MySQL query browser, there was some rubisch in the long
text field, but certainly not "bla bla bla"

Problem need to be fixed urgently of course.

Louis

How to repeat:
Sub tstmysql()

    Dim cn_mytst As New ADODB.Connection
    Dim rstst As New ADODB.Recordset
    
    Dim Name As String
    Dim Something As String
    
    cn_mytst.Open connTstDB
    
    rstst.Open "SELECT TstTable.* FROM TstTable;", cn_mytst, adOpenDynamic,
adLockOptimistic
    
    rstst.MoveFirst
    Something = rstst!Something

    rstst!Something = "This should work"        'it does
    rstst!MyText = "bla bla bla"               '####### NOT CORECLY INSERTED !!
    rstst.Update
    
    Something = rstst!Something
    
    rstst.Close
    
    Stop

Hello Louis and thanks for your report.

Three things:
 - Please try MyODBC 5.1.3 as this might be related to problem described in Bug#19065
(and fixed).
 - Use ADO "Update Criteria" option that, if set to adCriteriaKey, should cause a correct
WHERE clause to be generated (as I described previously.
 - Check if "Limit column size to signed 32-bit range" is in effect. MS was always lousy
dealing with other engines data types that it doesn't support.

Does any of this help?

Tonci,

Not much time to test no
- at first I am already running 5.1.3 (sorry for that mistake)
- rstst.Properties("Update Criteria") = adCriteriaKey ; is obscure and is not accepted,
no time to find out why now.
- can not find the odbc? flag FLAG_COLUMN_SIZE_S32, also feels as a work arround

Louis

Louis, I'll try to answer:

- rstst.Properties("Update Criteria") = adCriteriaKey ; is obscure and is not accepted,
no time to find out why now.

"Update Criteria" is *well documented* ADO feature, please see links either in our manual
manual or in MSDN. I have provided it many times. So, what's *obscure* about it!!!???
Default ADO (insane) behavior is to form update statement based on *all* field values!
This is sure to fail with BLOB's, FLOATs etc...

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Properties("Update Criteria").Value = 0
    .Open(strSQL)
End With

- can not find the odbc? flag FLAG_COLUMN_SIZE_S32, also feels as a work arround

No it is not. No Microsoft standard supports anything found in other database servers. As
they do not have fields so big (and this goes for BIGINT too, for example) their SW does
not work with them... This is just a sorry fact of life...

Now, please modify your code so that it uses UpdateCriteria and retest.

Waiting on your results.

Tonci,

Thanx, I did a quck test with the testtable using
Properties("Update Criteria").Value = 0 

Seems to work, will try to understand that and test with
the real DB later

Louis
Note that I did NOT use FLAG_COLUMN_SIZE_S32 !

Louis

Tonci,

I feel that it is NOT Properties("Update Criteria").Value = 0 that mad it work
but the fact that you use     rstst.CursorLocation = adUseClient

So, As far as I can see now, the problem is related to adUseServer ......

Louis

Louis.

Update criteria and limiting column size addresses two different issues; UpdateCriteria
addresses issue of ADO forming "impossible" UPDATE statements, while limiting column size
to signed 32bit value is due to MSSQL not being able to handle larger values thus all of
their frameworks enforce this restriction.

Waiting on your results.

Tonci,

Did a first quick test with the main DB. 
- cursor at client
- Properties("Update Criteria").Value = 0 
Still does not work, more is needed ....

Can you tell me how to use FLAG_COLUMN_SIZE_S32?
Can I set it in the ODBC-connection string? If so which value

Louis

Louis, right. This limitation is known and documented in manual. Please use adUseClient.
As for option it is 1>>27 or, in plain number: 67108864.

Can we close the report now?

Tonci,

I did some structurised testing using the tree parameters we identified:

Colum_1: FLAG_COLUMN_SIZE_S32 OPTION=67108864 (+ 3)     
Colum_2: adUseClient
Colum_3: adCriteriaKey 
Colum_4: Result

C1      C2      C3      C4
NO	NO	NO	NOT OK
NO	YES	NO	OK !
NO	NO	YES	not possible
NO	YES	YES	OK !
YES	YES	YES	OK !
YES	YES	NO	OK !
YES	NO	NO	NOT OK
YES	NO	YES	not possible

So the only thing which seems to matter is "You should use adUseClient"

Does not seems te be OK to me! 

Will do some futher testing with adUseClient 
(in combination with normal "3" connection option and also *not* using adCriteriaKey) 

Louis

Louis, I would say this table requires some sanity check as well as traces/log analysis.

Louis, I see no bug here.

http://dev.mysql.com/doc/refman/5.1/en/cursor-restrictions.html:
Restrictions on Server Side cursors: Cursors are read only; you cannot use a cursor to
update rows.

Coding with UpdateCriteria defined is best practice (due to insane "all fields" default
behavior of ADO), not a "must have"! It all depends of fields you want to update. If they
are BLOBs or FLOATs not defining UpdateCriteria will surely lead to error.

So,   cnxDatabase.Execute("create table bug36071(`Name` VARCHAR(16) NOT NULL PRIMARY KEY,
`Something` VARCHAR(45) DEFAULT NULL, `MyLongTextFld`   LONGTEXT, UNIQUE
KEY_UNI(`Something`));")
+
With rs1
    .ActiveConnection = cnxDatabase
    .LockType = adLockOptimistic '3
    .CursorType = 2
    .CursorLocation = 3
    .Properties("Update Criteria").Value = 0
    .Open(strSQL)
End With
+
rs1.MoveFirst
rs1("MyLongTextFld") = "This should work too. With: Some very long text to put into
MyLongTextFld field to test Bug#36071"
rs1.Update

works as expected:
080416 10:36:09	      6 Connect     root@localhost on test
		      6 Query       SET NAMES utf8
		      6 Query       SET character_set_results = NULL
		      6 Query       SET SQL_AUTO_IS_NULL = 0
		      6 Query       select database()
		      6 Query       select database()
080416 10:36:10	      6 Query       SELECT @@tx_isolation
		      6 Query       USE test
		      6 Query       DROP TABLE IF EXISTS `bug36071`
		      6 Query       create table bug36071(`Name` VARCHAR(16) NOT NULL PRIMARY KEY,
`Something` VARCHAR(45) DEFAULT NULL, `MyLongTextFld`   LONGTEXT, UNIQUE
KEY_UNI(`Something`))
		      6 Query       INSERT INTO bug36071 VALUES('Louis', 'Testje','First text in
longtext field')
		      6 Query       SELECT * FROM bug36071
080416 10:36:12	      6 Query       UPDATE `test`.`bug36071` SET `MyLongTextFld`='This
should work too. With: Some very long text to put into MyLongTextFld field to test
Bug#36071' WHERE `Name`='Louis'
		      6 Quit       

c:\mysql507\bin>mysql -uroot -p test
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.58-pb1083-log MySQL Pushbuild Edition, build 1083

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from bug36071;
+-------+-----------+-----------------------------------------------------------
----------------------------------------+
| Name  | Something | MyLongTextFld
                                        |
+-------+-----------+-----------------------------------------------------------
----------------------------------------+
| Louis | Testje    | This should work too. With: Some very long text to put int
o MyLongTextFld field to test Bug#36071 |
+-------+-----------+-----------------------------------------------------------
----------------------------------------+
1 row in set (0.00 sec)

mysql> show create table bug36071\G
*************************** 1. row ***************************
       Table: bug36071
Create Table: CREATE TABLE `bug36071` (
  `Name` varchar(16) NOT NULL,
  `Something` varchar(45) default NULL,
  `MyLongTextFld` longtext,
  PRIMARY KEY  (`Name`),
  UNIQUE KEY `KEY_UNI` (`Something`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

I'll be attaching test case shortly. In the meantime, interesting reading can be found on
our site too:
http://dev.mysql.com/tech-resources/articles/vb-cursors-and-locks.html
http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html
http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html
for example.

test case

Attachment: 36071.vbs (application/octet-stream, text), 3.04 KiB.

Also, Option 67108864 has little effect when field value is less than UINT32. I think this
covers just about everything you asked and what I know of this problem.

Again, I made same mistake as before, mixing up server's SS cursors with emulated ones
from driver... Please replace 3 (adUseClient) with 1 (adUseServer) in my test case and
error will pop up.

Verified that using SS emulated cursors updating TEXT field leads to "Multiple step
operation failed" error.

I apologize once again for my mistake.

Likely a duplicate of bug#37649.

Jess, just retested, there is still error when using SS cursor. Can you please assign?

Using attached test case and with .CursorLocation = 1 'adUseServer:
  If one uses rs.Properties("Update Criteria").Value = 0 then error is:
"Item can not be found in the collection corresponding to the requested name or ordinal.
800A0CC1 (ADODB.Recordset).
  If one goes *without* defining rs.Properties("Update Criteria").Value then error is as
reported:
"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if
available. No work was done". 80040E21 (Provider).

I wonder if Bug#37649 also fails with SS cursor.

Pushed as rev 831, will be released in 5.1.6.

An entry was added to the 5.1.6 changelog:

Insertion of data into a LONGTEXT table field did not work. If such an attempt was made
the corresponding field would be found to be empty on examination, or contain random
characters.