MySQL

DIRAC Basic MySQL Class It provides access to the basic MySQL methods in a multithread-safe mode keeping used connections in a python Queue for further reuse.

These are the coded methods:

__init__( host, user, passwd, name, [maxConnsInQueue=10] )

Initializes the Queue and tries to connect to the DB server, using the _connect method. “maxConnsInQueue” defines the size of the Queue of open connections that are kept for reuse. It also defined the maximum number of open connections available from the object. maxConnsInQueue = 0 means unlimited and it is not supported.

_except( methodName, exception, errorMessage )

Helper method for exceptions: the “methodName” and the “errorMessage” are printed with ERROR level, then the “exception” is printed (with full description if it is a MySQL Exception) and S_ERROR is returned with the errorMessage and the exception.

_connect()

Attempts connection to DB and sets the _connected flag to True upon success. Returns S_OK or S_ERROR.

_query( cmd, [conn=conn] )

Executes SQL command “cmd”. Gets a connection from the Queue (or open a new one if none is available), the used connection is back into the Queue. If a connection to the the DB is passed as second argument this connection is used and is not in the Queue. Returns S_OK with fetchall() out in Value or S_ERROR upon failure.

_update( cmd, [conn=conn] )

Executes SQL command “cmd” and issue a commit Gets a connection from the Queue (or open a new one if none is available), the used connection is back into the Queue. If a connection to the the DB is passed as second argument this connection is used and is not in the Queue Returns S_OK with number of updated registers in Value or S_ERROR upon failure.

_createTables( tableDict )

Create a new Table in the DB

_getConnection()

Gets a connection from the Queue (or open a new one if none is available) Returns S_OK with connection in Value or S_ERROR the calling method is responsible for closing this connection once it is no longer needed.

Some high level methods have been added to avoid the need to write SQL statement in most common cases. They should be used instead of low level _insert, _update methods when ever possible.

buildCondition( self, condDict = None, older = None, newer = None,

timeStamp = None, orderAttribute = None, limit = False, greater = None, smaller = None ):

Build SQL condition statement from provided condDict and other extra check on a specified time stamp. The conditions dictionary specifies for each attribute one or a List of possible values greater and smaller are dictionaries in which the keys are the names of the fields, that are requested to be >= or < than the corresponding value. For compatibility with current usage it uses Exceptions to exit in case of invalid arguments

insertFields( self, tableName, inFields = None, inValues = None, conn = None, inDict = None ):

Insert a new row in “tableName” assigning the values “inValues” to the fields “inFields”. Alternatively inDict can be used String type values will be appropriately escaped.

updateFields( self, tableName, updateFields = None, updateValues = None,

condDict = None, limit = False, conn = None, updateDict = None, older = None, newer = None, timeStamp = None, orderAttribute = None ):

Update “updateFields” from “tableName” with “updateValues”. updateDict alternative way to provide the updateFields and updateValues N records can match the condition return S_OK( number of updated rows ) if limit is not False, the given limit is set String type values will be appropriately escaped.

deleteEntries( self, tableName,

condDict = None, limit = False, conn = None, older = None, newer = None, timeStamp = None, orderAttribute = None ):

Delete rows from “tableName” with N records can match the condition if limit is not False, the given limit is set String type values will be appropriately escaped, they can be single values or lists of values.

getFields( self, tableName, outFields = None,

condDict = None, limit = False, conn = None, older = None, newer = None, timeStamp = None, orderAttribute = None ):

Select “outFields” from “tableName” with condDict N records can match the condition return S_OK( tuple(Field,Value) ) if limit is not False, the given limit is set String type values will be appropriately escaped, they can be single values or lists of values.

for compatibility with other methods condDict keyed argument is added

getCounters( self, table, attrList, condDict = None, older = None,

newer = None, timeStamp = None, connection = False ):

Count the number of records on each distinct combination of AttrList, selected with condition defined by condDict and time stamps

getDistinctAttributeValues( self, table, attribute, condDict = None, older = None,

newer = None, timeStamp = None, connection = False ):

Get distinct values of a table attribute under specified conditions

class DIRAC.Core.Utilities.MySQL.ConnectionPool(host, user, passwd, port=3306, graceTime=600)

Bases: object

Management of connections per thread

MYSQL_CONNECTION_GRACE_TIME = 600
__init__(host, user, passwd, port=3306, graceTime=600)
clean(now=False)
get(dbName, retries=10)
transactionCommit(dbName)
transactionRollback(dbName)
transactionStart(dbName)
class DIRAC.Core.Utilities.MySQL.MySQL(hostName='localhost', userName='dirac', passwd='dirac', dbName='', port=3306, debug=False)

Bases: object

Basic multithreaded DIRAC MySQL Client Class

