maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #03221
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
