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.
Driver is: MySQL ODBC 8.0 Unicode Driver (8.00.26.00)
Connector: Set option 'return matched rows instead of affected rows'
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!