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

  8. It’s great to come across a blog every once in a while, that isn’t the same out of date rehashed material. Fantastic read.
    industrial safety course in chennai

  9. Hey, would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would enjoy your content. Please let me know. Thank you.
    offshore safety course in chennai

  10. I am delighted to come to such a wonderful blog. I am really very impressed to read from top to bottom. I read every single line and understand the essence of every single word. I appreciate all efforts.
    TeraStation 5400DN 8TB NAS

  11. This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here keep up the good work סנדלים למכירה

  12. Spyzie application is definitely worth your attention. Use one of the best spy apps to monitor your kids’ activities or other people remotely.

  13. Amazing information about Php & Mysql thanks for sharing such type of information please keep sharing.

    SEO Company in Lucknow
    | Software Development Company in Lucknow