Project

General

Profile

Feature #4337

Cleanup database tables

Added by Robert Illingworth over 6 years ago. Updated almost 3 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Target version:
-
Start date:
07/15/2013
Due date:
01/17/2014
% Done:

100%

Estimated time:
Spent time:
Duration: 187

Description

The schema contains lots of obsolete and unused tables. We should have a general cleanup and removal of obsolete tables, views, and other database objects.

History

#1 Updated by Robert Illingworth almost 6 years ago

  • Due date set to 01/17/2014
  • Priority changed from Low to Normal
  • % Done changed from 0 to 70

#2 Updated by Stephen White almost 6 years ago

The scripts have been created.

The first databases to be cleaned up are MINOSDEV/MINOSPRD. See CHG000000007279.

#3 Updated by Arthur Kreymer almost 6 years ago

Deployment is being tracked in CHG000000007279 Remove obsolete SAM database tables

#4 Updated by Arthur Kreymer almost 6 years ago

minosdev was tested Wed Feb 12 around 08:30.

Most simple tests passed, but a project failed.

export SAM_STATION=minos

UNIV=dev # or dev for testing the development univers

setup sam -q ${UNIV}
sam ping dbserver
sam get dbserver info
sam get dbserver connection info
sam locate foo
sam locate F00031300_0000.mdaq.root
sam get metadata --file=F00031300_0000.mdaq.root

sam dump station --all

The above tests gave the expect outputs.
Running a project failed :

~kreymer/minos/scripts/sam_test_py minos ${UNIV}

OK running 
station minos
dbserver dev
dataset st-onesmall
project sam_test_project_20140212143752
fileCut 0
Traceback (most recent call last):
File "/afs/fnal.gov/files/home/room1/kreymer/minos/scripts/sam_test_py", line 97, in ?
fileCut = CUT
File "sam_common_pylib/SamCommand/BlessedCommandInterfacePlaceHolder.py", line 81, in call
File "sam_common_pylib/SamCommand/CommandInterface.py", line 251, in call
File "sam_common_pylib/SamCommand/SamCommandInterface.py", line 240, in apiWrapper
File "sam_user_pyapi/src/samProject.py", line 333, in implementation
File "sam_common_pylib/SamCommand/BlessedCommandInterfacePlaceHolder.py", line 81, in call
File "sam_common_pylib/SamCommand/CommandInterface.py", line 251, in call
File "sam_common_pylib/SamCommand/SamCommandInterface.py", line 240, in apiWrapper
File "sam_user_pyapi/src/samProject.py", line 407, in implementation
File "sam_common_pylib/SamCommand/BlessedCommandInterfacePlaceHolder.py", line 81, in call
File "sam_common_pylib/SamCommand/CommandInterface.py", line 251, in call
File "sam_common_pylib/SamCommand/SamCommandInterface.py", line 240, in apiWrapper
File "sam_user_pyapi/src/samProject.py", line 264, in implementation
File "sam_common_pylib/SamCorba/SamServerProxy.py", line 230, in _callRemoteMethod
File "sam_common_pylib/SamCorba/SamServerProxyRetryHandler.py", line 266, in handleCall
DbSQLException: looping chain of synonyms
Steve rolled the tables back, and the project succeeded

MINOS27 > ~kreymer/minos/scripts/sam_test_py minos ${UNIV}

OK running 
station minos
dbserver dev
dataset st-onesmall
project sam_test_project_20140212144056
fileCut 0
cid 12390
cpid 58317
job SAMStation.JobCount(jobsAtNode=1, jobsAll=1)
Got dcap://fndca1.fnal.gov:24136/pnfs/fnal.gov/usr/minos/fardet_data/2005-04/F00031300_0000.mdaq.root file 1
Decrementing the job count.
Stopping the project

#5 Updated by Arthur Kreymer almost 6 years ago

Another minosdev test per swhite :

MINOS27 > ~kreymer/minos/scripts/sam_test_py minos ${UNIV}

