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 ( 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.


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.


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)


  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.

  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:)

  3. This comment has been removed by the author.

  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

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

  6. In fact, have you though why the _id is a required field? According to the documentation ( 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...

  7. Reported to Oracle - see