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

json data type overwrites data in table on insert

    XMLWordPrintable

    Details

      Description

      Well I wasn't sure what to write in the report summary, but basically it looks like that:

      INNODB (seems fine to me):

      mysql> drop table if exists t1_innodb;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> CREATE TABLE t1_innodb (a int, jdoc JSON, primary key(a)) engine=innodb;
      Query OK, 0 rows affected (0.06 sec)
      
      mysql> INSERT INTO t1_innodb VALUES(1, '{"name": "Hannah Murphy", "company": "Murray-Kelley"}');
      Query OK, 1 row affected (0.00 sec)
      
      mysql> select * from t1_innodb;
      +---+-------------------------------------------------------+
      | a | jdoc                                                  |
      +---+-------------------------------------------------------+
      | 1 | {"name": "Hannah Murphy", "company": "Murray-Kelley"} |
      +---+-------------------------------------------------------+
      1 row in set (0.00 sec)
      
      mysql> INSERT INTO t1_innodb VALUES(2, '{"key1": "value1", "key2": "value2"}');
      Query OK, 1 row affected (0.00 sec)
      
      mysql> select * from t1_innodb;
      +---+-------------------------------------------------------+
      | a | jdoc                                                  |
      +---+-------------------------------------------------------+
      | 1 | {"name": "Hannah Murphy", "company": "Murray-Kelley"} |
      | 2 | {"key1": "value1", "key2": "value2"}                  |
      +---+-------------------------------------------------------+
      2 rows in set (0.00 sec)
      
      mysql> INSERT INTO t1_innodb VALUES(3, '{"key3": "value3", "key4": "value4"}');
      Query OK, 1 row affected (0.00 sec)
      
      mysql> select * from t1_innodb;
      +---+-------------------------------------------------------+
      | a | jdoc                                                  |
      +---+-------------------------------------------------------+
      | 1 | {"name": "Hannah Murphy", "company": "Murray-Kelley"} |
      | 2 | {"key1": "value1", "key2": "value2"}                  |
      | 3 | {"key3": "value3", "key4": "value4"}                  |
      +---+-------------------------------------------------------+
      3 rows in set (0.00 sec)
      

      ROCKSDB:

      mysql> drop table if exists t1_rocksdb;
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE t1_rocksdb (a int, jdoc JSON, primary key(a)) engine=rocksdb;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> INSERT INTO t1_rocksdb VALUES(1, '{"name": "Hannah Murphy", "company": "Murray-Kelley"}');
      Query OK, 1 row affected (0.00 sec)
      
      mysql> select * from t1_rocksdb;
      +---+-------------------------------------------------------+
      | a | jdoc                                                  |
      +---+-------------------------------------------------------+
      | 1 | {"name": "Hannah Murphy", "company": "Murray-Kelley"} |
      +---+-------------------------------------------------------+
      1 row in set (0.00 sec)
      
      mysql> INSERT INTO t1_rocksdb VALUES(2, '{"key1": "value1", "key2": "value2"}');
      Query OK, 1 row affected (0.00 sec)
      
      mysql> select * from t1_rocksdb;
      +---+--------------------------------------+
      | a | jdoc                                 |
      +---+--------------------------------------+
      | 1 | {"key1": "value1", "key2": "value2"} |
      | 2 | {"key1": "value1", "key2": "value2"} |
      +---+--------------------------------------+
      2 rows in set (0.00 sec)
      
      mysql> INSERT INTO t1_rocksdb VALUES(3, '{"key3": "value3", "key4": "value4"}');
      Query OK, 1 row affected (0.00 sec)
      
      mysql> select * from t1_rocksdb;
      +---+--------------------------------------+
      | a | jdoc                                 |
      +---+--------------------------------------+
      | 1 | {"key3": "value3", "key4": "value4"} |
      | 2 | {"key3": "value3", "key4": "value4"} |
      | 3 | {"key3": "value3", "key4": "value4"} |
      +---+--------------------------------------+
      3 rows in set (0.00 sec)
      

      DISCLAIMER: I was using the binary built from George Lorch compression libraries branch so it might not have everything merged, but since upstream was on 5.6 I'm not hopeful that this issue would be resolved just with upstream merges. I need to check this more!

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                george.lorch George Lorch
                Reporter:
                tomislav.plavcic@percona.com Tomislav Plavcic
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: