[MDEV-20715] Implement system variable to disallow local GTIDs in Galera - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Fix Version/s: 10.6.0
Component/s: Galera, Replication, wsrep
Labels:
None

Description

It is possible to use Galera Cluster with replication. For example:

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/configuring-mariadb-replication-between-two-mariadb-galera-clusters/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-gtids-with-mariadb-galera-cluster/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster-using-mariadb-replica/

However, if the slave of the Galera Cluster needs to fail over to a new master node, then that can be complicated by the fact that the different nodes may have different GTID positions due to local transactions.

Nodes can have GTIDs for local transactions in the following scenarios:

A DDL statement is executed with wsrep_OSU_method=RSU set.
A DML statement writes to a non-InnoDB table.
A DML statement writes to an InnoDB table with wsrep_on=OFF set.

We may want to come up with a way to disallow any statements that would cause local GTIDs in Galera Cluster.

For example, we could implement a new system variable called wsrep_disallow_local_gtids. If a user tries to execute a statement that would cause a local GTID, then it could trigger an error. For example:

SET GLOBAL wsrep_disallow_local_gtids=ON;

Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_OSU_method='RSU';

Query OK, 0 rows affected (0.000 sec)

ALTER TABLE tab FORCE;

ERROR X (Y): You can't execute statements that would generate local GTIDs when

  wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0"

  to execute this statement.

Or:

SET GLOBAL wsrep_disallow_local_gtids=ON;

Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;

Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');

ERROR X (Y): You can't execute statements that would generate local GTIDs when

  wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0"

  to execute this statement.

But users could still work around this by disabling binary logging. For example:

SET GLOBAL wsrep_disallow_local_gtids=ON;

Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;

Query OK, 0 rows affected (0.000 sec)

SET SESSION sql_log_bin=0;

Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');

Query OK, 1 rows affected (0.000 sec)

Attachments

Issue Links

relates to

MDEV-20720 Galera: Replicate MariaDB GTID to other nodes in the cluster

Closed

MDEV-22905 Support DO_DOMAIN_IDS and IGNORE_DOMAIN_IDS in replication handshake

Closed

MDEV-25024 Document wsrep_mode to disallow local GTID

Closed

MXS-2580 Clarify requirements of binlog router secondary masters

Closed

Activity

Ascending order - Click to sort in descending order

Geoff Montee (Inactive) created issue - 2019-10-01 18:58

Geoff Montee (Inactive) made changes - 2019-10-01 18:59

Field	Original Value	New Value
Link		This issue relates to ~~MENT-400~~ [ ~~MENT-400~~ ]

Geoff Montee (Inactive) made changes - 2019-10-01 19:01

Description

It is possible to use Galera Cluster with replication. For example:

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/configuring-mariadb-replication-between-two-mariadb-galera-clusters/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-gtids-with-mariadb-galera-cluster/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster-using-mariadb-replica/

However, if the slave of the Galera Cluster needs to fail over to a new master node, then that can be complicated by the fact that the different nodes may have different GTID positions due to local transactions.

Nodes can have GTIDs for local transactions in the following scenarios:

* A DDL statement is executed with {{wsrep_OSU_method=RSU}} set.
* A DML statement writes to a non-InnoDB table.
* A DML statement writes to an InnoDB table with {{wsrep_on=OFF}} set.

We may want to come up with a way to disallow any statements that would cause local GTIDs in Galera Cluster.

For example, we could implement a new system variable called {{wsrep_disallow_local_gtids}}. If a user tries to execute a statement that would cause a local GTID, then it could trigger an error. For example:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_OSU_method='RSU';
Query OK, 0 rows affected (0.000 sec)

ALTER TABLE tab FORCE;
ERROR X (Y): You can't execute statements that would generate local GTIDs when wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0" to execute this statement.
{code}

Or:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;
Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');
ERROR X (Y): You can't execute statements that would generate local GTIDs when wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0" to execute this statement.
{code}

But users could still work around this by enabling binary logging. For example:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;
Query OK, 0 rows affected (0.000 sec)

SET SESSION sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');
Query OK, 1 rows affected (0.000 sec)
{code}

It is possible to use Galera Cluster with replication. For example:

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/configuring-mariadb-replication-between-two-mariadb-galera-clusters/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-gtids-with-mariadb-galera-cluster/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster-using-mariadb-replica/

