Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
Description
Currently, if wsrep_OSU_method is set to TOI, then Galera Cluster will replicate all DDL statements. This is true even if the effected table does not support Galera replication.
https://mariadb.com/kb/en/library/galera-cluster-system-variables/#wsrep_osu_method
To confirm this, I performed the following test with a 3-node cluster running MariaDB 10.3.16:
Node 1:
|
|
|
SET SESSION wsrep_OSU_method='TOI';
|
USE db1;
|
CREATE TABLE aria_tab (id int primary key) ENGINE=Aria;
|
INSERT INTO aria_tab VALUES (1);
|
SELECT * FROM aria_tab;
|
|
|
Node 2:
|
|
|
USE db1;
|
SELECT * FROM aria_tab;
|
INSERT INTO aria_tab VALUES (2);
|
SELECT * FROM aria_tab;
|
|
|
Node 3:
|
|
|
USE db1;
|
SELECT * FROM aria_tab;
|
INSERT INTO aria_tab VALUES (3);
|
SELECT * FROM aria_tab;
|
|
|
Node 1:
|
|
|
SELECT * FROM aria_tab;
|
TRUNCATE TABLE aria_tab;
|
SELECT * FROM aria_tab;
|
|
|
|
|
Node 2:
|
|
|
|
|
SELECT * FROM aria_tab;
|
|
|
|
|
Node 3:
|
|
|
|
|
SELECT * FROM aria_tab;
|
First, the output of the CREATE and INSERT portion:
Node 1:
|
|
|
|
|
MariaDB [(none)]> SET SESSION wsrep_OSU_method='TOI';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [(none)]> USE db1;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Database changed
|
MariaDB [db1]> CREATE TABLE aria_tab (id int primary key) ENGINE=Aria;
|
Query OK, 0 rows affected (0.010 sec)
|
|
|
MariaDB [db1]> INSERT INTO aria_tab VALUES (1);
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [db1]> SELECT * FROM aria_tab;
|
+----+
|
| id |
|
+----+
|
| 1 |
|
+----+
|
1 row in set (0.000 sec)
|
|
|
|
|
Node 2:
|
|
|
|
|
MariaDB [(none)]> USE db1;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Database changed
|
MariaDB [db1]> SELECT * FROM aria_tab;
|
Empty set (0.000 sec)
|
|
|
MariaDB [db1]> INSERT INTO aria_tab VALUES (2);
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [db1]> SELECT * FROM aria_tab;
|
+----+
|
| id |
|
+----+
|
| 2 |
|
+----+
|
1 row in set (0.000 sec)
|
|
|
|
|
Node 3:
|
|
|
|
|
MariaDB [(none)]> USE db1;
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Database changed
|
MariaDB [db1]> SELECT * FROM aria_tab;
|
Empty set (0.000 sec)
|
|
|
MariaDB [db1]> INSERT INTO aria_tab VALUES (3);
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [db1]> SELECT * FROM aria_tab;
|
+----+
|
| id |
|
+----+
|
| 3 |
|
+----+
|
1 row in set (0.000 sec)
|
The CREATE was replicated, but the INSERTs were not.
Next, the output of the TRUNCATE portion:
Node 1:
|
|
|
|
|
MariaDB [db1]> SELECT * FROM aria_tab;
|
+----+
|
| id |
|
+----+
|
| 1 |
|
+----+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [db1]> TRUNCATE TABLE aria_tab;
|
Query OK, 0 rows affected (0.010 sec)
|
|
|
MariaDB [db1]> SELECT * FROM aria_tab;
|
Empty set (0.000 sec)
|
|
|
|
|
Node 2:
|
|
|
|
|
MariaDB [db1]> SELECT * FROM aria_tab;
|
Empty set (0.000 sec)
|
|
|
|
|
Node 3:
|
|
|
|
|
MariaDB [db1]> SELECT * FROM aria_tab;
|
Empty set (0.000 sec)
|
We can see that the TRUNCATE was also replicated.
It may be a good idea to add a new method to wsrep_OSU_method in which the storage engines of any tables effected by DDL statements are checked. If the underlying table does not support Galera's replication, then the DDL is not replicated in TOI mode.
Usually, this would mean that only DDL statements that effect InnoDB tables should be replicated.
However, if wsrep_replicate_myisam were enabled, then DDL statements that effect MyISAM tables should also be replicated.
https://mariadb.com/kb/en/library/galera-cluster-system-variables/#wsrep_replicate_myisam
If we decide to implement MDEV-20050, then we should also include Aria.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
MDEV-18778 mysql_tzinfo_to_sql does not work correctly in MariaDB Galera
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
GeoffMontee Can you check above behavior, is that acceptable ?
svoj I assigned this to you for now, if you know better reviewer please re-assign.
https://github.com/MariaDB/server/commit/7e99c7007c33f353e548839ab2373770da7a95d7
Fixed test problem on https://github.com/MariaDB/server/commit/3e85066a886a1c47e71d8dffc958615652ce4bf6
Only InnoDB by default but there is exceptions for wsrep_replicate_myisam and future wsrep_replicate_aria.
Review done and approved from my side, with one comment in the github patch about the necessicity of passing create_info for wsrep_to_isolation
Introduced a new wsrep_osu_method='STRICT' that should be for now set on all nodes in a cluster. When this setting is set following DDL-clauses accessing tables not supporting Galera replication are refused:
If one of these operations are tried error ER_GALERA_REPLICATION_NOT_SUPPORTED is returned to the client.