Manual Insert into MySQLx Collection

Question

I have a large amount of JSON data that needs to be inserted into a MySQLx Collection table. The current Node implementation keeps crashing when I attempt to load my JSON data in, and I suspect it's because I'm inserting too much at once through the collection API. I'd like to manually insert the data into the database using a traditional SQL statement (in the hope that they will get me pass this NodeJs crash).

The problem is that I have this table def:

+--------------+---------------+------+-----+---------+-------------------+
| Field        | Type          | Null | Key | Default | Extra             |
+--------------+---------------+------+-----+---------+-------------------+
| doc          | json          | YES  |     | NULL    |                   |
| _id          | varbinary(32) | NO   | PRI | NULL    | STORED GENERATED  |
| _json_schema | json          | YES  |     | NULL    | VIRTUAL GENERATED |
+--------------+---------------+------+-----+---------+-------------------+

But when running insert into documents values ('{}', DEFAULT, DEFAULT) I get:

ERROR 3105 (HY000): The value specified for generated column '_id' in table 'documents' is not allowed.

I've tried with not providing the DEFAULTs, with NULL (but _id doesn't allow NULL even though that's the default), with 0 for _id, with numbers and with uuid_to_bin(uuid()) but I still get the same error.

How can I insert this data into the table directly (I'm using session.sql('INSERT...').bind(JSON.stringify(data)).execute() - using the @mysql/xdevapi library)

ruiquelhas · Answer 1 · 2022-08-29T15:03:11.917

The _id column is auto generated from the value of the namesake field in the JSON document. When you use the CRUD interface to insert documents, the X Plugin is capable of generating a unique value for this field. However, by executing a plain SQL statement, you are also by-passing that logic. So, you are able to insert documents if you generate the _ids yourself, otherwise you will bump into that error.

As an example (using crypto.randomInt()):

const { randomInt } = require('crypto')

session.sql('insert into documents (doc) values (?)')
  .bind(JSON.stringify({ _id: randomInt(Math.pow(2, 48) - 1) }))
  .execute()

Though I'm curious about the issue with the CRUD API and I wanted to see if I was able to reproduce it as well. How are you inserting those documents in that case and what kind of feedback (if any) is provided when it "crashes"?

Disclaimer: I'm the lead developer of the MySQL X DevAPI connector for Node.js

Manual Insert into MySQLx Collection

1 Answers1