NoSQL Protocol Module

The nosqlprotocol module allows a MariaDB server or cluster to be used as the backend of an application using a MongoDB® client library. Internally, all documents are stored in a table containing two columns; an id column for the object id and a doc column for the document itself.

When the MongoDB® client application issues MongoDB protocol commands, either directly or indirectly via the client library, they are transparently converted into the equivalent SQL and executed against the MariaDB backend. The MariaDB responses are then in turn converted into the format expected by the MongoDB® client library and application.

Configuring

There are a number of parameters with which the behavior of nosqlprotocol can be adjusted. A minimal configuration looks like:

[TheService]
type=service
...

[NoSQL-Listener]
type=listener
service=TheService
protocol=nosqlprotocol
nosqlprotocol.user=the_user
nosqlprotocol.password=the_password
port=17017

nosqlprotocol.user and nosqlprotocol.password specify the credentials that will be used when accessing the backend database or cluster. Note that the same credentials will be used for all connecting MongoDB® clients.

Since nosqlprotocol is a listener, there must be a service to which the client requests will be sent. Nosqlprotocol places no limitations on what filters, routers or backends can be used.

To configure the same listener with MaxCtrl, the parameters must be passed in a JSON object in the following manner:

maxctrl create listener TheService MongoDB-Listener --protocol=nosqlprotocol 'nosqlprotocol={"user":"the_user", "password": "the_password"}'

All the parameters that the nosqlprotocol module takes must be passed in the same JSON object.

A complete example can be found at the end of this document.

Authentication

Nosqlprotocol supports SCRAM authentication as implemented by MongoDB®. The mechanisms SCRAM-SHA-1 and SCRAM-SHA-256 are both supported.

If nosqlprotocol has been setup so that no authentication is required, then when connecting only the host and port should be provided, but neither a username nor a password.

For instance, if the MongoDB Node.JS Driver is used, then the connection string should look like:

const uri = "mongodb://127.0.0.1:17017"

Similarly, if the Mongo Shell is used, only the host and port should be provided:

$ mongo --host 127.0.0.1 --port 17017
MongoDB shell version v4.4.1
...
>

NoSQL and MariaDB Users

A MariaDB user consists of a name and a host part. A user 'user'@'%' and a user 'user'@'127.0.0.1' are completely different. The host part specifies where a user may connect from, with % being a wildcard that matches all hosts. What data a user is allowed to access and modify is specified by what privileges are granted to the user.

A NoSQL user is somewhat different. It is created in the context of a particular database, so there may be a user userx in the database dbA and different user with the same name userx in the database dbB. What hosts a user may connect from can be restricted, but that is a property of the user and not an implicit part of it. What data a user is allowed to access and modify is specified by the roles that have been assigned to the user.

From the above it should be clear that there is not a 1-to-1 correspondence between the concept of a user in NoSQL and the concept of a user in MariaDB, but that some additional conventions are needed.

To make it possible to have different NoSQL users with the same name, the database in whose context the user is created is prepended to the user name, separated with a dot, when the MariaDB user is created.

This is perhaps easiest to illustrate using an example:

MariaDB [(none)]> select user, host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| bob         | %         |
| mysql       | localhost |
+-------------+-----------+
2 rows in set (0.001 sec)

Currently there are two user accounts defined. Even though there is a user bob, creating a NoSQL user bob succeeds.

> use test;
switched to db test
> db.runCommand({createUser: "bob", pwd: "bobspwd", roles: []});
{ "ok" : 1 }

If we now, from the MariaDB prompt, check the users we will see:

MariaDB [(none)]> select user, host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| bob         | %         |
| test.bob    | %         |
| mysql       | localhost |
+-------------+-----------+
3 rows in set (0.001 sec)

The MariaDB user corresponding to the NoSQL user bob, created in the context of the database test, has test as a prefix.

The mariadb database

The fact that NoSQL users have the database embedded in the MariaDB name may be inconvenient if the same data is accessed both as NoSQL via nosqlprotocol and as SQL directly from MariaDB. It also makes it impossible to use an existing MariaDB account from NoSQL.

To provide a solution for this problem, the database mariadb is treated in a specific fashion. A user created in the context of the mariadb database is created in the MariaDB server without the database prefix. If we now try to create a user bob in the mariadb database it will fail, because the user 'bob'@'%' exists already.

> use mariadb
switched to db mariadb
> db.runCommand({createUser: "bob", pwd: "bobspwd", roles: []});
{
    "ok" : 0,
    "errmsg" : "User \"bob\" already exists",
    "code" : 51003,
    "codeName" : "Location51003"
}

If we create a user with another name it will succeed.

> db.runCommand({createUser: "alice", pwd: "alicespwd", roles: []});
{ "ok" : 1 }

And if we check the situation from MariaDB,

MariaDB [(none)]> select user, host from mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| alice       | %         |
| bob         | %         |
| test.bob    | %         |
| mysql       | localhost |
+-------------+-----------+
4 rows in set (0.001 sec)

we will see that alice was created without a database prefix.

Roles and Privileges

When creating a user nosqlprotocol accepts all roles as predefined by MongoDB®, but not all of them are translated into GRANT privileges. The following table shows what privilege(s) a particular role is converted to.

The following roles are shorthands for several other roles.

dbOwner differs from root in that the privileges of the former apply only to a particular database, while the privileges of the latter apply to all databases. However, the role root can only be assigned to a user in the admin database.

