Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-7212

Modify processing to binary compare in order to do native JSON comparison

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.30-33
    • Fix Version/s: 5.7.31-34
    • Component/s: None
    • Labels:
      None

      Description

      Description:

      When running an INSERT ON DUPLICATE KEY UPDATE operation that does not actually change data, datatype of a field in JSON column changes unexpectedly. This leads to an unnecessary UPDATE written to binlog.

      On 8.0.20 this unneeded conversion does not happen.

      How to repeat:

       

      mysql> CREATE TABLE `j2` (
       `id` int(11) NOT NULL,
       `j` json DEFAULT NULL,
       `tombstone` tinyint(4) NOT NULL,
       PRIMARY KEY (`id`)
      ) ENGINE=InnoDB;
      mysql> insert into j2 values(2, '{"amount": 70.0}', 1);
      Query OK, 1 row affected (0.01 sec)
      mysql> select json_type(j->"$.amount") from j2;
      +--------------------------+
      | json_type(j->"$.amount") |
      +--------------------------+
      | DOUBLE |
      +--------------------------+
      1 row in set (0.01 sec)
      mysql> insert into j2(id,j,tombstone) values(2, '{}', 0) on duplicate key update j=if(tombstone=0, '{}', j);
      Query OK, 2 rows affected (0.01 sec)
      mysql> select json_type(j->"$.amount") from j2;
      +--------------------------+
      | json_type(j->"$.amount") |
      +--------------------------+
      | INTEGER |
      +--------------------------+
      1 row in set (0.00 sec) 
      

      The update should set "j = j" since "tombstone != 0", or not make any change. However, "2 rows affected" is reported since the datatype got changes, and this operation goes to binlog as an UPDATE.

      Suggested fix:

      5.7 should leave the datatype as-is in this case.
       
       

        Attachments

          Activity

            People

            Assignee:
            marcelo.altmann Marcelo Altmann
            Reporter:
            sami.ahlroos Sami Ahlroos
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 1 day, 2 hours, 30 minutes
                1d 2h 30m

                  Smart Checklist