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

Alter table modify column for same data type copying table

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

            Trying on 10.8:

            create table t1 (col1 text);
            insert into t1 select '{"foo":"bar"}' from seq_1_to_100000;
            

            MariaDB [test]> alter table t1 modify if exists col1 json;
            Query OK, 100000 rows affected (4.477 sec)             
            Records: 100000  Duplicates: 0  Warnings: 0
            

            It seems to have copied the table here.

            MariaDB [test]> alter table t1 modify if exists col1 json;
            Query OK, 0 rows affected (0.013 sec)
            Records: 0  Duplicates: 0  Warnings: 0
            

            If the column is already defined as JSON, it didn't.

            psergei Sergei Petrunia added a comment - Trying on 10.8: create table t1 (col1 text); insert into t1 select '{"foo":"bar"}' from seq_1_to_100000; MariaDB [test]> alter table t1 modify if exists col1 json; Query OK, 100000 rows affected (4.477 sec) Records: 100000 Duplicates: 0 Warnings: 0 It seems to have copied the table here. MariaDB [test]> alter table t1 modify if exists col1 json; Query OK, 0 rows affected (0.013 sec) Records: 0 Duplicates: 0 Warnings: 0 If the column is already defined as JSON, it didn't.

            I think that this needs to be fixed in a similar way to MDEV-15564, outside storage engines. The SQL layer should check if the encoding of the column is actually changing.

            marko Marko Mäkelä added a comment - I think that this needs to be fixed in a similar way to MDEV-15564 , outside storage engines. The SQL layer should check if the encoding of the column is actually changing.
            bar Alexander Barkov added a comment - - edited

            The test for a possible ALTER fails here in 10.4:

                if (new_field)
                {
                  /* Field is not dropped. Evaluate changes bitmap for it. */
             
                  /*
                    Check if type of column has changed.
                  */
                  bool is_equal= field->is_equal(*new_field);
                  if (!is_equal)
                  {
                    if (field->can_be_converted_by_engine(*new_field))
                    {
            

            field->is_equal() returns:

            • false in 10.4
            • true in 10.5

            The problem does not exist in 10.5. It was most likely fixed in 10.5 by this commit:

            commit e4b302e436c0a1e0d477a8e8e8e513112fd3ec7f
             
                MDEV-27018 IF and COALESCE lose "json" property
            

            bar Alexander Barkov added a comment - - edited The test for a possible ALTER fails here in 10.4: if (new_field) { /* Field is not dropped. Evaluate changes bitmap for it. */   /* Check if type of column has changed. */ bool is_equal= field->is_equal(*new_field); if (!is_equal) { if (field->can_be_converted_by_engine(*new_field)) { field->is_equal() returns: false in 10.4 true in 10.5 The problem does not exist in 10.5. It was most likely fixed in 10.5 by this commit: commit e4b302e436c0a1e0d477a8e8e8e513112fd3ec7f   MDEV-27018 IF and COALESCE lose "json" property
            bar Alexander Barkov added a comment - - edited

            This script:

            SET profiling=1;
            SET profiling_history_size=1;
             
            CREATE OR REPLACE TABLE stat (state TEXT, types TEXT);
             
            CREATE OR REPLACE TABLE t1 (a TEXT);
            ALTER TABLE t1 MODIFY a JSON;
            INSERT INTO stat
            SELECT state, '1. TEXT to JSON' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table';
             
            ALTER TABLE t1 MODIFY a JSON;
            INSERT INTO stat
            SELECT state, '2. JSON to JSON' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table';
             
            ALTER TABLE t1 MODIFY a TEXT;
            INSERT INTO stat
            SELECT state, '3. JSON to TEXT' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table';
             
            ALTER TABLE t1 MODIFY a TEXT;
            INSERT INTO stat
            SELECT state, '4. TEXT to TEXT' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table';
             
            SELECT state, types FROM stat;
            

            in 10.4.26 returns:

            +-------------------+-----------------+
            | state             | types           |
            +-------------------+-----------------+
            | Copy to tmp table | 1. TEXT to JSON |
            | Copy to tmp table | 1. TEXT to JSON |
            | Copy to tmp table | 2. JSON to JSON |
            | Copy to tmp table | 2. JSON to JSON |
            | Copy to tmp table | 3. JSON to TEXT |
            | Copy to tmp table | 3. JSON to TEXT |
            +-------------------+-----------------
            

            in 10.5.14 (also tested in 10.5.17) returns:

            +-------------------+-----------------+
            | state             | types           |
            +-------------------+-----------------+
            | copy to tmp table | 1. TEXT to JSON |
            | copy to tmp table | 3. JSON to TEXT |
            +-------------------+-----------------+
            

            in 10.5.13 (an in 10.5.14 before the fix for MDEV-27018, e.g. as of 28e166d6435741c46e0ea789657b739f78eb0425) returns:

            +-------------------+-----------------+
            | state             | types           |
            +-------------------+-----------------+
            | copy to tmp table | 1. TEXT to JSON |
            | copy to tmp table | 2. JSON to JSON |
            | copy to tmp table | 3. JSON to TEXT |
            +-------------------+-----------------+
            

            A temporary table for JSON->JSON conversion:

            • is created in 10.4.26 and in 10.5.x before the patch for MDEV-27018)
            • is not created starting from 10.5.14 (after the patch for MDEV-27018)
            bar Alexander Barkov added a comment - - edited This script: SET profiling=1; SET profiling_history_size=1;   CREATE OR REPLACE TABLE stat (state TEXT, types TEXT);   CREATE OR REPLACE TABLE t1 (a TEXT); ALTER TABLE t1 MODIFY a JSON; INSERT INTO stat SELECT state, '1. TEXT to JSON' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table' ;   ALTER TABLE t1 MODIFY a JSON; INSERT INTO stat SELECT state, '2. JSON to JSON' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table' ;   ALTER TABLE t1 MODIFY a TEXT; INSERT INTO stat SELECT state, '3. JSON to TEXT' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table' ;   ALTER TABLE t1 MODIFY a TEXT; INSERT INTO stat SELECT state, '4. TEXT to TEXT' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table' ;   SELECT state, types FROM stat; in 10.4.26 returns: +-------------------+-----------------+ | state | types | +-------------------+-----------------+ | Copy to tmp table | 1. TEXT to JSON | | Copy to tmp table | 1. TEXT to JSON | | Copy to tmp table | 2. JSON to JSON | | Copy to tmp table | 2. JSON to JSON | | Copy to tmp table | 3. JSON to TEXT | | Copy to tmp table | 3. JSON to TEXT | +-------------------+----------------- in 10.5.14 (also tested in 10.5.17) returns: +-------------------+-----------------+ | state | types | +-------------------+-----------------+ | copy to tmp table | 1. TEXT to JSON | | copy to tmp table | 3. JSON to TEXT | +-------------------+-----------------+ in 10.5.13 (an in 10.5.14 before the fix for MDEV-27018 , e.g. as of 28e166d6435741c46e0ea789657b739f78eb0425) returns: +-------------------+-----------------+ | state | types | +-------------------+-----------------+ | copy to tmp table | 1. TEXT to JSON | | copy to tmp table | 2. JSON to JSON | | copy to tmp table | 3. JSON to TEXT | +-------------------+-----------------+ A temporary table for JSON->JSON conversion: is created in 10.4.26 and in 10.5.x before the patch for MDEV-27018 ) is not created starting from 10.5.14 (after the patch for MDEV-27018 )

            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.