Results 1 to 9 of 9

Thread: Cannot Insert,Update in OPENQUERY.

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2016
    Posts
    279

    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'

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  3. #3
    coder. Lord Orwell's Avatar
    Join Date
    Feb 2001
    Location
    Elberfeld, IN
    Posts
    7,621

    Re: Cannot Insert,Update in OPENQUERY.

    Quote Originally Posted by jmcilhinney View Post
    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.
    My light show youtube page (it's made the news) www.youtube.com/@artnet2twinkly
    Contact me on the socials www.facebook.com/lordorwell

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2016
    Posts
    279

    Re: Cannot Insert,Update in OPENQUERY.

    Quote Originally Posted by jmcilhinney View Post
    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.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2016
    Posts
    279

    Re: Cannot Insert,Update in OPENQUERY.

    Quote Originally Posted by Lord Orwell View Post
    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?

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2016
    Posts
    279

    Re: Cannot Insert,Update in OPENQUERY.

    'GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' '' : says in mysql server

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2016
    Posts
    279

    Re: Cannot Insert,Update in OPENQUERY.

    Quote Originally Posted by jmcilhinney View Post
    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

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Cannot Insert,Update in OPENQUERY.

    Quote Originally Posted by earvinnill View Post
    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.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2016
    Posts
    279

    Re: Cannot Insert,Update in OPENQUERY.

    Quote Originally Posted by jmcilhinney View Post
    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
  •  



Click Here to Expand Forum to Full Width