My personal and professional life

2018-05-15

MySQL Shell 8 and automatically generated document IDs

Why I'm getting error ERROR: 5115: Document is missing a required field in MySQL Shell 8.0.11 connected to MySQL Server 5.7.22? Consider the following session in which I'm  first connecting to the server, creating a new schema, then a collection inside the schema and finally trying to create a document without explicitly providing identifier:

MySQL Shell 8.0.11

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


MySQL JS> \connect root@server
Creating a session to 'root@
server'
Enter password: ************
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1027578 (X protocol)
Server version: 5.7.22-log Source distribution
No default schema selected; type \use <schema> to set one.

MySQL [server+ ssl] JS> session.createSchema('test')
<schema:test>

MySQL [
server+ ssl] JS> \use test
Default schema `test` accessible through db.

MySQL [
server+ ssl/test] JS> per_coll = db.createCollection('persons')
<collection:persons>

MySQL [
server+ ssl/test] JS> per_coll.add({name:'Georgi'})
ERROR: 5115: Document is missing a required field

MySQL [
server+ ssl/test] JS> \py
Switching to Python mode...

MySQL [server+ ssl/test] Py> pcol = db.get_collection('persons')

MySQL [server+ ssl/test] Py> pcol.add({'name':'Georgi'})
ERROR: 5115: Document is missing a required field


In this case MySQL Shell should generate the identifier as specified in the manual (see MySQL 5.7 Reference manual, chapter 19.4.4.2 Add Documents), where it's written:
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).
When connected to MySQL 8.0 Server there is no such problem:

MySQL Shell 8.0.11

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


MySQL JS> \connect root@mysql8
Creating a session to 'root@mysql8'
Enter password: ************
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 6646 (X protocol)
Server version: 8.0.11 Source distribution
No default schema selected; type \use <schema> to set one.

MySQL [mysql8+ ssl] JS> session.createSchema('test')
<schema:test>

MySQL [mysql8+ ssl] JS> \use test
Default schema `test` accessible through db.

MySQL [mysql8+ ssl/test] JS> per_coll = db.createCollection('persons')
<collection:persons>

MySQL [mysql8+ ssl/test] JS> per_coll.add({name:'Georgi'})
Query OK, 1 item affected (0.2450 sec)


MySQL [192.168.79.46+ ssl/test2] JS> per_coll.find()
[
    {
        "_id": "00005adc69630000000000000002",
        "name": "Georgi"
    }
]
1 document in set (0.2035 sec)


I also tried connecting MySQL Server 5.7 with MySQL Shell 1.0 and again no problem:

$ mysqlsh
MySQL Shell 1.0.11

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.

Currently in Python mode. Use \sql to switch to SQL mode and execute queries.
mysql-py> \connect root@localhost
Creating a Session to 'root@localhost'
Enter password:
Your MySQL connection id is 1028442 (X protocol)
Server version: 5.7.22-log Source distribution
No default schema selected; type \use <schema> to set one.
mysql-py> session.create_schema('test')
<schema:test>
mysql-py> \use test
Schema `test` accessible through db.
mysql-py> pcol = db.create_collection('persons')
mysql-py> pcol.add({'name':'Georgi'})
Query OK, 1 item affected (0.16 sec)
mysql-py> pcol.find()
[
    {
        "_id": "aa0960462c58e811b27610feed07afc3",
        "name": "Georgi"
    }
]
1 document in set (0.00 sec)


The post MySQL Document Store Document IDs. by Dave Stokes is the top (and in fact only) result in Google for the error (when searching for exact match with double quotes). It points to chapter 5.1.1 Understanding Document IDs of X DevAPI User Guide, in which it's written:
X DevAPI relies on server based document ID generation, added in MySQL version 8.0.11, which results in sequentially increasing document IDs across all clients.
...
Whenever an _id field value is not present in an inserted document, the server generates an _id value.
Which quite explains the issue. Apparently, MySQL Shell 8.0.11 expects that the server would generate the identifier (for  _id column) without considering whether the Server is 5.7 or 8.0, which for me is a bug to report later.

Update 2018-05-16: Oracle verified my bug report (see bug 90876). Hope it would be fixed.

No comments: