How to use MySQL (for developers)


# Individual database accounts for each
# developer are set according to the following convention
#     user name              database name
#     xiezhen                 zhendb
#     girone                  mariadb
#     dirkd             dirkdb
#       yourloginname           yournamedb
#     ...               ...
#
# To create your database send an e-mail to Sasha <vaniachine@anl.gov>
# with a copy to Dirk <Dirk.Duellmann@cern.ch> where you should specify
# your account name. The preferred policy is to provide access to your
# database from the lxshare03... nodes only. That is why you have to copy
# your e-mail to Dirk who will add you to the list of the lxshare0322-331 users.
#
# To access your database from these nodes you can use the mysql client
# executable installed in the afs external area. To use it you can do:
#
 
setenv MYSQL /afs/cern.ch/sw/lcg/external/mysql/4.0.4-beta/Linux__2.4
setenv LD_LIBRARY_PATH $MYSQL/lib
 
$MYSQL/bin/mysql -h lxshare070d
 
# 
# If successful you will get to the mysql prompt:
#
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60 to server version: 4.0.4-beta-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> 
 
#
# Important tips: use ';' to end your commands, use "quit;" to quit.
#
 
#
# Useful SQL commands:
#
 
#
# To see the databases you have access to
#
 
mysql> SHOW DATABASES;
 
#
# Before issuing any query you must select your database first:
#
 
mysql> USE yournamedb;
 
#
# The general convention is to use the uppercase for the standard SQL kewords.
# But you can issue commands in the lowercase too. The database name is
# case-sensitive on Unix MySQL servers (since it is physically a directory name
# on the server).
#
 
#
# To learn a lot about the mysql switches use command:
#
 
$MYSQL/bin/mysql --help
 
#
# Below is the exersize on using your database for the FileCatalog test. 
# It will require some database managment tasks done by you.
#
 
#
# Do the scram stuff first to build the FileCatalog test
#
 
setenv PATH ${PATH}:/afs/cern.ch/cms/utils
setenv CVSROOT :pserver:anonymous@pool.cvs.cern.ch:/cvs/POOL

mkdir ~/LCG
cd ~/LCG
cvs co -r POOL_0_3_0 config
scram project file:config/BootStrapFile
 
cd POOL_0_3_0/src
 
#
# use your prefered tags below
#
 
cvs co -r FileCatalog-3-0-4 -d FileCatalog pool/FileCatalog
 
#
# check out all the packages that FileCatalog depends on
#
cvs co -r Common-3-0-3 -d Common pool/Common
cvs co -r EDGCatalog-3-0-1 -d EDGCatalog pool/EDGCatalog
cvs co -r  XMLCatalog-3-0-3 -d XMLCatalog pool/XMLCatalog
cvs co -r  MySQLCatalog-3-0-2 -d MySQLCatalog pool/MySQLCatalog
 
#
# build what you got
#
scram b
 
 
#
# now build the test example
#
 
cd FileCatalog/test
scram b
 
# set the runtime environment
 
eval `scram runtime -csh`
eval `scram runtime -csh pool.env`
 
# run the FCtest
# a small bug correction for these tags
setenv LD_LIBRARY_PATH /usr/local/gcc-alt-3.2/lib:${LD_LIBRARY_PATH}
FCtest
 
#
# this will create the FileCatalog.xml
#
 
#
# now edit the pool.env to use the MySQL File Catalog - move <Ignore> tags
# to 'uncomment' the MySQL db contact string and reset the environment
#
 
eval `scram runtime -csh pool.env`
 
#
# run the FCtest again - this will use the common database stFCdb
#
 
#
# Now switch to your own database. modify the pool.env, or just set the
#
 
setenv POOL_CATALOG mysql://@lxshare070d.cern.ch:3306/yournamedb
 
#
# and try the test again:
#
 
FCtest
 
#
# You will get the errors, because the tables does not exist.
#
# Now the most interesting part: you have to manage your database.
# That is how. Use the mysql command-line utility. Because you done
# the eval `scram runtime -csh` mysql is now in your PATH.
#
# To connect to your database use command similar to
 
mysql --database=yournamedb --host=lxshare070d
 
#
# or use the shorter form
#
 
mysql -h lxshare070d yournamedb
 
#
# Issue the table creation command, check, explore, then quit.
#
 
mysql> source ../../../../POOL_0_3_0/src/MySQLCatalog/misc/tbcreate.sql;
mysql> SHOW TABLES;
mysql> SHOW COLUMNS FROM t_lfn;
mysql> SHOW COLUMNS FROM t_pfn;
mysql> quit;
 
#
# Now the FCtest should work, producing the following output
#
 
FCtest
      no prefix
      no prefix
      prefix: 
      protocol: mysql
      username: sasha
      passwd:
      host: lxshare070d.cern.ch
      hostport: 3306
      path: sashadb
      IFileCatalogFactory::IFileCatalogFactory
      MySQL connecting: lxshare070d.cern.ch
      pfntest FileID ACF2A1F7-4B11-D711-9E1A-00D0B7B86A7B
      pfntest
      lfn1
      lfn2
      lfn3
      mylfns size 3
      pfntest
      replicapfn
      mypfns size 2
      MySQLFileCatalog::commit         committing      Status=SUCCESS
      LFN table size0
      PFN table size0
      MySQL disconnecting: 
      ~MySQLFileCatalogFactory()
      IFileCatalogFactory::~IFileCatalogFactory
#
# FileCatalog tests are designed to perform the following operations:
# insert, lookup, delete. No fragments should be left in the catalogs.
# You can chek the database content using command:
#
 
mysql -h lxshare070d yournamedb
 
mysql> SELECT * FROM t_lfn;
Empty set (0.00 sec)
mysql> SELECT * FROM  t_pfn;
Empty set (0.00 sec)
 
#
# To see the results of the end-user operations on the catalogs
# you can build the application in the MySQLCatalog/application.
#

Author(s): Alexandre Vaniachine, Juerg Beringer, Maria Girone