Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-8890

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

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: QA
    • Labels:
      None

      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.

        Attachments

          Issue Links

            Activity

            Hide
            afausti 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
            afausti 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
            jhoblitt 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
            jhoblitt 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
            afausti 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

            Show
            afausti 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:
                afausti Angelo Fausti
                Reporter:
                afausti Angelo Fausti
                Watchers:
                Angelo Fausti, Brian Van Klaveren, Joshua Hoblitt
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Summary Panel