Solid mysql database
This page has been moved to https://solid.jlab.org/wiki/index.php/mysql_database in the new SoLID Wiki. Please do not edit this page.
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: user "soliduser" password "ilovesolid" read/write account for updating database : user "soliddev" password "ask a solid developer, eg Zhiwen firstname.lastname@example.org"
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 email@example.com
then test the connection with
mysql -h 127.0.0.1 -u soliduser -pilovesolid
(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"
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 firstname.lastname@example.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
- go to svn "database_io/solid/geometry/"
- Run command "./go_table solid_BaBar_solenoid test", this will update the table "solid_BaBar_solenoid" in database "test"
- 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"
- 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/
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"
mysql --local-infile=1 -h $GEMC_HOST -u $GEMC_USER -p$GEMC_PASS < $command
otherwise, there could be ERROR 1148 (42000)