[MDEV-30669] Changing the Data Type of a Column from Text to JSON causes a unexpected Table Rebuild Created: 2023-02-17  Updated: 2023-04-10

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.4, 10.6.11, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Michael Amadi Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File Convert_to_JSON_Data_Type.txt    
Issue Links:
Blocks
is blocked by MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTR... Open
Relates
relates to MDEV-30934 InnoDB: Changing a Column Data Type f... Open
relates to MDEV-11424 Instant ALTER TABLE of failure-free r... Closed
relates to MDEV-27864 Alter table modify column for same da... Closed

 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.

 Comments   
Comment by Marko Mäkelä [ 2023-02-17 ]

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?

Comment by Alexander Barkov [ 2023-03-24 ]

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.

Comment by Alexander Barkov [ 2023-03-24 ]

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

Generated at Thu Feb 08 10:18:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.