maria-discuss team mailing list archive

[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