OK running 
station minos
dbserver dev
dataset st-onesmall
project sam_test_project_20140212162816
fileCut 0
Traceback (most recent call last):
File "/afs/fnal.gov/files/home/room1/kreymer/minos/scripts/sam_test_py", line 97, in ?
fileCut = CUT
File "sam_common_pylib/SamCommand/BlessedCommandInterfacePlaceHolder.py", line 81, in call
File "sam_common_pylib/SamCommand/CommandInterface.py", line 251, in call
File "sam_common_pylib/SamCommand/SamCommandInterface.py", line 240, in apiWrapper
File "sam_user_pyapi/src/samProject.py", line 333, in implementation
File "sam_common_pylib/SamCommand/BlessedCommandInterfacePlaceHolder.py", line 81, in call
File "sam_common_pylib/SamCommand/CommandInterface.py", line 251, in call
File "sam_common_pylib/SamCommand/SamCommandInterface.py", line 240, in apiWrapper
File "sam_user_pyapi/src/samProject.py", line 407, in implementation
File "sam_common_pylib/SamCommand/BlessedCommandInterfacePlaceHolder.py", line 81, in call
File "sam_common_pylib/SamCommand/CommandInterface.py", line 251, in call
File "sam_common_pylib/SamCommand/SamCommandInterface.py", line 240, in apiWrapper
File "sam_user_pyapi/src/samProject.py", line 264, in implementation
File "sam_common_pylib/SamCorba/SamServerProxy.py", line 230, in _callRemoteMethod
File "sam_common_pylib/SamCorba/SamServerProxyRetryHandler.py", line 266, in handleCall
DbSQLException: table or view does not exist

#6 Updated by Arthur Kreymer almost 6 years ago

FYI, the dev dbserver is running on ifsamdev.fnal.gov

#7 Updated by Stephen White almost 6 years ago

Robert looked at the error and it appears the “old style” dataset definitions are being used. These go straight to the dbserver and are no longer supported. I will not put this into production until we work out if end users need to make any changes.

#8 Updated by Arthur Kreymer almost 6 years ago

The sam test projects scripts are available at

/grid/fermiapp/minos/scripts/sam_test_py # python version
/grid/fermiapp/minos/scripts/sam_test_project # /bin/sh version

#9 Updated by Arthur Kreymer almost 6 years ago

Per telephone conversation, will quickly check two items in minos development.
1) Are traditional dataset definitions visible
2) Do samweb projects run correctly.

#10 Updated by Arthur Kreymer almost 6 years ago

The Minos dev universe does not see old sam dataset definitions, as expected.

Projects can run, using samweb-defined datasets, in Minos development :
DS=swt-onesmall
/grid/fermiapp/minos/scripts/sam_test_web minos ${DS} dev

#11 Updated by Arthur Kreymer almost 6 years ago

I am trying to convert all Minos datasets to samweb format, using samweb describe-definition

There are
4697 existing definitions,
4674 needing conversion.

I'm finding enough issues that we may want put the 70 dropped tables
back into dev for tesing there.

1) Over 1200 definitions contain ' ' and '%' characters.
These work fine with list-definition files
but fail with count-definition-files and describe-definition
The latter do not seem to be web-wrapping the definition string.
If I manually convert ' ' and '%' to '%20' and '%24',
list-definition files fails and the latter work.

2) Many of the definitions are not being translated automatically :

DEF="SAM:tjyang_2007-01-01_2007-01-02_all_sntp-far_physics%25_cedar_2007-04-01-19-49-48.304797000-0500"

samweb describe-definition "${DEF}"
Unable to convert old dimensions: Parse error at line 1, column 74: Expected ")"
(((((DATA_TIER sntp-far and RUN_TYPE physics%) and START_TIME > to_date('2007-01-01','YYYY-MM-DD')) and START_TIME < to_date('2007-01-02','YYYY-MM-DD')) and PHYSICAL_DATASTREAM_NAME like 'all%') and VERSION like 'cedar%')

#12 Updated by Robert Illingworth almost 6 years ago

Do you have an example for problem 1?

For 2, the to_date(...) clause isn't supported, mostly because I didn't know (or had forgotten) that it existed. It should be possible to fix it - there's already special date handling code in the converter.

#13 Updated by Robert Illingworth almost 6 years ago

Also note that some of these definitions are broken in old SAM:

$ sam list files --dim="__set__ far-all-sntp-2003-04-w4--defdesc=FarMuDrift 2003-04 week 4" 
Definition not found not found with '{'projDefName': 'far-all-sntp-2003-04-w4--defdescFarMuDrift 2003-04 week 4'}'.  It cannot be used as a predefined dataset definition.

(Notice it has lost the '=' character.)

Spaces and equals (among other characters) are no longer allowed in definition names - with good reason.

#14 Updated by Arthur Kreymer almost 6 years ago

Thanks !

There are only 143 definitions with ' '
These are the same 143 containing '='

These are of the form
far-all-sntp-2003-04-w4--defdesc=FarMuDrift 2003-04 week 4

