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

Alter table modify column for same data type copying table

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.4.21
    • Fix Version/s: 10.4
    • Component/s: None
    • Labels:
    • Environment:
      Amazon RDS

      Description

      The customer is observing the copying of the entire table when the DDL statement "alter table <table_name> modify column <column_name> json;" is ran against a table with an already existing data-type of JSON for the same column. I have recreated this in my local tests and have been able to compare with non JSON data-types where the table is not copied/recreated.
      Below you can see the table with the JSON field (dummy data) being recreate/copied:

      MariaDB [CS0373101]> select count(*) from test2;
      +----------+
      | count(*) |
      +----------+
      | 1000000 |
      +----------+
      1 row in set (0.249 sec)
       
      MariaDB [CS0373101]> alter table test2 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
      Query OK, 0 rows affected (4.673 sec)
      Records: 0 Duplicates: 0 Warnings: 0
       
      MariaDB [CS0373101]> update test2 set output=replace(output,"'",'"');
      Query OK, 1000000 rows affected (6.885 sec)
      Rows matched: 1000000 Changed: 1000000 Warnings: 0
       
      MariaDB [CS0373101]> alter table test2 modify column `output` json;
      Query OK, 1000000 rows affected (5.297 sec)
      Records: 1000000 Duplicates: 0 Warnings: 0
       
      MariaDB [CS0373101]> show create table test2;
      +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table |
      +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
      | test2 | CREATE TABLE `test2` (
      `output` text CHARACTER SET latin1 DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
      +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [CS0373101]> select * from test2 limit 10;
      +------------------------------------------------------------------+
      | output |
      +------------------------------------------------------------------+
      | {"name": "Eric Miller", "email": "doylecharlotte@singh.com"} |
      | {"name": "Sarah Cooper", "email": "sbaker@molina-rodriguez.com"} |
      | {"name": "John Williams", "email": "shepardsteven@griffith.com"} |
      | {"name": "James Scott", "email": "james43@taylor.info"} |
      | {"name": "Crystal West", "email": "garciasean@hotmail.com"} |
      | {"name": "Theresa Bush", "email": "julie61@gmail.com"} |
      | {"name": "Gerald Hamilton", "email": "lori32@gmail.com"} |
      | {"name": "Derek Conway", "email": "svasquez@becker-neal.com"} |
      | {"name": "Natalie Mitchell", "email": "dyermario@yahoo.com"} |
      | {"name": "Taylor Smith", "email": "deborahjenkins@brown.com"} |
      +------------------------------------------------------------------+
      10 rows in set (0.001 sec)
       
      MariaDB [CS0373101]> alter table test2 modify if exists output json;
      Query OK, 1000000 rows affected (5.687 sec)
      Records: 1000000 Duplicates: 0 Warnings: 0
      

      Conversely, I created another similar table, but this time with the data type of text and the recreation/copying of the table does not occur as shown below:

      MariaDB [CS0373101]> show create table test3;
      +-------+--------------------------------------------------------------------------------------------+
      | Table | Create Table |
      +-------+--------------------------------------------------------------------------------------------+
      | test3 | CREATE TABLE `test3` (
      `output` text DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [CS0373101]> select * from test3 limit 10;
      +---------------------------------------------------------------------+
      | output |
      +---------------------------------------------------------------------+
      | {'name': 'Steven Carter', 'email': 'williamskaren@hotmail.com'} |
      | {'name': 'David Paul', 'email': 'teresa62@becker.org'} |
      | {'name': 'Nicole Miller', 'email': 'bryan03@gmail.com'} |
      | {'name': 'Samantha Alexander', 'email': 'allentodd@yahoo.com'} |
      | {'name': 'Eric Jacobs', 'email': 'greggblack@yahoo.com'} |
      | {'name': 'Kimberly Ortiz', 'email': 'escott@mitchell-nelson.com'} |
      | {'name': 'Margaret Mitchell', 'email': 'franklinrichard@gmail.com'} |
      | {'name': 'Jennifer Roth', 'email': 'prollins@jones.com'} |
      | {'name': 'Michael Rivera', 'email': 'roy15@yahoo.com'} |
      | {'name': 'Amy Campbell', 'email': 'christina97@martin.com'} |
      +---------------------------------------------------------------------+
      10 rows in set (0.001 sec)
       
      MariaDB [CS0373101]> alter table test3 modify if exists output text;
      Query OK, 0 rows affected (0.020 sec)
      Records: 0 Duplicates: 0 Warnings: 0
       
      MariaDB [CS0373101]>
      

      Therefore, there appears to be an issue for the 10.4.21 MariaDB Community edition where despite a field already being of a JSON data-type, the table is being recreated.

      This is proving problematic for the customer, as they have very large tables with the JSON data-type column and the resultant table recreation when the DDL statement is ran, is causing delays in their environment.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              scottsommerville Scott Sommerville
              Votes:
              2 Vote for this issue
              Watchers:
              9 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.