MySQL Forums
Forum List  »  Connector/ODBC

Unable to update linked MySQL table from SQL Server with MySQL Connector/ODBC 8.0
Posted by: Ernst Höfler
Date: September 08, 2021 05:12AM

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!

Options: ReplyQuote


Subject
Written By
Posted
Unable to update linked MySQL table from SQL Server with MySQL Connector/ODBC 8.0
September 08, 2021 05:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.