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)
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 Out
Anybody has an idea about how to get running with update MySQL data again? Thnx in advance for any help!