Servers for CORAL and COOL tests

This twiki page describes all servers used for the functional tests of CORAL and COOL against all supported backends (Oracle, MySQL, SQLite, Frontier and CoralServer). More details about the functional tests themselves, or how to run them, can be found in the PersistencyTests page.

Specifically, this page describes the accounts and servers that are used for the nightly tests executed by EP-SFT as user sftnight. The authentication.xml and dblookup.xml XML files that are used for the nightly tests are installed on AFS under /afs/cern.ch/sw/lcg/app/pool/db/. Separate accounts on the same servers are also used by the two members of the development team (andreav and cdelort in the following) for the manual validation of releases and nightly or private builds. The relevant accounts are also mentioned on this page, but more details about the setup of such private tests are given in the PersistencyReleaseValidation page, in CORALCOOL-2932 and in /afs/cern.ch/sw/lcg/app/releases/CORAL/internal/private/db/README. In some special cases (e.g., to test the move from Oracle 11g to Oracle 12c servers), special ad-hoc servers are also needed, but these will not be described here.

As of June 2016, all servers for CORAL and COOL tests are kindly maintained by external providers. The central CORAL team in CERN IT is only responsible for the appropriate configuration of accounts and other local resources, in coordination with these service providers. In particular, only the MySQL server requires admin operations to be performed by the CORAL team. This is summarised in the table below.

Technology	Server name	Maintained by	Contact
Oracle	lcg_coral_nightly lcg_cool_nightly	CERN IT-DB, since 2003	CERN IT-DB
MySQL	dbod-coolmyod	CORAL team, since Dec 2013 (RQF0286991) [On-demand-database infrastructure maintained by IT-DB]	CORAL team [CERN IT-DB]
SQLite	[local files on client nodes]	[no maintenance required]	-
Frontier	cmsfrontier1	CMS, since Jul 2015 (CORALCOOL-2794)	Dave Dykstra
CoralServer	atlas-coral-01	ATLAS, since Jun 2016 (CORALCOOL-2907)	Andy Salnikov

More details about each of these backends are given in the sections below.

1 Oracle servers (lcg_coral_nightly and lcg_cool_nightly)

The Oracle servers used for CORAL and COOL tests have been maintained by the CERN IT physics database team (CERN IT-DB) since the beginning of the CORAL and COOL projects in 2003. Two separate Oracle network service names lcg_coral_nightly and lcg_cool_nightly are normally used for CORAL and COOL tests, but they both point to the same physical database (presently the test2 database, also known as intdb11 in the CERN Oracle resource portal).

The dblookup.xml file for the nightly tests is configured to execute Oracle tests against oracle://lcg_coral_nightly and oracle://lcg_cool_nightly.

To ensure that the lcg_coral_nightly and lcg_cool_nightly network service names can be correctly resolved, the environment variable TNS_ADMIN must point to a directory containing the appropriate tnsnames.ora file. This file is maintained by CERN IT-DB in /afs/cern.ch/project/oracle/admin/tnsnames.ora and is subject to frequent changes. Copying it to another directory is error-prone, while making a symbolic link is a viable solution (which also allows using a custom sqlnet.ora file in conjunction with it).

Only three accounts in total are used for CORAL and COOL nightly tests (as user sftnight):

• for CORAL: accounts lcg_coral_nightly and testexpired (the latter has an expired password and is meant to test precisely that)
• for COOL: account lcg_cool_nightly (this account is also used as a second independent reader account for one CORAL test)

In addition, the two accounts lcg_cool and lcg_pool_nightly are used for manual tests by the two members of the development team (andreav and cdelort, respectively; see CORALCOOL-2932).

All these accounts need to have sufficient quota (especially for COOL) to create several independent test tables. Note that all tables are created from scratch at each test execution. There is no need to back up the data in these accounts. Over time, you may actually notice old tables taking up space in these accounts: as these are not needed, you may regularly do some cleanup (CORALCOOL-1008):

• drop all tables older than 3 months by executing this SQL query and then copying/pasting its results to execute them as SQL DDL commands: select 'drop '||object_type||' '||object_name||' CASCADE CONSTRAINTS;' from user_objects where object_type in ('TABLE','VIEW') and created<sysdate-90 order by created desc;
• then, check what remains in your schema by using this SQL query: select * from user_objects order by created desc;

The lcg_coral_nightly, lcg_cool_nightly, lcg_cool and lcg_pool_nightly accounts have been granted special privileges for performance reasons. In particular, they can select from system tables, to allow faster queries on data dictionary tables, as described in detail in CORALCOOL-1451 (aka task #10775). The most relevant patches to the code were committed in 2009 and 2010.

Accounts on the CERN central Oracle servers can be requested and controlled via the CERN Oracle resources portal or by contacting IT-DB.

2 MySQL server on demand (dbod-coolmyod)

The MySQL server for CORAL and COOL tests, previously hosted on the Quattor-managed 'persistency' cluster maintained by the Persistency team, was moved in December 2013 to the IT-DB database-on-demand service (dbod-coolmyod, see RQF0286991).

The dblookup.xml file for the nightly tests is configured to execute MySQL tests against mysql://dbod-coolmyod.cern.ch:5500.

Accounts on the CERN IT-DB MySQL database-on-demand service can be requested and controlled via the CERN MySQL resources portal or by contacting IT-DB.

The following steps were taken to install and configure the MySQL server from scratch, on the service provided by IT-DB.

2.1 Configure the server to use the mysql ANSI mode

COOL tests fail (since many years) if the ANSI mode is not used. You must modify the server configuration to use ANSI mode.

On dbod-coolmyod, use the https://cern.ch/DBOnDemand web interface to download file my.cnf and add the following line at the bottom, then upload the modified my.cnf file:

  sql-mode = ansi

Then shutdown and start up again the database using the Web interface. Do not try to modify also default-character-set and character-set-server (this was attempted but the database would not start up again!). ANSI mode is enabled in your database if the following query returns the following output (as discussed in the MySQL manual).

  mysql> SELECT @@global.sql_mode;
  +-------------------------------------------------------------+
  | @@global.sql_mode                                           |
  +-------------------------------------------------------------+
  | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
  +-------------------------------------------------------------+

2.2 Create users and databases

Connect to the dbod-coolmyod database as admin:

  mysql -uadmin -hdbod-coolmyod -P5500 -pxxx

Create all databases

  create database LCG_COOL_NIGHT;
  create database LCG_CORAL_NIGHT;
  create database LCG_COOL_AV;
  create database LCG_COOL_CD;
  create database LCG_COOL_AS;

  create database LCG_COOL;
  create database LCG_POOL_NIGHT;
  create database AVALASSI;
  create database CDELORT;

Create all users

  GRANT ALL ON LCG_COOL_NIGHT.* TO 'LCG_COOL_NIGHT'@'%' identified by 'xxx';
  GRANT ALL ON LCG_CORAL_NIGHT.* TO 'LCG_CORAL_NIGHT'@'%' identified by 'xxx';
  GRANT ALL ON LCG_COOL_AV.* TO 'LCG_COOL_AV'@'%' identified by 'xxx';
  GRANT ALL ON LCG_COOL_CD.* TO 'LCG_COOL_CD'@'%' identified by 'xxx';
  GRANT ALL ON LCG_COOL_AS.* TO 'LCG_COOL_AS'@'%' identified by 'xxx';

  GRANT ALL ON LCG_COOL.* TO 'LCG_COOL'@'%' identified by 'xxx';
  GRANT ALL ON LCG_POOL_NIGHT.* TO 'LCG_POOL_NIGHT'@'%' identified by 'xxx';
  GRANT ALL ON AVALASSI.* TO 'AVALASSI'@'%' identified by 'xxx';
  GRANT ALL ON CDELORT.* TO 'CDELORT'@'%' identified by 'xxx';

Flush privileges

  flush privileges;

3 SQLite files

SQLite files for CORAL/COOL tests are created locally on client nodes and require no maintenance.

The dblookup.xml file for the nightly tests contains no configuration for SQlite files. Hardcoded local file paths are included in the test script/code infrastructure.

4 Frontier server (cmsfrontier1)

The Frontier server for CORAL/COOL nightly tests is cmsfrontier1. This is maintained by CMS since July 2015 (CORALCOOL-2794), thanks to Dave Dykstra.

The dblookup.xml file for the nightly tests is configured to execute Frontier tests against frontier://cmsfrontier1:8080/lcg_cool_nightly, the servlet serving the lcg_cool_nightly (aka lcg_coral_nightly) Oracle server in the cmsfrontier1 Frontier server. A slightly more complex syntax, frontier://(serverurl=http://cmsfrontier1:8080/lcg_cool_nightly)(clientcachemaxresultsize=0), had been used in the past for COOL, to ensure that test data is always retrieved from the Frontier server, without any caching; it was recently clarified, however, that this is no longer necessary (see CORALCOOL-2920).

The dblookup.xml file for the nightly tests is also configured to execute Frontier tests against frontier://cmsfrontier1:8000/lcg_cool_nightly, the Squid cache on port 8000 associated to the Frontier server on port 8080. This is only used for specific COOL tests, which take care of invalidating cached values when necessary.

5 CoralServer (atlas-coral-01)

The CoralServer instance for CORAL/COOL nightly tests is atlas-coral-01. This is maintained by ATLAS since June 2016 (CORALCOOL-2907), thanks to Andy Salnikov.

The dblookup.xml file for the nightly tests is configured to execute CoralServer tests against coral://atlas-coral-01:40007&, followed by the relevant Oracle or MySQL connection string describing the connection that the CoralServer itself must establish. Note that this must be an explicit connection string: CORAL aliases could be in principle resolved on the CoralServer, but this is discouraged and is no longer being tested or supported.

5.1 Configure XML files and TNS_ADMIN for nightly tests executed against this CoralServer instance

The XML files that are used for the nightly tests are not only needed on nightly build nodes to execute tests against the CoralServer on atlas-coral-01. They are also needed on the CoralServer host itself, atlas-coral-01, to let it authenticate on the correct Oracle and MySQL servers when required (note that this is only true for authentication.xml; there is no need to resolve dblookup aliases inside the CoralServer, so the dblookup.xml is not needed, unless one also wants to execute local tests on the same node). Copy them from AFS by executing:

  \cp /afs/cern.ch/sw/lcg/app/pool/db/authentication.xml <CORAL_AUTHENTICATION_PATH>
  \cp /afs/cern.ch/sw/lcg/app/pool/db/dblookup.xml <CORAL_DBLOOKUP_PATH>

Note, in particular, that the authentication.xml file on atlas-coral-01 must explicitly contain credentials to read from all four schemas lcg_coral_nightly, lcg_cool_nightly, lcg_cool and lcg_pool_nightly that are used for nightly tests or private tests by the development team. The CoralServer only needs read access and all of these accounts can read from any schema, so all four schemas can be configured to be read using the same Oracle account (it is not necessary to give the passwords for all four accounts).

Similarly, TNS_ADMIN must be properly configured on atlas-coral-01 to locate the appropriate Oracle databases. The CORAL server scripts were recently modified to take TNS_ADMIN from local directories, as a workaround for some Kerberos-related issues leading to ORA-12687 errors (CORALCOOL-1262). These directories must be modified from the original versions on AFS:

  cd <TNS_ADMIN>
  ln -sf /afs/cern.ch/project/oracle/admin/tnsnames.ora tnsnames.ora 
  \cp -dpr /afs/cern.ch/sw/lcg/app/releases/CORAL/internal/oracle/admin/sqlnet.ora sqlnet.ora.OLD
  cat sqlnet.ora.OLD | sed 's/SQLNET.KERBEROS5_CONF_MIT/#SQLNET.KERBEROS5_CONF_MIT/' \
    | sed 's/SQLNET.AUTHENTICATION_SERVICES/#SQLNET.AUTHENTICATION_SERVICES/' > sqlnet.ora

-- AndreaValassi - 2016-08-06