Other sop/dbconf

Table Of Contents

Previous topic

Rules for Code that writes to the Database

Next topic

DB Table Updating Workflow

This Page

Daya Bay Links

Content Skeleton

Configuring DB Access

As both DBI and db.py make heavy usage of the mysql configuration file and as this is the primary source of problems for beginners, the below elaborates on how to setup your configuration and troubleshoot problems.

Create Simple DB configuration file

Create a configuration file in your home directory ~/.my.cnf containing parameters to connect to relevant databases, for example:

[offline_db]
host    = dybdb2.ihep.ac.cn
database = offline_db
user    =  dayabay
password = youknowit

[tmp_offline_db]
host = dybdb2.ihep.ac.cn
database = tmp_wangzhm_offline_db
user = wangzhm
password = plaintestpw

[client]
host = dybdb2.ihep.ac.cn
database = tmp_wangzhm_offline_db
user = wangzhm
password = plaintextpw

Section Names

Note that the section names offline_db, tmp_offline_db do not exactly correspond to DB names, providing useful indirection : but keep it simple to avoid confusion.

Warning

At IHEP it is recommended that users connect to the slave machine dybdb2.ihep.ac.cn

The commandline mysql client by default reads the client section of the configuration file.

Note

localhost access

For localhost access, some systems are configured to use a location for the MySQL socket that is different than the hard-coded default of /tmp/mysql.sock and defining a “[client]” section will override this configuration. For such systems you must restore the “socket” directive by including it in your .my.cnf.

Standardized Section Names

section references role
offline_db nearest slave copy of master readonly access to content of central db
tmp_offline_db temporary copy of offline_db testing ground for updates, fair game to be dropped

Allows:

  1. easy communication
  2. scripts to have wider applicability, due to common roles
  3. testing system to tailor tests based on sections available

Section dependent testing

The test is only run if all DBCONF sections are available in the configuration file.

from DybPython import DBConf
want_conf = 'cascade_0:cascade_1:cascade_2'
has_conf = DBConf.has_config( want_conf )

def setup():
   os.environ['DBCONF'] = want_conf

def test_cascade():
    for dbno in range(3):
       ...
test_cascade.__test__ = has_conf

DBCONF envvar

DBI uses the configuration section pointed to by the DBCONF environment variable. For example:

DBCONF=offline_db nuwa.py ...
DBCONF=tmp_offline_db nuwa.py ...
DBCONF=offline_db python -c "from DybDbi import gDbi ; gDbi.Status() "

For recommendations on how to set envvars on the commanline and in scripts, see below N ways to set an envvar

Further details on DBCONF and related envvars are in doc:5290.

Cascade configuration

Configuring a cascade is achieved by using multiple section names delimited by a colon, for example:

DBCONF=tmp_offline_db:offline_db nuwa.py ...
DBCONF=tmp_offline_db:offline_db python -c "from DybDbi import gDbi ; gDbi.Status() "

The first section name takes priority in the cascade.

Configuring access to ascii catalog

A config section like the below with a database value of dbname#/absolute/path/to/catalog/file.cat specifies the catalog to use and the database into which temporary tables are loaded:

[tmp_offline_db_ascii]
host = your.local.domain
user = joe
password = plaintextpw
database = tmp_joe_offline_db#/home/joe/tmp_offline_db/tmp_offline_db.cat

Including such a section name in DBCONF allows the content of the catalog to be accessed. For a quick test get into dybgaudi:Database/DybDbi and:

DBCONF=tmp_offline_db_ascii            python tests/test_feecablemap.py
DBCONF=tmp_offline_db_ascii            python -c "from DybDbi import gDbi ; gDbi.Status() "
DBCONF=tmp_offline_db_ascii:offline_db python -c "from DybDbi import gDbi ; gDbi.Status() "

Caveats arising from DBI ascii catalog implementation with MySQL temporary tables:

  1. CREATE_TEMPORARY permission is required in the specified database
  2. the temporary tables only exist for a single session, they are atomically loaded from the catalog at each DBI startup

Using dybaux as ascii catalog

Note that ascii catalog config can use a URL rather than the absolute path to a checkout:

[tmp_offline_db_ascii]
host = your.local.domain
user = joe
password = plaintextpw
database = tmp_joe_offline_db#http://dayabay:youknowit\@dayabay.ihep.ac.cn/svn/dybaux/!svn/bc/5070/catalog/tmp_offline_db/tmp_offline_db.cat

The URL in the above example picks a particular revision of the catalog, to be loaded into temporary tables in the configured DB. This is equivalent to separately checking out dybaux to the desired revision and supplying the absolute path (or envvar prefixed) path in the config section.

N ways to set an envvar

bash

Pedestrian approach:

