[MDEV-32235] mysql_json cannot be used on newly created table Created: 2023-09-23  Updated: 2024-01-12  Resolved: 2024-01-12

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.6.14
Fix Version/s: 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Major
Reporter: Matthew Assignee: Anel Husakovic
Resolution: Fixed Votes: 0
Labels: None
Environment:

AWS RDS 5.5.5-10.6.14-MariaDB-log


 Description   

I have a customer that is attempting to migrate from RDS MySQL 5.7 to RDS MariaDB 10.6. They make extensive use of the JSON column type, which, as you know, is incompatible with MariaDB when using RBR.

The mysql_json plugin was loaded into RDS as a workaround, but the plugin has some severe issues from the beginning. Below is a brand new table creation. Why do I have to dump this data from MySQL (not in use here) to use a brand new table?

mysql> CREATE TABLE testjson (t mysql_json NOT NULL);
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql> INSERT INTO testjson VALUES ('{"k1":"v1"}');
 
Query OK, 1 row affected (0.00 sec)
 
 
 
mysql> SELECT * FROM testjson;
 
ERROR 1105 (HY000): Error parsing MySQL JSON format, please dump this table from MySQL and then restore it to be able to use it in MariaDB.



 Comments   
Comment by Vicențiu Ciorbaru [ 2023-09-24 ]

The mysql_json plugin is only meant to be used when opening a mysql data directory with MariaDB.

The insert succeeding is a bug, we shouldn't allow inserts in that case. The only way to migrate from Mysql to MariaDB is to run mariadb-upgrade with the mysql_json plugin loaded.

mariadb-upgrade automatically loads mysql_json plugin if it's needed.

For safety, it's best to take a backup of the datadir before upgrading, but the upgrade can be run in place.

Comment by Matthew [ 2023-09-24 ]

I followed the "docs" here: https://mariadb.org/making-mariadb-understand-mysql-json/

Just so I understand:
1) there's no way to replicate from MySQL 5.7 -> MariaDB using RBR, even with this plugin?
2) The intended use of this plugin is to copy a 5.7 MySQL datadir, binary replace mariadb 10.6 with plugin, start mariadb, run maria_upgrade.

Comment by Anel Husakovic [ 2024-01-11 ]

Fixed with 22f3ebe4bf

Comment by Alexander Barkov [ 2024-01-12 ]

Hi anel,

I propose a slightly different approach. Please find my comments here:

https://github.com/MariaDB/server/commit/22f3ebe4bf11acb7b5e854bc4e6b3439af6982f1

Comment by Anel Husakovic [ 2024-01-12 ]

Hi bar,
thank you, here is the branch to merge https://github.com/MariaDB/server/tree/bb-10.5-anel-MDEV-32235-bar-fix
Best regards,
Anel

Comment by Alexander Barkov [ 2024-01-12 ]

Thanks, anel. Pushed to the main 10.5.

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