Dimensions are like
(((((RUN_TYPE physics% or RUN_TYPE normal-data%) and DATA_TIER sntp-far) and PHYSICAL_DATASTREAM_NAME alldata) and VERSION r1.18) and START_TIME <= to_date('2003-04-30','yyyy-mm-dd')and end_time >=to_date('2003-04-22','yyyy-mm-dd'))

It would be nice if samweb describe-definition displayed the other metadata
before attempting the dimension conversion,
so we could see the orignin of these problem cases.

#15 Updated by Arthur Kreymer almost 6 years ago

We should rename the definitions containing special characters.
Blanks and = can be changed to underscore

Datasets names with % are of the form ...physics%_...
There are no conflicts if we drop to %

Is there a samweb way to do the rename ?

#16 Updated by Arthur Kreymer almost 6 years ago

Sorry for the typo, that should have been :

There are no conflicts if we drop the %

#17 Updated by Arthur Kreymer almost 6 years ago

Steve -
Please restore the obsolete tables in minos dev,
so that we can test Minos dataset renaming and conversions in dev.

#18 Updated by Stephen White almost 6 years ago

Art,

The obsolete tables are available for your tests.

#19 Updated by Arthur Kreymer almost 6 years ago

Thanks !

I have verified we have plenty of datasets for testing in dev, 4578
I have test-converted one of them, nickd-test-20090505.

We are in position to test the global conversion now,
once datasets are renamed and we can handle dates.

#20 Updated by Robert Illingworth almost 6 years ago

You should be able to use the samweb modify-definition command to rename existing definitions. I never tried it with one containing characters that are now forbidden, but I don't think it should matter (the new name will be run through the validation check).

#21 Updated by Arthur Kreymer almost 6 years ago

I tried this successfully in minos dev using sam_web_client v1_6

setup sam_web_client v1_6
kinit kreymer
getcert

DEF="far-all-sntp-2003-04-w4--defdesc=FarMuDrift 2003-04 week 4"
NEW="far-all-sntp-2003-04-w4--defdesc_FarMuDrift_2003-04_week_4"

samweb --dev modify-definition "${DEF}" --defname="${NEW}"

samweb --dev describe-definition "${DEF}"
Definition 'far-all-sntp-2003-04-w4--defdesc=FarMuDrift' not found

samweb --dev describe-definition "${NEW}"
Unable to convert old dimensions: Parse error at line 1, column 161: Expected ")"
(((((RUN_TYPE physics% or RUN_TYPE normal-data%) and DATA_TIER sntp-far) and PHYSICAL_DATASTREAM_NAME alldata) and VERSION r1.18) and START_TIME <= to_date('2003-04-30','yyyy-mm-dd')and end_time >=to_date('2003-04-22','yyyy-mm-dd'))

This is the correct result.

#22 Updated by Stephen White almost 6 years ago

This is great, Art. Can I again rename the obsolete tables in minosDEV? Also, are you applying your changes to production now?

#23 Updated by Arthur Kreymer almost 6 years ago

I need to test conversion of definitions containing dates in dev,
before we re-remove the old tables.
This is waiting for support for such defs to be added by Robert.

#24 Updated by Robert Illingworth over 5 years ago

I've updated the dev sam-web instances to the git head. You can try the conversion and see if the definitions using to_date now work.

(This update also makes list-definitions with no arguments list all of them rather than complaining about query parameters.)

#25 Updated by Arthur Kreymer over 5 years ago

Date: Wed, 12 Mar 2014 17:40:42 -0500
From: Nathaniel Tagg <>
To: Art Kreymer <>
Subject: Re: Minos SAM update

Ok, I'll swing by tomorrow.

The short version: I need a Loon job to run over a set of data files
described all files between run number N and run number M. They need to all
run in the same job. I can do do any script-foo around that accomplishes
this, ideally without need for local storage of the files.

---NJT

Advice to tagg :

Excellent !

The new way to create a file list like that in SAM dataset
fd-linearity-gaincurve-2014-2-1
is to do

setup sam_web_client
export SAM_EXPERIMENT=minos

SAMDIM="data_tier 'raw-far' and run_number >= 53477 and run_number <= 53483"
FILES=`samweb list-files "${SAMDIM}"`

If you run on node minos-slf6, you can open the files directly,
with this list of paths :

PNFSFILES=$(
for FILE in ${FILES} ; do
PNFS=`samweb locate-file ${FILE} | grep ^enstore | cut -f 2 -d : | cut -f 1 -d \(`
printf "${PNFS}/${FILE}\n"
done
)

In SLSF 5 nodes like minos50/51/52/53,
we have had best stability by taking local copies,
which complicates things,
as a month of data is nearly 100 GB of extra file copying.

