Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32354

Got error 170 "It is not possible to log this statement" from storage engine InnoDB on INSERT using scheduler where a regular INSERT will work

    XMLWordPrintable

Details

    Description

      --source include/have_binlog_format_statement.inc
      --source include/have_innodb.inc
      CREATE TABLE t (c INT) ENGINE=InnoDB;
      SET GLOBAL event_scheduler=ON;
      SET GLOBAL tx_isolation='READ-UNCOMMITTED';
      INSERT INTO t VALUES (1);  # OK
      CREATE EVENT e ON SCHEDULE AT CURRENT_TIMESTAMP DO INSERT INTO t VALUES (2);  # Will fail when executed
      SELECT SLEEP(2);
      SELECT * FROM t;  # 1 Only
      

      Leads to:

      11.3.0 905c3d61e18ae6222d0d195c43d335046eec65d9 (Debug)

      2023-10-05 17:30:35 6 [Note] Event Scheduler: scheduler thread started with id 6
      2023-10-05 17:30:36 6 [Note] Event Scheduler: Last execution of test.e. Dropping.
      2023-10-05 17:30:36 7 [Note] Event Scheduler: Dropping test.e
      2023-10-05 17:30:36 7 [ERROR] Event Scheduler: [root@localhost][test.e] 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.
      2023-10-05 17:30:36 7 [ERROR] Event Scheduler: [root@localhost][test.e] Got error 170 "It is not possible to log this statement" from storage engine InnoDB
      2023-10-05 17:30:36 7 [Note] Event Scheduler: [root@localhost][test.e] At line 1 in test.e
      

      Bug confirmed present in:
      MariaDB: 10.4.32 (dbg), 10.4.32 (opt), 10.5.23 (dbg), 10.5.23 (opt), 10.6.16 (dbg), 10.6.16 (opt), 10.9.8 (dbg), 10.9.8 (opt), 10.10.7 (dbg), 10.10.7 (opt), 10.11.6 (dbg), 10.11.6 (opt), 11.0.4 (dbg), 11.0.4 (opt), 11.1.3 (dbg), 11.1.3 (opt), 11.2.2 (dbg), 11.2.2 (opt), 11.3.0 (dbg), 11.3.0 (opt)

      Attachments

        Activity

          People

            Elkin Andrei Elkin
            Roel Roel Van de Paar
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.