MySQL Forums
Forum List  »  Connector/Node.js

Overwriting _id column in nosql document store a good idea?
Posted by: David Godfrey
Date: December 15, 2021 04:16PM

We are using the xdevapi and node.js to build a new application. We will be using the latest MySQL version. I am a DBA, not a node.js developer.

I wonder if any applications use the _id value for anything outside of a look up? Wouldn't application-specific id values be more appropriate for day to day operations of a general application?

A developer is asking for unintended consequences of overriding the default _id value generated when creating a new document. Aside from performance issues and possible PK constraint violations, I wonder if I shouldn't just tell him to create his own internal application IDs and stop using the generated _id values the database is creating?

I pointed him to this statement from (https://dev.mysql.com/doc/x-devapi-userguide/en/understanding-automatic-document-ids.html ) where it states

"It is possible to override the automatic generation of document IDs by manually including an ID in an inserted document.

Important
X Plugin is not aware of the data inserted into the collection, including any manual document IDs you use. When using manual document IDs, you must ensure that they do not clash with any IDs that might ever be generated automatically by the server (see Document ID Generation for details), in order to avoid any errors due to primary key duplication.

Whenever an _id field value is not present in an inserted document, the server generates an _id value. The generated _id value used for a document is returned to the client as part of the Result (Result for Connector/J) object of the add() operation. If you are using X DevAPI on an InnoDB Cluster, the automatically generated _id must be unique within the whole cluster. By setting the mysqlx_document_id_unique_prefix to a unique value per cluster instance, you can ensure document IDs are unique across all the instances.

The _id field must be sequential (always incrementing) for optimal InnoDB insertion performance (at least within a single server). The sequential nature of _id values is maintained across server restarts."

Any opinions on this matter would be appreciated!

Options: ReplyQuote


Subject
Written By
Posted
Overwriting _id column in nosql document store a good idea?
December 15, 2021 04:16PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.