We have had persistent problems using direct Dcache file access like
dcap://fndca1.fnal.gov:24136/pnfs/fnal.gov/usr/minos/neardet_data/...

Whether accessing directly or making copies, the DCAP paths are

DCPRE=dcap://fndca1.fnal.gov:24136/pnfs/fnal.gov/usr
DCAPFILES=$(
for FILE in ${FILES} ; do
PNFS=`samweb locate-file ${FILE} | grep ^enstore | cut -f 2 -d : | cut -f 1 -d \(`
printf "${DCPRE}/${PNFS:6}/${FILE}\n"
done
)

Copies would be like

FILE=F00053483_0022.mdaq.root
DCAPFILE=dcap://fndca1.fnal.gov:24136/pnfs/fnal.gov/usr/minos/fardet_data/2014-02/F00053481_0000.mdaq.root
dccp ${DCAPFILE} /local/scratch27/kreymer/DCCPSTASH/${FILE}
6989564 bytes in 0 seconds

I recommend trying things on minos-slf6 first.

#26 Updated by Robert Illingworth over 5 years ago

The version of sam-web that can handle to_date definitions was deployed last week. So you should be able to convert your production definitions now.

#27 Updated by Arthur Kreymer over 5 years ago

04/09 16:16

Work remaining on this issue :

1) Rename all datasets in DEV
2) Rename all datasets in PRD
3) describe-definitions in DEV
4) describe-definitions in PRD
5) drop old tables in DEV
6) drop old tables in PRD

#28 Updated by Arthur Kreymer over 5 years ago

04/09 16:21

I have renamed all dataset definitions in DEV/PRD
having bad characters.

DEV = were previouly renamed

Login details

mindata@minos27

kinit kreymer
getcert
. /grid/fermiapp/products/setups.sh
setup sam_web_client v1_6
export SAM_EXPERIMENT=minos
cd /minos/app/users/mindata/log/samweb

DEV NAME FIX

samweb --dev list-definitions > dev-defs

grep '%' dev-defs > devperc

cat devperc | while read DEF ; do
NEW=`echo ${DEF} | tr -d %`
samweb --dev modify-definition "${DEF}" --defname="${NEW}"
done 2>&1 | tee devperc-fix

Definition name 'SAM:tjyang_2007-01-01_2007-01-02_all_sntp-far_physics_cedar_2007-04-01-19-49-48.304797000-0500' contains forbidden characters
Definition name 'SAM:tjyang_2007-01-01_2007-01-02_all_sntp-far_physics_cedar_2007-04-01-19-50-58.366050000-0500' contains forbidden characters
Definition 'murgia_raw-near_physics%_alldata_2006-01-01_2006-02-01' not found
Definition 'tjyang_raw-near_physics%_alldata_2005-10-05_2005-10-07' not found

SAM: needed removal, the others not found were already modified
grep SAM devperc | while read DEF ; do
NEW=`echo ${DEF} | cut -f2 -d : | tr -d %`
samweb --dev modify-definition "${DEF}" --defname="${NEW}"
done 2>&1 | tee devSAM-fix
date
Wed Apr 9 15:41:02 CDT 2014

PRD NAME FIX

samweb list-definitions > prddefs
grep '=' prddefs > prdeq
grep '%' prddefs | grep -v SAM > prdperc

SAM

grep SAM prddefs | while read DEF ; do
NEW=`echo ${DEF} | cut -f2 -d : | tr -d %`
samweb modify-definition "${DEF}" --defname="${NEW}"
done 2>&1 | tee prdSAM-fix
date
Wed Apr 9 16:05:58 CDT 2014

PRDEQ

cat prdeq | while read DEF ; do
NEW=`echo ${DEF} | tr ' ' _ | tr = _`
samweb modify-definition "${DEF}" --defname="${NEW}"
done 2>&1 | tee prdeq-fix
date
Wed Apr 9 16:11:32 CDT 2014

PEDPERC

cat prdperc | while read DEF ; do
NEW=`echo ${DEF} | tr -d %`
samweb modify-definition "${DEF}" --defname="${NEW}"
done 2>&1 | tee prdperc-fix
date
Wed Apr 9 16:18:09 CDT 2014

#29 Updated by Arthur Kreymer over 5 years ago

I have started a first pass at describing DEV datasets.

samweb --dev list-definitions > devgood
wc -l devgood
4578 devgood

cat devgood | while read DEF ; do
samweb --dev describe-definition "${DEF}"
done 2>&1 | tee dev-describe
date

