Can Select but not Insert from SQL Server to MySQL

Question

I'm having a problem with inserting data from SQL Server to MySQL. I saw this syntax that helped others:

EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MySQL',
@provider='MSDASQL', @provstr='DRIVER={MySQL ODBC 5.1
Driver};SERVER=HOST;Port=3306;USER=uid;PASSWORD=pw;OPTION=3;DATABASE=mydb;

but the error is still:

OLE DB provider "MSDASQL" for linked server "MYSQL1" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.5.44-MariaDB-log]Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.".

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "MSDASQL" for linked server "MYSQL1" could not INSERT INTO table "[MSDASQL]".

It says here that the BINLOG_FORMAT is equal to STATEMENT but when I checked it, it is set to MIXED.

Found out a solution for these, they said that the BINLOG_FORMAT must be ROW but then I'm having a doubt or difficulty to edit in my.cnf because I don't know what will happen if I changed the binlog format from mixed to row.

How can I resolve this problem?

Answer 1

Found out a solution for these, they said that the BINLOG_FORMAT must be "ROW" but then I'm having a doubt or difficulty to edit in my.cnf because I don't know what will happen if I changed the binlog format from mixed to row.

Try it. If MySQL is going to eat your data, it's not going to care about that option -- it'll just plow forward regardless of your configuration. BINLOG_FORMAT says explicitly

If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.

That means you have two options, either use the default isolation level of REPEATABLE READ or set the option.

On a side note, MySQL Isolation Levels are pretty borked and probably don't do what you want anyway