MySQL Bugs: #99418: MS-Access,Long Text

Bug #99418	MS-Access,Long Text
Submitted:	2 May 2020 15:07	Modified:	9 Sep 2020 17:45
Reporter:	Finn Nielsen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.20	OS:	Windows (windows 10 64 bit)
Assigned to:		CPU Architecture:	Any
Tags:	access, long text

Description:
I get error message "
[MySQL][ODBC 8.0(w) Driver][mysqld-5.7.29-32-log]You have an error in your sql-syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':?h?
|' at line 1 (#1064)
" when executing "
INSERT INTO SITE_tblKunstnere (KunstnerID,Betegnelse,Type,Separator,Fra,Til,BorgerligtNavn,Kommentar)
SELECT Kunstnere.KunstnerID,Kunstnere.Betegnelse,Kunstnere.Type,Kunstnere.Separator,Kunstnere.Fra,Kunstnere.Til,Kunstnere.BorgerligtNavn,Kunstnere.Kommentar
FROM Kunstnere
WHERE (Kunstnere.KunstnerID Between 1 and 40);
" in MS-Access.

The table SITE_tblKunstnere is linked into MS-Access database "
ODBC;Driver=MySQL ODBC 8.0 Unicode Driver;SERVER=mysql15.simply.com;UID=????????;DATABASE=????????;PORT=3306;CHARSET=utf8;AUTO_RECONNECT=1;MULTI_STATEMENTS=1;DFLT_BIGINT_BIND_STR=1;NO_SSPS=1;CAN_HANDLE_EXP_PWD=1;TABLE=tblKunstnere
"

The MySQL table tblKunstnere is created "
CREATE TABLE `tblKunstnere` (
  `KunstnerID` int(11) NOT NULL,
  `Betegnelse` varchar(50) COLLATE utf8_danish_ci NOT NULL,
  `Type` tinyint(4) unsigned NOT NULL COMMENT '0=Gruppe\\n1=Konstellation\\n2=Medlem\\n3=Solist',
  `Separator` tinyint(4) unsigned NOT NULL DEFAULT '1',
  `Fra` smallint(6) DEFAULT NULL,
  `Til` smallint(6) DEFAULT NULL,
  `BorgerligtNavn` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
  `Kommentar` text COLLATE utf8_danish_ci,
  PRIMARY KEY (`KunstnerID`),
  KEY `IX_Betegnelse` (`Betegnelse`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
"

ODBC version 8.0.18 and previous: no problems.
The long text field "Kommentar" (comment in danish) is the center problem. Without that field no problem using 8.0.20.
My host (simply.com) database is version 5.7.29-32-log.

How to repeat:
Execute sql from MS-Access inserting into linked table with long text field.

32 bit Connector and Office.

Version 8.0.21 - same problem.
Also editing the long text field in a linked in table i Access will cause a similar problem.

Hello Finn Nielsen,

Thank you for the bug report.
Could you please provide a complete test case and code file which hit the issue reported along with the "SITE_tblKunstnere" table structure. Thanks.

Regards,
Ashwini Patil

Create the table "tblKunstnere" on a MySQL server using "CreateTable.sql".

In Access:
Open "TestMySqlODBC.accdb".
Insert linked table for the MySQL table "tblKunstnere" and name the linked table "SITE_tblKunstnere".
Run code in code module.

regards

Hello Finn Nielsen,

Thank you for the details.
I tried to reproduce your issue on windows 10 with MySQL server 8.0.21 with the details provided but I am not seeing any issues at my end.

Regards,
Ashwini Patil

Must be my providers old version 5.7.31-34-log, that's causing the problem then. I'll see what i can do.

Thanks for your effort

Regards

Hello

I found a workaround (if that's what it is).
Without checkbox "Prepare statements on the client" set (NO_SSPS=1) in connection, i have no problem using 8.0.21 (except the almost double time spent on execution).

regards