-
Oct 23rd, 2017, 08:37 PM
#1
Thread Starter
Hyperactive Member
Cannot Insert,Update in OPENQUERY.
I have a linked server (mysql) named MYSQL. I can select data from it but can't insert.
The error is:
OLE DB provider "MSDASQL" for linked server "MYSQL" 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 4, Line 1
The OLE DB provider "MSDASQL" for linked server "MYSQL" could not UPDATE table "[MSDASQL]".
CODE USED:
Code:
UPDATE OPENQUERY(MYSQL, 'Select * from test.earvin where age=12') SET name='earvin'
-
Oct 23rd, 2017, 08:56 PM
#2
Re: Cannot Insert,Update in OPENQUERY.
Did you read this:
impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
Did you investigate what 'BINLOG_FORMAT = STATEMENT' means and what "row-based logging" is and how you might configure it on your database? I don't know those things myself but there are plenty of keywords to research there if I did need to.
-
Oct 24th, 2017, 05:40 AM
#3
Re: Cannot Insert,Update in OPENQUERY.
Originally Posted by jmcilhinney
Did you read this:
Did you investigate what 'BINLOG_FORMAT = STATEMENT' means and what "row-based logging" is and how you might configure it on your database? I don't know those things myself but there are plenty of keywords to research there if I did need to.
it looks like the account logging into MySQL only has read access to that database. MySQL has user by user permission levels for everything possible you can do. It's possible for example to have the ability to change but not delete. If you created the database you configured your user account wrong.
-
Oct 24th, 2017, 08:06 PM
#4
Thread Starter
Hyperactive Member
Re: Cannot Insert,Update in OPENQUERY.
Originally Posted by jmcilhinney
Did you read this:
Did you investigate what 'BINLOG_FORMAT = STATEMENT' means and what "row-based logging" is and how you might configure it on your database? I don't know those things myself but there are plenty of keywords to research there if I did need to.
I've searched and it says the binlog format is better in MIXED, but when I checked, it is already in mixed. I don't wnat to messed up the server because there are some data on it.
-
Oct 24th, 2017, 08:15 PM
#5
Thread Starter
Hyperactive Member
Re: Cannot Insert,Update in OPENQUERY.
Originally Posted by Lord Orwell
it looks like the account logging into MySQL only has read access to that database. MySQL has user by user permission levels for everything possible you can do. It's possible for example to have the ability to change but not delete. If you created the database you configured your user account wrong.
even though i am using root?
-
Oct 24th, 2017, 08:27 PM
#6
Thread Starter
Hyperactive Member
Re: Cannot Insert,Update in OPENQUERY.
'GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' '' : says in mysql server
-
Oct 24th, 2017, 10:07 PM
#7
Thread Starter
Hyperactive Member
Re: Cannot Insert,Update in OPENQUERY.
Originally Posted by jmcilhinney
Did you read this:
Did you investigate what 'BINLOG_FORMAT = STATEMENT' means and what "row-based logging" is and how you might configure it on your database? I don't know those things myself but there are plenty of keywords to research there if I did need to.
my binlog format is MIXED. they said it needs to be row for sql to mysql. but the mixed definition means if it is needed to be row the mixed format will changed to row, when needed to be a statment, the mixed will chagned to statement
-
Oct 24th, 2017, 10:30 PM
#8
Re: Cannot Insert,Update in OPENQUERY.
Originally Posted by earvinnill
my binlog format is MIXED. they said it needs to be row for sql to mysql. but the mixed definition means if it is needed to be row the mixed format will changed to row, when needed to be a statment, the mixed will chagned to statement
I don't think it means that exactly. This page says that it will switch under certain circumstances. Maybe what you're doing doesn't satisfy any of those conditions. I'm not sure.
-
Oct 25th, 2017, 12:39 AM
#9
Thread Starter
Hyperactive Member
Re: Cannot Insert,Update in OPENQUERY.
Originally Posted by jmcilhinney
I don't think it means that exactly. This page says that it will switch under certain circumstances. Maybe what you're doing doesn't satisfy any of those conditions. I'm not sure.
and yes i followed what you second in the #2 post. I changed it to ROW and now it is working. Thank you very much.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|