export DBCONF=tmp_offline_db
python myscript.py

Inline:

DBCONF=tmp_offline_db ipython
DBCONF=tmp_offline_db python myscript.py
DBCONF=tmp_offline_db nuwa.py ...
DBCONF=tmp_offline_db nosetests -v -s
DBCONF=tmp_offline_db ./dybinst trunk tests dbivalidate
DBCONF=tmp_offline_db ./dybinst trunk tests
DBCONF=tmp_offline_db ./dybinst trunk tests db_conditional

Inferior shells such as tcsh/csh

setenv DBCONF tmp_offline_db
python myscript.py

python

import os
os.environ['DBCONF'] = "tmp_offline_db"
import os
os.environ.update( DBCONF="tmp_offline_db" )
import os
os.environ.setdefault( 'DBCONF', "tmp_offline_db" )

Question : what is the below going to return ?

export DBCONF=offline_db
python -c "import os ; os.environ.setdefault('DBCONF','tmp_offline_db') ; print os.environ['DBCONF']"

Using the easily overridden approach allows convenient testing against whatever Database or cascade:

DBCONF=tmp_offline_db:offline_db ./dybinst trunk tests dybdbi

Warning

tests that operate beneath DBI, eg DbiValidate which connects with MySQL-python, have not yet been modified to work in cascade.

Background Information

What is a mysql dump file ?

A text serialisation of a MySQL database that contains the SQL commands necessary to recreate the table structure and content. They are complex and not well suited to human consumption.

What is a DBI ascii catalog ?

DBI ascii catalogs are a serialization of database tables composed of a directory structure containing .csv files and .cat files to link them together:

/path/to/<catname>/
           <catname>.cat
           CalibFeeSpec/
               CalibFeeSpec.csv
               CalibFeeSpecVld.csv
           CalibPmtSpec/
               CalibPmtSpec.csv
               CalibPmtSpecVld.csv
           ...
           LOCALSEQNO/
               LOCALSEQNO.csv

DBI ascii catalogs have several advantages over mysqldump (.sql) files:

  1. effectively native DBI format that can be used in ascii cascades allowing previewing of future database before real updates are made
  2. very simple/easily parsable .csv that can be read by multiple tools
  3. very simple diffs (DBI updates should be contiguous additional lines), unlike mysqldump, this means efficient storage in SVN
  4. no-variants/options that change the format (unlike mysqldump)
  5. no changes between versions of mysql

Mysqldump serialization has the advantage of being easily usable remotely.

Hands-On Exercise 1 : Troubleshooting DB connection configuration

Warning

Protect ~/.my.cnf with chmod go-rwx and never commit it into a repository

Approaches to isolating connection problems.

Check with mysql client

Verify that the mysql client can connect and check you are talking to the expected DB:

echo status | mysql   ## only the client section of the config

Check with db.py

Verify that db.py (a sibling of nuwa.py) can connect using the client section

db.py client check
  dbconf : reading config from section "client" obtained from ['/etc/my.cnf', '/home/blyth/.my.cnf'] (last one wins)
  {'VERSION()': '4.1.22-log', 'CURRENT_USER()': 'root@belle7.nuu.edu.tw', 'DATABASE()': 'offline_db_20110103', 'CONNECTION_ID()': 32080L}

Verify that db.py can connect using other sections of the config:

db.py offline_db check
  dbconf : reading config from section "offline_db" obtained from ['/etc/my.cnf', '/home/blyth/.my.cnf'] (last one wins)
  {'VERSION()': '5.0.45-community-log', 'CURRENT_USER()': 'dayabay@%', 'DATABASE()': 'offline_db', 'CONNECTION_ID()': 32112L}

Check with DBI

Verify that DBI (and DybDbi) can connect. Do not be concerned regarding the Closed status mentioned in the output, the connection is opened when needed:

DBCONF=client python -c "from DybDbi import gDbi ; gDbi.Status() "
   DybDbi ctor
   DybDbi activating DbiTableProxyRegistry
   Using DBConf.Export to prime environment with : from DybPython import DBConf ; DBConf.Export('client') ;
   dbconf : reading config from section "client" obtained from ['/etc/my.cnf', '/home/blyth/.my.cnf'] (last one wins)
   dbconf:export_to_env from /etc/my.cnf:$SITEROOT/../.my.cnf:~/.my.cnf section client
   Successfully opened connection to: mysql://cms01.phys.ntu.edu.tw/offline_db_20110103
   This client, and MySQL server (MySQL 4.1.22-log) does support prepared statements.
   DbiCascader Status:-
   Status   URL

   Closed         0 mysql://cms01.phys.ntu.edu.tw/offline_db_20110103

Similarly test other sections of the config with:

DBCONF=offline_db python -c "from DybDbi import gDbi ; gDbi.Status() "

