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

Question

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: ODBC driver information

ODBC connector 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!

Tomerikoo · Accepted Answer · 2021-10-12 11:04:24Z

1

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

For updating data:

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

edited Oct 12, 2021 at 11:04

Tomerikoo

19.6k16 gold badges57 silver badges68 bronze badges

answered Oct 12, 2021 at 9:50

ehoefler

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jbz Over a year ago

This saved my bacon. Didn't realize this alternative way of doing it existed. Thank you!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related