[MDEV-9312] storage engine not enforced during galera cluster replication - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.1.9
Fix Version/s: 10.1.18
Component/s: Galera, Replication
Labels:
None
Environment:
CentOS 7.1, installed from repo: http://yum.mariadb.org/10.1/centos7-amd64

Sprint:
10.1.18

Description

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

MDEV-34514 Storage engine is not forced during replication

Closed

links to

Github commit

Activity

Ascending order - Click to sort in descending order

Orru created issue - 2015-12-22 16:46

Orru made changes - 2015-12-22 16:52

Field	Original Value	New Value
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....	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....

Elena Stepanova made changes - 2015-12-26 17:41

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....

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:

{noformat}
enforce_storage_engine='InnoDB'
wsrep_on=ON
sql_mode=''
{noformat}

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

{noformat}
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)
{noformat}

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:

{noformat}
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)
{noformat}

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....

Elena Stepanova made changes - 2015-12-26 17:42

Assignee

Nirbhay Choubey [ nirbhay_c ]

Nirbhay Choubey (Inactive) made changes - 2016-06-03 23:05

Status

Open [ 1 ]

In Progress [ 3 ]

Nirbhay Choubey (Inactive) made changes - 2016-06-06 12:39

Assignee	Nirbhay Choubey [ nirbhay_c ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2016-06-17 06:05

Fix Version/s

10.1 [ 16100 ]

Nirbhay Choubey (Inactive) made changes - 2016-06-29 21:12

Fix Version/s		10.1.16 [ 22019 ]
Fix Version/s	10.1 [ 16100 ]

Sergei Golubchik made changes - 2016-06-29 22:19

Fix Version/s		10.1 [ 16100 ]
Fix Version/s	10.1.16 [ 22019 ]

Nirbhay Choubey (Inactive) made changes - 2016-09-20 19:08

Sprint

10.1.18 [ 98 ]

Sergei Golubchik made changes - 2016-09-23 14:37

Assignee	Sergei Golubchik [ serg ]	Nirbhay Choubey [ nirbhay_c ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Nirbhay Choubey (Inactive) made changes - 2016-09-24 04:32

Assignee	Nirbhay Choubey [ nirbhay_c ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2016-09-27 07:25

Assignee	Sergei Golubchik [ serg ]	Nirbhay Choubey [ nirbhay_c ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Nirbhay Choubey (Inactive) made changes - 2016-09-27 13:05

Assignee	Nirbhay Choubey [ nirbhay_c ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2016-09-28 16:16

Assignee	Sergei Golubchik [ serg ]	Nirbhay Choubey [ nirbhay_c ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Nirbhay Choubey (Inactive) made changes - 2016-09-29 02:32

Fix Version/s		10.1.18 [ 22110 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Nirbhay Choubey (Inactive) made changes - 2016-09-29 02:35

Remote Link

This issue links to "Github commit (Web Link)" [ 27610 ]

Nirbhay Choubey (Inactive) made changes - 2016-09-29 02:35

Remote Link

This issue links to "Github commit (Web Link)" [ 27611 ]

Nirbhay Choubey (Inactive) made changes - 2016-09-29 02:36

Remote Link

This issue links to "Github commit (Web Link)" [ 27610 ]

Nirbhay Choubey (Inactive) made changes - 2016-09-29 02:36

Remote Link

This issue links to "Github commit (Web Link)" [ 27611 ]

Nirbhay Choubey (Inactive) made changes - 2016-09-29 02:37

Remote Link

This issue links to "Github commit (Web Link)" [ 27612 ]

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 73306 ]

MariaDB v4 [ 149932 ]

Alice Sherepa made changes - 2024-07-02 11:04

Link

This issue relates to ~~MDEV-34514~~ [ ~~MDEV-34514~~ ]

People

Assignee:: Nirbhay Choubey (Inactive)

Reporter:: Orru

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2015-12-22 16:46

Updated:: 2024-07-02 11:04

Resolved:: 2016-09-29 02:32

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration