Difference between revisions of "Mysql database"

Latest revision as of 12:21, 15 October 2024

soliddb.jlab.org

GEMC read geometry/material/sensitivity,magnetic field map info and output banks info in a mysql database.

For solid, the database server is "soliddb.jlab.org", which is running mysql server 5.x and runs on a mysql cluster within virtual machine nodes and is managed by Jlab computer center.

You need to access it when you running the simulation or update detector info into the database.

There's two user accounts.

read only account for running  simulation
read/write account for updating database

ssh tunnel to access the server

make sure you don't have mysql server running at your local machine as it will take up the port 3306 and make the tunneling fail,try to check by 'ps -ef |grep mysqld', if you see mysqld running, stop and disable it using 'system-config-services' after login as root

The soliddb server is behind Jlab firewall, if you are not on jlab internal network, you only can access it by ssh tunneling with the command line below in linux

ssh -N -L 3306:soliddb:3306 your_jlab_username@login.jlab.org

then test the connection with

mysql -h 127.0.0.1 -u $USERNAME -p$PASSWORD

(mysql treats hostname localhost as socket, not TCP/IP, so make sure to use 127.0.0.1 instead)

If you see the connection is made, then you are all set. The mysql command to host 127.0.0.1 will be redirect to soliddb.jlab.org. You can then use 127.0.0.1 in place of soliddb.jlab.org when specifying "GEMC_HOST" in the setup script "set_solidGEMC"

database name

On the server, four groups of database "geometry" and "banks" are used.

* used for the full solid simulation (any change should be notified and script checked in svn)
"solid_geometry" for detector geometry/material/sensitivity and magnetic field map info. 
"solid_banks"   for official output banks info.

* used for solid subsystem simulation (any change should be notified and script checked in svn)
"user_geometry" and "user_banks", for coil and yoke
"user_geometry_ec" and "user_banks_ec", for caloremiter
"user_geometry_cc" and "user_banks_cc", for cherenkov
"user_geometry_gem" and "user_banks_gem", for gem
"user_geometry_baffle" and "user_banks_baffle", for baffle

* used by individual user (ask Zhiwen Zhao zwzhao@jlab.org, if you want your own database)
 "user_geometry_1" and "user_banks_1" for Zhiwen Zhao
 "user_geometry_2" and "user_banks_2" for Rakitha Sanjeewa Beminiwattha
 "user_geometry_3" and "user_banks_3" for Seamus Riordan
 "user_geometry_4" and "user_banks_4" for Richard Holmes
 "user_geometry_5" and "user_banks_5" for users from Duke and Kalyan
 "user_geometry_6" and "user_banks_6" for Lorenzo Zana  
 "user_geometry_7" and "user_banks_7" for Michael Paolone  
 "user_geometry_8" and "user_banks_8" for Alexandre Camsonne
 "user_geometry_9" and "user_banks_9" for Zhihong Ye
 "user_geometry_10" and "user_banks_10" for Michael Paolone
 "user_geometry_11" and "user_banks_11" for Yi Wang
 "user_geometry_12" and "user_banks_12"
 "user_geometry_13" and "user_banks_13"
 "user_geometry_14" and "user_banks_14"
 "user_geometry_15" and "user_banks_15"

* "test" for any test

how to update the database

First edit "set_solidGEMC" according to your system, see instruction in the file.

Use the user "soliddev" with the correct password in the file.

run "source set_solidGEMC" to setup environment.

There're script for you to use for updating the database. Here is an example of updating geometry database

1. go to svn "database_io/solid/geometry/"
2. Run command "./go_table solid_BaBar_solenoid test", this will update the table "solid_BaBar_solenoid" in database "test"
3. The script deletes the table first and create a new one with the content from "solid_BaBar_solenoid.txt" which is created by "solid_BaBar_solenoid.pl". The database command is recorded in "solid_BaBar_solenoid.log"
4. Read onscreen output to see if it's done without error.

DON'T update "solid_geometry" and "solid_banks" if you are not sure the result. This will affect everyone. Use other database to test first any change.

mysql query tool

Besides the standard "mysql" command line tool, there are graphic query tools, for example

mysql-workbench, view soliddb (running mysql 5 server) http://dev.mysql.com/downloads/workbench/

note

a server problem and fix

the error message is like "Host 'jlab15.jlab.org' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'". Its cause is the default mysql has max_connect_errors as 10, if from one ip, the connect error reach 10, the ip will be blocked. It happened to the login server group (login1 with alias jlab14, and login2 with jlab15). The only way to reopen the ip is "mysqladmin flush-hosts" on the host which only CC can do it. I asked Sherman from CC to raise the limit to 1000. It should be adequate as every successful connection will reflush the counter. In principal, it can happen to any ip, no matter it's within jlab or tunnel through firewall. refer to http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html

a client problem and fix

if mysql client is not compiled with --enable-local-infile (like some debian built version), you will need to run it with the option "--local-infile=1"

for example

mysql --local-infile=1 -h $GEMC_HOST -u $GEMC_USER -p$GEMC_PASS < $command

otherwise, there could be ERROR 1148 (42000)