maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #03222
Re: enforce_storage_engine galera cluster
I think Percona with Galera has the option to force all tables to innodb.
I seem to recall that, but it has been months since I touched anything Percona related. What a blessing that turned out to be.
Sent from my iPhone
> On Dec 22, 2015, at 11:02 AM, Federico Razzoli <federico_raz@xxxxxxxx> wrote:
>
> 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
>
> _______________________________________________
> 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
