MySQL Bugs: #91856: ODBC 8.0.12 32-bit, Microsoft Access and Tables with MEDIUMTEXT or LONGTEXT

Bug #91856	ODBC 8.0.12 32-bit, Microsoft Access and Tables with MEDIUMTEXT or LONGTEXT
Submitted:	1 Aug 2018 16:05	Modified:	20 Sep 2018 23:08
Reporter:	Kirk Haggard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:		OS:	Windows (Server 2012 R2)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	x86 (Intel Xeon E5-2680 v2 2.80GHz)
Tags:	#DELETED, 32-bit, 32bit, 8.0.12, longtext, Mediumtext, Microsoft Access, MSAccess, ODBC

Description:
We are having an issue with Microsoft Access 2016 32-bit and the 32-bit version of the MySQL 8.0.12 ODBC driver.
Microsoft Access will not load the first 10 rows of a table if it contains one or more MEDIUMTEXT or LONGTEXT fields and two or more rows in that table have more than 1 character in those text fields.
If the table is refreshed after opened then all rows, including the problem rows, are loaded.
If the table is closed and then re-opened then the issue is back until it is refreshed.
This has been replicated by physically opening the linked table object and by opening the table via a recordset object in VBA code.

How to repeat:
1. Create the table below in either MySQL Community Server 5.7.17-log or 8.0.12 and insert the records below.

CREATE TABLE `texttest` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TestText` mediumtext,
  `MySQL_Created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `MySQL_Updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test1');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test2');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test3');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test4');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test5');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test6');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test7');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test8');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test9');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test10');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test11');
INSERT INTO `it`.`texttest` (`TestText`) VALUES ('test12');

2. Link the table to Microsoft Access 2016 32-bit using the 32-bit version of the MySQL 8.0.12 ODBC driver and open the table.

Complete Bug Info

Attachment: mysql-bug-data-91856.zip (application/x-zip-compressed, text), 59.96 KiB.

Can confirm this bug occurs with Access 2010, both 32-bit and 64-bit versions of Office.  For our tests, the bug was affects fewer table when accessed from 64-bit.  Also affects some TEXT fields in addition to MEDIUMTEXT/LONGTEXT.  Changing fields to VARCHAR(2000) corrected all but 1 table, where the issue persisted even as VARCHAR(1000) and ultimately was only corrected once altered to VARCHAR(255).  In all test cases we remained under the record VARCHAR limit.  Additionally, noticed that the issue was resolved when we removed all non-primary keys / constraints.  Also noticed that the issue was resolved if we created a view (select *) on the affected table, and then selected NO primary key when linking in Access.  It is worth noting that none of these workarounds were serviceable solutions in our case.

I tested changing all TEXT, MEDIUMTEXT and LONGTEXT fields to VARCHAR() on a clone of our MySQL server and am still experiencing the issue with any VARCHAR() fields that are set to anything greater than VARCHAR(341). This is with all of the same specs as the original ticket. MySQL Community 5.7.17 64-bit running on Windows Server 2012 R2. Microsoft Access 2016 32-bit running on a separate Windows Server 2012 R2 with the ODBC 8.0.12 32-bit driver.

To replicate:

1. Create a table with a text field set to VARCHAR(342)

CREATE TABLE `varchartest` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Comments` varchar(342) DEFAULT NULL,
  `MySQLTimestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

2. Link the table in Access and insert at least 10 rows with at least 2 characters in the Comments field.

3. Close and re-open to table to see up to the first 10 records with #Deleted in all fields. If you modify the comments field in all records to only have one character then the problem goes away.

4. Change the Comments field to VARCHAR(341) and refresh the table link in MSAccess.

ALTER TABLE `varchartest` CHANGE COLUMN `Comments` `Comments` VARCHAR(341) NULL DEFAULT NULL ;

5. Open the table in MSAccess and all records are displayed properly.

This same issue also happens with the ODBC 5.3.11 driver. I had to go back to using the 5.3.6 driver.

Is this similar? https://bugs.mysql.com/bug.php?id=92078

The issue is confirmed and verified.
It might be related to the change in handling of the TEXT/BLOB type names. The earlier versions reported only TEXT and BLOB for the variety of TINY-, MEDIUM- and LONG- types.

Screenshot demonstrating the initial problem with #DELETED# records in MS Access

Attachment: before.png (image/png, text), 26.48 KiB.

Posted by developer:
 
Issue is fixed in ODBC Driver 8.0.13

After fixing the problem is gone

Attachment: after.png (image/png, text), 35.13 KiB.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.13 release, and here's the changelog entry:

Fixed column metadata handling with Microsoft Access.

Thank you for the bug report.

We are experiencing same issue with ODBC 8.0.12: presence of a mediumtext field in a select query causes no results to be returned when using a parameter in the query.  Setting NO_SSPS=1 (client-side preparation) or using a parameter-less query returns results.

Posted by developer:
 
Hello, sorry this bug has caused you problems. The fix is reported against 8.0.13 (not 8.0.12). I'm not at liberty to say when 8.0.13 will be released, so will instead say "soon" (for real).

Not fixed in 8.0.13 for me. #deleted still occurs like in 8.0.12.

Ronny,

For your information the problem with #deleted is not one of a kind issue.
It happens for literally thousands of different reasons and many of them are unclear since MS Access does not provide any error information on such occasions.
This particular problem has been fixed and the screenshots uploaded before prove it.

If you are facing a problem with #deleted this must be due to another reason, which means another bug has to be reported. Please try to be as specific as possible with the details of how it can be repeated along with other important information such as CREATE TABLE statement for the MySQL table, ODBC options being used, etc.

Thank you.

Okay, I will try to investigate to isolate the cause. So far I can say that version 5.3.10 is not affected.

Created a new related bug: https://bugs.mysql.com/bug.php?id=92947

I also tried the update and my issues were not fixed. It was different in that some table entries showed fine and some showed #deleted, whereas before all top 10 entries showed #deleted. These tables use the medium blob field and have a timestamp. I haven't investigated further to try to narrow down the difference between those that show and those that don't (assuming there is one), rather I immediately reverted to ODBC connector 5.3.6 which works perfectly. I accept that there may be many causes of this, as you have explained to Ronny, but it makes no sense to me that an old version works perfectly while the newer versions don't. At some point I might take the time to investigate further, but for now I just want my database to work so reverting is the solution for me at this stage.