question

ehoefler avatar image
ehoefler asked

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:

1631015111193.png1631015170838.png

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!

updatemysqlodbcopenquerylinked server
1631015111193.png (29.9 KiB)
1631015170838.png (59.1 KiB)
10 |1200 characters needed characters left characters exceeded

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

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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