In addition there are AnyDatabase versions of dbAdmin, read and readWrite (e.g readAnyDatabase) that can be assigned to a user in the admin database. If so, then the privilege is granted on *.*, otherwise on <db>.*.

If the root role is assigned to a user in the admin database, then the privileges are granted on *.*, otherwise on <db>.*.

Other pre-defined roles are recognized and stored in the local nosqlprotocol account database, but they do not affect what privileges are granted to the MariaDB user. Currently user-defined roles are not supported.

Client Authentication

Authenticationwise nosqlprotocol can be used in three different ways: - Anonymously - Shared credentials - Unique credentials

Anonymously

If there is an anonymous user on the MariaDB server and if nosqlprotocol is configured without a user/password, then all nosqlprotocol clients will access the MariaDB server as anonymous users.

Note that the anonymous MariaDB user is only intended for testing and should in general not be used, but deleted.

Shared Credentials

If nosqlprotocol is configured with

...
nosqlprotocol.user=theuser
nosqlprotocol.password=thepassword

then each MongoDB® client will use those credentials when accessing the MariaDB server. Note that from the perspective of the MariaDB server, it is not possibe to distinguish between different MongoDB® clients.

Unique Credentials

If nosqlprotocol authentication has been taken into use and a MongoDB® client authenticates, either when connecting or later, then the credentials of MongoDB® client will be used when accessing the MariaDB server.

Note that even if nosqlprotocol authentication has been enabled, authentication is not required, and if the MongoDB® client has not authenticated itself, the credentials specified with nosqlprotocol.[user|password] (or the anonymous user) will be used when accessing the MariaDB server.

Enforce Authentication

To enforce authentication, specify

nosqlprotocol.authentication_required=true

in the configuration. If authentication is required, then any command that requires access to the MariaDB server will fail, unless the client has authenticated.

Authorization

By default nosqlprotocol does no authorization. However, a nosqlprotocol client is always subject to the authorization performed by the MariaDB server.

When nosqlprotocol authorization is enabled by adding

nosqlprotocol.authorization_enabled=true

to the configuration file, some commands will be subject to authorization, by nosqlprotocol. The following table lists the commands and what role they require.

It is important to note that even if nosqlprotocol authorization is enabled, the MariaDB server has the final word. That is, even if the roles of a user would be sufficient for a particular operation, if the granted privileges are not, the operation will not succeed. There may be a mismatch between roles and grants, for instance, if the wrong roles were specified when the user was added, or if the grants have been altered directly and not via nosqlprotocol.

Bootstrapping the Authentication/Authorization

In order to enable authorization you need to have NoSQL users and those can be created with createUser or added with mxsAddUser.

If you want to create a user, then you first need to configure nosqlprotocol with credentials that are sufficient for creating a user:

nosqlprotocol.user = user_with_privileges_for_creating_a_user
nosqlprotocol.password = the_users_password

At this point nosqlprotocol.authentication_required and nosqlprotocol.authorization_enabled should both be false. Note that as those are their default values, they do not have to be specified.

Start MaxScale and connect to it with the MongoDB® command line client

$ mongo --port 17017
...
>

Then create the user.

> use admin;
switched to db admin
> db.runCommand({createUser: "nosql_admin", pwd: "nosql_pwd", roles: ["userAdmin"]});
{ "ok" : 1 }

Alternatively you can add an existing user. Note that it should be added to the mariadb database, unless it was created with the convention of having the database as a prefix, e.g. db.bob.

> use mariadb;
switched to db admin
> db.runCommand({mxsAddUser: "bob", pwd: "bob_pwd", roles: ["userAdmin"]});
{ "ok" : 1 }

Now you should shutdown MaxScale and add the entries

nosqlprotocol.authentication_required=true
nosqlprotocol.authorization_enabled=true

and start MaxScale.

The nosqlprotocol.user and nosqlprotocol.password can be removed but as they will be ignored with nosqlprotocol.authentication_required=true being present, it is not mandatory.

If you now try to create a user when not having been authenticated or when authenticated as a user without the userAdmin role, the result will be:

> use test;
switched to db test
> db.runCommand({createUser: "alice", pwd: "alices_pwd", roles: []});
{
    "ok" : 0,
    "errmsg" : "command createUser requires authentication",
    "code" : 13,
    "codeName" : "Unauthorized"
}

NOTE When a client authenticates, the password will not be transferred in cleartext over the network, so, even without SSL, it is not possible to gain access to a password by monitoring the network traffic.

However, when a user is created or added (or the password is changed), the password will be transferred in cleartext. To prevent eavesdropping, create/add users when connecting over a domain socket, or use TLS/SSL

TLS/SSL

Since nosqlprotocol is a regular protocol module used in a listener, the TLS/SSL support of listeners is available. Please see TSLSSL encryption for details.

Local Account Database

So as to be able to log in on behalf of clients, nosqlprotocol must know their password. As the password is not transferred to nosqlprotocol during the authentication in a way that could be used when logging into MariaDB, the password must be stored locally when the user is created with createUser or added with mxsAddUser.

Note that the password is not stored in cleartext but as three different hashes; hashed with sha1 for use with MariaDB, salted and hashed with sha1 for use with the SCRAM-SHA-1 authentication mechanism (if that is enabled for the user) and salted and hashed with sha256 for use with the SCRAM-SHA-256 authentication mechanism (if that is enabled for the user).

