Manually inserting into a 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 try to load JSON data, I suspect this is because I'm inserting too much data at once via the collection API. I want to manually insert data into the database using traditional SQL statements (hopefully they will get me through this NodeJs crash).

The problem is that I have this table definition:

+--------------+---------------+------+-----+---------+-------------------+
| 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 document value ('{}', DEFAULT, DEFAULT) I get:

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

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

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

Answer 1

The

_id column is automatically generated based on the value of the field with the same name in the JSON document. The X Plugin is able to generate unique values ​​for this field when you insert a document using the CRUD interface. However, by executing a simple SQL statement, you also bypass that logic. So if you generate the _id yourself you can insert into the document, otherwise you will get this 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()

Although I'm curious about the issue with the CRUD API and wanted to see if I could reproduce it as well. In this case, how do you insert these documents and what feedback (if any) is provided when it "crashes"?

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