However, if the slave of the Galera Cluster needs to fail over to a new master node, then that can be complicated by the fact that the different nodes may have different GTID positions due to local transactions.

Nodes can have GTIDs for local transactions in the following scenarios:

* A DDL statement is executed with {{wsrep_OSU_method=RSU}} set.
* A DML statement writes to a non-InnoDB table.
* A DML statement writes to an InnoDB table with {{wsrep_on=OFF}} set.

We may want to come up with a way to disallow any statements that would cause local GTIDs in Galera Cluster.

For example, we could implement a new system variable called {{wsrep_disallow_local_gtids}}. If a user tries to execute a statement that would cause a local GTID, then it could trigger an error. For example:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_OSU_method='RSU';
Query OK, 0 rows affected (0.000 sec)

ALTER TABLE tab FORCE;
ERROR X (Y): You can't execute statements that would generate local GTIDs when
wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0" to execute this statement.
{code}

Or:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;
Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');
ERROR X (Y): You can't execute statements that would generate local GTIDs when
wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0" to execute this statement.
{code}

But users could still work around this by disabling binary logging. For example:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;
Query OK, 0 rows affected (0.000 sec)

SET SESSION sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');
Query OK, 1 rows affected (0.000 sec)
{code}

Geoff Montee (Inactive) made changes - 2019-10-01 19:02

Description

It is possible to use Galera Cluster with replication. For example:

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/configuring-mariadb-replication-between-two-mariadb-galera-clusters/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-gtids-with-mariadb-galera-cluster/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster-using-mariadb-replica/

However, if the slave of the Galera Cluster needs to fail over to a new master node, then that can be complicated by the fact that the different nodes may have different GTID positions due to local transactions.

Nodes can have GTIDs for local transactions in the following scenarios:

* A DDL statement is executed with {{wsrep_OSU_method=RSU}} set.
* A DML statement writes to a non-InnoDB table.
* A DML statement writes to an InnoDB table with {{wsrep_on=OFF}} set.

We may want to come up with a way to disallow any statements that would cause local GTIDs in Galera Cluster.

For example, we could implement a new system variable called {{wsrep_disallow_local_gtids}}. If a user tries to execute a statement that would cause a local GTID, then it could trigger an error. For example:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_OSU_method='RSU';
Query OK, 0 rows affected (0.000 sec)

ALTER TABLE tab FORCE;
ERROR X (Y): You can't execute statements that would generate local GTIDs when
wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0" to execute this statement.
{code}

Or:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;
Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');
ERROR X (Y): You can't execute statements that would generate local GTIDs when
wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0" to execute this statement.
{code}

But users could still work around this by disabling binary logging. For example:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;
Query OK, 0 rows affected (0.000 sec)

SET SESSION sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');
Query OK, 1 rows affected (0.000 sec)
{code}

It is possible to use Galera Cluster with replication. For example:

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/configuring-mariadb-replication-between-two-mariadb-galera-clusters/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-gtids-with-mariadb-galera-cluster/

https://mariadb.com/kb/en/library/documentation/replication/galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster/using-mariadb-replication-with-mariadb-galera-cluster-using-mariadb-replica/

However, if the slave of the Galera Cluster needs to fail over to a new master node, then that can be complicated by the fact that the different nodes may have different GTID positions due to local transactions.

Nodes can have GTIDs for local transactions in the following scenarios:

* A DDL statement is executed with {{wsrep_OSU_method=RSU}} set.
* A DML statement writes to a non-InnoDB table.
* A DML statement writes to an InnoDB table with {{wsrep_on=OFF}} set.

We may want to come up with a way to disallow any statements that would cause local GTIDs in Galera Cluster.

For example, we could implement a new system variable called {{wsrep_disallow_local_gtids}}. If a user tries to execute a statement that would cause a local GTID, then it could trigger an error. For example:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_OSU_method='RSU';
Query OK, 0 rows affected (0.000 sec)

ALTER TABLE tab FORCE;
ERROR X (Y): You can't execute statements that would generate local GTIDs when
  wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0"
  to execute this statement.
{code}

Or:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;
Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');
ERROR X (Y): You can't execute statements that would generate local GTIDs when
  wsrep_disallow_local_gtids is set. Try disabling binary logging with "SET sql_log_bin=0"
  to execute this statement.
{code}

