[MDEV-16877] system_versioning_alter_history with galera corruption - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.3.8
Fix Version/s: N/A
Component/s: Data Definition - Alter Table, Galera, Versioned Tables
Labels:
None
Environment:
10.3.8-MariaDB-1:10.3.8+maria~stretch-log mariadb.org binary distribution

Description

ALTER TABLE is not propagating over Galera cluster for tables with SYSTEM VERSIONING enabled. Even if the other nodes in the cluster have system_versioning_alter_history set to "KEEP" globally, they're not receiving the table schema alterations.

Attempting to modify data in one of these tables after the schema mismatch causes a whole world of problems, worse than any other time I've seen inconsistent schemas in the past.

Every node in the Galera cluster shuts down (this is to be expected). However, afterwards, it was near impossible to bring the cluster back online.

All but one node in the cluster lost their "uuid" info. Their grastate.dat contained the following contents:

# GALERA saved state

version: 2.1

uuid:    00000000-0000-0000-0000-000000000000

seqno:   -1

safe_to_bootstrap: 0

Attempting to modify the safe_to_bootstrap value to "1" and running galera_new_cluster or service mysql start would fail. Attempting to manually run mysqld_safe --wsrep_new_cluster would show the following on the console, followed by 100% CPU usage on all cores, while never actually getting anywhere, even after letting it sit several minutes (no disk activity at all btw)

mysqld_safe starting mysqld daemon with databases from /var/lib/mysql

Luckily, there was one single node in the cluster that still had proper "uuid" information and I was able to bootstrap the cluster from there, then one by one have every other node run a full SST to get their contents back (but this is extremely time consuming)

Attachments

Issue Links

relates to

MDEV-14767 system_versioning_alter_history breaks ALTER replication

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2018-08-03 17:25

Please provide the table structure, example of the ALTER statement which is not replicated, error logs from the node which performs the ALTER and the one that should receive the change bug doesn't, and configuration files for both. I can't reproduce it on a basic example:

node 1
MariaDB [test]> show variables like 'wsrep_cluster%';
+-----------------------+------------------+
\| Variable_name \| Value \|
+-----------------------+------------------+
\| wsrep_cluster_address \| gcomm:// \|
\| wsrep_cluster_name \| my_wsrep_cluster \|
+-----------------------+------------------+
2 rows in set (0.01 sec)

MariaDB [test]> create table t1 (a int) with system versioning;
Query OK, 0 rows affected (0.32 sec)

node 2
MariaDB [test]> show variables like 'wsrep_cluster%';
+-----------------------+----------------------------------------------------------------+
\| Variable_name \| Value \|
+-----------------------+----------------------------------------------------------------+
\| wsrep_cluster_address \| gcomm://127.0.0.1:4567?gmcast.listen_addr=tcp://127.0.0.1:4566 \|
\| wsrep_cluster_name \| my_wsrep_cluster \|
+-----------------------+----------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test]> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------+
\| Table \| Create Table \|
+-------+--------------------------------------------------------------------------------------------------------------+
\| t1 \| CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING \|
+-------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

node 1
MariaDB [test]> alter table t1 add column b int;
Query OK, 0 rows affected (1.64 sec)
Records: 0 Duplicates: 0 Warnings: 0

node 2
MariaDB [test]> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
\| Table \| Create Table \|
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
\| t1 \| CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING \|
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> alter table t1 add key(b);
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0

node 1
MariaDB [test]> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
\| Table \| Create Table \|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
\| t1 \| CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING \|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t1 values (1,1),(2,2);
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> delete from t1 where a = 1;
Query OK, 1 row affected (0.12 sec)

node 2
MariaDB [test]> select * from t1;
+------+------+
\| a \| b \|
+------+------+
\| 2 \| 2 \|
+------+------+
1 row in set (0.00 sec)

node 1
MariaDB [test]> alter table t1 add column c int;
Query OK, 2 rows affected (1.77 sec)
Records: 2 Duplicates: 0 Warnings: 0

