Bug #90876 Shell 8.0.11 gives error 5115 on adding documents to collection in Server 5.7
Submitted: 15 May 2018 20:07 Modified: 21 Jun 2018 13:25
Reporter: Georgi Sotirov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Document Store: MySQL Shell Severity:S3 (Non-critical)
Version:8.0.11 OS:Windows (Windows 7)
Assigned to: CPU Architecture:x86
Tags: regression

[15 May 2018 20:07] Georgi Sotirov
Description:
MySQL X Protocol is available as plugin since MySQL 5.7.12, so when using recently released MySQL Shell 8.0.11 connected to a MySQL Server 5.7.22 with enabled MySQL X Plugin, I have a problem adding documents without explicitly specifying identifier. This is illustrated by the following session transcript:

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 1234557 (X protocol)
Server version: 5.7.22-log Source distribution
No default schema selected; type \use to set one.

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

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

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

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

Or in plain words: I connect to a MySQL 5.7.22 Server with enabled MySQL X Plugin, I create test schema and collection persons. However, when I try to add a document to the collection I get error 'ERROR: 5115: Document is missing a required field'.

I do not have problem with MySQL Shell 8.0.11 connected to MySQL Server 8.0.11 Server or with MySQL Shell 1.0.11 connected to MySQL Server 5.7.22, but consider the following information.

In MySQL 5.7 Reference manual, chapter 19.4.4.2 Add Documents (see https://dev.mysql.com/doc/refman/5.7/en/mysql-shell-tutorial-javascript-collections-add.ht...), 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).

However, in chapter 5.1.1 Understanding Document IDs of X DevAPI User Guide (see https://dev.mysql.com/doc/x-devapi-userguide/en/understanding-automatic-document-ids.html) 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.

The above excerpts from the specifications confirm my observations. Apparently, MySQL 8.0.11 expects always the server to generate the identifier, but this cannot be expected by Server versions before 8.0.11, so effectively the document remains without identifier hence error is raised.

How to repeat:
1. Connect with MySQL Shell 8.0.11 to a MySQL Server 5.7 with enabled X Plugin.
2. Create a collection.
3. Try to add document without identifier to the collection.

Suggested fix:
If MySQL Server 5.7 continue to be supported, then MySQL Shell 8.0 should properly consider MySQL Server's version and either

* generate an UUID identifier, if MySQL Server version is prior to 8.0.11; or
* leave the server to generate the identifier in the new way for better InnoDB indexing.
[16 May 2018 6:14] MySQL Verification Team
Hello Georg,

Thank you for the report!

Thanks,
Umesh
[21 Jun 2018 13:25] Margaret Fisher
Posted by developer:
 
Documentation change made in
https://dev.mysql.com/doc/refman/8.0/en/mysql-shell-tutorial-javascript-collections-add.ht...
and
https://dev.mysql.com/doc/refman/8.0/en/mysql-shell-tutorial-python-documents-add.html

Each document requires an identifier field called _id. The value of the _id field must be unique among all documents in the same collection. In MySQL Shell 1.0.11 (which was part of MySQL 5.7), if the document passed to the add() method did not contain the _id field, MySQL Shell as the client automatically inserted a field into the document and set the value to a generated universal unique identifier (UUID). In MySQL Shell 8.0.11 and higher, document IDs are generated by the server, not the client, so MySQL Shell does not now automatically set an _id value. A MySQL server at 8.0.11 or higher sets an _id value if the document does not contain the _id field. A MySQL server at an earlier 8.0 release or at 5.7 does not set an _id value in this situation, so you must specify it explicitly. If you do not, MySQL Shell returns error 5115 "Document is missing a required field".
[5 Jul 2022 23:18] Jason Mallory
Still seeing this issue with 8.0.29 using the json import tool:

ERROR: Document contains a field value that is not unique but required to be