SonarSQL

Overview

SonarSQL is effectively a MySQL front end to SonarW. It can be used with any modern MySQL client exactly the same way as MySQL server is used.

Behind the scenes, SonarSQL is actually a thin layer that translates MySQL queries to SonarW aggregation pipelines, submits the pipelines to the backend server, and translates the results back to MySQL protocol.

SonarSQL uses SonarW extensions to the Aggregation Framwork, so currently it requires SonarW backend.

SonarSQL uses the native MySQL Embedded Server (same as MySQL) for query parsing - which ensures accurate and complete parsing of the complete MySQL syntax.

See installation instructions as part of SonarW installation instructions at http://sonarwdocs.jsonar.com/installsonar.html

SonarW URI

This is the URI that is used by SonarSql to connect to SonarW. The user used in the URI must have userAdmin or userAdminAnyDatabase role in SonarW. This is required since SonarSQL needs to create users (See Creating Users).

The URI is a standard MongoDB connection string as documented at http://docs.mongodb.org/manual/reference/connection-string. For example:

mongodb://admin:admin@localhost:27117

will connect to SonarW server running on localhost and listening on port 27117, with user admin and password admin.

The default timeout for queries is 5 minutes. If you need to support longer running queries, you can append `?socketTimeoutMS to the end of the URI. The value is given in milliseconds. So if you would like to allow queries to run up to an hour, use:

mongodb://admin:admin@localhost:27117/admin?socketTimeoutMS=3600000

If you would like SonarSQL to never time out, use -1:

mongodb://admin:admin@localhost:27117/admin?socketTimeoutMS=-1

You can modify the URI using sonarsql-setup as above, or directly in the configuration file under uri.

SonarSQL root password

The root user is a special user that allows one to connect to SonarSQL and create users. It will use the SonarW URI given above to generate the users, and should not be used for data queries.

The password itself is not stored in the configuration file, rather a hash thereof. If you wish to set this value directly in the configuration file, you’d need to generate the hash first, using:

sudo sonarsql --generate-password-has <password>

and enter the resulting hash into the configuration file.

Once all users are defined, it is advised to disable root login completely by setting this value to empty.

The root password hash is stored in the configuration file under root-pwhash.

Listen port

This is the port that SonarSQL listens on. The default value is 27133. You can use a different port as needed. The default port MySql uses is 3306. If you don’t have MySql server running on the machine, you can set the port to 3306, so clients will use their default port.

The port is stored in the configuration file as listen.

Threads

Maximum number of threads that SonarSQL might spawn to respond to user queries. Roughly matches the number of queries that can run in parallel against SonarW. Note, this does not limit the number of connections SonarSQL can handle, which can be much larger than the number of threads. The default value is 4, which provides a sane default. If more queries are expected to run in parallel, you can increase that number up to the number of CPUs/cores the machine has.

This value is stored in the configuration file under threads.

maxTimeMS

SonarW allows limiting the time allowed for a query to run. This can be modified per connection using SET @maxTimeMS command in your MySQL client.

Note this value is given in milliseconds. The default is 100000, or a 100 seconds.

The default value is given in the configuration file under max-time-ms.

Debug

To ease debugging, SonarSQL can run in the foreground with a single thread. This is normally done by using --debug command line switch, but can also be set in the configuration file under debug.

Command line switches

All the above parameters can also be given in the command line. The command line values take precedence over values in the configuration file. The names of the parameters are the same as they are in the configuration file.

Use --help to get detailed information.

Authentication

When users connect to SonarSQL their password will be verified first. Once verified, a connection as this user will be opened by SonarSQL to SonarW. This essentially generates an end-to-end user session, so the user privilege is ultimately fully controlled by SonarW.

Creating users

To create users, connect to MySQL as root using the password you supplied during configuration. Then create new users in your MySQL client using:

CREATE USER '<user>'[@'<role>'] IDENTIFIED BY PASSWORD '<password>'

This will create a user in both SonarSQL and SonarW, with the same password. All the user details and password hashes are saved within SonarW in the system.users collection within the admin database. Only hashes are saved, and not the clear text password.

If you do not specify a role, or specify the role as %, the user will be granted the built-in role sonarSqlUser in SonarW. This role will be created by SonarSQL with reasonable permissions if it does not exist. It can be modified further in SonarW for more fine grained control.

If you do choose to specify a role, it will be converted to lowercase letters, and used as the user role. There will be no verification that the role exists, or an attempt to create it. For example:

CREATE USER john@doe IDENTIFIED BY PASSWORD 's3cr3t';

will create the user john, assign its role as doe with password s3cr3t. If the role doe does not exist, this will result in an error.

Managing Users

Fine grained permissions can be set by manipulating the roles array for each user, or by modifying the actions for the roles given. This is done directly with SonarW using the standard MongoDB tools or the shell, and takes effect immediately.

Consult the documentation at https://docs.mongodb.org/manual/tutorial/manage-users-and-roles/ for managing users and roles.

Please note that to connect using MySQL client, you must allow SonarSQL to create the users first. Do not attempt to create new users using createUser command.

You can use dropUser command to drop users though. In fact, this is the only way to remove users as DROP USER is not supported at the moment.

Changing Passwords

Changing passwords is not supported at the moment. To modify a user’s password, you’d need to drop the user using mongodb shell, and recreate the user using SonarSQL.

To reiterate: SET PASSWORD, and DROP USER are not supported currently.

sonarSQLUser role

The sonarSQLUSer role is being created by SonarSQL with the following privileges:

[
  {
    resource: {db:"", collection: "lmrm__metadata2"},
    actions: ["find", "insert", "remove", "update"]
  },
  {
    resource: {db:"", colection:""},
    actions: ["find", "insert", "remove"]
  },
  {
    resource: {cluster: true},
    actions: ["listDatabases"]
]

The actions defined for the lmrm__metadata2 collection above are essential for proper operation of SonarSQL.

The insert and remove actions that are defined for the empty collection allow support for INSERT ... SELECT type of queries. If these are not required for the organization, these can be removed.

The listDatabases action on the cluster resource is required to support SHOW DATABASES command, and can be removed if needed.

Troubleshooting

Version Number

Check the version:

sonarsql --version

Log Files

Log file is located in /var/log/sonarsql and contains useful information to detect errors. Please also include the query you tried to execute and the approximate time. Sometimes the log from SonarW operation can be useful to detecting problems.

Pipeline Information

If you’d like to know what pipeline is being generated from the query you are using, you can use EXPLAIN in your MySQL client:

MySQL> EXPLAIN SELECT name FROM ghosts WHERE points>3 \G
*************************** 1. row ***************************
Collection: ghosts
  Pipeline: db.ghosts.aggregate([
{
    "$match" : {
        "points" : {
            "$gt" : 3
        }
    }
},
{
    "$project" : {
        "_0000" : "$name",
        "_id" : 0
    }
}])
1 row in set (0.00 sec)

sql2pipeline is a standalone tool that translates the pipeline without connecting to SonarW at all. It is convenient to use when one wants to quickly check generated pipelines without actually running them, or needing to create the underlying structure of collections. To run, just execute sql2pipelene and supply the SQL query as the single parameter. Make sure it is quoted properly, especially of backquotes or double quotes appear in the query:

$ sql2pipeline 'select name from ghosts where points between 2 and 10'
db.ghosts.aggregate([
{
    "$match" : {
        "$and" : [
            {
                "points" : {
                    "$gte" : 2
                }
            },
            {
                "points" : {
                    "$lte" : 10
                }
            }
        ]
    }
},
{
    "$project" : {
        "_0000" : "$name",
        "_id" : 0
    }
}])

The pipelines are printed in a way that you can copy and paste them directly into MongoDB shell if you’d need to see the actual results returned from SonarW.

Note that since sql2pipeline does not communicate with SonarW, it has no knowledge of metadata, and thus cannot handle wild cards in fields name like SELECT * FROM.., and cannot determine required $unwind operations.

New/updated collections

When a collection’s data is updated in SonarW, the information is available immediately through SonarSQL (no caching).

However,*SonarSQL* does cache “schema” information for presenting to MySQL clients. This cache needs to be refreshed when the metadata of the underlying database changes. This includes adding significant numbers of documents to a collection, or creating new collections.

This is only relevant for changes that happened after the initial connection. As long as the user connection is active, the metadata will be refreshed every 10 minutes.

If a collection was created after the user connections made (but less than 10 minutes after), it might not be shown in SHOW TABLES or DESCRIBE. You can simply wait a few minutes or disconnect and connect back to get the updated info. Alternatively, issue USE <db> where <db> is the database you are currently connected to. This will force the refresh and should show you the latest data:

MySQL [test]> use test;
Database changed

After issuing the above command, the metadata has been updated, and new collections are visible through SonarSQL.