Difference between revisions of "G2pmysql"
From Hall A Wiki
| Line 37: | Line 37: | ||
+---------------+ | +---------------+ | ||
3 rows in set (0.00 sec) | 3 rows in set (0.00 sec) | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
Table Descriptions: | Table Descriptions: | ||
| Line 214: | Line 186: | ||
+--------------------+------------+------+-----+-------------------+-------+ | +--------------------+------------+------+-----+-------------------+-------+ | ||
31 rows in set (0.00 sec) | 31 rows in set (0.00 sec) | ||
| + | |||
| + | |||
| + | 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 | ||
Revision as of 14:15, 29 May 2012
This is just a page to help me collect useful mysql commands for now. It'll be more user friendly in the future. -Ryan
Things to check for:
- One 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.
- Two 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.
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)
Now that you've connected you should see the mysql prompt. Type the following to see the list of databases. mysql> show Databases; +--------------------+ | Database | +--------------------+ | information_schema | | g2p | | test | +--------------------+ 3 rows in set (0.00 sec)
The only Database we are using (for now at least) is g2p. To select it type mysql> use g2p; Now you've selected the g2p database
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)
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
