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!

Answer 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