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
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| Link |
This issue relates to |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Comment | [ CREATE TABLE is not a problem to avoid. However, some of the other DLL-statements are harder. This is because to find out used storage engine we need to do it after open_and_lock_tables. However, that is bad for concurrent DDL. I will see if I can start TOI but when storage engine is exposed fail if storage engine does not support Galera replication. This will not help to PROCEDURE, EVENT, FUNCTION -statements as tables are opened only when they are executed. Similarly, this will not help to USER, ROLE, SERVER, DATABASE -statements as they do not really have storage engine. ] |
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
| Assignee | Jan Lindström [ jplindst ] | Sergey Vojtovich [ svoj ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergey Vojtovich [ svoj ] | Jan Lindström [ jplindst ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Jan Lindström [ jplindst ] | Seppo Jaakola [ seppo ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Comment | [ A comment with security level 'Developers' was removed. ] |
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
| Assignee | Seppo Jaakola [ seppo ] | |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Jan Lindström [ jplindst ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| issue.field.resolutiondate | 2020-02-11 13:49:30.0 | 2020-02-11 13:49:30.238 |
| Fix Version/s | 10.5.1 [ 24029 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 98195 ] | MariaDB v4 [ 134043 ] |
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.