Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7647

Virtual column and RBR (Galera Cluster)

Details

    • 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?

      Attachments

        1. node_stress.py
          5 kB
          Sachin Setiya
        2. One hour with more inserts.txt
          224 kB
          Sachin Setiya

        Issue Links

          Activity

            elenst Elena Stepanova added a comment - - edited

            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").

            elenst Elena Stepanova added a comment - - edited 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").
            marko Marko Mäkelä added a comment - - edited

            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.

            marko Marko Mäkelä added a comment - - edited For what it is worth, I was confused by the terminology. In MySQL 5.7, there are three kinds of columns: normal (base) columns GENERATED STORED columns (not distinguishable from base columns by storage engines) 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: UPDATE of base columns that affect base columns of generated columns 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.
            sachin.setiya.007 Sachin Setiya (Inactive) added a comment - http://lists.askmonty.org/pipermail/commits/2016-December/010331.html

            People

              sachin.setiya.007 Sachin Setiya (Inactive)
              leomkkwan Leo Kwan
              Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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