node 2
MariaDB [test]> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
\| Table \| Create Table \|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
\| t1 \| CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING \|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Elena Stepanova added a comment - 2018-08-03 17:25 Please provide the table structure, example of the ALTER statement which is not replicated, error logs from the node which performs the ALTER and the one that should receive the change bug doesn't, and configuration files for both. I can't reproduce it on a basic example: node 1 MariaDB [test]> show variables like 'wsrep_cluster%' ; + -----------------------+------------------+ | Variable_name | Value | + -----------------------+------------------+ | wsrep_cluster_address | gcomm:// | | wsrep_cluster_name | my_wsrep_cluster | + -----------------------+------------------+ 2 rows in set (0.01 sec) MariaDB [test]> create table t1 (a int ) with system versioning; Query OK, 0 rows affected (0.32 sec) node 2 MariaDB [test]> show variables like 'wsrep_cluster%' ; + -----------------------+----------------------------------------------------------------+ | Variable_name | Value | + -----------------------+----------------------------------------------------------------+ | wsrep_cluster_address | gcomm://127.0.0.1:4567?gmcast.listen_addr=tcp://127.0.0.1:4566 | | wsrep_cluster_name | my_wsrep_cluster | + -----------------------+----------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [test]> show create table t1; + -------+--------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------+--------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` int (11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING | + -------+--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) node 1 MariaDB [test]> alter table t1 add column b int ; Query OK, 0 rows affected (1.64 sec) Records: 0 Duplicates: 0 Warnings: 0 node 2 MariaDB [test]> show create table t1; + -------+------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------+------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` int (11) DEFAULT NULL , `b` int (11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING | + -------+------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> alter table t1 add key (b); Query OK, 0 rows affected (0.52 sec) Records: 0 Duplicates: 0 Warnings: 0 node 1 MariaDB [test]> show create table t1; + -------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` int (11) DEFAULT NULL , `b` int (11) DEFAULT NULL , KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING | + -------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> insert into t1 values (1,1),(2,2); Query OK, 2 rows affected (0.10 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> delete from t1 where a = 1; Query OK, 1 row affected (0.12 sec) node 2 MariaDB [test]> select * from t1; + ------+------+ | a | b | + ------+------+ | 2 | 2 | + ------+------+ 1 row in set (0.00 sec) node 1 MariaDB [test]> alter table t1 add column c int ; Query OK, 2 rows affected (1.77 sec) Records: 2 Duplicates: 0 Warnings: 0 node 2 MariaDB [test]> show create table t1; + -------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` int (11) DEFAULT NULL , `b` int (11) DEFAULT NULL , `c` int (11) DEFAULT NULL , KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING | + -------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

Vincent Milum Jr added a comment - 2018-08-03 22:01

The bug happens if system_versioning_alter_history is changed while MariaDB is running. The setting is unset in the cnf file, therefor defaulting to "ERROR"

Installations are fresh Debian 9 VMs with official MariaDB binaries running with stock configuration with the following file added.

Config on both nodes:

root@galera-test-1:/# cat /etc/mysql/conf.d/galera.cnf

[mysqld]

character-set-server=utf8mb4

log_slave_updates=1

innodb_buffer_pool_size=768M

binlog_format=ROW

default-storage-engine=innodb

innodb_autoinc_lock_mode=2

bind-address=0.0.0.0

skip-name-resolve

[galera]

wsrep_on=ON

wsrep_provider=/usr/lib/galera/libgalera_smm.so

wsrep_provider_options="gmcast.segment=1"

wsrep_cluster_address="gcomm://192.168.100.124,192.168.100.132"

wsrep_sst_method=rsync

[client]

default-character-set=utf8mb4

On each node, the following is executed:

MariaDB [test]> set global system_versioning_alter_history='keep';

Node 1:

MariaDB [test]> create table tbl3 (x int) with system versioning;

MariaDB [test]> alter table tbl3 add y int;

MariaDB [test]> desc tbl3;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| x     | int(11) | YES  |     | NULL    |       |

| y     | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

Node 2:

MariaDB [test]> desc tbl3;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| x     | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

Node 2 Log:

Aug  3 14:44:46 galera-test-2 mysqld[15499]: 2018-08-03 14:44:46 1 [ERROR] Slave SQL: Error 'Not allowed for system-versioned `test`.`tbl3`. Change @@system_versioning_alter_history to proceed with ALTER.' on query. Default database: 'test'. Query: 'alter table tbl3 add z int', Internal MariaDB error code: 4119

Because the Galera session is already started, I believe it is inheriting, and thus storing a copy of system_versioning_alter_history='error', so when the global variable is changed while the instance is running, the command execution will still fail for child nodes. Only having the system_versioning_alter_history='keep' in the cnf file followed by a cluster (rolling) restart seems to work properly.

~~MDEV-14767~~ mentioned almost this exact scenario and was marked as fixed, however the issue still persists, though I think that was for master > slave replication, not Galera replication.

If it is indeed an issue with the Galera session's instance of this particular variable, it might be wise to just force it to 'keep' always for that particular session. if it is set to 'error' globally or for a user session, the alter table statement will fail before even being propagated to other galera nodes, so there shoulnd't be any conflicts going on.

Vincent Milum Jr added a comment - 2018-08-03 22:01 The bug happens if system_versioning_alter_history is changed while MariaDB is running. The setting is unset in the cnf file, therefor defaulting to "ERROR" Installations are fresh Debian 9 VMs with official MariaDB binaries running with stock configuration with the following file added. Config on both nodes: root@galera-test-1:/# cat /etc/mysql/conf.d/galera.cnf [mysqld] character-set-server=utf8mb4 log_slave_updates=1 innodb_buffer_pool_size=768M binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 skip-name-resolve [galera] wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_provider_options="gmcast.segment=1" wsrep_cluster_address="gcomm://192.168.100.124,192.168.100.132" wsrep_sst_method=rsync [client] default-character-set=utf8mb4 On each node, the following is executed: MariaDB [test]> set global system_versioning_alter_history= 'keep' ; Node 1: MariaDB [test]> create table tbl3 (x int ) with system versioning; MariaDB [test]> alter table tbl3 add y int ; MariaDB [test]> desc tbl3; + -------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | + -------+---------+------+-----+---------+-------+ | x | int (11) | YES | | NULL | | | y | int (11) | YES | | NULL | | + -------+---------+------+-----+---------+-------+ Node 2: MariaDB [test]> desc tbl3; + -------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | + -------+---------+------+-----+---------+-------+ | x | int (11) | YES | | NULL | | + -------+---------+------+-----+---------+-------+ Node 2 Log: Aug 3 14:44:46 galera-test-2 mysqld[15499]: 2018-08-03 14:44:46 1 [ERROR] Slave SQL: Error 'Not allowed for system-versioned `test`.`tbl3`. Change @@system_versioning_alter_history to proceed with ALTER.' on query. Default database: 'test'. Query: 'alter table tbl3 add z int', Internal MariaDB error code: 4119 Because the Galera session is already started, I believe it is inheriting, and thus storing a copy of system_versioning_alter_history='error', so when the global variable is changed while the instance is running, the command execution will still fail for child nodes. Only having the system_versioning_alter_history='keep' in the cnf file followed by a cluster (rolling) restart seems to work properly. MDEV-14767 mentioned almost this exact scenario and was marked as fixed, however the issue still persists, though I think that was for master > slave replication, not Galera replication. If it is indeed an issue with the Galera session's instance of this particular variable, it might be wise to just force it to 'keep' always for that particular session. if it is set to 'error' globally or for a user session, the alter table statement will fail before even being propagated to other galera nodes, so there shoulnd't be any conflicts going on.

Elena Stepanova added a comment - 2018-09-02 20:49

This is expectedly true.
I have no idea what can be done about it, if anything – assigning to system versioning experts to decide. Please feel free to reassign if you think it should be considered on the Galera side.

Elena Stepanova added a comment - 2018-09-02 20:49 This is expectedly true. I have no idea what can be done about it, if anything – assigning to system versioning experts to decide. Please feel free to reassign if you think it should be considered on the Galera side.

Christian Ciach added a comment - 2021-08-25 14:26

The worst thing about this issue is that an unprivileged user can destroy the whole cluster just by setting an otherwise harmless session variable.

Christian Ciach added a comment - 2021-08-25 14:26 The worst thing about this issue is that an unprivileged user can destroy the whole cluster just by setting an otherwise harmless session variable.

Vincent Milum Jr added a comment - 2021-08-25 15:33

That's a very interesting thought I didn't even have.

This should probably be escalated as a security issue then, since a minimally privileged user could essentially "crash" an entire MariaDB cluster taking the entire thing offline.

And again, this EXACT same issue existed for non-Galera, traditional replication. That EXACT same fix can be applied here. There is little reason why this should literally be taking years to address!?

https://jira.mariadb.org/browse/MDEV-14767?focusedCommentId=107305&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-107305

Vincent Milum Jr added a comment - 2021-08-25 15:33 That's a very interesting thought I didn't even have. This should probably be escalated as a security issue then, since a minimally privileged user could essentially "crash" an entire MariaDB cluster taking the entire thing offline. And again, this EXACT same issue existed for non-Galera, traditional replication. That EXACT same fix can be applied here. There is little reason why this should literally be taking years to address!? https://jira.mariadb.org/browse/MDEV-14767?focusedCommentId=107305&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-107305

Jan Lindström added a comment - 2023-06-06 06:30

10.3 is EOL soon.

Jan Lindström added a comment - 2023-06-06 06:30 10.3 is EOL soon.

People

Assignee:: Nikita Malyavin

Reporter:: Vincent Milum Jr

Votes:: 2 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2018-08-01 20:52

Updated:: 2023-06-06 06:30

Resolved:: 2023-06-06 06:30

Git Integration

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

MariaDB Server