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

storage engine not enforced during galera cluster replication

Details

    • 10.1.18

    Description

      Hi,
      we have a problem using enforce_storage_engine variable set to InnoDB.
      We have three servers in cluster (multi masters).
      When we issue create table with engine=MyISAM the engine is correctly enforced to InnoDB only on the node we are connected to. On the other nodes the replicated table is created as MyISAM.
      In our servers configuration (the same on all nodes) we have:

      enforce_storage_engine='InnoDB'
      		 
      wsrep_on=ON
      		 
      sql_mode=''

      Some console output follows.
      On the first node we have:

      MariaDB [galera_test]> SHOW VARIABLES WHERE Variable_name='enforce_storage_engine' OR Variable_name='sql_mode';
      		 
      +------------------------+--------+
      		 
      | Variable_name          | Value  |
      		 
      +------------------------+--------+
      		 
      | enforce_storage_engine | InnoDB |
      		 
      | sql_mode               |        |
      		 
      +------------------------+--------+
      		 
      2 rows in set (0.00 sec)
      		 
       
      		 
      MariaDB [galera_test]> CREATE TABLE table_1(id INTEGER) ENGINE=InnoDB;
      		 
      Query OK, 0 rows affected (0.01 sec)
      		 
       
      		 
      MariaDB [galera_test]> CREATE TABLE table_2(id INTEGER) ENGINE=MyISAM;
      		 
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      		 
       
      		 
      MariaDB [galera_test]> SHOW WARNINGS;
      		 
      +-------+------+-------------------------------------------------+
      		 
      | Level | Code | Message                                         |
      		 
      +-------+------+-------------------------------------------------+
      		 
      | Note  | 1266 | Using storage engine InnoDB for table 'table_2' |
      		 
      +-------+------+-------------------------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [galera_test]>  SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'galera_test';
      		 
      +------------+--------+
      		 
      | TABLE_NAME | ENGINE |
      		 
      +------------+--------+
      		 
      | table_1    | InnoDB |
      		 
      | table_2    | InnoDB |
      		 
      +------------+--------+
      		 
      2 rows in set (0.00 sec)

      Creating table_2 creation we obtain a warning, table_2 is correctly created using InnoDB, and until this point, everything is working great.
      When we check the same table on another cluster node, we have:

      MariaDB [galera_test]> SHOW VARIABLES WHERE Variable_name='enforce_storage_engine' OR Variable_name='sql_mode';
      		 
      +------------------------+--------+
      		 
      | Variable_name          | Value  |
      		 
      +------------------------+--------+
      		 
      | enforce_storage_engine | InnoDB |
      		 
      | sql_mode               |        |
      		 
      +------------------------+--------+
      		 
      2 rows in set (0.00 sec)
      		 
       
      		 
      MariaDB [galera_test]>  SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'galera_test';
      		 
      +------------+--------+
      		 
      | TABLE_NAME | ENGINE |
      		 
      +------------+--------+
      		 
      | table_1    | InnoDB |
      		 
      | table_2    | MyISAM |
      		 
      +------------+--------+
      		 
      2 rows in set (0.00 sec)
      		 
       
      		 
      MariaDB [galera_test]> SHOW TABLE STATUS WHERE Name = 'table_2';
      		 
      +---------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
      		 
      | Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
      		 
      +---------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
      		 
      | table_2 | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 1970324836974591 |         1024 |         0 |           NULL | 2015-12-22 11:08:34 | 2015-12-22 11:08:34 | NULL       | latin1_swedish_ci |     NULL |                |         |
      		 
      +---------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
      		 
      1 row in set (0.00 sec)

      As you can read, creating a MyISAM table (table_2) on the first node produces a correct InnoDB table, but replication of the same table on the other cluster nodes happens using MyISAM engine.
      It seems like engine enforcing is used during creation but not during replication....

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34514 Storage engine is not forced during replication

          • Major - Major loss of function.
          • Closed
          links to

          Web Link Github commit

          Activity

            People

              nirbhay_c Nirbhay Choubey (Inactive)
              Ramon Orru
              Votes:
              1 Vote for this issue
              Watchers:
              6 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.