Other sop/dbdumpload

Table Of Contents

Previous topic

DB Administration

Next topic

DB Services

This Page

Daya Bay Links

Content Skeleton

Custom DB Operations

On rare occasions it is expedient to perform DB operations without following SOP approaches. For example when jumpstarting large or expensive to create tables such as the DcsAdWpHv table. Typically tables are communicated via mysqldump files in this case.

Mostly such custom operations are performed by DB managers, although table updaters can benefit from being aware of how things are done.

Tools to manipulate mysqldump files

Scripts to facilitate non-SOP operations:

dbdumpload.py
dump provided simple interface to the full mysqldump command, load does similar for loading using mysql client NB this script simply emits command strings to stdout is does not run them
mysql.py
simple interface to mysql client that is DBCONF aware, avoids reentering tedious connection parameters

Many examples of using these are provided below.

Preparing and Comparing Dump files

Table renaming in DB

After using interactive mysql to rename the shunted tables in tmp_offline_db:

mysql> drop table  DcsAdWpHv, DcsAdWpHvVld ;
Query OK, 0 rows affected (0.10 sec)

mysql> rename table DcsAdWpHvShunted  to DcsAdWpHv ;
Query OK, 0 rows affected (0.00 sec)

mysql> rename table DcsAdWpHvShuntedVld to DcsAdWpHvVld ;
Query OK, 0 rows affected (0.00 sec)

Dump using extended insert

Using extended insert (the default emitted by dbdumpload.py) is regarded as safer as it produces smaller dumps and faster loads and dumps. The disadvantage is very few newlines in the dump making diff and vi unusable:

dbdumpload.py tmp_offline_db          dump          ~/tmp_offline_db.DcsAdWpHv.xi.sql -t "DcsAdWpHv DcsAdWpHvVld" | sh
dbdumpload.py tmp_ynakajim_offline_db dump ~/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql -t "DcsAdWpHv DcsAdWpHvVld" | sh

Compare extended insert dumps

Try comparison against dump from Yasu’s DB:

du -h  ~/tmp_offline_db.DcsAdWpHv.xi.sql ~/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql
25M     /home/blyth/tmp_offline_db.DcsAdWpHv.xi.sql
25M     /home/blyth/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql

wc  ~/tmp_offline_db.DcsAdWpHv.xi.sql ~/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql
      94    16043 26050743 /home/blyth/tmp_offline_db.DcsAdWpHv.xi.sql
      94    16043 26050752 /home/blyth/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql
     188    32086 52101495 total

Insert dates in vld tables differ but they all have similar dates in the 2* of Aug so make em all the same:

perl -p -e 's,2012-08-2\d \d\d:\d\d:\d\d,2012-08-2X XX:XX:XX,g' ~/tmp_offline_db.DcsAdWpHv.xi.sql > ~/tmp_offline_db.DcsAdWpHv.xi.sql.cf
perl -p -e 's,2012-08-2\d \d\d:\d\d:\d\d,2012-08-2X XX:XX:XX,g' ~/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql > ~/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql.cf

Check that did not change size:

[blyth@belle7 DybDbi]$ wc ~/tmp_offline_db.DcsAdWpHv.xi.sql* ~/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql*
       94     16043  26050743 /home/blyth/tmp_offline_db.DcsAdWpHv.xi.sql
       94     16043  26050743 /home/blyth/tmp_offline_db.DcsAdWpHv.xi.sql.cf
       94     16043  26050752 /home/blyth/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql
       94     16043  26050752 /home/blyth/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql.cf
      376     64172 104202990 total

Now can diff:

diff  ~/tmp_offline_db.DcsAdWpHv.xi.sql.cf ~/tmp_ynakajim_offline_db.DcsAdWpHv.xi.sql.cf
3c3
< -- Host: belle7.nuu.edu.tw    Database: tmp_offline_db
---
> -- Host: dayabaydb.lbl.gov    Database: tmp_ynakajim_offline_db
5c5
< -- Server version     5.0.77-log
---
> -- Server version     5.0.95-log
94c94
< -- Dump completed on 2012-08-30  4:09:09
---
> -- Dump completed on 2012-08-30  4:13:45

communicating dumps via website

Distributing large files via email is inefficient its is must preferable to use DocDB or other webserver that you control.

On source machine, record the digest of the dump:

[blyth@belle7 utils]$ du -h /home/blyth/tmp_offline_db.DcsAdWpHv.xi.sql
25M     /home/blyth/tmp_offline_db.DcsAdWpHv.xi.sql
[blyth@belle7 utils]$ md5sum /home/blyth/tmp_offline_db.DcsAdWpHv.xi.sql
90ac4649f5ae3f2a94f187e1885819d8  /home/blyth/tmp_offline_db.DcsAdWpHv.xi.sql

Transfers to publish via nginx:

simon:lode blyth$ scp N:tmp_offline_db.DcsAdWpHv.xi.sql .
simon:lode blyth$ scp tmp_offline_db.DcsAdWpHv.xi.sql WW:local/nginx/html/data/

Download mysqldump file and load into DB

download dump and verify digest

Check the digest matches after downloading elsewhere:

[blyth@cms01 ~]$ curl -O http://dayabay.ihep.ac.cn:8080/data/tmp_offline_db.DcsAdWpHv.xi.sql
[blyth@cms01 ~]$
[blyth@cms01 ~]$ md5sum tmp_offline_db.DcsAdWpHv.xi.sql
90ac4649f5ae3f2a94f187e1885819d8  tmp_offline_db.DcsAdWpHv.xi.sql

Checking the dump

Check the head and tail of the dump, use -c option to avoid problems of very long lines:

[blyth@cms01 ~]$ head -c 2000 tmp_offline_db.DcsAdWpHv.xi.sql
-- MySQL dump 10.11
--
-- Host: belle7.nuu.edu.tw    Database: tmp_offline_db
-- ------------------------------------------------------


[blyth@cms01 ~]$ tail -c 2000 tmp_offline_db.DcsAdWpHv.xi.sql
-- Dump completed on 2012-08-30  4:09:09

Check that the dump has CREATE only for the expected new tables and has no DROP:

[blyth@belle7 DybDbi]$ grep CREATE  ~/tmp_offline_db.DcsAdWpHv.xi.sql
CREATE TABLE `DcsAdWpHv` (
CREATE TABLE `DcsAdWpHvVld` (
[blyth@belle7 DybDbi]$ grep DROP  ~/tmp_offline_db.DcsAdWpHv.xi.sql
[blyth@belle7 DybDbi]$

Warning

DANGER OF BLASTING ALL TABLES IN DB HERE : BE DOUBLY CERTAIN THAT ONLY DESIRED NEW TABLES ARE THERE

Testing loading into tmp_copy_db

The dbdumpload.py script simply emits a string to stdout with the command to check before running by piping to sh, when loading this command cats the dump to the mysql client.

[blyth@belle7 DybDbi]$ dbdumpload.py tmp_copy_db load ~/tmp_offline_db.DcsAdWpHv.xi.sql    ## check command is correct and are targetting intended host/DB/user
cat /home/blyth/tmp_offline_db.DcsAdWpHv.sql | /data1/env/local/dyb/external/mysql/5.0.67/i686-slc5-gcc41-dbg/bin/mysql --no-defaults --host=belle7.nuu.edu.tw --user=*** --password=*** tmp_copy_db
[blyth@belle7 DybDbi]$
[blyth@belle7 DybDbi]$ dbdumpload.py tmp_copy_db load ~/tmp_offline_db.DcsAdWpHv.xi.sql | sh    ## run the comman

Warning

the tables must not exist already for the load to succeed

Simple checks on loaded table

Check see expected number of SEQNO in the loaded table:

[blyth@belle7 DybDbi]$ echo "select min(SEQNO),max(SEQNO),max(SEQNO)-min(SEQNO)+1,count(*) as N from DcsAdWpHv " | $(mysql.py tmp_copy_db)
+------------+------------+-------------------------+---------+
| min(SEQNO) | max(SEQNO) | max(SEQNO)-min(SEQNO)+1 | N       |
+------------+------------+-------------------------+---------+
|          1 |       3926 |                    3926 | 1003200 |
+------------+------------+-------------------------+---------+
[blyth@belle7 DybDbi]$ echo "select min(SEQNO),max(SEQNO),max(SEQNO)-min(SEQNO)+1,count(*) as N from DcsAdWpHvVld " | $(mysql.py tmp_copy_db)
+------------+------------+-------------------------+------+
| min(SEQNO) | max(SEQNO) | max(SEQNO)-min(SEQNO)+1 | N    |
+------------+------------+-------------------------+------+
|          1 |       3926 |                    3926 | 3926 |
+------------+------------+-------------------------+------+

Fixup DBI metadata table LOCALSEQNO

Fixup the LOCALSEQNO metdata table setting the LASTUSEDSEQNO for the jumpstarted table using interactive mysql:

mysql> use tmp_copy_db

Database changed
mysql> select * from LOCALSEQNO ;
+-------------------+---------------+
| TABLENAME         | LASTUSEDSEQNO |
+-------------------+---------------+
| *                 |             0 |
| CalibFeeSpec      |           113 |
| CalibPmtSpec      |           713 |
| FeeCableMap       |             3 |
| HardwareID        |           386 |
| CableMap          |           509 |
| Reactor           |           960 |
| CoordinateAd      |             1 |
| CoordinateReactor |             2 |
| CalibPmtHighGain  |          1268 |
| CalibPmtPedBias   |             1 |
| EnergyRecon       |           914 |
| CalibPmtFineGain  |          7943 |
+-------------------+---------------+
13 rows in set (0.00 sec)


mysql> insert into LOCALSEQNO values ('DcsAdWpHv', 3926 ) ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from LOCALSEQNO ;
+-------------------+---------------+
| TABLENAME         | LASTUSEDSEQNO |
+-------------------+---------------+
| *                 |             0 |
| CalibFeeSpec      |           113 |
| CalibPmtSpec      |           713 |
| FeeCableMap       |             3 |
| HardwareID        |           386 |
| CableMap          |           509 |
| Reactor           |           960 |
| CoordinateAd      |             1 |
| CoordinateReactor |             2 |
| CalibPmtHighGain  |          1268 |
| CalibPmtPedBias   |             1 |
| EnergyRecon       |           914 |
| CalibPmtFineGain  |          7943 |
| DcsAdWpHv         |          3926 |
+-------------------+---------------+
14 rows in set (0.00 sec)

Verifying offline_db load by another dump

[blyth@belle7 DybDbi]$ dbdumpload.py offline_db dump ~/offline_db.DcsAdWpHv.sql -t "DcsAdWpHv DcsAdWpHvVld" | sh
real    0m29.624s

[blyth@belle7 DybDbi]$ diff  ~/offline_db.DcsAdWpHv.sql ~/tmp_offline_db.DcsAdWpHv.xi.sql
3c3
< -- Host: dybdb2.ihep.ac.cn    Database: offline_db
---
> -- Host: belle7.nuu.edu.tw    Database: tmp_offline_db
5c5
< -- Server version     5.0.45-community
---
> -- Server version     5.0.77-log
94c94
< -- Dump completed on 2012-08-31  3:58:24
---
> -- Dump completed on 2012-08-30  4:09:09
[blyth@belle7 DybDbi]$
[blyth@belle7 DybDbi]$
[blyth@belle7 DybDbi]$ du  ~/offline_db.DcsAdWpHv.sql ~/tmp_offline_db.DcsAdWpHv.xi.sql
25476   /home/blyth/offline_db.DcsAdWpHv.sql
25476   /home/blyth/tmp_offline_db.DcsAdWpHv.xi.sql
[blyth@belle7 DybDbi]$
[blyth@belle7 DybDbi]$ echo select \* from LOCALSEQNO where TABLENAME=\'DcsAdWpHv\' | $(mysql.py offline_db)
+-----------+---------------+
| TABLENAME | LASTUSEDSEQNO |
+-----------+---------------+
| DcsAdWpHv |          3926 |
+-----------+---------------+

Copying a few DBI tables between DBs using rdumpcat, rloadcat

Note that the procedure presented in this section relies on options added to the db.py script in dybsvn:r18671, (circa Nov 10th, 2012) thus ensure your version of db.py is at that revision or later before attempting the below.:

db.py --help   ## check revision of script in use

Talking to two or more DBI cascades from the same process is not easily achievable, thus it is expedient and actually rather efficient to copy DBI tables between Databases by means of serializations in the form of ascii catalogs.

The normal SOP procedure to create a partial copy of offline_db in each users tmp_offline_db by design creates the target DB anew. This policy is adopted as the tmp_offline_db should be regarded as temporary expedients of limited lifetime created while working on an update.

Experts wishing to copy a few DBI tables between Databases without blasting the target DB can do so using special options to the same rdumpcat and rloadcat commands of the db.py script.

Non-decoupled rdumpcat into empty folder

Serialize one or more DBI tables specified using comma delimited -t,–tselect option from a DB specified by DBCONF a section name into a partial ascii catalog created in an empty folder:

rm -rf ~/dbicopy ; mkdir ~/dbicopy
db.py -D -t PhysAd tmp_offline_db rdumpcat ~/dbicopy/tmp_offline_db

The option -D,–nodecoupled is required to avoid: AssertionError: decoupled rdumpcat must be done into a preexisting catalog

Loading of partial ascii catalog into target DB with fastforwarding of INSERTDATEs

db.py -P -t PhysAd tmp_offline_db  rloadcat ~/dbicopy/tmp_offline_db

The option -P,–ALLOW_PARTIAL is required to allow dealing with partial catalogs. Normally the integrity of the catalog is checked by verifying that all expected tables are present, this option skips these checks.

If the tmp_offline_db has a preexisting version of the table which matches that in the ascii catalog then the rloadcat command does nothing, and warns:

WARNING:DybPython.db:no updates (new tables or new SEQNO) are detected, nothing to do

In order to test the load, first remove some entries eg using the below bash functions.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
#!/bin/sh 
tab-usage(){ cat << EOU

Bash Functions for chopping DBI tables
=======================================

.. warning:: **ONLY** for test usage in `tmp_offline_db` 

Functions::

   tab-chop- <payload-table-name> <max-seqno-to-keep>
   tab-meta- <payload-table-name>


Usage::

   . tab.sh                  # source the functions
   echo status | mysql      # verify are talking to desired DB (in the client section of .my.cnf)

   echo select \* from LOCALSEQNO | mysql -t 
   echo select \* from PhysAdVld  | mysql -t    # check tables before chopping
   echo select \* from PhysAd     | mysql -t 

   tab-chop- PhysAd 4 | mysql       # remove all SEQNO from PhysAd and PhysAdVld with SEQNO > 4 
   tab-fixmeta- PhysAd  | mysql     # adjust LOCALSEQNO metadata table, changing LASTUSEDSEQNO for PhysAd pair


EOU
}
tab-chop-(){ 
    local tab=${1:-PhysAd}
    local seqno=${2:-1000000}
    cat << EOC
delete from $tab, ${tab}Vld using $tab inner join ${tab}Vld where ${tab}.SEQNO = ${tab}Vld.SEQNO and ${tab}.SEQNO > $seqno ;
EOC
}
tab-fixmeta-(){
    local tab=${1:-PhysAd}
    cat << EOC
update LOCALSEQNO set LASTUSEDSEQNO=(select max(SEQNO) from $tab) where TABLENAME='$tab' ; 
EOC
}

Then run the rloadcat command, and enter YES in response to the prompt.

    [blyth@belle7 DybPython]$ db.py -P -t PhysAd tmp_offline_db  rloadcat ~/dbicopy/tmp_offline_db
    INFO:DybPython.db:{'VERSION()': '5.0.77-log', 'CURRENT_USER()': 'root@belle7.nuu.edu.tw', 'DATABASE()': 'tmp_offline_db', 'CONNECTION_ID()': 14958L}
    INFO:DybPython.asciicat:read /home/blyth/dbicopy/tmp_offline_db/tmp_offline_db.cat
    INFO:DybPython.asciicat:reading table LOCALSEQNO
    INFO:DybPython.asciicat:reading table PhysAdVld
    INFO:DybPython.asciicat:done AsciiCat [3    ] /home/blyth/dbicopy/tmp_offline_db {'PhysAd': 9, 'Demo': 11, 'HardwareID': 386, 'DcsAdWpHv': 4549, 'CalibPmtHighGain': 1268, 'CalibPmtFineGain': 1410, 'CableMap': 509}
    INFO:DybPython.asciicat:seqno_updates : ascii catalog LASTUSEDSEQNO changes relative to target : {'PhysAd': 9}
    INFO:DybPython.db: PhysAd               has 5 new SEQNO : [5, 6, 7, 8, 9]
    INFO:DybPython.db:changed tables ['PhysAd']
    Enter YES to proceed with rloadcat for  : ['PhysAd']
    INFO:DybPython.db:user consents to update tables ['PhysAd']
    INFO:DybPython.asciicat:seqno_updates : ascii catalog LASTUSEDSEQNO changes relative to target : {'PhysAd': 9}
    INFO:DybPython.asciicat:fastforward 5 validity rows of PhysAd to 2012-11-26 08:45:14
    WARNING:DybPython.asciicat:inplace overwriting /home/blyth/dbicopy/tmp_offline_db/PhysAd/PhysAdVld.csv
    INFO:DybPython.db:loadcsv_  PhysAd loading paths ['/home/blyth/dbicopy/tmp_offline_db/PhysAd/PhysAd.csv', '/home/blyth/dbicopy/tmp_offline_db/PhysAd/PhysAdVld.csv'] into tabs ('PhysAd', 'PhysAdVld') replace_ignore IGNORE
    INFO:DybPython.dbcmd:MySQLImport time /data1/env/local/dyb/external/mysql/5.0.67/i686-slc5-gcc41-dbg/bin/mysqlimport --no-defaults --local --verbose --ignore --host=belle7.nuu.edu.tw --user=root --password=***  --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --ignore-lines=1  --lock-tables tmp_offline_db /home/blyth/dbicopy/tmp_offline_db/PhysAd/PhysAd.csv /home/blyth/dbicopy/tmp_offline_db/PhysAd/PhysAdVld.csv

    real    0m0.020s
    user    0m0.005s
    sys     0m0.005s
    INFO:DybPython.db:Connecting to belle7.nuu.edu.tw
    Selecting database tmp_offline_db
    Locking tables for write
    Loading data from LOCAL file: /home/blyth/dbicopy/tmp_offline_db/PhysAd/PhysAd.csv into PhysAd
    tmp_offline_db.PhysAd: Records: 9  Deleted: 0  Skipped: 4  Warnings: 0
    Loading data from LOCAL file: /home/blyth/dbicopy/tmp_offline_db/PhysAd/PhysAdVld.csv into PhysAdVld
    tmp_offline_db.PhysAdVld: Records: 9  Deleted: 0  Skipped: 4  Warnings: 0
    Disconnecting from belle7.nuu.edu.tw

    INFO:DybPython.db:loadcsv_  LOCALSEQNO loading paths ['/home/blyth/dbicopy/tmp_offline_db/LOCALSEQNO/LOCALSEQNO.csv'] into tabs ('LOCALSEQNO',) replace_ignore REPLACE
    INFO:DybPython.dbcmd:MySQLImport time /data1/env/local/dyb/external/mysql/5.0.67/i686-slc5-gcc41-dbg/bin/mysqlimport --no-defaults --local --verbose --replace --host=belle7.nuu.edu.tw --user=root --password=***  --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --ignore-lines=1  --lock-tables tmp_offline_db /home/blyth/dbicopy/tmp_offline_db/LOCALSEQNO/LOCALSEQNO.csv

    real    0m0.010s
    user    0m0.004s
    sys     0m0.005s
    INFO:DybPython.db:Connecting to belle7.nuu.edu.tw
    Selecting database tmp_offline_db
    Locking tables for write
    Loading data from LOCAL file: /home/blyth/dbicopy/tmp_offline_db/LOCALSEQNO/LOCALSEQNO.csv into LOCALSEQNO
    tmp_offline_db.LOCALSEQNO: Records: 8  Deleted: 8  Skipped: 0  Warnings: 0
    Disconnecting from belle7.nuu.edu.tw

In the above output notice confirmation required that reports the additional SEQNO to be loaded and the fastforwarding of the validity dates to the time of the insert. The rloadcat internally uses mysqlimport command to efficiently load the ascii catalog into the DB. Note the different –replace and –ignore options used for the LOCALSEQNO table and the others. These options to mysqlimport control handling of input rows that duplicate existing rows on unique key values.

–replace
new rows replace existing rows that have the same unique key value, used for LOCALSEQNO which has PK the TABLENAME as the metadata table needed to have LASTUSEDSEQNO values updated.
–ignore
input rows that duplicate an existing row on a unique key value are skipped, used for DBI payload and validity tables with PK (SEQNO) or (SEQNO,ROW_COUNTER). This means that rloadcat cannot change pre-existing DBI table content, it can only add new entries.

Warning

All LOCALSEQNO entries from the ascii catalog are loaded and will replace any preceeding entries. Thus make sure only expected SEQNO changes are propagated.

Jumpstarting offline_db.DqChannelPacked table

The DqChannelPacked tables were prepared by compressing the channelquality_db.DqChannelStatus table.

Create mysqldump file

Local mysqldump of 396202 packed entries is quick, less than 3 seconds:

[blyth@belle7 ~]$ dbdumpload.py tmp_testpack_offline_db dump ~/tmp_testpack_offline_db.DqChannelPacked.sql --tables 'DqChannelPacked DqChannelPackedVld'  # check command
[blyth@belle7 ~]$ dbdumpload.py tmp_testpack_offline_db dump ~/tmp_testpack_offline_db.DqChannelPacked.sql --tables 'DqChannelPacked DqChannelPackedVld' | sh   # do it
real    0m2.494s
user    0m1.973s
sys     0m0.241s

Use head -c tail -c and grep -i create:

[blyth@belle7 ~]$ grep -i create  ~/tmp_testpack_offline_db.DqChannelPacked.sql
CREATE TABLE `DqChannelPacked` (
CREATE TABLE `DqChannelPackedVld` (

Record size/digest of dump

Check the dumpfile:

[blyth@belle7 ~]$ du -h ~/tmp_testpack_offline_db.DqChannelPacked.sql     ## only 75M
75M     /home/blyth/tmp_testpack_offline_db.DqChannelPacked.sql

[blyth@belle7 ~]$ md5sum ~/tmp_testpack_offline_db.DqChannelPacked.sql
60c66fce91b760a3e8865c4c60f4f86c  /home/blyth/tmp_testpack_offline_db.DqChannelPacked.sql

[blyth@belle7 ~]$ ls -l ~/tmp_testpack_offline_db.DqChannelPacked.sql
-rw-rw-r-- 1 blyth blyth 77746774 Jul 29 16:56 /home/blyth/tmp_testpack_offline_db.DqChannelPacked.sql

Check its viable by creating a DB from it

Create and populate tmp_checkpack_offline_db section:

[blyth@belle7 ~]$ vi ~/.my.cnf       # add a tmp_checkpack_offline_db section pointing at DB of that name on local server
[blyth@belle7 ~]$ echo status | mysql     #  make sure the client section of ~/.my.cnf is pointing at the desired server, the DB name does not matter
[blyth@belle7 ~]$ echo "create database tmp_checkpack_offline_db" | mysql      # create the check DB, will get an error if exists already
[blyth@belle7 ~]$ dbdumpload.py tmp_checkpack_offline_db load ~/tmp_testpack_offline_db.DqChannelPacked.sql    # check the loading command
[blyth@belle7 ~]$ dbdumpload.py tmp_checkpack_offline_db load ~/tmp_testpack_offline_db.DqChannelPacked.sql | sh    # run it, 4~5 seconds for local load

Sanity checking the dump via the DB created from it:

[blyth@belle7 ~]$ echo "select count(*) from DqChannelPacked" | mysql tmp_testpack_offline_db  -N
396202
[blyth@belle7 ~]$ echo "select count(*) from DqChannelPackedVld" | mysql tmp_testpack_offline_db  -N
396202
[blyth@belle7 ~]$ echo "select count(*) from DqChannelPacked" | mysql tmp_checkpack_offline_db  -N
396202
[blyth@belle7 ~]$ echo "select count(*) from DqChannelPackedVld" | mysql tmp_checkpack_offline_db  -N
396202

Position that for web accessibility (admin reminder)

Make the dump available at http://dayabay.ihep.ac.cn:8080/data/tmp_testpack_offline_db.DqChannelPacked.sql:

simon:~ blyth$ scp N:tmp_testpack_offline_db.DqChannelPacked.sql .
simon:~ blyth$ scp tmp_testpack_offline_db.DqChannelPacked.sql WW:/home/blyth/local/nginx/html/data/
simon:~ blyth$ curl -s http://dayabay.ihep.ac.cn:8080/data/tmp_testpack_offline_db.DqChannelPacked.sql | md5sum
60c66fce91b760a3e8865c4c60f4f86c

Download the dump and check digest

Digest and size matches expectations:

-bash-3.2$ curl -s -O http://dayabay.ihep.ac.cn:8080/data/tmp_testpack_offline_db.DqChannelPacked.sql

-bash-3.2$ md5sum tmp_testpack_offline_db.DqChannelPacked.sql
60c66fce91b760a3e8865c4c60f4f86c  tmp_testpack_offline_db.DqChannelPacked.sql

-bash-3.2$ ll tmp_testpack_offline_db.DqChannelPacked.sql
-rw-r--r-- 1 blyth dyw 77746774 Jul 29 18:24 tmp_testpack_offline_db.DqChannelPacked.sql

CQScraper testing

The CQScraper reads from channelquality_db.DqChannelStatus using MySQL-python and writes to the DB pointed to by DBCONF using DBI. The target DB needs to contain the CableMap table, in order for the canonical channel ordering to be accessible.

Create a test DB to check CQScraper Operation

Use db.py dump/load in the normal manner to make a copy of offline_db into eg tmp_cqscrapertest_offline_db

Load the mysqldump creating the new new tables

Use the techniques described above to add the pre-cooked DqChannelPacked and DqChannelPackedVld tables to the test DB. If pre-existing empty tables are present, they will need to be dropped first.:

mysql> drop tables DqChannelPacked, DqChannelPackedVld ;
Query OK, 0 rows affected (0.02 sec)

mysql> delete from LOCALSEQNO where TABLENAME='DqChannelPacked' ;     # remove any pre-existing entry for DqChannelPacked
Query OK, 1 row affected (0.00 sec)

Fixup LOCALSEQNO

Using the maximum SEQNO in the mysqldump, to fixup the LOCALSEQNO for the new table:

mysql> insert into LOCALSEQNO VALUES ('DqChannelPacked',396202 ) ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into LOCALSEQNO VALUES ('DqChannelPacked',396202 ) ;      # cannot change this way, would need to delete first
ERROR 1062 (23000): Duplicate entry 'DqChannelPacked' for key 1
mysql>

Configure a node to run the CQScraper cron task

The node requires

  1. recent NuWa installation (one of the IHEP slave nodes perhaps ?)
  2. crontab permissions to add the cron commandline

An example cron command line, that invokes the dybinst command every hour:

SHELL=/bin/bash
CRONLOG_DIR=/home/blyth/cronlog
DYBINST_DIR=/data1/env/local/dyb
#
15 * * * * ( cd $DYBINST_DIR ; DBCONF=tmp_cqscrapertest_offline_db ./dybinst trunk scrape CQScraper ) > $CRONLOG_DIR/CQScraper/CQScraper_$(date +"\%Y\%m\%d-\%H\%M").log 2>&1
#
# after good behaviour is confirmed the log writing can be scaled back to just keeping the last months worth with:  $(date +"\%d-\%H\%M")

The scraper checks where it is up to in the target DB and propagates any new entries from source into target. See the docstring for details dybgaudi:Database/Scraper/python/Scraper/dq/CQScraper.py

Repeat for offline_db

If a test run of a few days into the tmp_ DB is OK then Liang/Qiumei can repeat the steps for offline_db Catching up a few days worth of entries is not prohibitive, so starting from the initial mysqldump will be simpler that creating a new one.