DBI error when DBCONF not defined

To connect to a database with DBI (and thus DybDbi) requires the DBCONF envvar to be defined. If it is not defined or is invalid you will see an abort with error message.

( unset DBCONF ;  python -c "from DybDbi import gDbi ; gDbi.Status() "  ; )
  DybDbi activating DbiTableProxyRegistry
  Cannot open Database cascade as DBCONF envvar is not defined :
  search for "DBCONF" in the Offline User Manual
  ABORTING

Hands On Exercise 2 : Interactive DybDBI

Get into ipython

Get into NuWa environment and fire up ipython with DBCONF defined, with bash:

DBCONF=offline_db ipython

with (t)csh:

setenv DBCONF "offline_db"
ipython

Interactively verify connection

Duplicate the below to verify a DB connection:

In [1]: from DybDbi import gDbi
Warning in <TEnvRec::ChangeValue>: duplicate entry <Library.vector<short>=vector.dll> for level 0; ignored
Warning in <TEnvRec::ChangeValue>: duplicate entry <Library.vector<unsigned-int>=vector.dll> for level 0; ignored
(Bool_t)1
DybDbi ctor

In [2]: gDbi.Status()
DybDbi activating DbiTableProxyRegistry
Using DBConf.Export to prime environment with : from DybPython import DBConf ; DBConf.Export('offline_db') ;
dbconf:export_to_env from $SITEROOT/../.my.cnf:~/.my.cnf section offline_db
Successfully opened connection to: mysql://dybdb2.ihep.ac.cn/offline_db
This client, and MySQL server (MySQL 5.0.45-community) does support prepared statements.
DbiCascader Status:-
Status   URL

Closed         0 mysql://dybdb2.ihep.ac.cn/offline_db

DbiCascader Status:-
Status   URL

Closed         0 mysql://dybdb2.ihep.ac.cn/offline_db

Interactive Exploration with ipython TAB completion

Use ipython tab completion to interactively explore:

In [3]: gDbi.<TAB>
gDbi.ClearRollbackDates  gDbi.IsA                 gDbi.__class__           gDbi.__ge__              gDbi.__lt__              gDbi.__reduce_ex__       gDbi.__weakref__
gDbi.ConfigRollback      gDbi.IsActive            gDbi.__delattr__         gDbi.__getattribute__    gDbi.__module__          gDbi.__repr__            gDbi.cascader
gDbi.GetCascader         gDbi.MakeTimeStamp       gDbi.__dict__            gDbi.__gt__              gDbi.__ne__              gDbi.__setattr__         gDbi.comment
gDbi.GetOutputLevel      gDbi.SetOutputLevel      gDbi.__doc__             gDbi.__hash__            gDbi.__new__             gDbi.__sizeof__          gDbi.outputlevel
gDbi.GetRegistry         gDbi.ShowMembers         gDbi.__eq__              gDbi.__init__            gDbi.__nonzero__         gDbi.__str__             gDbi.registry
gDbi.Instance

The name of a object followed by <RETURN> outputs the repr (representation) of the object:

In [3]: gDbi.cascader
Out[3]:
DbiCascader numdb 1 authorisingdbno -1 (1st with GLOBALSEQNO)
Closed      offline_db           #0 tmp        closed     mysql://dybdb2.ihep.ac.cn/offline_db

In [4]: gDbi.cascader.__class__
Out[4]: <class 'DybDbi.DbiCascader'>

In [5]: gDbi.cascader.<TAB>
gDbi.cascader.AllocateSeqNo         gDbi.cascader.GetURL                gDbi.cascader.__delattr__           gDbi.cascader.__le__                gDbi.cascader.__sizeof__
gDbi.cascader.CreateStatement       gDbi.cascader.HoldConnections       gDbi.cascader.__dict__              gDbi.cascader.__len__               gDbi.cascader.__skip__
gDbi.cascader.CreateTemporaryTable  gDbi.cascader.IsA                   gDbi.cascader.__doc__               gDbi.cascader.__lt__                gDbi.cascader.__str__
gDbi.cascader.GetAuthorisingDbNo    gDbi.cascader.IsTemporaryTable      gDbi.cascader.__eq__                gDbi.cascader.__module__            gDbi.cascader.__subclasshook__
gDbi.cascader.GetConnection         gDbi.cascader.ReleaseConnections    gDbi.cascader.__format__            gDbi.cascader.__ne__                gDbi.cascader.__weakref__
gDbi.cascader.GetDbName             gDbi.cascader.SetAuthorisingEntry   gDbi.cascader.__ge__                gDbi.cascader.__new__               gDbi.cascader.authorisingdbno
gDbi.cascader.GetDbNo               gDbi.cascader.SetPermanent          gDbi.cascader.__getattribute__      gDbi.cascader.__nonzero__           gDbi.cascader.check
gDbi.cascader.GetNumDb              gDbi.cascader.ShowMembers           gDbi.cascader.__getitem__           gDbi.cascader.__reduce__            gDbi.cascader.kClosed
gDbi.cascader.GetStatus             gDbi.cascader.TableExists           gDbi.cascader.__gt__                gDbi.cascader.__reduce_ex__         gDbi.cascader.kFailed
gDbi.cascader.GetStatusAsString     gDbi.cascader.__assign__            gDbi.cascader.__hash__              gDbi.cascader.__repr__              gDbi.cascader.kOpen
gDbi.cascader.GetTableDbNo          gDbi.cascader.__class__             gDbi.cascader.__init__              gDbi.cascader.__setattr__           gDbi.cascader.numdb

