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

Changing the Data Type of a Column from Text to JSON causes a unexpected Table Rebuild

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.6.11, 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
    • None
    • Storage Engine - InnoDB
    • None

    Description

      when attempting to change the Data type of an InnoDB table, from TEXT to JSON, given that they should be of the same storage format, we should not expect a table rebuild.

      However, using both ALGORITHM=INSTANT & INPLACE fails. only works with ALGORITHM=COPY (which the implicit ALGORITHM=DEFAULT would map to in this case).

      (P.S: Just a pointer to help, 10.4 already has fixed cases where changing the collation from utf8mb3 to utf8mb4 would not unnecessarily rebuild the table.)

      will attach test scenario to show this.

      Attachments

        Issue Links

          is blocked by

          Task - A task that needs to be done. MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT

          • Major - Major loss of function.
          • Open
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30934 InnoDB: Changing a Column Data Type from VARCHAR/TEXT to a super type causes a unexpected Table Rebuild

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-11424 Instant ALTER TABLE of failure-free record format changes

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27864 Alter table modify column for same data type copying table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            I can reproduce this with the following test. I would expect this to be performed as a schema-only change for any storage engine:

            --source include/have_innodb.inc
            		 
            CREATE TABLE t1(a INT PRIMARY KEY, data TEXT CHARACTER SET utf8mb4)
            		 
            ENGINE=InnoDB;
            		 
            --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
            		 
            ALTER TABLE t1 MODIFY data JSON, ALGORITHM=INSTANT;
            		 
            --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
            		 
            ALTER TABLE t1 MODIFY data JSON, ALGORITHM=INPLACE;
            		 
            ALTER TABLE t1 MODIFY data JSON;

            In MDEV-11424, I found a mention of an earlier bug MDEV-27864. Maybe this could be fixed as part of fixing that?

            marko Marko Mäkelä added a comment - I can reproduce this with the following test. I would expect this to be performed as a schema-only change for any storage engine: --source include/have_innodb.inc CREATE TABLE t1(a INT PRIMARY KEY , data TEXT CHARACTER SET utf8mb4) ENGINE=InnoDB; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 MODIFY data JSON, ALGORITHM=INSTANT; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 MODIFY data JSON, ALGORITHM=INPLACE; ALTER TABLE t1 MODIFY data JSON; In MDEV-11424 , I found a mention of an earlier bug MDEV-27864 . Maybe this could be fixed as part of fixing that?
            bar Alexander Barkov added a comment - - edited

            TEXT cannot be converted to JSON with a schema-only ALTER.
            JSON implies a constraint to check values for a certain format. While TEXT is a general purpose data type which allows any arbitrary format.
            Therefore we convert a super set to a subset. This needs a full table copy in the current code base.

            bar Alexander Barkov added a comment - - edited TEXT cannot be converted to JSON with a schema-only ALTER. JSON implies a constraint to check values for a certain format. While TEXT is a general purpose data type which allows any arbitrary format. Therefore we convert a super set to a subset. This needs a full table copy in the current code base.
            bar Alexander Barkov added a comment - - edited

            A better way to handle altering from TEXT to JSON will be possible after MDEV-16356 is done.

            bar Alexander Barkov added a comment - - edited A better way to handle altering from TEXT to JSON will be possible after MDEV-16356 is done.

            People

              Unassigned Unassigned
              michael.amadi Michael Amadi
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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