The account information of nosqlprotocol is stored in an sqlite3 database whose name is <libdir>/nosqlprotocol/<listener-name>-v1.db, where <libdir> is the libdir of MaxScale, typically /var/lib/maxscale, <listener-name> is the name of the listener section in the MaxScale configuration file, and -v1 a suffix for making schema evolution easier, should there be a need for that.

For instance, given a configuration like

[NoSQL-Listener]
type=listener
service=TheService
protocol=nosqlprotocol
...

the account information will be stored in the file <libdir>/nosqlprotocol/NoSQL-Listener-v1.db.

Note that since the database name is derived from the listener name, changing the name of the listener in the configuration file will have the effect of making all accounts disappear. To retain the accounts, the database file should also be renamed.

At first startup, the nosqlprotocol directory and the file NoSQL-Listener-v1.db will be created. They will be created with file permissions that only allow MaxScale access. At subsequent startups the permissions will be checked and MaxScale will refuse to start if the permissions allow access to others.

We strongly recommend that no manual modifications are made to the database.

Note that we make no guarantees that the way in which the account information is stored by nosqlprotocol will remain the same even between maintenance releases. We do guarantee, however, that even if the way in which the account information is stored changes, existing account information will automatically be converted and no manual intervention, such as re-creation of accounts, will be needed.

Wire Protocol

Nosqlprotocol fully supports wire protocol version 6 and only provides rudimentary support for earlier wire protocol versions, but reports at startup that it would support versions 0 to 6. The reason is that some client libraries are buggy and use an old wire protocol version if the server claims to support only version 6. Consequently, one should use a client library version that at least supports wire protocol version 6.

Client Library

As the goal of nosqlprotocol is to implement, to the extent that it is feasible, the wire protocol and the database commands the way MongoDB® implements them, it should be possible to use any language specific driver.

However, during the development of nosqlprotocol, the only client library that has been verified to work is version 3.6 of MongoDB Node.JS Driver.

Parameters

Using the following parameters, the behavior of nosqlprotocol can be adjusted. As they are not generic listener parameters, but specific to nosqlprotocol they must be qualified with the nosqlprotocol-prefix.

For instance:

[NoSQL-Listener]
type=listener
service=TheService
protocol=nosqlprotocol
nosqlprotocol.user=the_user
nosqlprotocol.password=the_password
nosqlprotocol.on_unknown_command=return_error
...

user

• Type: string
• Mandatory: No
• Default: ""

Specifies the user to be used when connecting to the backend, if the MongoDB® client is not authenticated.

password

• Type: string
• Mandatory: No
• Default: ""

Specifies the password to be used when connecting to the backend, is the MongoDB® client is not authenticated. Note that the same user/password combination will be used for all unauthenticated MongoDB® clients connecting to the same listener port.

authentication_required

• Type: boolean
• Mandatory: No
• Default: false

Specifies whether the client always must authenticate. If authentication is required, it does not matter whether user and password have been specified, the client must authenticate.

Authentication should not be required before users have been created with createUser or added with mxsAddUser, with authentication being optional and authorization being disabled.

NOTE: All client activity is always subject to authorization performed by the MariaDB server.

authorization_enabled

• Type: boolean
• Mandatory: No
• Default: false

Specifies whether nosqlprotocol itself should perform authorization in the context of the commands mxsAddUser, mxsRemoveUser and mxsUpdateUser. Authorization should not be enabled before users have been created with createUser or added with mxsAddUser with authorization being disabled.

NOTE: All client activity is always subject to authorization performed by the MariaDB server.

host

• Type: string
• Mandatory: No
• Default: "%"

Specifies the host to be used when a MariaDB user is created via nosqlprotocol. By default all users are created as ...@'%', which means that it is possible to connect to the MariaDB server from any host using the credentials of the created user. For tighter security, the IP-address of the MaxScale host can be specified.

NOTE: This value does not specify from which host it is allowed to connect to MaxScale.

on_unknown_command

• Type: enumeration
• Mandatory: No
• Values: return_error, return_empty
• Default: return_error

Specifies what should happen in case a clients sends an unrecognized command.

Enumeration values:

• return_error: An error document is returned.
• return_empty: An empty document is returned.

log_unknown_command

• Type: boolean
• Mandatory: No
• Default: false

Specifies whether an unknown command should be logged. This is primarily for debugging purposes, to find out whether a client uses a command that currently is not supported.

auto_create_databases

• Type: boolean
• Mandatory: No
• Default: true

Specifies whether databases should automatically be created, as needed.

Note that setting this parameter to true, without also setting auto_create_tables to true, has no effect at all.

auto_create_tables

• Type: boolean
• Mandatory: No
• Default: true

Specifies whether tables should automatically be created, as needed.

Note that this applies only if the relevant database already exists. If a database should also be created if needed, then auto_create_databases must also be set to true.

id_length

• Type: count
• Mandatory: No
• Range: [35, 2048]
• *Default: 35

Specifies the length of the id column in tables that are automatically created.

ordered_insert_behavior

• Type: enumeration
• Mandatory: No
• Values: atomic, default
• Default: default

Enumeration values:

• default: Each document is inserted using a separate INSERT, either in a multi-statement or in a compound statement. Whether an error causes the remaining insertions to be aborted, depends on the value of ordered specified in the insert command.
• atomic: If the value of ordered in the insert command is true (the default) then all documents are inserted using a single INSERT statement, that is, either all insertions succeed or none will. If ordered is false, then the behavior is as in the default case.

