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)

Storage Engine Vendor Meeting

Oracle will be hosting a MySQL Storage Engine Vendor Meeting next week. I have invited all the usual folks who participate but with the changes in MySQL 8, I may not know if you and or your company is contemplating a new storage engine or are planned to use MySQL 8. Last year we had about ten attendees discussing various topics and I would like to have you too there is you are interested. Please contact me by close of business this Friday if you are interested and will be in the Santa Clara area.

Array Ranges in MySQL JSON

Pretend you have a JSON array of data that looks roughly like the following.

mysql> insert into x(y) values('["a","b","c","d"]');
Query OK, 1 row affected (0.10 sec)

You could get all the values from that array using $[*]

mysql> select y->"$[*]" from x;
+----------------------+
| y->"$[*]"            |
+----------------------+
| ["a", "b", "c", "d"] |
+----------------------+
1 row in set (0.00 sec)

Or the individual members of the array with an index that starts with zero.

mysql> select y->"$[0]" from x;
+-----------+
| y->"$[0]" |
+-----------+
| "a"       |
+-----------+
1 row in set (0.00 sec)

But what about the times you want the last item in the array and really do not want to loop through all the items? How about using 'last'?

mysql> select y->"$[last]" as 'last' from x;
+------+
| last |
+------+
| "d"  |
+------+
1 row in set (0.00 sec)

Holey Moley! What is this? Well Roland Bouman, who was my predecessor on the MySQL Certification Team many years ago, still contributes to MySQL on a regular basis. He put in a (feature request for a JSON range operator.
So please thank Roland when you see him!!
Be sides 'last' there is 'to' too!.

mysql> select y->'$[last - 3 to last - 1]' as 'last three' from x;
+-----------------+
| last three      |
+-----------------+
| ["a", "b", "c"] |
+-----------------+
1 row in set (0.01 sec)

You can also use the ordinal number, here $[1], to strip off the first value of the array from the rest.

mysql> select y->'$[1 to last]' as 'strip first' from x;
+-----------------+
| strip first     |
+-----------------+
| ["b", "c", "d"] |
+-----------------+
1 row in set (0.00 sec)

This makes juggling array data in JSON columns much easier.