'MariaDB: Unknown data type "MYSQL_JSON"

According to MariaDB's website, you can simply replace MySQL with MariaDB. Great, I thought, however after installing MariaDB (10.5.7), some of my InnoDB tables are corrupt. The error is Unknown data type "MYSQL_JSON". One of the corrupt tables also says "in use" in phpmyadmin, and I can't access it at all.

So, I was looking for a solution everywhere, and apparently MariaDB doesn't support that type. Is there any way to fix that? There must be some way to covert those columns, right?

I tried to downgrade back to MySQL 5.7, but now MySQL isn't working anymore, so I reinstalled MariaDB again. I couldn't find any downgrade guide either.

Here's a create table statement for the corrupt table, made from my local test-database. I was able to mysqldump all the other ones successfully. Just not this one. Granted, I have a backup from 1 day ago, but losing work from a whole day is not a nice thing regardless.

CREATE TABLE `news` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `author_id` int(10) unsigned DEFAULT NULL,
 `title` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
 `content` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `excerpt` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `sources` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '(DC2Type:json)',
 `header` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `copyright` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `category` smallint(5) unsigned DEFAULT NULL,
 `featured` tinyint(1) NOT NULL,
 `language` smallint(5) unsigned NOT NULL,
 `published` datetime DEFAULT NULL,
 `status` smallint(5) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `IDX_1DD39950F675F31B` (`author_id`),
 CONSTRAINT `FK_1DD39950F675F31B` FOREIGN KEY (`author_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=167 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The table is auto-generated by Doctrine ORM.

If I could somehow get just a few rows of data out of that table, that would make my week. I just need the content column from the last day. I got my system back running from my backup. Just those news are missing.

Update

I was able to find a .ibd file under /var/lib/mysql/{mydatabase}/ which contains all the data I needed in plain text. I'm just manually copying the content and insert it back into the database.

mysqlmariadbinnodbubuntu-18.04


Solution 1:[1]

To fix Unknown data type "MYSQL_JSON" in the general case when migrating from MySQL to MariaDB.

You have 2 options.

  1. Dump your database from MySQL and import it to MariaDB.
  2. Fix all tables that have a JSON type column.

The first option is straightforward but to go with the second option as suggested by MariaDB website.

stop MySQL service if it is running

sudo service mysql stop

start a new mysql server instance

mysqld --no-defaults --datadir=<Your data dir directory> --lc-messages_dir=./share --plugin-dir=<path to directory containing type_mysql_json.so> --plugin-maturity=alpha

default datadir directory (mysql 5.7, ubuntu 18.04) is /var/lib/mysql/

default plugin directory is /usr/lib/mysql/plugin

while keeping the server running start a new MySQL command-line client and install mysql_json plugin.

install soname 'type_mysql_json';

Alter all tables that have a JSON type column.

 ALTER TABLE `database_name`.`table_name` FORCE;

stop MySQL server instance and start MySQL service and everything should be fine.

ps: If you have lots of tables that contains JSON type column (as in my case) you can use this command to get list of all commands you have to run in order to fix all tables

SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` FORCE;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "<YOUR_DATABASE_NAME>";

Solution 2:[2]

An in-place upgrade of the mysql-5.7 to MariaDB, for the JSON type requires a plugin to read its data format:

Add the following to your MariaDB server configuration:

plugin_load_add=mysql_json=type_mysql_json
plugin_maturity=alpha

Run mysql_upgrade --force to pick up all the JSON data types and convert them.

Alternately just ALTER TABLE news FORCE to change that one table.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Kibo
Solution 2 danblack

Related Questions