Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
10.4.21
-
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
- relates to
-
MDEV-15564 Avoid table rebuild in ALTER TABLE on collation or charset changes
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-