maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #03219
Re: enforce_storage_engine galera cluster
in my opnion, itś a bug, report to jira issue tracker
2015-12-22 10:00 GMT-02:00 Federico Razzoli <federico_raz@xxxxxxxx>:
> Sounds like a bug. But, bug or not bug, I suggest you set sql_mode =
> 'NO_ENGINE_SUBSTITUTION'. Changes to non-InnoDB tables are NOT replicated.
> (There seems to be a sort of support for MyISAM, but it's unreliable and
> needs to be manually enabled on all nodes)
>
> Federico
>
>
> --------------------------------------------
> Mar 22/12/15, Michele Tota <michele.tota@xxxxxxxxxxx> ha scritto:
>
> Oggetto: Re: [Maria-discuss] enforce_storage_engine galera cluster
> A: maria-discuss@xxxxxxxxxxxxxxxxxxx
> Data: Martedì 22 dicembre 2015, 12:22
>
>
> 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@xxxxxxxxxxx>
> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help : https://help.launchpad.net/ListHelp
>
>
>
>
>
> -----Segue allegato-----
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help : https://help.launchpad.net/ListHelp
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help : https://help.launchpad.net/ListHelp
>
--
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle
Follow ups
References
