# Improved connection management for the MySQL services in the Qserv Replication system

XMLWordPrintable

#### Details

• Type: Improvement
• Status: Done
• Resolution: Done
• Fix Version/s: None
• Component/s:
• Labels:
None
• Sprint:
DB_S21_12
• Team:
Data Access and Database

# The problem

The current version of the Replication system has a bit complicated way of configuring connections to the Qserv czar’s and Qserv worker’s MySQL services. In particular, some connection parameters to the services, such as host, port, user (and in some cases - database) need to be pre-configured in the persistent configuration of the system, while a value of the parameter password is passed into a component via a command-line option as shown below:

 qserv-replica-master-http --qserv-db-password=CHANGEME qserv-replica-worker --qserv-db-password=CHANGEME 

On top of that, some algorithms may also use the hardcoded string for the MySQL root account when opening connections.
Another problem with this way of configuring the connections is that a lack of scalability since adding a new database requires adding configuration parameters for each type (or instance) of a connection.
Altogether this complicates the process of configuring the system, and it also makes it difficult to debug problems when a connection can't be established.

## The proposed solution

Hence, the idea is to use a similar approach that was taken for configuring the replication system with the connection string modeled after one of Python's sqlalchemy. The string has the following general syntax:

 mysql://user[:password]@host[:port]/ 

This is an example of how the string could look like:

 --config=mysql://qsreplica:CHANGEME@master01:23306/qservReplica 

In the proposed implementation the connection configuration mechanism will be unified to rely upon the connection strings instead of the multiple disjoined parameters scattered in the Configuration and command-line options. The corresponding configuration parameters will be removed from the database. Existing command-line option --qserv-db-password will get eliminated, and the following command-line options will be added:

 --qserv-czar-db=mysql://qsreplica:CHANGEME@localhost:3306/qservMeta --qserv-worker-db=mysql://qsreplica:CHANGEME@localhost:3306/qservw_worker 

Consider adding the third command-line option for configuring connections to the Qserv's mysql-proxy service for submitting queries. This will be needed by a yet-to-be-added REST service used by the Qserv Web Dashboard for submitting queries to Qserv. The proposed command-line option will be supported by the Master Controller :

 --qserv-mysql-proxy=mysql://qsmaster@localhost:4040/dummyDb 

## Implementaiton notes

The proposed enhancement won't require making significant changes to the code since the current implementation of the system already has an infrastructure for parsing, validating, and using the connection strings for making MySQL connections.
Changes to the code will include:

• Removing obsolete command-line option(s).
• Adding the new command-line options qserv-czar-db, qserv-worker-db, and qserv-mysql-proxy.
• Adding static "setter" and "getter" methods to the class Configuration for these parameters.
• Migrating existing clients (within the system) to use the new parameters.
• Removing support for the obsolete configuration parameters from the transient schema definition at the class ConfigurationSchema.
• Remove unused data members dbHost, dbPort and dbUser from the worker descriptor WorkerInfo and prom relevant classes.

## Other notes

Update the documentation on configuring and using the Replication/Ingest system:

#### Activity

Hide
Andy Salnikov added a comment -

Looks good, few minor comments on PR.

Show
Andy Salnikov added a comment - Looks good, few minor comments on PR.

#### People

Assignee:
Igor Gaponenko
Reporter:
Igor Gaponenko
Reviewers:
Andy Salnikov
Watchers:
Andy Salnikov, Fabrice Jammes, Fritz Mueller, Igor Gaponenko, Nate Pease