Difference between revisions of "G2pmysql"
From Hall A Wiki
(→Modifications made to RunInfo Tables after Run Period =) |
(→Modifications made to RunInfo Tables after Run Period) |
||
| Line 221: | Line 221: | ||
After the run period, a few minor issues were corrected with the RunInfo tables. The changes are listed below. | After the run period, a few minor issues were corrected with the RunInfo tables. The changes are listed below. | ||
| + | |||
| + | #Backed up DB - 6/4/12 | ||
| + | ##/adaqfs/home/adaq/g2p/g2pbackup.sql - This is a backup of the original DB prior to any modifications | ||
| + | ## I also have a copy of this file on my own computer | ||
| + | #Updated Target Encoder Position for both L/R HRS - 6/4/12 | ||
| + | ## /adaqfs/home/a-onl/scripts/mysql/FixTargetPosition_g2p(R).pl accesses end of run files and updates the DB | ||
| + | ## /adaqfs/home/a-onl/scripts/mysql/Target.sh loops through however many runs you want to using FixTargetPosition_g2p(R).pl | ||
| + | # Updated Target Number for both L/R HRS - 6/7/12 | ||
| + | ## Updated FixTargetPosition_g2p.pl to do this | ||
| + | ## Fixed a bug so perl script now correctly finds negative encoder values | ||
| + | # Renamed Target.sh to mysql_g2p.sh 6/12/12 | ||
| + | # Updated HWP Status using FixHWP_g2p(R).pl 6/12/12 | ||
| + | # Updated PS Status using FixPS_g2p(R).pl 6/12/12 | ||
| + | # Create a script to parse EPICS files from run period epics.pl 6/13/12 | ||
| + | ## Located on ifarm /w/halla-1/g2p/ryan/mysql | ||
| + | ## EPICS files located at w/halla-1/g2p/epics | ||
| + | ## Currently set up to loop through all the lines of EPICS file and compute the average of an EPICS variable between a start and stop time. This will eventually be tied to run times! | ||
| + | # Epics variables and descriptions can be found here https://hallaweb.jlab.org/wiki/index.php/G2p_epics | ||
Revision as of 20:48, 9 August 2012
This is just a page to help me collect useful mysql commands for now and document my work. It'll be more user friendly in the future. -Ryan
What's been done so far:
- Backed up DB - 6/4/12
- /adaqfs/home/adaq/g2p/g2pbackup.sql
- I also have a copy of this file on my own computer
- Updated Target Encoder Position for both L/R HRS - 6/4/12
- /adaqfs/home/a-onl/scripts/mysql/FixTargetPosition_g2p(R).pl accesses end of run files and updates the DB
- /adaqfs/home/a-onl/scripts/mysql/Target.sh loops through however many runs you want to using FixTargetPosition_g2p(R).pl
- Updated Target Number for both L/R HRS - 6/7/12
- Updated FixTargetPosition_g2p.pl to do this
- Fixed a bug so perl script now correctly finds negative encoder values
- Renamed Target.sh to mysql_g2p.sh 6/12/12
- Updated HWP Status using FixHWP_g2p(R).pl 6/12/12
- Updated PS Status using FixPS_g2p(R).pl 6/12/12
- Create a script to parse EPICS files from run period epics.pl 6/13/12
- Located on ifarm /w/halla-1/g2p/ryan/mysql
- EPICS files located at w/halla-1/g2p/epics
- Currently set up to loop through all the lines of EPICS file and compute the average of an EPICS variable between a start and stop time. This will eventually be tied to run times!
- Epics variables and descriptions can be found here https://hallaweb.jlab.org/wiki/index.php/G2p_epics
Things to check for/To Do List:
-
HWP Status. Because the entry is filled at the start of run, what to make sure that it updated before a new run was started. -
Target Encoder Position. Again the entry is filled from the start of run so you want to check with the end of run and make sure this updated correctly. Most problems occur around dilution runs. -
Prescale factor. Up until a certain point there was a bug in the code so that the DB scripts only recorded the first digit of the prescale factor. So a PS of 10 would only record as a PS of 1. This was eventually fixed but the runs need to be checked. - Based off of Encoder position should be able to correct Target Type and Target Number in beam. Encoder positions are in ().
- Decided to only update the Target Number because this was determined via EPICS so it is definitely not right. The Target Type could be useful because it was shift worker controlled, so if the Target Encoder was not reporting to EPICS correctly the Target Type may be correct. I stress 'may' because you have to trust the shift worker.
- Carbon - (10,000 , 15,000) 6 (at some point Carbon target became thicker but not sure when this should be checked)
- Carbon Hole - (41,000 , 45,000) 2
- CH2 - ( 37,000 , 41,000 ) 3
- NH3 (Top/Bottom determined by Target Number) - ( 28,000 , 35,000) 4 (45,000 , 51,000) 1
- Dummy - (20,000 , 25,000) 5
- Home - < 1000 7
- If 999 or 0 Target Encoder is not reporting to EPICS - Record Target Number as 0
- Important Notes on Target Variable Epics reporting: Going through the HALOG I don't see Target Encoder Variables accurately reporting until Runs 3220/22305 on 3/16/12. See http://hallaweb.jlab.org/halog/log/html/1203_archive/120316153201.html
- For analysis table, create column for Target Material Type and get info from here https://hallaweb.jlab.org/wiki/index.php/Target_Material_Summary
-
List of EPICS variables and what they actually correspond to - Should base my mysql stuff off of this: https://userweb.jlab.org/~singhj/runsummary/
List of good mysql tutorial websites.
http://www.tutorialspoint.com/mysql/index.htm http://www.tizag.com/mysqlTutorial/ http://www.dreamincode.net/forums/topic/42301-beginners-guide-to-creating-simple-mysql-tables/
Using the MYSQL DB (as hosted on adaql10):
Currently you can only connect to the DB from an adaq machine. To connect: [adaq@adaql5 ~]$ mysql -h adaql10 -u g2p -p (email me or ask me for the password! Alexandre knows it too and maybe Vince)
g2p mysql Table Descriptions
There are two main categories of tables in the g2p mysql database. The first category includes tables that were filled with data online. The fields were filled either at the start or end of run with a snapshot of the EPICS information associated with the table variable or with summary information from the end of run script. Some of this information was provided by the shift worker so one should be careful with these tables. These tables are the RunInfo Tables and there is one for each DAQ arm (Left, Right, Third).
RunInfo Tables
Inside the g2p database are the following tables: mysql> show Tables; +---------------+ | Tables_in_g2p | +---------------+ | RunInfoL | | RunInfoR | | RunInfoT | +---------------+ 3 rows in set (0.00 sec)
Table Descriptions:
mysql> describe RunInfoL; +--------------------+------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+-------------------+-------+ | RunNumber | int(11) | NO | PRI | 0 | | Start of Run | RunStartTime | datetime | YES | | NULL | | Start of Run | EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | | | RunTime | float | YES | | NULL | | End of Run | TargetPosition | float | YES | | NULL | | Start of Run | TargetPolarization | float | YES | | NULL | | Start of Run | TargetField | float | YES | | NULL | | Start of Run | LeftMomentum | float | YES | | NULL | | Start of Run | LeftAngle | float | YES | | NULL | | Start of Run | LeftSeptum | float | YES | | NULL | | Start of Run | Energy | float | YES | | NULL | | Start of Run | Current | float | YES | | NULL | | Start of Run | Events | int(11) | YES | | NULL | | End of Run | Deadtime | float | YES | | NULL | | End of Run | BCM_Current | float | YES | | NULL | | End of Run | u1_Charge | float | YES | | NULL | | End of Run | d1_Charge | float | YES | | NULL | | End of Run | HalfWavePlate | longtext | YES | | NULL | | Start of Run | RunType | longtext | YES | | NULL | | End of Run (shift worker controlled!) | TargetType | longtext | YES | | NULL | | End of Run (shift worker controlled!) | sw_comment | longtext | YES | | NULL | | End of Run (shift worker controlled!) | GoodRun | tinyint(1) | YES | | NULL | | | T3prescaled | int(11) | YES | | NULL | | End of Run | T3real | int(11) | YES | | NULL | | End of Run | T4prescaled | int(11) | YES | | NULL | | End of Run | T4real | int(11) | YES | | NULL | | End of Run | T7prescaled | int(11) | YES | | NULL | | End of Run | T7real | int(11) | YES | | NULL | | End of Run | T8prescaled | int(11) | YES | | NULL | | End of Run | T8real | int(11) | YES | | NULL | | End of Run | NMRArea | float | YES | | NULL | | Start of Run | muFreq | float | YES | | NULL | | Start of Run | T3realRate | float | YES | | NULL | | End of Run | T4realRate | float | YES | | NULL | | End of Run | T3preRate | float | YES | | NULL | | End of Run | T4preRate | float | YES | | NULL | | End of Run | ps3 | float | YES | | NULL | | End of Run | ps4 | float | YES | | NULL | | End of Run | ps7 | float | YES | | NULL | | End of Run | ps8 | float | YES | | NULL | | End of Run | FastRasterIx | float | YES | | NULL | | Start of Run | FastRasterIy | float | YES | | NULL | | Start of Run | SlowRasterIy | float | YES | | NULL | | Start of Run | SlowRasterIx | float | YES | | NULL | | Start of Run | TargetEncoder | float | YES | | NULL | | Start of Run | SeptaStatus | int(11) | YES | | NULL | | | RunStatus | int(11) | YES | | NULL | | +--------------------+------------+------+-----+-------------------+-------+ 47 rows in set (0.01 sec)
mysql> describe RunInfoR; +--------------------+------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+-------------------+-------+ | RunNumber | int(11) | NO | PRI | 0 | | | RunStartTime | datetime | YES | | NULL | | | EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | | | RunTime | float | YES | | NULL | | | TargetPosition | float | YES | | NULL | | | TargetPolarization | float | YES | | NULL | | | TargetField | float | YES | | NULL | | | RightMomentum | float | YES | | NULL | | | RightAngle | float | YES | | NULL | | | RightSeptum | float | YES | | NULL | | | Energy | float | YES | | NULL | | | Current | float | YES | | NULL | | | Events | int(11) | YES | | NULL | | | Deadtime | float | YES | | NULL | | | BCM_Current | float | YES | | NULL | | | u1_Charge | float | YES | | NULL | | | d1_Charge | float | YES | | NULL | | | HalfWavePlate | longtext | YES | | NULL | | | RunType | longtext | YES | | NULL | | | TargetType | longtext | YES | | NULL | | | sw_comment | longtext | YES | | NULL | | | GoodRun | tinyint(1) | YES | | NULL | | | T1prescaled | int(11) | YES | | NULL | | | T1real | int(11) | YES | | NULL | | | T2prescaled | int(11) | YES | | NULL | | | T2real | int(11) | YES | | NULL | | | T7prescaled | int(11) | YES | | NULL | | | T7real | int(11) | YES | | NULL | | | T8prescaled | int(11) | YES | | NULL | | | T8real | int(11) | YES | | NULL | | | NMRArea | float | YES | | NULL | | | muFreq | float | YES | | NULL | | | T1realRate | float | YES | | NULL | | | T2realRate | float | YES | | NULL | | | T1preRate | float | YES | | NULL | | | T2preRate | float | YES | | NULL | | | ps1 | float | YES | | NULL | | | ps2 | float | YES | | NULL | | | ps7 | float | YES | | NULL | | | ps8 | float | YES | | NULL | | | SlowRasterIx | float | YES | | NULL | | | SlowRasterIy | float | YES | | NULL | | | FastRasterIy | float | YES | | NULL | | | FastRasterIx | float | YES | | NULL | | | TargetEncoder | float | YES | | NULL | | | SeptaStatus | int(11) | YES | | NULL | | | RunStatus | int(11) | YES | | NULL | | +--------------------+------------+------+-----+-------------------+-------+ 47 rows in set (0.00 sec)
mysql> describe RunInfoT; +--------------------+------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+-------------------+-------+ | RunNumber | int(11) | NO | PRI | 0 | | | RunStartTime | datetime | YES | | NULL | | | EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | | | RunTime | float | YES | | NULL | | | LeftMomentum | float | YES | | NULL | | | RightMomentum | float | YES | | NULL | | | Energy | float | YES | | NULL | | | Current | float | YES | | NULL | | | Events | int(11) | YES | | NULL | | | Deadtime | float | YES | | NULL | | | BCM_Current | float | YES | | NULL | | | u1_Charge | float | YES | | NULL | | | d1_Charge | float | YES | | NULL | | | HalfWavePlate | longtext | YES | | NULL | | | RunType | longtext | YES | | NULL | | | TargetType | longtext | YES | | NULL | | | sw_comment | longtext | YES | | NULL | | | GoodRun | tinyint(1) | YES | | NULL | | | T1prescaled | int(11) | YES | | NULL | | | T1real | int(11) | YES | | NULL | | | T8prescaled | int(11) | YES | | NULL | | | T8real | int(11) | YES | | NULL | | | T1realRate | float | YES | | NULL | | | T1preRate | float | YES | | NULL | | | ps1 | int(11) | YES | | NULL | | | TargetPolarization | float | YES | | NULL | | | TargetPosition | float | YES | | NULL | | | TargetField | float | YES | | NULL | | | NMRArea | float | YES | | NULL | | | muFreq | float | YES | | NULL | | | TargetEncoder | float | YES | | NULL | | +--------------------+------------+------+-----+-------------------+-------+ 31 rows in set (0.00 sec)
Modifications made to RunInfo Tables after Run Period
After the run period, a few minor issues were corrected with the RunInfo tables. The changes are listed below.
- Backed up DB - 6/4/12
- /adaqfs/home/adaq/g2p/g2pbackup.sql - This is a backup of the original DB prior to any modifications
- I also have a copy of this file on my own computer
- Updated Target Encoder Position for both L/R HRS - 6/4/12
- /adaqfs/home/a-onl/scripts/mysql/FixTargetPosition_g2p(R).pl accesses end of run files and updates the DB
- /adaqfs/home/a-onl/scripts/mysql/Target.sh loops through however many runs you want to using FixTargetPosition_g2p(R).pl
- Updated Target Number for both L/R HRS - 6/7/12
- Updated FixTargetPosition_g2p.pl to do this
- Fixed a bug so perl script now correctly finds negative encoder values
- Renamed Target.sh to mysql_g2p.sh 6/12/12
- Updated HWP Status using FixHWP_g2p(R).pl 6/12/12
- Updated PS Status using FixPS_g2p(R).pl 6/12/12
- Create a script to parse EPICS files from run period epics.pl 6/13/12
- Located on ifarm /w/halla-1/g2p/ryan/mysql
- EPICS files located at w/halla-1/g2p/epics
- Currently set up to loop through all the lines of EPICS file and compute the average of an EPICS variable between a start and stop time. This will eventually be tied to run times!
- Epics variables and descriptions can be found here https://hallaweb.jlab.org/wiki/index.php/G2p_epics
Installing a MYSQL Server on your own machine:
http://dev.mysql.com/doc/refman/5.0/en/macosx-installation.html http://dev.mysql.com/doc/refman/5.0/en/windows-installation.html http://dev.mysql.com/doc/refman/5.0/en/linux-installation-rpm.html Note: Default ROOT password for mysql user is blank
Checking Database size:
SELECT SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) "Size in MB" FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "g2p";
+------------+
| Size in MB |
+------------+
| 3.34 |
+------------+
1 row in set (0.11 sec)
Dumping and Restoring the DB:
backup: # mysqldump -h adaql10 -u g2p -p[password] [database_name] > dumpfilename.sql restore:# mysql -h adaq10 -u g2p -p[password] [database_name] < dumpfilename.sql
