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)
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
The
_idcolumn 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_idyourself 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