On Mon, Dec 21, 2015 at 12:47 PM, Michele Tota <michele.tota@lnf.infn.it> wrote:

Hello Nirbhay, thanks for your reply. We have the 10.1.9 mariadb version; the bug that your refers is similar but in our case the enforcing works properly on the node used for the initial write, while replicated writes on other nodes generate tables using another engine.

I tried the following on 10.1.9 : Version: Server version: 10.1.9-MariaDB-debug-log Source distribution Node 1: MariaDB [test]> SET @@enforce_storage_engine=INNODB; Query OK, 0 rows affected (0.01 sec) MariaDB [test]> CREATE TABLE t1(i INT) ENGINE=INNODB; Query OK, 0 rows affected (0.07 sec) MariaDB [test]> CREATE TABLE t2(i INT) ENGINE=MYISAM; ERROR 1286 (42000): Unknown storage engine 'MyISAM' Node 2: MariaDB [test]> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) Do you get different results? Or your's is a different scenario altogether? Best, Nirbhay

On 21/12/2015 18:41, Nirbhay Choubey wrote:

Hi!

On Mon, Dec 21, 2015 at 12:23 PM, Michele Tota <michele.tota@lnf.infn.it> wrote:

...

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.

This is our servers configuration, the same on all nodes:

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

How can we enforce InnoDB on all nodes?

What's the MariaDB server version? A similar issue was fixed in 10.1.8.

https://mariadb.atlassian.net/browse/MDEV-8831

Best, Nirbhay

...

Thanks, Michele

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Thanks guys, as Ian has pointed out, in recent releases sql_mode contains by default NO_ENGINE_SUBSTITUTION so in our deployment we set sql_mode to blank to gracefully use InnoDB when MyISAM table creation is issued. To be clearer on the problem we're experiencing, I'll paste some console output. 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) Please note that in table_2 creation we obtain a warning (not an error), and 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 note, 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.... On 21/12/2015 20:34, Ian Gilfillan wrote:

On 21/12/2015 20:10, Nirbhay Choubey wrote:

...

On Mon, Dec 21, 2015 at 12:47 PM, Michele Tota <michele.tota@lnf.infn.it <mailto:michele.tota@lnf.infn.it>> wrote:

Hello Nirbhay, thanks for your reply. We have the 10.1.9 mariadb version; the bug that your refers is similar but in our case the enforcing works properly on the node used for the initial write, while replicated writes on other nodes generate tables using another engine.

I tried the following on 10.1.9 :

Version: Server version: 10.1.9-MariaDB-debug-log Source distribution

Node 1: MariaDB [test]> SET @@enforce_storage_engine=INNODB; Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE t1(i INT) ENGINE=INNODB; Query OK, 0 rows affected (0.07 sec)

MariaDB [test]> CREATE TABLE t2(i INT) ENGINE=MYISAM; ERROR 1286 (42000): Unknown storage engine 'MyISAM'

Node 2: MariaDB [test]> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec)

Do you get different results? Or your's is a different scenario altogether?

Best, Nirbhay

From the sounds of it, the sql_mode option NO_ENGINE_SUBSTITUTION is set differently in Nirbhay and Michele's scenarios, so the MyISAM table is converted to InnoDB with a warning in one, and fails in the other.

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp