DPM MySQL database

This page describes the structure of the MySQL database tables that DPM (v1.5.7) uses. The interesting ones are dpm_db which deals with the get and put requests (and therefore does not need to be backed up) and cns_db which holds the state of the DPM namespace (which should be backed up). The c in cns_db comes from the fact that the DPNS shares code with CASTOR. The database user and password that you need to use is contained in the file /opt/lcg/etc/DPMCONFIG (remember to set the permissions so that only the root/dpmmgr can read this file).

# mysql --user=<username> -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48 to server version: 4.1.11-standard
 
mysql> show databases;
+----------+
| Database |
+----------+
| cns_db   |
| dpm_db   |
| mysql    |
| test     |
+----------+
4 rows in set (0.00 sec)
 
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
 
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
15 rows in set (0.00 sec)
 
mysql> use dpm_db;
Database changed
mysql> show tables;
+------------------+
| Tables_in_dpm_db |
+------------------+
| dpm_copy_filereq |
| dpm_fs           |
| dpm_get_filereq  |
| dpm_pending_req  |
| dpm_pool         |
| dpm_put_filereq  |
| dpm_req          |
| dpm_space_reserv |
| dpm_unique_id    |
| schema_version   |
+------------------+
10 rows in set (0.00 sec)
 
mysql> use cns_db;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_cns_db   |
+--------------------+
| Cns_class_metadata |
| Cns_file_metadata  |
| Cns_file_replica   |
| Cns_groupinfo      |
| Cns_symlinks       |
| Cns_unique_gid     |
| Cns_unique_id      |
| Cns_unique_uid     |
| Cns_user_metadata  |
| Cns_userinfo       |
| schema_version     |
+--------------------+
11 rows in set (0.00 sec)

cns_db

Probably the most interesting tables are Cns_file_metadata and Cns_file_replica as these contain information on the location (pool and filesystem) of the file, its size, ACLs, creation time etc. i.e.

mysql> select f_type, poolname, fs, sfn from Cns_file_replica;
+--------+-----------+-----------+----------------------------------------------------------------------+
| f_type | poolname  | fs        | sfn                                                                  |
+--------+-----------+-----------+----------------------------------------------------------------------+
| P      | dpm-pool1 | /storage1 | wn4.epcc.ed.ac.uk:/storage1/dteam/2006-09-13/20060913_191110.txt.1.0 |
+--------+-----------+-----------+----------------------------------------------------------------------+

Cns_groupinfo and Cns_userinfo contain the mappings of the internal (i.e. non-Unix) DPM gids and uids to the VO groups and user DNs. i.e.

mysql> select * from Cns_groupinfo;
+-------+------+-----------+
| rowid | gid  | groupname |
+-------+------+-----------+
|     1 |  102 | atlas     |
|     2 |  103 | alice     |
|     3 |  104 | lhcb      |
|     4 |  105 | cms       |
|     5 |  106 | dteam     |
|     6 |  107 | biomed    |
+-------+------+-----------+

mysql> select * from Cns_userinfo;
+-------+--------+---------------------------------------------------------------------------------------+
| rowid | userid | username                                                                              |
+-------+--------+---------------------------------------------------------------------------------------+
|     1 |    101 | /C=UK/O=eScience/BlahBlah..........................                                   |
+-------+--------+---------------------------------------------------------------------------------------+

When you assign a VO to a specific pool, you must use either the VO name or the internal DPM gid, not the Unix gid for that group.