MySQL Bugs: #80716: JSON data not validated if inserted via InnoDB memcache plugin

Bug #80716	JSON data not validated if inserted via InnoDB memcache plugin
Submitted:	13 Mar 2016 9:53	Modified:	31 Mar 2016 5:23
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Memcached	Severity:	S3 (Non-critical)
Version:	5.7.10, 5.7.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, json, Memcache

Description:
I can insert invalid data in a JSON column via the memcache interface.

How to repeat:
mysql [innodb_memcache] > show create table test.demo_test\G
*************************** 1. row ***************************
       Table: demo_test
Create Table: CREATE TABLE `demo_test` (
  `c1` varchar(32) NOT NULL,
  `c2` varchar(1024) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` bigint(20) unsigned DEFAULT NULL,
  `c5` int(11) DEFAULT NULL,
  `j` json DEFAULT NULL,
  `foo` varchar(200) GENERATED ALWAYS AS (json_extract(`j`,'$.foo')) STORED,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql [innodb_memcache] > select * from test.demo_test;
+----+------+------+------+------+----------------+-------+
| c1 | c2   | c3   | c4   | c5   | j              | foo   |
+----+------+------+------+------+----------------+-------+
| AA | NULL |    0 |    1 |    0 | {"foo": "bar"} | "bar" |
+----+------+------+------+------+----------------+-------+
1 row in set (0.00 sec)

get AA
VALUE AA 0 19

  
   foobar
END
set AA 0 0 5
xxxxx
STORED

mysql [innodb_memcache] > select * from test.demo_test;
ERROR 3142 (HY000): The JSON binary value contains invalid data.

get AA
VALUE AA 0 5
xxxxx
END

Suggested fix:
Ensuere memcache validate JSON data

Might be related to Bug #79691

Hello Daniël,

Thank you for the report.
Observed this with 5.7.11 build.

Thanks,
Umesh

Posted by developer:

[30 Mar 2016 21:32] Jimmy Yang

This is expected, since InnoDB memcached bypasses the optimizer and query execution. The JSON data validation is done by the server, and InnoDB memcached goes around it, and directly interacts with InnoDB.

So the user should add one additional layer themselves to validate the data
before pump into memcached interfaces.

The point is that the data returned and set via the innodb memcached plugin is in the INTERNAL format.  Which I don't believe is documented so it becomes very hard to do any validation on it. 

This is a real shame because for rapid memcached lookups I've had to go back to storing JSON in something that I can parse and re-serialize.  If there was a definition of the format or functions for creating and unpacking it then it would be very much more useful with innodb memcached.