[MDEV-7647] Virtual column and RBR (Galera Cluster) Created: 2015-02-27  Updated: 2017-01-01  Resolved: 2017-01-01

Status: Closed
Project: MariaDB Server
Component/s: Replication, Virtual Columns
Affects Version/s: 10.0.16-galera
Fix Version/s: 10.0.29, 10.1.21, 10.2.4, 10.0.29-galera

Type: Bug Priority: Major
Reporter: Leo Kwan Assignee: Sachin Setiya (Inactive)
Resolution: Fixed Votes: 2
Labels: None
Environment:

RHEL6

Attachments: Text File One hour with more inserts.txt     File node_stress.py    
Issue Links:
Blocks
is blocked by MDEV-11636 Extra persistent columns on slave alw... Closed
Sprint: 5.5.54

 Description   

Try to add virtual column in to an existing table. The server is one of the node in a galera cluster, which is RBR.

It's a live table, and update/insert cannot be stopped. Alter table with add new virtual column by RSU without any issue. New row is coming in without problem. However, all PERSISTENT virtual column value will become null, VIRTUAL one is populated without issue.

It may be make sense for these columns become null if it is RBR? but how can I fix this null value after all server have the new table structure?

Are there any statement I can run to force the DB to re-calculate the PERSISTENT virtual column?



 Comments   
Comment by Elena Stepanova [ 2015-02-28 ]

If I understand the problem correctly, my suggestion would be to try to run ALTER TABLE ... MODIFY <initial virtual column definition>.
For example, the initial table was
create table t1 (a int)
Then you added
alter table t1 add vcol int as (a+5) persistent
Then it got populated with (1,NULL) through the replication
Then you run
alter table t1 modify vcol int as (a+5) persistent
It should become (1,6).

But I will assign the issue to Nirbhay, maybe he can think of a more elegant solution, from Galera cluster perspective (The problem is not specific to Galera cluster, it would be the same if lets say the table was altered on the slave in a normal replication; but when it's done people who introduce such differences between master and slave are supposed to take care about possible problems, while with Galera it should generally "just work").

Comment by Marko Mäkelä [ 2016-12-15 ]

For what it is worth, I was confused by the terminology. In MySQL 5.7, there are three kinds of columns:

  1. normal (base) columns
  2. GENERATED STORED columns (not distinguishable from base columns by storage engines)
  3. GENERATED VIRTUAL columns (only materialized in secondary indexes and some storage engine logs)

It appears that PERSISTENT is the same as GENERATED STORED. From the storage engine point of view, the table schema is changing.

Some corner cases:

  1. UPDATE of base columns that affect base columns of generated columns
  2. FOREIGN KEY CASCADE or SET NULL operations that affect base columns of generated columns

The SQL layer is responsible for ensuring that GENERATED STORED columns are consistently updated and stored. On read, the column values will be returned by the storage engine.
The storage engine is responsible for ensuring that indexed GENERATED VIRTUAL columns are consistently materialized. For this, the storage engine may need to ask the SQL layer to evaluate virtual column values.
Non-indexed GENERATED VIRTUAL columns are basically ignored by the storage engine.

It appears to me that the replication log should include values for PERSISTENT columns, If the values of VIRTUAL columns are omitted, the replication slave must evaluate them for those VIRTUAL columns that are indexed.

Comment by Sachin Setiya (Inactive) [ 2017-01-01 ]

http://lists.askmonty.org/pipermail/commits/2016-December/010331.html

Generated at Thu Feb 08 07:21:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.