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

Transactional commit not supported by involved engine(s)

Details

    Description

      connection node_2;
      		 
      connection node_1;
      		 
      SET AUTOCOMMIT=ON;
      		 
      SELECT @@autocommit;
      		 
      @@autocommit
      		 
      1
      		 
      SET LOCAL enforce_storage_engine=InnoDB;
      		 
      CREATE TABLE t1(id int not null primary key auto_increment, name varchar(64)) ENGINE=InnoDB;
      		 
      INSERT INTO t1(name) VALUES ('name1'),('name3'),('name6'),('name2');
      		 
      CREATE PROCEDURE sel_proc()
      		 
      BEGIN
      		 
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
      		 
      SELECT * FROM t1;
      		 
      END|
      		 
      CREATE PROCEDURE ins_proc()
      		 
      BEGIN
      		 
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
      		 
      INSERT INTO t1 VALUES ('name_proc');
      		 
      END|
      		 
      SET AUTOCOMMIT=OFF;
      		 
      SELECT @@autocommit;
      		 
      @@autocommit
      		 
      0
      		 
      START TRANSACTION;
      		 
      insert into t1(name) values('name10');
      		 
      select param_list, returns, db, type from mysql.proc where name='sel_proc';
      		 
      param_list	returns	db	type
      		 
      		test	PROCEDURE
      		 
      call ins_proc();
      		 
      COMMIT;
      		 
      galera.jan 'innodb'                [ fail ]
      		 
              Test ended at 2024-04-04 13:35:10
      		 
       
      		 
      CURRENT_TEST: galera.jan
      		 
      mysqltest: At line 37: query 'COMMIT' failed: 1180: Transactional commit not supported by involved engine(s)

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30804 Assertion `state() == s_committing' failed in int wsrep::transaction::ordered_commit()

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            janlindstrom Jan Lindström created issue -
            janlindstrom Jan Lindström made changes -
            Field Original Value New Value
            Description customer upgraded to the latest version 10.6.16-11 ES (from 10.6.9_5-1)
            We have a problem now with one of out applications.
            A lot or all of DML request received the error "Transactional commit not supported by involved engine(s)".
            We only use InnoDB engine and SELECT @@autocommit = 1 ;

            Table definition:

            {code:java}
            CREATE TABLE `subparam` (
            `subplan_id` int(11) NOT NULL,
            `argument` varchar(64) NOT NULL,
            `param_id` int(11) NOT NULL,
            PRIMARY KEY (`subplan_id`,`argument`,`param_id`),
            UNIQUE KEY `subparam_argument` (`subplan_id`,`argument`),
            KEY `subparam_ibfk_2` (`param_id`),
            CONSTRAINT `subparam_ibfk_1` FOREIGN KEY (`subplan_id`) REFERENCES `subplan` (`id`) ON DELETE CASCADE,
            CONSTRAINT `subparam_ibfk_2` FOREIGN KEY (`param_id`) REFERENCES `param` (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
            {code}

            aap code on insert

            {code:java}
            28-02-2024 11:09:25.881 [https-jsse-nio-8443-exec-10] [36mDEBUG[m c.o.i.p.s.subRoutingPlanJaxApp - DatabaseQuery(inserting): sql : 'insert into sure.subparam (subplan_id,param_id,argument) values(?,?,?)', parameters : [174556,20391,'$annoBarrData']
            28-02-2024 11:09:25.881 [https-jsse-nio-8443-exec-10] [36mDEBUG[m c.o.i.p.s.subRoutingPlanJaxApp - Executing query
            28-02-2024 11:09:25.882 [https-jsse-nio-8443-exec-10] [36mDEBUG[m c.o.i.p.s.subRoutingPlanJaxApp - Statement executeUpdate returned: 1
            28-02-2024 11:09:25.882 [https-jsse-nio-8443-exec-10] [36mDEBUG[m c.o.i.p.s.subRoutingPlanJaxApp - Statement didn't return autogenerated keys
            28-02-2024 11:09:25.882 [https-jsse-nio-8443-exec-10] [36mDEBUG[m c.o.i.p.s.subRoutingPlanJaxApp - SRPP insert result null
            28-02-2024 11:09:25.885 [https-jsse-nio-8443-exec-10] [33mWARN [m c.o.i.p.s.subRoutingPlanJaxApp - Insert new subscriptionroutingplan: (conn=2714605) Transactional commit not supported by involved engine(s) [code=1180, state=HY000]
            28-02-2024 11:09:25.885 [https-jsse-nio-8443-exec-10] [36mDEBUG[m c.o.i.p.s.subRoutingPlanJaxApp - Exception inserting subscription routing plan
            java.sql.SQLException: (conn=2714605) Transactional commit not supported by involved engine(s)
            {code}
            janlindstrom Jan Lindström made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24027 ]
            Key MENT-2062 MDEV-33828
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6.16-11 [ 29415 ]
            Project MariaDB Enterprise [ 11500 ] MariaDB Server [ 10000 ]
            janlindstrom Jan Lindström made changes -
            Description {noformat}
            connection node_2;
            connection node_1;
            SET AUTOCOMMIT=ON;
            SELECT @@autocommit;
            @@autocommit
            1
            SET LOCAL enforce_storage_engine=InnoDB;
            CREATE TABLE t1(id int not null primary key auto_increment, name varchar(64)) ENGINE=InnoDB;
            INSERT INTO t1(name) VALUES ('name1'),('name3'),('name6'),('name2');
            CREATE PROCEDURE sel_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            SELECT * FROM t1;
            END|
            CREATE PROCEDURE ins_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            INSERT INTO t1 VALUES ('name_proc');
            END|
            SET AUTOCOMMIT=OFF;
            SELECT @@autocommit;
            @@autocommit
            0
            START TRANSACTION;
            insert into t1(name) values('name10');
            select param_list, returns, db, type from mysql.proc where name='sel_proc';
            param_list returns db type
            test PROCEDURE
            call ins_proc();
            COMMIT;
            galera.MENT-2055 'innodb' [ fail ]
                    Test ended at 2024-04-04 13:35:10

            CURRENT_TEST: galera.MENT-2055
            mysqltest: At line 37: query 'COMMIT' failed: 1180: Transactional commit not supported by involved engine(s)
            {noformat}
            janlindstrom Jan Lindström made changes -
            Description {noformat}
            connection node_2;
            connection node_1;
            SET AUTOCOMMIT=ON;
            SELECT @@autocommit;
            @@autocommit
            1
            SET LOCAL enforce_storage_engine=InnoDB;
            CREATE TABLE t1(id int not null primary key auto_increment, name varchar(64)) ENGINE=InnoDB;
            INSERT INTO t1(name) VALUES ('name1'),('name3'),('name6'),('name2');
            CREATE PROCEDURE sel_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            SELECT * FROM t1;
            END|
            CREATE PROCEDURE ins_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            INSERT INTO t1 VALUES ('name_proc');
            END|
            SET AUTOCOMMIT=OFF;
            SELECT @@autocommit;
            @@autocommit
            0
            START TRANSACTION;
            insert into t1(name) values('name10');
            select param_list, returns, db, type from mysql.proc where name='sel_proc';
            param_list returns db type
            test PROCEDURE
            call ins_proc();
            COMMIT;
            galera.MENT-2055 'innodb' [ fail ]
                    Test ended at 2024-04-04 13:35:10

            CURRENT_TEST: galera.MENT-2055
            mysqltest: At line 37: query 'COMMIT' failed: 1180: Transactional commit not supported by involved engine(s)
            {noformat}             		{noformat}
            connection node_2;
            connection node_1;
            SET AUTOCOMMIT=ON;
            SELECT @@autocommit;
            @@autocommit
            1
            SET LOCAL enforce_storage_engine=InnoDB;
            CREATE TABLE t1(id int not null primary key auto_increment, name varchar(64)) ENGINE=InnoDB;
            INSERT INTO t1(name) VALUES ('name1'),('name3'),('name6'),('name2');
            CREATE PROCEDURE sel_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            SELECT * FROM t1;
            END|
            CREATE PROCEDURE ins_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            INSERT INTO t1 VALUES ('name_proc');
            END|
            SET AUTOCOMMIT=OFF;
            SELECT @@autocommit;
            @@autocommit
            0
            START TRANSACTION;
            insert into t1(name) values('name10');
            select param_list, returns, db, type from mysql.proc where name='sel_proc';
            param_list returns db type
            test PROCEDURE
            call ins_proc();
            COMMIT;
            galera.MENT-2055 'innodb' [ fail ]
                    Test ended at 2024-04-04 13:35:10

            CURRENT_TEST: galera.jan
            mysqltest: At line 37: query 'COMMIT' failed: 1180: Transactional commit not supported by involved engine(s)
            {noformat}
            janlindstrom Jan Lindström made changes -
            Component/s Galera [ 10124 ]
            janlindstrom Jan Lindström made changes -
            janlindstrom Jan Lindström made changes -
            Description {noformat}
            connection node_2;
            connection node_1;
            SET AUTOCOMMIT=ON;
            SELECT @@autocommit;
            @@autocommit
            1
            SET LOCAL enforce_storage_engine=InnoDB;
            CREATE TABLE t1(id int not null primary key auto_increment, name varchar(64)) ENGINE=InnoDB;
            INSERT INTO t1(name) VALUES ('name1'),('name3'),('name6'),('name2');
            CREATE PROCEDURE sel_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            SELECT * FROM t1;
            END|
            CREATE PROCEDURE ins_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            INSERT INTO t1 VALUES ('name_proc');
            END|
            SET AUTOCOMMIT=OFF;
            SELECT @@autocommit;
            @@autocommit
            0
            START TRANSACTION;
            insert into t1(name) values('name10');
            select param_list, returns, db, type from mysql.proc where name='sel_proc';
            param_list returns db type
            test PROCEDURE
            call ins_proc();
            COMMIT;
            galera.MENT-2055 'innodb' [ fail ]
                    Test ended at 2024-04-04 13:35:10

            CURRENT_TEST: galera.jan
            mysqltest: At line 37: query 'COMMIT' failed: 1180: Transactional commit not supported by involved engine(s)
            {noformat}             		{noformat}
            connection node_2;
            connection node_1;
            SET AUTOCOMMIT=ON;
            SELECT @@autocommit;
            @@autocommit
            1
            SET LOCAL enforce_storage_engine=InnoDB;
            CREATE TABLE t1(id int not null primary key auto_increment, name varchar(64)) ENGINE=InnoDB;
            INSERT INTO t1(name) VALUES ('name1'),('name3'),('name6'),('name2');
            CREATE PROCEDURE sel_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            SELECT * FROM t1;
            END|
            CREATE PROCEDURE ins_proc()
            BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
            INSERT INTO t1 VALUES ('name_proc');
            END|
            SET AUTOCOMMIT=OFF;
            SELECT @@autocommit;
            @@autocommit
            0
            START TRANSACTION;
            insert into t1(name) values('name10');
            select param_list, returns, db, type from mysql.proc where name='sel_proc';
            param_list returns db type
            test PROCEDURE
            call ins_proc();
            COMMIT;
            galera.jan 'innodb' [ fail ]
                    Test ended at 2024-04-04 13:35:10

            CURRENT_TEST: galera.jan
            mysqltest: At line 37: query 'COMMIT' failed: 1180: Transactional commit not supported by involved engine(s)
            {noformat}
            janlindstrom Jan Lindström made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            janlindstrom Jan Lindström added a comment -

            https://github.com/MariaDB/server/pull/3180

            janlindstrom Jan Lindström added a comment - https://github.com/MariaDB/server/pull/3180
            janlindstrom Jan Lindström made changes -
            Assignee Jan Lindström [ JIRAUSER53125 ] Julius Goryavsky [ sysprg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            janlindstrom Jan Lindström made changes -
            sysprg Julius Goryavsky made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            sysprg Julius Goryavsky made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            sysprg Julius Goryavsky added a comment -

            Fix merged with head revision: https://github.com/MariaDB/server/commit/7aa86eb1e183178063527c90094bdc2b2ed2c44e

            sysprg Julius Goryavsky added a comment - Fix merged with head revision: https://github.com/MariaDB/server/commit/7aa86eb1e183178063527c90094bdc2b2ed2c44e
            sysprg Julius Goryavsky made changes -
            Fix Version/s 10.5.25 [ 29626 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            sysprg Julius Goryavsky made changes -
            Fix Version/s 10.6.18 [ 29627 ]
            Fix Version/s 10.11.8 [ 29630 ]
            Fix Version/s 11.0.6 [ 29628 ]
            Fix Version/s 11.1.5 [ 29629 ]
            Fix Version/s 11.2.4 [ 29631 ]
            Fix Version/s 11.4.2 [ 29633 ]
            Fix Version/s 11.5.1 [ 29634 ]
            sysprg Julius Goryavsky made changes -
            Fix Version/s 11.5.1 [ 29634 ]
            sysprg Julius Goryavsky made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 202528

            People

              sysprg Julius Goryavsky
              janlindstrom Jan Lindström
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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