Problem

If you have MySQL (5.7) table with JSON field/data and you want to select data in MariaDB you will not be able to see the data. Reason for this is that MariaDB currently doesn't support reading the MySQL table with JSON data (server will not crash but rather inform you mysqld: Incorrect information in file: './<schema>/<mysql-table>.frm') because JSON type is native type in MySQL and JSON type is alias for longtext type, what is relevant to SQL standard. Another reason is that JSON type is treated as enum_type (245) what is the same as MYSQL_TYPE_VIRTUAL (which is not enum type - defined in "include/mysql_com.h").

To reproduce this type error do the following:

1. Create the table in MySQL with JSON field and insert some data (for example an object)

   CREATE TABLE json (t json) ENGINE=myisam

   INSERT INTO json VALUES ('{"key1":"val1", "key2":"val2"}');

   what will create files <table-name>.frm, <table-name>.MYI, <table-name>.MYD (in our example <table-name>=json) in <database-name> directory placed in <datadir> directory,
   or download the MySQL files in attachment (json.frm, json.MYI, json.MYD).

2. On MariaDB side -> created/downloaded files copy to <database-name> directory in <datadir> directory.
3. Start the client and switch to database (test for example) where .frm file is located
   (you will get information in mysqld -> `[ERROR] mysqld: Incorrect information in file: './test/json.frm'`)
4. Start the client and `select *from json` -> you will get the same information `[ERROR] mysqld: Incorrect information in file: './test/json.frm' `.

Solution

In order to prevent this behaviour, server should be able :

• Detect MySQL server version and process mysql <table-name>.frm files ("sql/table.cc : init_from_binary_frm_image()", write the test case),
• Retrieve the data from mysql table, such that `select * from json` works:
  >> Create a new enum enum_field : "mysql_json_type" and appropriated field: Field_mysql_json which is (as in mysql) inherited from Field_blob
  >> Implement Field_mysql_json::val_str()
• to convert data from mysql to mariadb using `mysql_upgrade`
  >> Behind the scenes mysql_upgrade calls 2 commands for the server:

• CHECK TABLE <table-name> FOR UPGRADE; -> If the table is detected to require an upgrade it will call:
• ALTER TABLE <table-name> FORCE -> to upgrade it.
  >> Make CHECK TABLE correctly identify that we are looking at MySQL 5.7 created table with JSON columns
  Examine the .frm’s server version and the field types.
  If the .frm’s version is for MySQL 5.7(50700) and up (but not Mariadb 10.0 (10000) and up) and we encounter the field type 245, we know we are in MySQL space and we need to
  upgrade the table
  >> Extend ALTER TABLE <table-name> FORCE

• Create a COPY of the table and for all JSON columns values, convert them from MySQL binary json format to our text-based representation.
  If conversion succeeds, replace original table with the copy.
  If conversion doesn’t succeed, delete the copy and return an error.

Relevant parts of the code:

[1] Error raised: open_table_def()
[2] Reading of the .frm file: sql/table.cc : init_from_binary_frm_image()
[3] Creating a type in Mariadb: include/mysql_com.h : enum enum_field_types
[4] Upgrade related: client/mysql_upgrade.c run_mysqlcheck_upgrade()
[5] Upgrade related: client/mysqlcheck.c rebuild_table()
[6] sql/sql_admin.cc
Links:
[1] https://mariadb.com/kb/en/library/mysql_upgrade/
[2] https://mariadb.com/kb/en/library/sql-commands-check-table/
[3] https://mariadb.com/kb/en/library/mysqlcheck/
[4] https://mariadb.com/kb/en/library/upgrading-to-mariadb-from-mysql-50-or-older/
[5] https://mariadb.com/kb/en/library/backing-up-and-restoring-databases/