| Bug #106688 | Cannot insert deep JSON object into DB using @mysql/xdevapi node library | ||
|---|---|---|---|
| Submitted: | 10 Mar 2022 5:44 | Modified: | 17 Mar 2022 12:06 |
| Reporter: | Joel Nation | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Document Store: X Plugin | Severity: | S2 (Serious) |
| Version: | Latest, 8.0.28 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[10 Mar 2022 16:17]
MySQL Verification Team
Hi,
On the server side everything is ok. What version of
- mysql server
- node.js interpreter
- mysql connector for node.js
are you using?
thanks
p.s. server side is ok, it is some parsing issue on the client/connector side just to see where exactly
mysql [localhost:8027] {msandbox} (test) > insert into t1 values (
-> '{
'> "a": {
'> "b": {
'> "c": {
'> "d": {
'> "e": {
'> "f": {
'> "g": {
'> "h": {
'> "i": {
'> "j": {
'> "k": {
'> "l": {
'> "m": {
'> "n": {
'> "o": {
'> "p": {
'> "q": {
'> "r": {
'> "s": {
'> "t": {
}
}
}
}
}
'> }
"u": {
'> "v": {
'> "w": {
'> "x": {
'> "y": {
'> "z": {
'> "a": {
'> "b": {
'> "c": {
'> "d": {
'> "e": {
'> "f": {
'> "g": {
'>
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> }
'> '
-> );
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8027] {msandbox} (test) > select * from t1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| jf |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"a": {"b": {"c": {"d": {"e": {"f": {"g": {"h": {"i": {"j": {"k": {"l": {"m": {"n": {"o": {"p": {"q": {"r": {"s": {"t": {"u": {"v": {"w": {"x": {"y": {"z": {"a": {"b": {"c": {"d": {"e": {"f": {"g": {}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[16 Mar 2022 5:55]
Joel Nation
Node Mysql library: "@mysql/xdevapi": "^8.0.27" Node: docker/node:16-alpine (Node 16.14.0) MySQL server: docker/mysql:latest (as of 5 days ago) (8.0.28-1debian10)
[17 Mar 2022 12:06]
MySQL Verification Team
Thank you Joel for the details.
[17 Mar 2022 15:34]
Rui Quelhas
Posted by developer:
This is an issue with the X Plugin. It's also easily reproducible with the MySQL Shell using the following script:
var config = {
user: 'root',
schema: 'deep_obj_test_s',
table: 'deep_obj_test_t'
}
var deep = {
a: {
b: {
c: {
d: {
e: {
f: {
g: {
h: {
i: {
j: {
k: {
l: {
m: {
n: {
o: {
p: {
q: {
r: {
s: {
t: {
u: {
v: {
w: {
x: {
y: {
z: {
a: {
b: {
c: {
d: {
e: {
f: {
g: null
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
var session = mysqlx.getSession({ user: config.user })
var schema
try {
schema = session.getSchema(config.schema)
} catch (err) {
schema = session.createSchema(config.schema)
}
schema.dropCollection(config.table)
var collection = schema.createCollection(config.table)
var res = collection.add(deep).execute()
print('affected items: ' + res.getAffectedItemsCount() + '\n')
if (session) {
session.dropSchema(config.schema)
session.close()
}
Assuming the script is available at "/path/to/script.js":
$ mysqlsh --file=/path/to/script.js
Parse error unserializing protobuf message (MySQL Error 5000)
at /path/to/script.js:88:32
in var res = collection.add(deep).execute()
In the meantime, I'll try to figure out what the problem might be, but the bug should be re-assigned to the X Plugin team.
[18 Mar 2022 9:46]
Rui Quelhas
Posted by developer: I've managed to find the root cause for this issue. The problem is that the X Plugin relies on the default protobuf recursion limit, which limits the number of embedded messages to 100 (https://developers.google.com/protocol-buffers/docs/reference/cpp/google.protobuf.io.coded...). In this case, the message generated in the client (both Connector/Node.js and MySQL Shell) exceeds that limit when a JavaScript/JSON object has 33 (or more) levels, because each (nested) object generates 3 different protobuf messages (99 messages) and they are encoded in a "Mysqlx.Crud.Insert.TypedRow" (https://github.com/mysql/mysql-server/blob/8.0/plugin/x/protocol/protobuf/mysqlx_crud.prot...) message which, in turn, is encoded in the top-level "Mysqlx.Crud.Insert" (https://github.com/mysql/mysql-server/blob/8.0/plugin/x/protocol/protobuf/mysqlx_crud.prot...) message. Thus, 99 + 1 + 1 = 101 messages. This can be observed by enabling the debug log with the "NODE_DEBUG" env variable. For instance, "NODE_DEBUG='protocol:outbound:Mysqlx.Crud.Insert'". PROTOCOL:OUTBOUND:MYSQLX.CRUD.INSERT <PID>: [INFO] { "collection": { "name": "deep_obj_test_t", "schema": "deep_obj_test_s" }, "data_model": "DOCUMENT", "row": [ { "field": [ { "type": "OBJECT", "object": { "fld": [ { "key": "a", "value": { "type": "OBJECT", "object": { "fld": [ { "key": "b", "value": { "type": "OBJECT", "object": { // ... } } } ] } } } ] } } ] } ] } Apparently, once upon a time, the error message sent by the server was a bit more clear, but it was being extracted through the use of private protobuf APIs, which have changed in the meantime. We can observe that change in the following commit: https://github.com/mysql/mysql-server/commit/16c99756ee308db24556927f0c058874f1d8e184 So, there are two alternatives here. The MySQL can somehow allow the recursion limit to be configurable (I guess it is possible), maybe via an option, or something else. Or, the clients can encode JavaScript/JSON objects as strings, which means they also risk hitting the protobuf message size limit (https://developers.google.com/protocol-buffers/docs/reference/cpp/google.protobuf.io.coded...) which is approximately 2GB max, but apparently is set at 64MB by default (https://stackoverflow.com/a/34186672). Either way, I think it is possible to somehow address this. I'll keep the bug open until there's some clarity on what to do about the underlying issue.
Description: When trying to insert a very deep payload (greater than 32 levels of depth) you get a 'Parse error unserializing protobuf mesage' with an error code of HY000, code 5000. The stack trace is as follows: Parse error unserializing protobuf message at SqlResultHandler.Object.<anonymous>.BaseHandler.<computed> (node_modules/@mysql/xdevapi/lib/Protocol/InboundHandlers/BaseHandler.js:121:17) at Array.entry (node_modules/@mysql/xdevapi/lib/Protocol/InboundHandlers/BaseHandler.js:92:29) at WorkQueue.Object.<anonymous>.WorkQueue.process (node_modules/@mysql/xdevapi/lib/WorkQueue.js:77:19) at Client.Object.<anonymous>.Client.handleServerMessage (node_modules/@mysql/xdevapi/lib/Protocol/Client.js:169:21) at Client.Object.<anonymous>.Client.handleNetworkFragment (node_modules/@mysql/xdevapi/lib/Protocol/Client.js:213:14) at TLSSocket.<anonymous> (node_modules/@mysql/xdevapi/lib/DevAPI/Connection.js:796:34) Inserting a document with one less level than the maximum works. Payload size does not seem to matter. How to repeat: Insert the following object into a collection (eg: collection.add): let deep = { a: { b: { c: { d: { e: { f: { g: { h: { i: { j: { k: { l: { m: { n: { o: { p: { q: { r: { s: { t: { u: { v: { w: { x: { y: { z: { a: { b: { c: { d: { e: { f: { g: { } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } Suggested fix: Something must be wrong with either an underlying JSON parsing library or the database is unable to handle this depth. I've inserted documents like this into Oracle Database using it's JSON support with no problem.