What combination of ordered_insert_behavior and ordered (in the insert command document) is used, has an impact on the performance. Please see the discussion at insert.

cursor_timeout

• Type: duration
• Mandatory: No
• Default: 60s

Specifies how long a cursor can be idle, that is, not accessed, before it is automatically closed.

debug

• Type: enumeration (multiple values separated by , can be specified)
• Mandatory: No
• Values: none, in, out, back
• Default: none

Specifies what should be logged as notice messages.

Enumeration values:

• none: Nothing is logged.
• in: The incoming protocol command is logged.
• out: The outgoing SQL sent to the backend is logged.
• back: The response sent back to the client is logged.

So, specify

nosqlprotocol.debug=in,out,back

to have the incoming command, the corresponding SQL sent to the backend and the resulting response sent to the client logged.

Databases and Tables

By default, nosqlprotocol automatically creates databases as needed. The default behavior can be changed by setting auto_create_databases to false. In that case, databases must manually be created.

Each MongoDB® collection corresponds to a MariaDB table with the same name. However, it is always possible to access a collection irrespective of whether the corresponding table exists or not; it will simply appear to be empty.

Inserting documents into a collection, whose corresponding table does not exist, succeeds, provided auto_create_tables is true, as the table will in that case be created.

When nosqlprotocol creates a table, it uses a statement like

CREATE TABLE name (id VARCHAR(35) AS (JSON_COMPACT(JSON_EXTRACT(doc, "$._id"))) UNIQUE KEY,
                   doc JSON,
                   CONSTRAINT id_not_null CHECK(id IS NOT NULL));

where the length of the VARCHAR is specified by the value of id_length, whose default and minimum is 35.

NOTE If the tables are created manually, then the CREATE statement must contain a similar AS-clause as the one above and should contain a similar constraint.

Note that nosqlprotocol does not in any way verify that the table corresponding to a collection being accessed or modified does indeed have the expected columns id and doc of the expected types, but it simply uses the table, which will fail if the layout is not the expected one.

To reduce the risk for confusion, the recommendation is to use a specific database for tables that contain documents.

Operators

The following operators are currently supported.

Query and Projection Operators

Comparison Query Operators

• $eq
• $gt
• $gte
• $in
• $lt
• $lte
• $ne
• $nin

Logical Query Operators

• $and
• $not
• $nor
• $or
• $alwaysFalse
• $alwaysTrue

Element Query Operators

• $exists
• $type

$type

When $type is used, it will be converted into a condition involving one or more JSON_TYPE comparisons. The following subset of types can be used in $type queries:

The "number" alias is supported and will match values whose MariaDB type is DOUBLE or INTEGER.

Evaluation Query Operators

• $mod
• $regex

Array Query Operators

• $all
• $elemMatch
• $size

$elemMatch

As arguments, only the operators $eq and $ne are supported.

Update Operators

Field Update Operators

• $bit
• $currentDate
• $inc
• $max
• $min
• $mul
• $pop
• $push
• $rename
• $set
• $unset

Database Commands

The following commands are supported. At each command is specified what fields are relevant for the command.

All non-listed fields are ignored; their presence or absence have no impact, unless otherwise explicitly specified.

Aggregation Commands

count

The following fields are relevant.

distinct

The following fields are relevant.

Query and Write Operation Commands

delete

The following fields are relevant.

Each element of the deletes array contains the following fields:

find

The following fields are relevant.

All other fields are ignored.

Projection

The projection parameter determines which fields are returned in the matching documents. The projection parameter takes a document of the following form:

{ <field1>: <value>, <field2>: <value> ... }

If a projection document is not provided or if it is empty, the entire document will be returned.

Embedded Field Specification

For fields in an embedded documents, the field can be specified using:

• dot notation; e.g. "field.nestedfield": <value>

In particular, specifying fields in embedded documents using nested form is not supported.

_id Field Projection

The _id field is included in the returned documents by default unless you explicitly specify _id: 0 in the projection to suppress the field.

Inclusion or Exclusion

A projection cannot contain both include and exclude specifications, with the exception of the _id field:

• In projections that explicitly include fields, the _id field is the only field that can be explicitly excluded.
• In projections that explicitly excludes fields, the _id field is the only field that can be explicitly include; however, the _id field is included by default.

NOTE Currently _id is the only field that can be excluded, and only if other fields are explicitly included. NOTE Currently exclusion of other fields but _id is not supported.

Filtering by _id

Note that there is a significant difference between

> db.runCommand({find: "collection", filter: { _id: 4711 }});

and

> db.runCommand({find: "collection", filter: { _id: { $eq: 4711 }}});

In the former case the generated WHERE clause will be

... WHERE (id = '4711')

and in the latter

... WHERE (JSON_EXTRACT(doc, '$._id') = 4711)

That is, in the former case the indexed column id will be used, in the latter it will not.

findAndModify

The following fields are relevant.

All other fields are ignored.

getLastError

The following fields are relevant.

getMore

The following fields are relevant.

insert

The insert command inserts one or more documents into the table whose name is the same as that of the collection. If the option auto_create_tables is true, then the table is created if it does not already exist. If the value is false, then the insert will fail unless the table already exists.

The following fields are relevant.

ordered

