Saturday, April 21, 2018

MySQL Document Store Document IDs.

Yesterday I was presenting on the MySQL Document Store and was asked if the _id fields created by the server as an InnoDB primary key is a UUID.  I knew that it was not a UUID but I had to hit the documentations (https://dev.mysql.com/doc/x-devapi-userguide/en/understanding-automatic-document-ids.html) to find out what the document ID really is -- a very interesting piece of information.

The Details

 If you are inserting a document lacking a _id key, the server generates a value. The _id is 32 bits of a unique prefix (4 bytes), a time stamp (8 bytes), and serial number (16 bytes). The prefix is assigned by the InnoDB Cluster to help ensure uniqueness across a cluster. The timestamp is the encoded startup time of the server.  The serial numbers uses the auto increment offset and auto increment increment server variables .  From the manual page:
This document ID format ensures that:
The primary key value monotonically increments for inserts originating from a single server instance, although the interval between values is not uniform within a table.
When using multi-primary Group Replication or InnoDB cluster, inserts to the same table from different instances do not have conflicting primary key values; assuming that the instances have the auto_increment_* system variables configured properly.

Properties

Once set, the _id can not be set to another value. Inserting your own value overrides the server assignment. And if you attempt to insert a document with the same _id you the server will generate a duplicate primary key error.

The _id values must be always increasing and sequential for optimal InnoDB performance. The server will keep track of theses numbers across restarts.

The generated _id values for each table/collection  are unique across instances to avoid primary key conflicts and minimize transaction certification in multi-primary Group Replication or InnoDB cluster environments.

Required?

So, you are adding a document to a collection and you get an ERROR: 5115!  That means in the following cast that the _id key/value pair is needed:

JS> db.foobar.add(
-> {
->  Data: "This is a test!"
-> }
-> )
->
ERROR: 5115: Document is missing a required field

JS> db.foobar.add( 
{  Data: "This is a test!" , 
-> _id: "first"
} 
)
Query OK, 1 item affected (0.0076 sec)

9 comments:

  1. This is an improvement as in the previous version the _id was not sequential and performance wasn't optimal for InnoDB of course in case of inserts.

    ReplyDelete
  2. I am really happy to say it’s an interesting post to read. I learn new information from your article, you are doing a great job.
    php training in IndoreKeep Posting:)

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This post is a knowledgeable and informative post. Actually PHP is a beautiful source for developing a database driven web application. I have to learning for lot of information for these sites. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing!!
    Thanks and Regards,
    Mohit Verma
    Seo Company in Lucknow |Seo Services in Lucknow

    ReplyDelete
  5. Really your content is so informative. So please share some more content ..
    PHP training in Delhi

    ReplyDelete
  6. In fact, have you though why the _id is a required field? According to the documentation (https://dev.mysql.com/doc/refman/5.7/en/mysql-shell-tutorial-javascript-collections-add.html and the same in 8.0):

    Each document requires an identifier field called _id. The value of the _id field must be unique among all documents in the same collection. If the document passed to the add() method does not contain the _id field, MySQL Shell automatically inserts a field into the document and sets the value to a generated universal unique identifier (UUID).

    I have error in MySQL Shell 8.0.11 connected to MySQL Server 5.7.22, but I do not have this problem when connected to MySQL Server 8.0.11 or when connected through MySQL Shell 1.0.11, which I find strange...

    ReplyDelete
  7. Reported to Oracle - see https://bugs.mysql.com/bug.php?id=90876

    ReplyDelete
  8. It's nice article to improve knowledge.thank you.
    web programming tutorial
    welookups

    ReplyDelete
  9. I was getting the same error while adding a document into my collectio. The issue was with property ID. Thanks for the code. Issue solved now. wordpress development services

    ReplyDelete