I'm trying to run the example code from this page:
https://dev.mysql.com/doc/x-devapi-userguide/en/working-with-collections-basic-crud.html
The documentation says "After establishing a connection to a MySQL Server, a new collection that can hold JSON documents is created and several documents are inserted. Then, a find operation is executed to search for a specific document from the collection. Finally, the collection is dropped again from the database. The example assumes that the test schema exists and that the collection my_collection does not exist."
I had to modify the code to work with Node.js using the xdevapi connector 8.0.8 but I still cannot get the find to work.
Here is the modified version with 3 changes made by myself in an attempt to get it to work using the @mysql/xdevapi node module.
1.
var mysqlx = require('mysqlx');
becomes
const mysqlx = require('@mysql/xdevapi');
2.
Added ssl: false option to avoid the error message "Error: The server's X plugin version does not support SSL. Please refer to
https://dev.mysql.com/doc/refman/5.7/en/x-plugin-ssl-connections.html for more details on how to enable secure connections.
at capabilitiesSet.then.then.then.then.catch.err (/Users/andy/projects/express-dating/node_modules/@mysql/xdevapi/lib/Protocol/Client.js:141:19)
at process._tickCallback (internal/process/next_tick.js:109:7)"
3. Added
console.error(err);
To the .catch block
// --------------------------------------------------
// Connecting to MySQL Server and working with a Collection
const mysqlx = require('@mysql/xdevapi');
// Connect to server
mysqlx.getSession( {
host: 'localhost', port: '33060',
dbUser: 'root', dbPassword: 'mysql',
ssl: false
}).then(function(session) {
return session.getSchema('test');
}).then(function(db) {
// Create a new collection 'my_collection'
return db.createCollection('my_collection');
}).then(function(myColl) {
// Insert documents
return Promise.all([
myColl.add({name: 'Sakila', age: 15}).execute(),
myColl.add({name: 'Susanne', age: 24}).execute(),
myColl.add({name: 'Mike', age: 39}).execute()
]).then(function() {
// Find a document
return myColl.find('name like :name AND age < :age')
.bind({ name: 'S*', age: 20 }).limit(1).execute();
}).then(function(docs) {
// Print document
console.log(docs.fetchOne());
// Drop the collection
return myColl.drop();
});
}).catch(function(err) {
// Handle error
console.error(err);
});
When I run it I get the following error:
Function {
message: 'Parse error on line 1:\nname like :name AND age <\n-----^\nExpecting \'.\', \'(\', got \'like\'',
hash:
{ text: 'like',
token: 'like',
line: 0,
loc: { first_line: 1, last_line: 1, first_column: 0, last_column: 4 },
expected: [ '\'.\'', '\'(\'' ] } }
The Mysql Log shows the following:
2017-10-20T11:12:39.083327Z 57 Query /* xplugin authentication */ SELECT @@require_secure_transport, `authentication_string`,`account_locked`, (`password_expired`!='N') as `is_password_expired`, @@disconnect_on_expired_password as `disconnect_on_expired_password`, @@offline_mode and (`Super_priv`='N') as `is_offline_mode_and_isnt_super_user`,`ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject` FROM mysql.user WHERE 'root' = `user` AND 'localhost' = `host`
2017-10-20T11:12:39.090688Z 57 Query CREATE TABLE `test1137`.`my_collection` (doc JSON,_id VARCHAR(32) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4 ENGINE=InnoDB
2017-10-20T11:12:39.107542Z 57 Query INSERT INTO `test1137`.`my_collection` (doc) VALUES ('{\"name\":\"Sakila\",\"age\":15,\"_id\":\"93C3E1878BBAAB5B11E7B5879578BDD0\"}')
2017-10-20T11:12:39.109389Z 57 Query INSERT INTO `test1137`.`my_collection` (doc) VALUES ('{\"name\":\"Susanne\",\"age\":24,\"_id\":\"93C3E1878BBABEB911E7B58795798130\"}')
2017-10-20T11:12:39.110036Z 57 Query INSERT INTO `test1137`.`my_collection` (doc) VALUES ('{\"name\":\"Mike\",\"age\":39,\"_id\":\"93C3E1878BBABCE411E7B5879579CF40\"}')