The impact of ordered is dependent upon the value of ordered_insert_behavior.

default

In this case ordered has the same impact as in MongoDB®. That is, if the value is true, then when an insert of a document fails, return without inserting any remaining documents listed in the inserts array. If false, then when an insert of a document fails, continue to insert the remaining documents.

atomic

If ordered is true, then all documents will be inserted using a single INSERT command. That is, if the insertion of any document fails, for instance, due to a duplicate id, then no document will be inserted. If ordered is false, then the behavior is identical with that of default.

Performance

What combination of ordered_insert_behavior and ordered is used, has an impact on the performance.

Of these, atomic + true is the fastest and atomic|default + false the slowest, being roughly twice as slow. The performance of 'default + true' is halfway between the two.

resetError

The following fields are relevant.

update

The following fields are relevant.

All other fields are ignored.

Update Statements

Each element of the updates array is an update statement document. Each document contains the following fields:

Note that currently it is possible to set multi to true in conjunction with a replacement-style update, even though MongoDB® rejects that.

All other fields are ignored, with the exception of upsert that if present with the value of true will cause the command to fail.

Behavior

Currently only updating using update operator expressions or with a replacement document is supported. In particular, updating using an aggregation pipeline is not supported.

Update with an Update Operator Expressions document

The update statement field u can accept a document that only contains update operator expressions. For example:

updates: [
   {
     q: <query>,
     u: { $set: { status: "D" } },
      ...
   },
   ...
]

In this case, the update command updates only the corresponding fields in the document.

Update with a Replacement Document

The update statement field u field can accept a replacement document, i.e. the document contains only field:value expressions. For example:

updates: [
   {
      q: <query>,
      u: { status: "D", quantity: 4 },
      ...
   },
   ...
]

In this case, the update command replaces the matching document with the update document. The update command can only replace a single matching document; i.e. the multi field cannot be true.

Note If the replacement document contains an _id field, it will be ignored and the document id will remain non-changed while the document otherwise is replaced. This is different from MongoDB® where the presence of the _id field in the replacement document causes an error, if the value is not the same as it is in the document being replaced.

Authentication Commands

Logout

The following fields are relevant.

If you are not logged in and using authentication, logout has no effect.

Note that in order to be logged out, the logging out must be done while using the same database that was used when you logged on.

Always returns

{ ok: 1 }

User Management Commands

createUser

Creates a new MariaDB user and adds an entry to the local nosqlprotocol account database.

The following fields are relevant.

The MariaDB user will be created as '<db>.<user>'@'%' where <db> is the name of the NoSQL database in whose context the user is created, and <user> the value of the createUser field. For instance, with the following command

> use myDatabase;
> db.runCommand({createUser: "user1", pwd: "pwd1", roles: []});

the MariaDB user 'myDatabase.user1'@'%' will be created.

The elements of the roles array are converted into privileges as explained in here.

In practice the creation is performed as follows: First the MariaDB user is created. Then the privileges are granted. * Finally the local nosqlprotocol account database is updated.

If the granting of privileges fails, an attempt will be made to drop the user.

dropAllUsersFromDatabase

Drops all users from the local nosqlprotocol account database and the corresponding MariaDB users.

The following fields are relevant.

If no users can be dropped, e.g. due to an authorization error, then an error will be returned. If even a single user can be dropped the returned document tells how many were dropped, which does not necessarily indicate that all users were dropped.

dropUser

The following fields are relevant.

The user will first be dropped from the MariaDB server and if that succeeds also from the local nosqlprotocol account database.

grantRolesToUser

This command adds more roles to a NoSQL user, which may imply that additional privileges are granted to the corresponding MariaDB user.

Note that roles assigned to different databases will result in separate GRANT statements, which means that it is possible that some succeed and others do not.

revokeRolesFromUser

This command _removes roles fron an NoSQL user, which may imply that privileges are revoked from the corresponding MariaDB user.

Note that roles to be removed from different databases will result in separate REVOKE statements, which means that it is possible that some succeed and others do not.

updateUser

This command updates the information about a particular user.

Changes to customData or mechanisism are made only to the local nosqlprotocol database, but changes to pwd or roles require the MariaDB server to be updated.

Replication Commands

isMaster

The following fields are relevant.

replSetGetStatus

The following fields are relevant.

All other fields are ignored.

This command will always return the document

{
    "ok" : 0,
    "errmsg" : "not running with --replSet",
    "code" : 76,
    "codeName" : "NoReplicationEnabled"
}

Sessions Commands

endSessions

The following fields are relevant.

The following document will always be returned:

{ "ok" : 1 }

Administration Commands

create

The following fields are relevant.

Currently, capped collections and views are not supported. Consequently, specifying that the collection should be capped or that it should be a view on another collection, will cause the command to fail.

createIndexes

The following fields are relevant.

NOTE Currently it is not possible to create indexes, but the command will nonetheless return success, provide the index specification passes some rudimentary sanity checks. Note also that the collection will be created if it does not exist.

drop

The following fields are relevant.

dropDatabase

The following fields are relevant.

dropIndexes

The following fields are relevant.

NOTE Currently it is not possible to create indexes and thus there will never be any indexes that could be dropped. However, provided the specfied collection exists, dropping indexes will always succeed except for an attempt to drop the built-in _id_ index.

fsync

The following fields are relevant.

The response will always be

