JSON datatype and MariaDB

json_logomariadb_logo

 

In one of my projects I had MySQL database which had a table with a field of JSON datatype, where I stored form data.

All was good until I moved the project from my local server to production. During the database import I’ve got an error of table creation. It was looking like that:

MariaDB [big_project]> CREATE TABLE testing (my_field JSON);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘JSON)’ at line 1

 

After reading  MariaDB documentation I’ve found out that at the moment there is no “JSON” datatype support in MariaDB engine, and it’s not being planned yet.

Anders Karlsson had described it in details in his articale: https://mariadb.com/resources/blog/json-with-mariadb-10-2/

Though  starting from version 10.2.7  MariaDB developers introduced “JSON” type as an alias for “LONGTEXT” . So that starting from version 10.2.7  we can use “JSON” datatype  and MariaDB will not throw an error, though in fact it will be “LONGTEXT” datatype used for your data.  For more details: https://mariadb.com/kb/en/library/json-data-type/. In this article you also find information about migration/interaction between MySQL and MariaDB in case of DB migration or DB replication between those database engines.

Indeed my production server had MariaDB version 10.1.26, where “JSON” alias wasn’t implemented yet.

 

MariaDB [big_project]> select @@version;
+————————–+
| @@version |
+————————–+
| 10.1.26-MariaDB |
+————————–+
1 row in set (0.00 sec)

 

In my case I wasn’t able to make MariaDB version upgrade at that moment and also I hadn’t real need in “LONGTEXT” datatype so I just replaced “JSON” datatype to “TEXT” in my DB dump without changing anything on the server and it was quite enough for me.

I have also found  MariaDB JSON support to Laravel package.  Those who use Laravel  and have no possibility to update software on the server  may find this package  useful, after it’s installation it’d be possible to use “JSON” type in database migrations:

$table->json(‘my_field’)

So those who couldn’t upgrade MariaDB to version 10.2.7 and higher can just replace filed’s “JSON” datatype to “TEXT” or to “LONGTEXT” depending on the needs.

Напишите комментарий

Your email address will not be published. Required fields are marked *