← Back to team overview

maria-discuss team mailing list archive

Re: enforce_storage_engine galera cluster

 

Thank you all, we're going to open a bug as soon as possible;

@Federico: Thanks for the suggestion, but we are interested in forcing engine substitution, because this way a table creation made by a generic user can be issued using both InnoDB and MyISAM (users can't be always aware of which engine is actually reported in a scripted creation) can produce only InnoDB tables that can be actually replicated on other nodes (as you stated previously). Anyway, this bug seems to break our plans, so we should report the bug and find another way in the meanwhile....in addition, MyISAM replication is described as experimental and we don't want to use it in such early development phase.

Thank you very much again for the support.

On 22/12/2015 13:03, Roberto Spadim wrote:
in my opnion, itś a bug, report to jira issue tracker

2015-12-22 10:00 GMT-02:00 Federico Razzoli <federico_raz@xxxxxxxx <mailto: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
    <mailto:michele.tota@xxxxxxxxxxx>> ha scritto:

     Oggetto: Re: [Maria-discuss] enforce_storage_engine galera cluster
     A: maria-discuss@xxxxxxxxxxxxxxxxxxx
    <mailto: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
    <mailto: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
    <https://launchpad.net/%7Emaria-discuss>
     Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
    <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
     Unsubscribe : https://launchpad.net/~maria-discuss
    <https://launchpad.net/%7Emaria-discuss>
     More help   : https://help.launchpad.net/ListHelp





     -----Segue allegato-----

     _______________________________________________
     Mailing list: https://launchpad.net/~maria-discuss
    <https://launchpad.net/%7Emaria-discuss>
     Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
    <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
     Unsubscribe : https://launchpad.net/~maria-discuss
    <https://launchpad.net/%7Emaria-discuss>
     More help   : https://help.launchpad.net/ListHelp

    _______________________________________________
    Mailing list: https://launchpad.net/~maria-discuss
    <https://launchpad.net/%7Emaria-discuss>
    Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
    <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
    Unsubscribe : https://launchpad.net/~maria-discuss
    <https://launchpad.net/%7Emaria-discuss>
    More help   : https://help.launchpad.net/ListHelp




--
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle


_______________________________________________
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


References