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_...@yahoo.it> 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.t...@lnf.infn.it> ha scritto: > > Oggetto: Re: [Maria-discuss] enforce_storage_engine galera cluster > A: maria-discuss@lists.launchpad.net > 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_...@yahoo.it>: > > 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.t...@lnf.infn.it> > ha scritto: > > > > Oggetto: Re: [Maria-discuss] > enforce_storage_engine galera > cluster > > A: maria-discuss@lists.launchpad.net > > 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.t...@lnf.infn.it> > > 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@lists.launchpad.net > > 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@lists.launchpad.net > > Unsubscribe : https://launchpad.net/~maria-discuss > > More help : https://help.launchpad.net/ListHelp > > > > > _______________________________________________ > > Mailing list: https://launchpad.net/~maria-discuss > > Post to : maria-discuss@lists.launchpad.net > > 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@lists.launchpad.net > 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@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