So far several errors like

Unable to convert old dimensions: Parse error at line 1, column 137: Expected "("
run_type physics% and data_tier 'sntp-near' and version like cedar_phy_bhcurv and physical_datastream_name like spill and start_time >= '2007-05-04
^
Unable to convert old dimensions: Unable to convert old dimension: file mismatch
Unable to convert old dimensions: Unable to convert old dimension: file mismatch

Unable to convert old dimensions: Date value '2007-11-18 00:00:00' does not match any known date format

Many more are converting successfully.

#30 Updated by Arthur Kreymer over 5 years ago

Conversions ran about 2 hours
16:25 to 18:34

4114 conversions succeeded,
434 could not be converted automatically
30 got Internal Server Error

Conversion errors :
272 file mismatch
134 Parse errors
26 Date format errors
2 stray issues
Unable to convert old dimensions: column ambiguously defined
Unable to convert old dimensions: date format picture ends before converting entire input string

An example of the Server Error

$ samweb --dev describe-definition user-CC0325-RunII-L250z200-ND-Data
HTTP error: 500 500 Internal Server Error
The server encountered an unexpected condition which prevented it from fulfilling the request.
URL: http://samweb.fnal.gov:8480/sam/minos/dev/api/definitions/name/user-CC0325-RunII-L250z200-ND-Data/describe?format=plain

#31 Updated by Stephen White over 5 years ago

Robert,

While most of the conversions succeeded, you can see that Art encountered errors during some dataset conversions. Please let me know how you would like to approach this. The dev tables still exist under the original names and will not be renamed/deleted for now.

#32 Updated by Robert Illingworth over 5 years ago

I've had a quick look at some of the errors:

The file mismatch is due to what appears to be a bug in the old dimension evaluator - in application versions it's not escaping the sql wildcard character '_', so version 'cedar_phy' is also picking up 'cedar.phy'. Is that what the person who created it wanted? I have no idea. In some cases - for example filenames and run_types - '_' is escaped and treated as a literal, so it's definitely inconsistent. The new version always makes it literal, but in this case this gives a different answer.

The parse errors are due to having ';' characters in the run types. They'd work if the value was quoted in the query string, but unfortunately it isn't.
The internal server error is due to hitting the python recursion limit. The query I found for that isn't something that should be encouraged, but I'm not sure what to do about it, other than trap the error to give something better than an internal error. We could raise the recursion limit and hope the python interpreter doesn't crash.

These two can't be run using the old system either:

The "column ambiguously defined" is a bug of some sort in the old dimension evaluator.
The "date format picture ends before converting entire input string" one is an invalid query: " and start_time < '2006-01-034' "

#33 Updated by Arthur Kreymer over 5 years ago

Thanks for the information on the errors !

Is there a way for me to manually correct the definitions that are defective ?

file mismatch - the _ were certainly not intended to be wild cards.
It is acceptable to have the new behaviour.
Is there a way to permit the mismatch ?

parse errors - is ther a way to escape the ; in the definitions,
or have the samweb server do this automatically as with % and the like ?

internal server error -
we may just abandon these 30 bad definitions
perhaps samweb describe-definition could tell what the definition is/was
before attempting the conversion

I will generate a list of DEV and PRD definitions using the old sam.
I will review that list, then we can decide how to proceed.
We may be able to abandon all these problem cases for present.

#34 Updated by Arthur Kreymer over 5 years ago

I have captured the DEV and PRD dataset definitions in
/minos/app/users/mindata/log/samweb/olddevdefs
/minos/app/users/mindata/log/samweb/oldprddefs

They look reasonable to me.

#35 Updated by Robert Illingworth over 5 years ago

An example of one of the internal errors is:

(((((RUN_TYPE  physics% and DATA_TIER  sntp-near) and VERSION like 'cedar_phy_bhcurv') and PHYSICAL_DATASTREAM_NAME like 'spill') and START_TIME >= '2006-06-11 17:43:32') and (((((((((((((((((((((((((((((((((((((END_TIME <= '2006-08-13 18:30:00' minus RUN_NUMBER  7629) minus RUN_NUMBER  7683) minus RUN_NUMBER  7697) minus RUN_NUMBER  7716) minus RUN_NUMBER  7937) minus RUN_NUMBER  7942) minus RUN_NUMBER  7981) minus RUN_NUMBER  7982) minus RUN_NUMBER  7988) minus RUN_NUMBER  8165) minus RUN_NUMBER  8168) minus RUN_NUMBER  8169) minus RUN_NUMBER  8214) minus RUN_NUMBER  8308) minus RUN_NUMBER  8385) minus RUN_NUMBER  8423) minus RUN_NUMBER  8426) minus RUN_NUMBER  8645) minus RUN_NUMBER  9809) minus RUN_NUMBER  9831) minus RUN_NUMBER  9867) minus RUN_NUMBER  10271) minus RUN_NUMBER  10896) minus RUN_NUMBER  11231) minus RUN_NUMBER  11318) minus RUN_NUMBER  11386) minus RUN_NUMBER  11598) minus RUN_NUMBER  11778) minus RUN_NUMBER  11782) minus RUN_NUMBER  11785) minus RUN_NUMBER  11881) minus RUN_NUMBER  11893) minus RUN_NUMBER  11977) minus RUN_NUMBER  12200) minus RUN_NUMBER  12540) minus RUN_NUMBER  12543) minus RUN_NUMBER  12587))

I was able to get it to parse by bumping up the python max recursion limit to 2500. Not that I'd advise writing definitions like this.

#36 Updated by Robert Illingworth over 5 years ago

Investigating the wildcards in the old system a bit more:

