======== 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 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 ''[@''] IDENTIFIED BY 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 `` where ```` 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*.