MySQL Bugs: #1292: Problems writing VARCHAR or reading TEXT

Bug #1292	Problems writing VARCHAR or reading TEXT
Submitted:	15 Sep 2003 21:03	Modified:	30 Oct 2003 10:09
Reporter:	David Snell	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.06	OS:	Windows (Windows 2000 Server)
Assigned to:		CPU Architecture:	Any

Description:
I am attempting to build an ASP website on a Windows 2000 Server using mysqld-4.0.13-max and MyODBC 3.51.06.  I am having problems reading and writing using ADODB recordsets.

The problem seems to be with the ODBC connection option "Don't Optimize Column Width".  If this is checked, an attempt to read a TEXT field returns the error:

Error #-2147217887: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

If it is not checked, an attempt to write to a VARCHAR field returns the same error.

The only other option that I have checked is "Return Matching Rows".

Currently, I have two system DSNs set up, one for reading and one for writing.  This is not really satisfactory.

How to repeat:
I have had a lot of problems trying to repeat these problems.  They always happen with my live system, but I can't necessarily duplicate them using test data. 

I have not been able to reproduce the 'read TEXT' problem.

The 'write VARCHAR' problem didn't show up at first.  I created a table and wrote to it successfully.  The problem showed up after I deleted all the rows and tried to write again.  The problem seemed to be fixed when I wrote data into the VARCHAR column using SQL, but it appears in other tables as well, and this does not fix it.

Here is the table that consistently gives trouble:

mysql> describe documents;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| CategoryID   | int(11)      | YES  |     | NULL    |       |
| Filename     | varchar(100) | YES  |     | NULL    |       |
| Title        | varchar(100) | YES  |     | NULL    |       |
| Description  | text         | YES  |     | NULL    |       |
| UploadedBy   | varchar(100) | YES  |     | NULL    |       |
| DateFiled    | datetime     | YES  |     | NULL    |       |
| DateUploaded | datetime     | YES  |     | NULL    |       |
| DaysCurrent  | int(11)      | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
8 rows in set (0.20 sec)

Using your table definition on Windows 2000 server with MDAC 2.7
I didn't have problems for to read TEXT, write varchar, display them
with a simples asp page (MyODBC 3.51).
The dsn was configured with the default values.

I have managed to repeat the bug.

Note that I have upgraded to version 4.0.16, but the bug was the same before and after the upgrade.

****************************************************************************
MySQL output:
****************************************************************************
-- MySQL dump 9.09
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	4.0.16-max-debug

--
-- Table structure for table `documents`
--

CREATE TABLE documents (
  DocumentID int(11) NOT NULL default '0',
  CategoryID int(11) default NULL,
  Filename varchar(100) default NULL,
  Title varchar(100) default NULL,
  Description text,
  DateFiled datetime default NULL,
  DateUploaded datetime default NULL,
  DaysCurrent int(11) default NULL,
  UserID int(11) NOT NULL default '0'
) TYPE=MyISAM;

--
-- Dumping data for table `documents`
--

INSERT INTO documents VALUES (580,19,'test.doc','Test','','2003-10-28 00:00:00','2003-10-28 14:57:29',7,143);

****************************************************************************
Test Page (Test.asp):
****************************************************************************
<%
  Set Connection = Server.CreateObject( "ADODB.Connection" )
  Connection.Open "Test"
  
  Set RecordSet = Connection.Execute( "SELECT * FROM Documents WHERE DocumentID = 580" )
  Response.Write RecordSet( "Description" )
%>

****************************************************************************
Results:
****************************************************************************

The page cannot be displayed 
There is a problem with the page you are trying to reach and it cannot be displayed. 

--------------------------------------------------------------------------------

Please try the following:

Click the Refresh button, or try again later.

Open the file:// home page, and then look for links to the information you want. 
HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80020009)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/test.asp

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705) 

Page:
GET /test.asp 

Time:
Wednesday, 29 October 2003, 15:34:10 

More information:
Microsoft Support 
 

David Snell

I have just set up the test data on our web server.  This is a Windows NT 4.0 Server running MySql 4.0.13-nt.  The test page is:

http://www.geraldton.wa.gov.au/test.asp

If you are interested, I can email you a password so you can connect to the database yourself.

David Snell

I also am unable to duplicate this using MDAC 2.7, MyODBC 3.51.06 and MySQL 4.0.16.

You may wish to verify that you have the latest MDAC from Microsoft installed.

Thank you

I have noticed that the error that you encounter when reading and Text type column only when the column does not contain any data. Once I have added a default value of NULL for the TEXT column, everything worked fine.

I haven't had any problems recently.  I upgraded to the latest MDAC, used the default settings for MyODBC, and, most importantly, set the Connection's cursor type to adUseClient.

Sample code:

<!-- #INCLUDE "adovbs.inc" -->
<%
  Set Con = Server.CreateObject( "ADODB.Connection" )
  Con.Open "DSN1" 
  Con.CursorLocation = adUseClient
  
  ...

I had a very similar problem to that listed. Upgrading the server from MDAC 2.5 to MDAC 2.8 cured the problem with no apparent side-effects.

Agreed.  Upgrade to MDAC 2.8 solves this problem.  It seems that an updated install of MyODBC may have somehow downgraded us back to a previous MDAC -- I'm sure we were already on MDAC 2.8... Re-ran the installer for MDAC 2.8 and it solved the problem.

this bug send after comand select minute(time_field - time_field) as min time field is differnt time value from table