{
  "errmsg" : "fsync not supported by MaxScale:nosqlprotocol",
  "code" : 115,
  "codeName" : "CommandNotSupported",
  "ok" : 0
}

killCursors

The following fields are relevant.

listCollections

The following fields are relevant.

Note that the command lists all collections (that is, tables) that are found in the current database. The listed collections may or may not be suitable for being accessed using nosqlprotocol.

listDatabases

The following fields are relevant.

listIndexes

The following fields are relevant.

NOTE As it currently is not possible to actually create indexes, although an attempt to do so using createIndexes will succeed, the result will always only contain information about the built-in index _id_.

renameCollection

The following fields are relevant.

setParameter

The following fields are relevant.

Any kind of parameter is accepted and the response will always be:

{ "ok" : 1 }

Diagnostic Commands

buildInfo

The following fields are relevant.

The command returns a document containing the stable fields. In addition, there is a field maxscale whose value is the MaxScale version, expressed as a string.

explain

The following fields are relevant.

The command will return a document of the expected layout, but the content is only rudimentary.

getCmdLineOpts

The following fields are relevant.

getLog

The following fields are relevant.

The command returns a document of the correct format, but no actual log data will be returned.

hostInfo

The following fields are relevant.

listCommands

The following fields are relevant.

ping

The following fields are relevant.

serverStatus

The following fields are relevant.

validate

The following fields are relevant.

The command does not actually perform any validation but for checking that the collection exists. The response will contain in nrecords the current number of documents/rows it contains.

whatsmyuri

The following fields are relevant.

This is an internal command, implemented only because the Mongo Shell uses it.

Free Monitoring Commands

getFreeMonitoringStatus

The following fields are relevant.

The following document will always be returned:

{ "state" : "undecided", "ok" : 1 }

MaxScale Specific Commands

mxsAddUser

Definition

mxsAddUser

The mxsAddUser command adds an existing MariaDB user to the local nosqlprotocol account database. Use createUser if the MariaDB user should be created as well.

Note that the mxsAddUser command does not check that the user exists or that the specified roles are compatible with the grants of the user.

Syntax

The 'mxsAddUser' command has the following syntax:

db.runCommand(
    {
        mxsAddUser: "<name>",
        pwd: passwordPrompt(),  // Or "<cleartext password>"
        customData: { <any information> },
        roles: [
            { role: "<role>", db: "<database>" } | "<role>",
            ...
        ],
        mechanisms: [ "<scram-mechanism>", ...],
        digestPassword: <boolean>
    }
)

Command Fields

The command has the following fields:

The value of mxsAddUser should be the name (without the host part) of an existing user in the MariaDB server and the value of pwd should be that user's password in cleartext.

The roles array should contain roles that a compatible with the grants of the user. Please check roles and grants for a discussion on how to map roles map to grants.

Returns

If the addition of the user succeeds, the command returns a document with the single field ok whose value is 1.

> db.runCommand({mxsAddUser: "user", pwd: "pwd", roles: ["readWrite"]});
{ "ok" : 1 }

If there is a failure of some kind, the command returns an error document

> db.runCommand({mxsAddUser: "user2", pwd: "pwd2", roles: ["redWrite"]});
{
    "ok" : 0,
    "errmsg" : "No role named redWrite@test",
    "code" : 31,
    "codeName" : "RoleNotFound"
}

mxsCreateDatabase

Definition

mxsCreateDatabase

The 'mxsCreateDatabase' command creates a new database and must be run against the admin database.

Syntax

The 'mxsCreateDatabase' has the following syntax:

db.adminCommand(
    {
       mxsCreateDatabase: <name>
    }
)

Command Fields

The command takes the following fields:

Returns

If database creation succeeds, the command returns a document with the single field ok whose value is 1.

> db.adminCommand({mxsCreateDatabase: "db"});
{ "ok" : 1 }

If the database creation fails, the command returns an error document.

> db.adminCommand({mxsCreateDatabase: "db"});
{
    "ok" : 0,
    "errmsg" : "The database 'db' exists already.",
    "code" : 48,
    "codeName" : "NamespaceExists"
}

mxsDiagnose

Definition

mxsDiagnose

The mxsDiagnose command provides diagnostics for any other command; that is, how MaxScale will handle that command.

Syntax

The mxsDiagnose command has the following syntax:

db.runCommand(
    {
       mxsDiagnose: <command>
    }
)

Command Fields

The command takes the following fields:

Returns

The command returns a document that contains diagnostics of the command provided as argument. For example:

> db.runCommand({mxsDiagnose: {ping:1}});
{ "kind" : "immediate", "response" : { "ok" : 1 }, "ok" : 1 }

> db.runCommand({mxsDiagnose: {find:"person", filter: { name: "Bob"}}});
{
  "kind" : "single",
  "sql" : "SELECT doc FROM `test`.`person` WHERE ( JSON_EXTRACT(doc, '$.name') = 'Bob') ",
  "ok" : 1
}

> db.runCommand({mxsDiagnose: {delete:"person", deletes: [{q: { name: "Bob"}, limit:0}, {q: {name: "Alice"}, limit:0}]}});
{
  "kind" : "single",
  "sql" : [
    "DELETE FROM `test`.`person` WHERE ( JSON_EXTRACT(doc, '$.name') = 'Bob') ",
    "DELETE FROM `test`.`person` WHERE ( JSON_EXTRACT(doc, '$.name') = 'Alice') "
  ],
  "ok" : 1
}

kind specifies of what kind the command is; an immediate command is one for which MaxScale autonomously can generate the response, a single command is one where the command will cause a single SQL statement to be sent to the backend, and a multi command is one where potentially multiple SQL statements will be sent to the backend.

If the command is immediate then there will be a field response containing the actual response of the command, if the command is single then there will be a field sql containing the actual statement that would have been sent to the backend, and if the command is multi then there will be a field sql containing an array of statements that would have been sent to the backend.

If an error occurs while the command is being diagnosed, then there will be no response field but an error field whose value is an error document. Note that the value of ok will always be 1.

mxsGetConfig

Definition

mxsGetConfig

The mxsGetConfig command returns the current configuration of the session and must be run against the 'admin' database.

Syntax

The mxsGetConfig has the following syntax:

db.runCommand(
    {
        mxsGetConfig: <any>
    });

Command Fields

The command takes the following fields:

Returns

The command returns a document that contains the current configuration of the session. For example:

> db.runCommand({mxsGetConfig: 1});
{
    "config" : {
        "on_unknown_command" : "return_error",
        "auto_create_tables" : true,
        "id_length" : 35
                ...
    },
    "ok" : 1
}

mxsRemoveUser

Definition

mxsRemoveUser

The mxsRemoveUser removes a user from the local nosqlprotocol account database. Use dropUser if the MariaDB user should be dropped as well.

Syntax

The 'mxsRemoveUser' command has the following syntax:

db.runCommand(
    {
        mxsRemoveUser: "<name>"
    }
)

Command Fields

The command has the following fields:

Returns

If the removal of the user succeeds, the command returns a document with the single field ok whose value is 1.

> db.runCommand({mxsRemoveUser: "user"});
{ "ok" : 1 }

If there is a failure of some kind, the command returns an error document

> db.runCommand({mxsRemoveUser: "user"});
{
    "ok" : 0,
    "errmsg" : "User 'user@test' not found",
    "code" : 11,
    "codeName" : "UserNotFound"
}

mxsSetConfig

Definition

mxsSetConfig

The mxsSetConfig command changes the configuration of the session and must be run against the 'admin' database.

Note that the changes only affect the current session and are not persisted.

Syntax

The mxsSetConfig has the following syntax:

db.runCommand(
    {
        mxsSetConfig: document
    });

Command Fields

The command takes the following fields:

The document takes the following fields:

Returns

The command returns a document that contains the changed configuration of the session. For example:

> db.runCommand({mxsGetConfig: 1});
{
    "config" : {
        "on_unknown_command" : "return_error",
        "auto_create_tables" : true,
        "id_length" : 35
                ...
    },
    "ok" : 1
}
> db.runCommand({mxsSetConfig: { auto_create_tables: false}});
{
    "config" : {
        "on_unknown_command" : "return_error",
        "auto_create_tables" : false,
        "id_length" : 35
                ...
    },
    "ok" : 1
}

mxsUpdateUser

Definition

mxsUpdateUser

The mxsUpdateUser command updates a user in the local nosqlprotocol account database. Use updateUser to update MariaDB user as well.

Note that the mxsUpdateUser command does not check that the changed data is compatible e.g. with the grants of the corresponding MariaDB user.

Syntax

The 'mxsUpdateUser' command has the following syntax:

db.runCommand(
    {
        mxsUpdateUser: "<name>",
        pwd: passwordPrompt(),  // Or "<cleartext password>"
        customData: { <any information> },
        roles: [
            { role: "<role>", db: "<database>" } | "<role>",
            ...
        ],
        mechanisms: [ "<scram-mechanism>", ...],
        digestPassword: <boolean>
    }
)

Command Fields

The command has the following fields:

The roles array should contain roles that a compatible with the grants of the user. Please check roles and grants for a discussion on how to map roles map to grants.

Returns

If the updating of the user succeeds, the command returns a document with the single field ok whose value is 1.

> db.runCommand({mxsUpdateUser: "user", pwd: "pwd", roles: ["readWrite"]});
{ "ok" : 1 }

If there is a failure of some kind, the command returns an error document

> db.runCommand({mxsUpdateUser: "user", roles: ["redWrite"]});
{
    "ok" : 0,
    "errmsg" : "No role named redWrite@test",
    "code" : 31,
    "codeName" : "RoleNotFound"
}

Object Id

When a document is created, an id of type ObjectId will be autogenerated by the MongoDB® client library. If the id is provided explicitly, by assigning a value to the _id field, the value must be an ObjectId, a string or an integer.

Compatibility

Currently 30% of the tests in the MongoDB® core test-suite pass.

Example

The following is a minimal setup for getting nosqlprotocol up and running. It is assumed the reader knows how to configure MaxScale for normal use. If not, please start with the MaxScale tutorial. Note that as nosqlprotocol is the first component in the MaxScale routing chain, it can be used with all routers and filters.

Configuring MaxScale

In the following it is assumed that MaxScale already has been configured for normal use and that there exists a service [TheService].

[TheService]
type=service
...

[NoSQL-Listener]
type=listener
service=TheService
protocol=nosqlprotocol
nosqlprotocol.user=the_user
nosqlprotocol.password=the_password
port=17017

The values the_user and the_password must be replaced with the actual credentials to be used for every MongoDB® client that connects.

If MaxScale is now started, the following entry should appear in the log file.