But users could still work around this by disabling binary logging. For example:

{code}
SET GLOBAL wsrep_disallow_local_gtids=ON;
Query OK, 0 rows affected (0.000 sec)

SET SESSION wsrep_on=OFF;
Query OK, 0 rows affected (0.000 sec)

SET SESSION sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)

INSERT INTO innodb_tab VALUES (1, 'str');
Query OK, 1 rows affected (0.000 sec)
{code}

Geoff Montee (Inactive) made changes - 2019-10-02 18:52

Link

This issue relates to ~~MXS-2580~~ [ ~~MXS-2580~~ ]

Geoff Montee (Inactive) made changes - 2019-10-02 18:57

Link

This issue relates to MENT-407 [ MENT-407 ]

Julien Fritsch made changes - 2019-10-28 11:05

Link

This issue duplicates MENT-436 [ MENT-436 ]

Richard Stracke made changes - 2020-06-16 06:45

Link

This issue relates to ~~MDEV-22905~~ [ ~~MDEV-22905~~ ]

Jan Lindström (Inactive) made changes - 2021-02-11 07:23

Fix Version/s

10.6 [ 24028 ]

Jan Lindström (Inactive) made changes - 2021-02-26 06:59

Status

Open [ 1 ]

In Progress [ 3 ]

Jan Lindström (Inactive) added a comment - 2021-03-01 13:29

https://github.com/MariaDB/server/commit/ebb2db5912ebb9b4e3ef230f15ae65529ff30be4

Jan Lindström (Inactive) added a comment - 2021-03-01 13:29 https://github.com/MariaDB/server/commit/ebb2db5912ebb9b4e3ef230f15ae65529ff30be4

Jan Lindström (Inactive) made changes - 2021-03-01 13:29

Assignee	Jan Lindström [ jplindst ]	Julius Goryavsky [ sysprg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Julius Goryavsky made changes - 2021-03-02 02:16

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Julius Goryavsky made changes - 2021-03-02 02:16

Assignee

Julius Goryavsky [ sysprg ]

Jan Lindström [ jplindst ]

Jan Lindström (Inactive) made changes - 2021-03-02 05:58

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Jan Lindström (Inactive) made changes - 2021-03-02 06:14

issue.field.resolutiondate

2021-03-02 06:14:34.0

2021-03-02 06:14:34.124

Jan Lindström (Inactive) made changes - 2021-03-02 06:14

Fix Version/s		10.6.0 [ 24431 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Jan Lindström (Inactive) added a comment - 2021-03-02 06:15

Added a new wsrep_mode feature DISALLOW_LOCAL_GTID for this. Nodes can have GTIDs for local transactions in the following scenarios:

A DDL statement is executed with wsrep_OSU_method=RSU set.
A DML statement writes to a non-InnoDB table.
A DML statement writes to an InnoDB table with wsrep_on=OFF set.

If user has set wsrep_mode=DISALLOW_LOCAL_GTID these operations produce a error ERROR HY000: Galera replication not supported.

Jan Lindström (Inactive) added a comment - 2021-03-02 06:15 Added a new wsrep_mode feature DISALLOW_LOCAL_GTID for this. Nodes can have GTIDs for local transactions in the following scenarios: A DDL statement is executed with wsrep_OSU_method=RSU set. A DML statement writes to a non-InnoDB table. A DML statement writes to an InnoDB table with wsrep_on=OFF set. If user has set wsrep_mode=DISALLOW_LOCAL_GTID these operations produce a error ERROR HY000: Galera replication not supported.

Jan Lindström (Inactive) made changes - 2021-03-02 06:18

Link

This issue relates to ~~MDEV-25024~~ [ ~~MDEV-25024~~ ]

Sergei Golubchik made changes - 2021-12-06 21:24

Workflow

MariaDB v3 [ 100102 ]

MariaDB v4 [ 134103 ]

Jira Automation (IT) made changes - 2024-07-04 03:32

Zendesk Related Tickets

184649

People

Assignee:: Jan Lindström (Inactive)

Reporter:: Geoff Montee (Inactive)

Votes:: 2 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2019-10-01 18:58

Updated:: 2024-07-07 22:33

Resolved:: 2021-03-02 06:14

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration