Re: [Maria-discuss] 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/Fi...
Hope that this helps.
Federico
-------------------------------------------- Mar 22/12/15, Michele Tota michele.tota@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_raz@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.tota@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.tota@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
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@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/Fi...
Hope that this helps.
Federico
-------------------------------------------- Mar 22/12/15, Michele Tota michele.tota@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_raz@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.tota@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.tota@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
participants (2)
-
Federico Razzoli -
Justin Swanhart