← Back to team overview

maria-discuss team mailing list archive

Re: enforce_storage_engine galera cluster

 

I didn't suggest to use MyISAM :)

Now I understand your purpose. If you use MaxScale, you can use a filter and replace MyISAM with InnoDB. Something like:
/engine( )*=( *)myisam/i
And you can use similar regexps for each storage engine.

The filter that you need is this one:
https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Filters/Regex-Filter.md

Hope that this helps.

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, 14:29
 
 
     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>:
 
           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
               
             
           
         
       
       
 
       
       
 
       _______________________________________________
 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


Follow ups