__init__(hostName='localhost', userName='dirac', passwd='dirac', dbName='', port=3306, debug=False)

set MySQL connection parameters and try to connect

Parameters:

debug – unused

buildCondition(condDict=None, older=None, newer=None, timeStamp=None, orderAttribute=None, limit=False, greater=None, smaller=None, offset=None, useLikeQuery=False)

Build SQL condition statement from provided condDict and other extra check on a specified time stamp. The conditions dictionary specifies for each attribute one or a List of possible values greater and smaller are dictionaries in which the keys are the names of the fields, that are requested to be >= or < than the corresponding value. For compatibility with current usage it uses Exceptions to exit in case of invalid arguments For performing LIKE queries use the parameter useLikeQuery=True

countEntries(table, condDict, older=None, newer=None, timeStamp=None, connection=False, greater=None, smaller=None)

Count the number of entries wit the given conditions

deleteEntries(tableName, condDict=None, limit=False, conn=None, older=None, newer=None, timeStamp=None, orderAttribute=None, greater=None, smaller=None)

Delete rows from “tableName” with N records can match the condition if limit is not False, the given limit is set String type values will be appropriately escaped, they can be single values or lists of values.

executeStoredProcedure(packageName, parameters, outputIds, *, conn=None)
executeStoredProcedureWithCursor(packageName, parameters, *, conn=None)
getCounters(table, attrList, condDict, older=None, newer=None, timeStamp=None, connection=False, greater=None, smaller=None)

Count the number of records on each distinct combination of AttrList, selected with condition defined by condDict and time stamps

getDistinctAttributeValues(table, attribute, condDict=None, older=None, newer=None, timeStamp=None, connection=False, greater=None, smaller=None)

Get distinct values of a table attribute under specified conditions

getFields(tableName, outFields=None, condDict=None, limit=False, conn=None, older=None, newer=None, timeStamp=None, orderAttribute=None, greater=None, smaller=None, useLikeQuery=False)

Select “outFields” from “tableName” with condDict N records can match the condition return S_OK(tuple(Field, Value)) if outFields is None all fields in “tableName” are returned if limit is not False, the given limit is set inValues are properly escaped using the _escape_string method, they can be single values or lists of values. if useLikeQuery=True, then conDict can return matched rows if “%” is defined inside conDict.

insertFields(tableName, inFields=None, inValues=None, conn=None, inDict=None)

Insert a new row in “tableName” assigning the values “inValues” to the fields “inFields”. String type values will be appropriately escaped.

transactionCommit()
transactionRollback()
transactionStart()
updateFields(tableName, updateFields=None, updateValues=None, condDict=None, limit=False, conn=None, updateDict=None, older=None, newer=None, timeStamp=None, orderAttribute=None, greater=None, smaller=None)

Update “updateFields” from “tableName” with “updateValues”. updateDict alternative way to provide the updateFields and updateValues N records can match the condition return S_OK( number of updated rows ) if limit is not False, the given limit is set String type values will be appropriately escaped.

DIRAC.Core.Utilities.MySQL.captureOptimizerTraces(meth)

If enabled, this will dump the optimizer trace for each query performed. Obviously, it has a performance cost…

In order to enable the tracing, the environment variable DIRAC_MYSQL_OPTIMIZER_TRACES_PATH should be set and point to an existing directory where the files will be stored.

It makes sense to enable it when preparing the migration to a newer major version of mysql: you run your integration tests (or whever scenario you prepared) with the old version, then the same tests with the new version, and compare the output files.

The file produced are called “optimizer_trace_<timestamp>_<hash>.json” The hash is here to minimize the risk of concurence for the same file. The timestamp is to maintain the execution order. For easier comparison between two executions, you can rename the files with a sequence number.

cd ${DIRAC_MYSQL_OPTIMIZER_TRACES_PATH}
c=0; for i in $(ls); do newFn=$(echo $i | sed -E "s/_trace_[0-9]+.[0-9]+_(.*)/_trace_${c}_/g"); mv $i $newFn; c=$(( c + 1 )); done

This tool is useful then to compare the files https://github.com/crusaderky/recursive_diff

Note that this method is far from pretty:

  • error handling is not really done. Of course, I could add a lot of safety and try/catch and what not, but if you are using this, it means you reaaaally want to profile something. And in that case, you want things to go smoothly. And if they don’t, you want to see it, and you want it to crash.

  • it mangles a bit with the connection pool to be able to capture the traces

All the docs related to the optimizer tracing is available here https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

The generated file contains one of the following:

  • {"EmptyTrace": arguments}: some method like “show tables” do not generate a trace

  • A list of dictionaries, one per trace for the specific call:

    • { "Query": <query executed>, "Trace" : <optimizer analysis>} if all is fine

    • {"Error": <the error>} in case something goes wrong. See the lower in the code

      for the description of errors