... notice : (NoSQL-Listener); Listening for connections at [127.0.0.1]:17017

MongoDB® Shell

The mongo Shell is a powerful tool with which to access and manipulate a MongoDB database. It is part of the MongoDB® package. Having the native MongoDB database installed is convenient, as it makes it easy to ascertain whether a problem is due to nosqlprotocol not fully implementing something or due to the API not being used in the correct fashion.

With the mongo shell, all that is needed is to invoke it with the port nosqlprotocol is listening on:

$ mongo --port 17017
MongoDB shell version v4.4.1
connecting to: mongodb://127.0.0.1:17017/?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("694f3eed-329f-487a-8d73-9a2d4cf82d62") }
MongoDB server version: 4.4.1
---
        ...
---
>

If the shell prompt appears, then a connection was successfully established and the shell can be used.

> db.runCommand({insert: "collection", documents: [{_id: 1, "hello": "world"}]});
{ "n" : 1, "ok" : 1 }

The db variable is implicitly available, and refers by default to the test database.

The command inserted a document into the collection called collection. The table corresponding to that collection is created implicitly because the default value of auto_create_tables is true. Here, the object id is specified explicitly, but there is no need for that, as one will be created if needed.

To check whether the documents was inserted into the collection, the find command can be issued:

> db.runCommand({find: "collection"});
{
    "cursor" : {
        "firstBatch" : [
            {
                "_id" : 1,
                "hello" : "world"
            }
        ],
        "id" : NumberLong(0),
        "ns" : "test.collection"
    },
    "ok" : 1
}

As can be seen, the document was indeed inserted into the collection

With the mysql shell, the content of the actual table can be checked.

MariaDB [(none)]> select * from test.collection;
+------+------------------------------------+
| id   | doc                                |
+------+------------------------------------+
| 1.0  | { "_id" : 1.0, "hello" : "world" } |
+------+------------------------------------+

The collection collection is represented by a table collection with the two colums id and doc. id is a virtual column whose content is the value of the _id field of the document in the doc column.

All MongoDB® commands that mongdbprotocol support (but for the ones that do not require database access), basically access or manipulate the content in the doc column using the JSON functions of MariaDB.

From within the mongo shell itself it is easy to find out just what SQL a particular MongoDB command is translated into.

For instance, the SQL that the insert command with which the document was added can be found out like:

> db.runCommand({mxsDiagnose: {insert: "collection", documents: [{_id: 1, "hello": "world"}]}});
{
    "kind" : "multi",
    "sql" : [
        "INSERT INTO `test`.`collection` (doc) VALUES ('{ \"_id\" : 1.0, \"hello\" : \"world\" }')"
    ],
    "ok" : 1
}

Similarily, the SQL of the find command can be find out like:

> db.runCommand({mxsDiagnose: {find: "collection"}});
{
    "kind" : "single",
    "sql" : "SELECT doc FROM `test`.`collection` ",
    "ok" : 1
}

The returned SQL can be directly pasted at the mysql prompt, which is quite convenient in case the MongoDB® command does not behave as expected.

MongoDB® Node.JS Driver

As all client libraries implement and depend on the the MongoDB® wire protocol, all client libraries should work with nosqlprotocol. However, the only client library that has been used and that has been verified to work is version 3.6 of the MongoDB Node.JS Driver.

In principle, the only thing that needs to be altered in an existing program using the library is to change the uri string that typically is something like

const uri = "mongodb+srv://<user>:<password>@<cluster-url>?writeConcern=majority";

to

const uri = "mongodb://<maxscale-ip>:17017";

with the assumption that the default nosqlprotocol port is used.

In practice, additional modifications may be needed since nosqlprotocol does not implement all commands and does not in all cases implement the full functionality of the commands that it supports.

Inserting a Document

Store the following into a file called insert.js.

const { MongoClient } = require("mongodb");

const uri = "mongodb://127.0.0.1:17017";

const client = new MongoClient(uri, { useUnifiedTopology: true });
async function run() {
  try {
    await client.connect();
    const database = client.db("mydb");
    const movies = database.collection("movies");
    // create a document to be inserted
    const movie = { title: "Apocalypse Now", director: "Francis Ford Coppola" };
    const result = await movies.insertOne(movie);
    console.log(
      `${result.insertedCount} documents were inserted with the _id: ${result.insertedId}`,
    );
  } finally {
    await client.close();
  }
}
run().catch(console.dir);

Then, run the program like

$ nodejs insert.js
1 documents were inserted with the _id: 60afca73bf486114e3fb48b8

As the id is not explicitly provided, it will not be the same.

Finding a Document

Store the following into a file called find.js.

const { MongoClient } = require("mongodb");

const uri = "mongodb://127.0.0.1:17017";

const client = new MongoClient(uri, { useUnifiedTopology: true });
async function run() {
  try {
    await client.connect();
    const database = client.db("mydb");
    const movies = database.collection("movies");
    // Query for a movie that has the title 'Apocalypse Now'
    const query = { title: "Apocalypse Now" };
    const options = {
      // Include only the 'director' field in the returned document
      projection: { _id: 0, director: 1 },
    };
    const movie = await movies.findOne(query, options);
    // Returns a document and not a cursor, so print directly.
    console.log(movie);
  } finally {
    await client.close();
  }
}
run().catch(console.dir);

Then, run the program like

$ nodejs find.js
{ director: 'Francis Ford Coppola' }