Unable to update linked MySQL table from SQL Server with MySQL Connector/ODBC 8.0

Question

question

ehoefler asked Sep 7, '21

Unable to update linked MySQL table from SQL Server with MySQL Connector/ODBC 8.0

I'm using a linked server on a SQL 2016 server to read and write data in different MySQL tables.

With MySQL Connector/ODBC 5.3 everything works fine, after updating the MySQL Connector/ODBC to latest version 8.0.26 due to security reasons updating the MySQL data causes an error! Selecting MySQL data still works fine, as well as inserting new data; updating and deleting MySQL data is not possible any longer.

In different threads I found hints for correct structure of MySQL tables as requirement to maintain the data via linked server. Tables must have a primary key column (with no auto increment) and at least one column with type timestamp must exist. So I created following simple test table:

CREATE TABLE `test_odbc_3` (
  `TDBC_ID` int(11) NOT NULL,
  `TDBC_DESC` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `TDBC_TSU` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TDBC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_c

For maintenance of MySQL data I use the OPENQUERY syntax as follows:

Inserting a new row:

INSERT OPENQUERY(TDBAMYSQLTEST, 'SELECT TDBC_ID, TDBC_DESC, TDBC_TSU FROM admin_db.test_odbc_3')
VALUES (24,'row 4','2019-05-19 14:22:41)

works fine!

Selecting all rows:

SELECT * 
FROM   OPENQUERY( TDBAMYSQLTEST, 'SELECT TDBC_ID, TDBC_DESC, TDBC_TSU FROM admin_db.test_odbc_3')

works fine – result is:
TDBC_ID TDBC_DESC TDBC_TSU
21 row 1 2009-04-17 14:11:41.0000000
22 row 2 2009-04-17 14:11:41.0000000
23 row 3 2009-04-17 14:11:41.0000000
24 row 4 2019-05-19 14:22:41.0000000

Trying to update a row in this table causes the following error:

UPDATE OPENQUERY( TDBAMYSQLTEST, 'SELECT TDBC_ID, TDBC_DESC, TDBC_TSU FROM admin_db.test_odbc_3 WHERE TDBC_ID = 23')
SET    TDBC_DESC = 'mydesc'
WHERE  TDBC_ID = 23

Msg 7343, Level 16, State 4, Line 10
The OLE DB provider "MSDASQL" for linked server "TDBAMYSQLTEST" could not UPDATE table "[MSDASQL]". Unknown provider error

Based on different threads I checked and set the configuration of environment as well.

MySQL Connector/ODBC configuration:

Enabled Provider Options:

Dynamic parameter
Nested queries
Level zero only
Allow inprocess
Index as access path

Linked Server Properties set to True:

Data Access
RPC
RPC Out

Anybody has an idea about how to get running with update MySQL data again? Thnx in advance for any help!

update mysql odbc openquery linked server

1631015111193.png (29.9 KiB)

1631015170838.png (59.1 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-10-12T09:41:07Z

ehoefler answered Oct 12, '21

After a very long time with try and error I could find the solution!

Instead of using QPENQUERY it's necessary to use EXEC AT syntax as follows:

For selecting data

EXEC('SELECT * FROM  admin_db.test_odbc_3 WHERE TDBC_ID = 3') AT TDBAMYSQLTEST

works fine.

And for updating data

EXEC('UPDATE admin_db.test_odbc_3 set TDBC_DESC = ''mynew_value'' WHERE TDBC_ID = 3') AT TDBAMYSQLTEST

works fine as well!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question