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

Alter table modify column for same data type copying table

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Incomplete
    • 10.4.21
    • N/A
    • None
    • 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

              bar Alexander Barkov
              scottsommerville Scott Sommerville (Inactive)
              Votes:
              3 Vote for this issue
              Watchers:
              11 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.