# Investigate if MariaDB 10.1+ DynamicField() can be used for storing JSON blobs

XMLWordPrintable

## Details

• Type: Story
• Status: Done
• Resolution: Done
• Fix Version/s: None
• Component/s:
• Labels:
None
• Story Points:
2
• Team:
SQuaRE

## Description

During deployment of SQuaSH we realized that JSONField() as implemented in DM-8414 works only with MySQL and that the corresponding field type for MariaDB is the DynamicField()

This ticket is to make sure we can use MariaDB features and stick with it in production.

## Activity

Hide
Angelo Fausti added a comment - - edited

My conclusion is that DynamicField https://django-mysql.readthedocs.io/en/latest/model_fields/dynamic_field.html is not the right mechanism to store JSON data in MariaDB, it actually can be used to store simple key value pairs (i.e dynamic columns) like the examples in the link above.

Also the in the tests performed the 'spec' argument is required to validate the data types when inserting data, that is still possible for the JSON documents we want to store and would serve to validate the input data.

 from django_mysql.models import DynamicField, Model    class Measurement(Model):  metric = models.ForeignKey(Metric, null=False)  job = models.ForeignKey(Job, null=False, related_name='measurements')  value = DynamicField(spec={'test': str}) 

However the main limitation is that django-mysql does not support JSON arrays for MariaDB dynamic columns which is required if we want to store the validate_drp outputs.

Native JSON support (equivalent to the JSONField() in MySQL 5.7+) will come only with MariaDB 10.2 see http://serge.frezefond.com/2016/01/mariadb-and-native-json-support/

Storing JSON in a binary format or in a standart text may have respective advantages and drawbacks but it might be the only solution for now if we want to avoid using MySQL in production.

Show
Angelo Fausti added a comment - - edited My conclusion is that DynamicField https://django-mysql.readthedocs.io/en/latest/model_fields/dynamic_field.html is not the right mechanism to store JSON data in MariaDB, it actually can be used to store simple key value pairs (i.e dynamic columns) like the examples in the link above. Also the in the tests performed the 'spec' argument is required to validate the data types when inserting data, that is still possible for the JSON documents we want to store and would serve to validate the input data. from django_mysql.models import DynamicField, Model class Measurement(Model): metric = models.ForeignKey(Metric, null = False ) job = models.ForeignKey(Job, null = False , related_name = 'measurements' ) value = DynamicField(spec = { 'test' : str }) However the main limitation is that django-mysql does not support JSON arrays for MariaDB dynamic columns which is required if we want to store the validate_drp outputs. Native JSON support (equivalent to the JSONField() in MySQL 5.7+) will come only with MariaDB 10.2 see http://serge.frezefond.com/2016/01/mariadb-and-native-json-support/ Storing JSON in a binary format or in a standart text may have respective advantages and drawbacks but it might be the only solution for now if we want to avoid using MySQL in production.
Hide
Joshua Hoblitt added a comment -

It looks like MariaDB 10.2 has been in beta status since last April, so it is probably close to fully baked. However, the latest version available through RDS is 10.1.19. Converting from RDS to running the DB on a instance isn't a big deal but we'd need to spend some time sorting out DB backups.

My recommendation at this point is to store the json as a BLOB/TEXT and to migrate to MariaDB 10.2 once it is out of beta status.

Show
Joshua Hoblitt added a comment - It looks like MariaDB 10.2 has been in beta status since last April, so it is probably close to fully baked. However, the latest version available through RDS is 10.1.19. Converting from RDS to running the DB on a instance isn't a big deal but we'd need to spend some time sorting out DB backups. My recommendation at this point is to store the json as a BLOB/TEXT and to migrate to MariaDB 10.2 once it is out of beta status.
Hide
Angelo Fausti added a comment - - edited

Pushed related code to test MariaDB DynamicField feature for future reference. See README for instructions

Show
Angelo Fausti added a comment - - edited Pushed related code to test MariaDB DynamicField feature for future reference. See README for instructions https://github.com/lsst-sqre/qa-dashboard/tree/tickets/DM-8890

## People

• Assignee:
Angelo Fausti
Reporter:
Angelo Fausti
Watchers:
Angelo Fausti, Brian Van Klaveren, Joshua Hoblitt