If you use implicit wildcards, like version cedar_phy%, this is translated to SQL version like 'cedar\_phy%' escape('\'), so _ is a literal. If you use the like operator as version like cedar_phy% this is used as-is in the sql version like 'cedar_phy%', so _ is a single character wildcard.

samweb treats both the same - _ is escaped, and ? is used as the single character wildcard by converting it to a bare _. So I can make the converter replace _ with ? for clauses using like only and they ought to give the same results.

Until now I was not aware of the difference between implicit and like in the old system - essentially the way samweb works now seemed so obvious that I didn't even think about it.

#37 Updated by Arthur Kreymer over 5 years ago

I will rename and create new versions of the defective datasets,
if they are needed. This is not not likely.

Steve - please rename the obsolete DEV tables now.

Please schedule the rename of obsolete PRD tables Wed Apr 16.

#38 Updated by Robert Illingworth over 5 years ago

Don't we want to fix as many of the remaining definitions as we can before removing the tables? There are still all those ones with ; characters.

#39 Updated by Arthur Kreymer over 5 years ago

It would certainly be nice to fix the _ and ; cases
as these are likely to continue to arise in new datasets.

I'll probably not be at the SAM meeting today,
due to Minos Collaboration Meeting plenary sessions.

#40 Updated by Stephen White over 5 years ago

The tables in the dev database have not been changed. We are leaving it alone for the moment for any changes Robert may be making. Art is planing on updating dimensions in prd with what we can currently do.

#41 Updated by Robert Illingworth over 5 years ago

I've pushed out an update to minos/dev which I think should fix the majority of the previous failures. I've successfully converted these three:

zeval-near-snts-physicstm-spill-r1_14 ( parse error due to ; in data_tier )
user-CC0325-RunII-L250z200-ND-Data ( internal error due to too many parentheses )
tjyang_2007-03-26_2007-04-01_all_sntp-far_physics_cedar_phy_2007-06-18-21-17-18.776856000-0500 ( mismatch due to inconsistent handling of _ as a wildcard )

#42 Updated by Arthur Kreymer over 5 years ago

I have updated the remaining DEV datasets.
There are only two ancent definitions not converted,
and I see no reason to convert them.

Steve,
you have my green light to drop the obsolete DEV tables.

I will run describe-dataset in production tomorrow.

Today's actions :

date | tee dev-describe-pass3
cat devgood | while read DEF ; do
echo SAMWEB DEF ${DEF}
samweb --dev describe-definition "${DEF}" 
done 2>&1 | tee -a dev-describe-pass3
date | tee -a dev-describe-pass3
Tue Apr 15 15:27:45 CDT 2014
Tue Apr 15 15:52:16 CDT 2014

   LOOK FOR Unable in the log

SAMWEB DEF je-R1_18_2-near-all-sntp-2005-11-w4
Unable to convert old dimensions:  column ambiguously defined
Original definition is:
((((RUN_TYPE  physics% and DATA_TIER  sntp-near) and PHYSICAL_DATASTREAM_NAME  cosmic) and VERSION  r1.18.2) and START_TIME >= to_date('2005-11-22','yyyy-mm-dd')and start_time <= to_date('2005-12-01','yyyy-mm-dd'))

SAMWEB DEF tjyang_2006-01-01_2006-01-034_cosmic_sntp-near_physics_cedar_2007-04-22-09-18-51.985702000-0500
Unable to convert old dimensions:  date format picture ends before converting entire input string
Original definition is:
(((((DATA_TIER  sntp-near and RUN_TYPE  physics%) and START_TIME > to_date('2006-01-01','YYYY-MM-DD')) and START_TIME < to_date('2006-01-034','YYYY-MM-DD')) and PHYSICAL_DATASTREAM_NAME like 'cosmic%') and VERSION like 'cedar%')

#43 Updated by Stephen White over 5 years ago

Seventy tables in MINOSDEV have been renamed to OLD_tablename. Their synonyms have been dropped.

Steve

#44 Updated by Arthur Kreymer over 5 years ago

Thanks for updating the DEV tables !

I have run a project in Minos DEV, all looks OK

    kreymer@minos27

common
setup sam_web_client
DS=swt-onesmall
/grid/fermiapp/minos/scripts/sam_test_web minos ${DS} dev

#45 Updated by Stephen White over 5 years ago

I believe the next step is for the changes made to SAM-Web to be put into production. Then Art can update those dimensions.

#46 Updated by Arthur Kreymer over 5 years ago

I ran the PRD dataset conversions, now that the newer server is deployed.
148 datasets failed.

mindata@minos27

kinit kreymer
getcert
. /grid/fermiapp/products/setups.sh
setup sam_web_client v1_6
export SAM_EXPERIMENT=minos
cd /minos/app/users/mindata/log/samweb

samweb server-info
SAMWeb API for minos
Server version: 1.7.6
Cherrypy version: 3.2.4
HTTP User-Agent: SAMWebClient/v1_6 (samweb) python/2.4.3
User information:
Untrusted identity:
Unauthenticated

samweb list-definitions | sort > prdgood
wc -l prdgood
4698 prdgood

date | tee prd-describe
cat prdgood | while read DEF ; do
echo SAMWEB DEF ${DEF}
samweb describe-definition "${DEF}"
done 2>&1 | tee -a prd-describe
date | tee -a prd-describe

Mon Apr 21 13:04:31 CDT 2014
SAMWEB DEF ahimmel-BForW1Cedar_phy_bhcurv
...
SAMWEB DEF ahimmel-BForW2Cedar_phy_bhcurv
...
SAMWEB DEF zeval-near-snts-physicst-spill-r1_14
Mon Apr 21 15:12:41 CDT 2014

grep Unable prd-describe | wc -l
148

#47 Updated by Robert Illingworth over 5 years ago

Unfortunately the fixes to make other definitions work broke a couple of other things - I'm now incorrectly putting quotes around comma separated lists, which makes them not work, plus there are things of the form end_time >=to_date('2005-12-16','yyyy-mm-dd') in there (no space before the to_date). I think I can fix both of these.

It may be more sensible to just set up a local server with the fixes and run against that - releasing a new version just for this isn't worth it when it's a one off operation. D0 and CDF can benefit from a future release when there's some other new stuff to make it worthwhile.

#48 Updated by Arthur Kreymer over 5 years ago

Thanks !

Feel free to set up a special server for this,
or roll back the production server
long enough to pick up the missed Minos definitions.
Whichever is easier for you.

As you say, this can all be done right later for CDF/D0,
which in any case will problably need more adjustments.

#49 Updated by Robert Illingworth over 5 years ago

I've set up a instance of the very latest server running on fermicloud052 using minos prd database. To use it, set up sam_web_client@ and then do

export SAM_WEB_BASE_URL=http://fermicloud052.fnal.gov:8480/sam/minos/api

I've tried the defintions st-cen and far-all-sntp-2004-10-w4--defdesc_FarMuDrift_2004-10_week_4 to verify they now work. Which they do.

There are some existing definitions that are completely broken and so won't convert at all. Hopefully these will be the only ones left now.

#50 Updated by Arthur Kreymer over 5 years ago

As suggested by Robert,
I have run a second pass converting production datasets.

kinit kreymer
getcert
export SAM_WEB_BASE_URL=http://fermicloud052.fnal.gov:8480/sam/minos/api

date | tee prd-describe2
cat prdgood | while read DEF ; do
echo SAMWEB DEF ${DEF}
samweb describe-definition "${DEF}"
done 2>&1 | tee -a prd-describe2
date | tee -a prd-describe2

Tue Apr 22 12:21:35 CDT 2014
...
Tue Apr 22 12:34:55 CDT 2014

This converted everything but the expected
je-R1_18_2-near-all-sntp-2005-11-w4
tjyang_2006-01-01_2006-01-034_cosmic_sntp-near_physics_cedar_2007-04-22-09-18-51.985702000-0500
and one more ill defined dataset not in development,
tagg-tof-fd-study-40800-40810

The conversions are complete, thanks !

#51 Updated by Arthur Kreymer over 5 years ago

Steve -

Please rename the obsolete tables in Minos SAM production
on Tuesday Apr 29, when you are back in town.

#52 Updated by Stephen White over 5 years ago

Seventy tables in the production database minosprd have been renamed.

#53 Updated by Arthur Kreymer over 5 years ago

Steve - please rename the retired Minos production tables back into place.

We have a couple of Minos production sam queries which are failing.
Dimensions like FILE_NAME ... and PARENT_BY_NAME ...

I will investigate in development.

#54 Updated by Arthur Kreymer over 5 years ago

The errors seen today from our Roundup script are like :

Wed Apr 30 07:57:29 CDT 2014
Traceback (most recent call last):
File "/grid/fermiapp/minos/minospro/scripts//samdup", line 189, in ?
TRANS = sam.translateConstraints(dimensions=SAMDIM)
File "sam_common_pylib/SamCommand/BlessedCommandInterfacePlaceHolder.py",
line 81, in call
File "sam_common_pylib/SamCommand/CommandInterface.py", line 251, in
call
File "sam_common_pylib/SamCommand/SamCommandInterface.py", line 243, in
apiWrapper
File "sam_user_pyapi/src/samDataset.py", line 309, in implementation
File "sam_common_pylib/SamCorba/SamServerProxy.py", line 257, in
_callRemoteMethod
File "sam_common_pylib/SamCorba/SamServerProxyRetryHandler.py", line 266,
in handleCall
DbSQLException: table or view does not exist

#55 Updated by Stephen White over 5 years ago

Art the tables in minosprd are back.

#56 Updated by Stephen White over 5 years ago

Art, I found the error in the Dbserver's log file. It looks like the dimensions for these queries just need to be converted for sam_web's use.

Steve

&lt;04/30/2014 00:00:00 DbCore(servantId=223868).query[connId=3]&gt; select DIMENSIONS.DIMENSION_NAME, DIMENSIONS.DIM_TABLE, DIMENSIONS.DIM_COLUMN, DIMENSIONS.DIM_TYPE, DIMENSIONS.DIM_ALIAS, DIMENSIONS.DIM_ACTI
VE from DIMENSIONS where DIMENSIONS.DIMENSION_NAME = 'FILE_ID' order by DIMENSIONS.DIMENSION_NAME
&lt;04/30/2014 00:00:00 Sql(servantId=223868).query[connId=3]&gt; ORA-00942: table or view does not exist
&lt;04/30/2014 00:00:00 Connection(servantId=223868).rollback[connId=3]&gt;
&lt;04/30/2014 00:00:00 Connection(servantId=223868).rollback[connId=3]&gt;
&lt;04/30/2014 00:00:00 DimensionsImpl(:saddreco_v7_7_1(223868)).exceptionHandler&gt; handling exception DbSQLException(" table or view does not exist
"), with declaredList = ['InternalException', 'DimensionError', 'ServantImportError', 'ServantAlreadyClosed', 'DbSQLException']

#57 Updated by Marc Mengel over 5 years ago

Art,

These errors:
-------------------
Wed Apr 30 07:57:29 CDT 2014
Traceback (most recent call last):
File "/grid/fermiapp/minos/minospro/scripts//samdup", line 189, in ?
TRANS = sam.translateConstraints(dimensions=SAMDIM)
File "sam_common_pylib/SamCommand/BlessedCommandInterfacePlaceHolder.py",
line 81, in call
File "sam_common_pylib/SamCommand/CommandInterface.py", line 251, in
call
File "sam_common_pylib/SamCommand/SamCommandInterface.py", line 243, in
apiWrapper
File "sam_user_pyapi/src/samDataset.py", line 309, in implementation
File "sam_common_pylib/SamCorba/SamServerProxy.py", line 257, in
-------------------
mean that that script: /grid/fermiapp/minos/minospro/scripts/samdup is not using
samweb, but the old SAM interface... It is a fairly short script; we should convert
it to use samweb.

#58 Updated by Arthur Kreymer about 5 years ago

  • Status changed from Assigned to Resolved
  • % Done changed from 70 to 100

The table cleanup was done at the move from Oracle to Postgres.

Minos completed the conversion to samweb at the last minute,
so the cleanup was not done ahead of the migration.

#59 Updated by Stephen White almost 3 years ago

  • Status changed from Resolved to Closed


Also available in: Atom PDF