DybDbi with some magic

Explore what DybDbi provides:

In [1]: import DybDbi

In [2]: DybDbi.<TAB>
Display all 117 possibilities? (y or n)
DybDbi.CSV                     DybDbi.DbiStatement__del__     DybDbi.GDbiLogEntry            DybDbi.TimeStamp               DybDbi.__reduce__              DybDbi.gbl
DybDbi.Context                 DybDbi.DbiTableProxy           DybDbi.GDcsAdTemp              DybDbi.TimeStampExt            DybDbi.__reduce_ex__           DybDbi.genDbi
DybDbi.ContextRange            DybDbi.DbiTableProxyRegistry   DybDbi.GDcsPmtHv               DybDbi.UTC                     DybDbi.__repr__                DybDbi.h
DybDbi.Ctx                     DybDbi.DbiValRecSet            DybDbi.GFeeCableMap            DybDbi.Wrap                    DybDbi.__setattr__             DybDbi.inspect
DybDbi.DBCas                   DybDbi.DbiValidityRec          DybDbi.GNAMES                  DybDbi.ZERO                    DybDbi.__sizeof__              DybDbi.kNow
DybDbi.DBConf                  DybDbi.Detector                DybDbi.GPhysAd                 DybDbi.__all__                 DybDbi.__str__                 DybDbi.log
DybDbi.Dbi                     DybDbi.DetectorId              DybDbi.GSimPmtSpec             DybDbi.__builtins__            DybDbi.__subclasshook__        DybDbi.logging
DybDbi.DbiCache                DybDbi.DetectorSensor          DybDbi.LOG                     DybDbi.__class__               DybDbi.__warningregistry__     DybDbi.make_decorator
DybDbi.DbiCascader             DybDbi.DybDbi                  DybDbi.Level                   DybDbi.__delattr__             DybDbi._getattr                DybDbi.mapper
DybDbi.DbiCascader__check      DybDbi.DybDbi__comment         DybDbi.MYSQLDUMP               DybDbi.__dict__                DybDbi.attfn                   DybDbi.n
DybDbi.DbiCascader__getitem__  DybDbi.GCalibFeeSpec           DybDbi.Mapper                  DybDbi.__doc__                 DybDbi.cls                     DybDbi.os
DybDbi.DbiCascader__repr__     DybDbi.GCalibPmtSpec           DybDbi.NullHandler             DybDbi.__file__                DybDbi.clss                    DybDbi.pprint
DybDbi.DbiConnection           DybDbi.GDaqCalibRunInfo        DybDbi.POST                    DybDbi.__format__              DybDbi.csv_tools               DybDbi.t0
DybDbi.DbiConnection__repr__   DybDbi.GDaqRawDataFileInfo     DybDbi.ServiceMode             DybDbi.__getattribute__        DybDbi.csvrw                   DybDbi.timedelta
DybDbi.DbiCtx                  DybDbi.GDaqRunInfo             DybDbi.SimFlag                 DybDbi.__hash__                DybDbi.datetime                DybDbi.tzinfo
DybDbi.DbiCtx__call__          DybDbi.GDbiConfigSet           DybDbi.Site                    DybDbi.__init__                DybDbi.detector_reps           DybDbi.utc
DybDbi.DbiCtx__repr__          DybDbi.GDbiDemoData1           DybDbi.Source                  DybDbi.__name__                DybDbi.dtr                     DybDbi.wrap
DybDbi.DbiSqlContext           DybDbi.GDbiDemoData2           DybDbi.TList                   DybDbi.__new__                 DybDbi.dtrs
DybDbi.DbiSqlValPacket         DybDbi.GDbiDemoData3           DybDbi.TMap                    DybDbi.__package__             DybDbi.fromUTCDatetime
DybDbi.DbiStatement            DybDbi.GDbiDemoData4           DybDbi.TObject                 DybDbi.__path__                DybDbi.gDbi