OBSOLETE. Migration is Complete. Contents of this section is of historical interest only!

Migrating to DBMauto

Overview
Table Types
- CVS Tables
- Non-CVS Tables
Tasks
Disaster Recovery
Schedule

Overview

Until now database distribution has been via a series of MySQL scripts held in CVS. Executing the scripts results in the entire database being cleared and regenerated. This was never going to be a long term solution for a variety of reasons and for a long time (indeed FAR too long) an alternative scheme has been worked on that just propagates updates instead. This document describes the migration plan to that scheme called DBMauto.

The next section Table Types looks at the types of tables we have, how they are propagated now, and how we will migrate to DBMauto. The following section Tasks looks at the jobs that different players in this game have to undertake. The next section considers Disaster Recovery once the safety net of CVS has gone. The final section attempts to lay down a Schedule

Table Types

The first, of several, complications is that we have two types of tables: those that are stored in CVS (CAL*, PLEX*, and UGLI*)and those that are not. We will deal with the two types separately.

CVS Tables (CAL, PLEX, and UGLI*)

Currently changes are made by adding or extending a series of MySQL scripts and updating the Repository. Local site librarians play these scripts into mysql resulting in tables being erased and replaced.

The DBMauto system isn't triggered by changes in the composition of a table but by insertion dates that are part of the data. With very careful timing it would be possible to set the insertion dates in the scripts to match the date and time that they are first played into the database, but such an approach is error prone.

The trick, described in Rules on SEQNO, INSERTDATE and CREATIONDATE of placing insertion dates to the far future and then letting DBMauto fix them up will not work: each time the scripts gets played it would reset the insert dates again and trigger a fresh update. Instead the following transition strategy is planned:-

Initially updates continue to be made and entered as now, but all updates must go directly, or via CVS, into the database on minos-db1. Nick will detect changes to this database by doing a daily comparison between this database and another that is being maintained by DBMauto. For each change, he will update the insertion date on the minos-db1 database so that they propagate updates to bring DBMauto DBs back into line.
Database updaters who maintain these scripts need to plan a migration to a scheme whereby the data is entered directly into the minos-db1 database. As these updates will only get added once, setting insert dates to the far future will work.
PLEX* and UGLI* may be excused this change System are in place that track changes during detector construction which will complete this summer. So long as the frequency of updates is not excessive, it will be better to refresh dates to keep DBMauto distributing these tables. This exclusion will not cover any long term maintenance of these table

Non-CVS Tables

Non-CVS tables are written by systems, e.g. DBU, directly into a database and are not propagated at all. So long as the systems that created them use, current, or far future insertion dates, they will be propagated by DBMauto. Here we meet the next two snags:-

Some tables, e.g. DBUVACHIPPEDS, currently have corrupt entries that need to be fixed before distribution can begin. In fact currently only DBUSUBRUNSUMMARY are being propagated.
The system will only propagates updates, ignoring entries that predate the system coming on line. To handle this "primer" MySql scripts will be made available as tables come on stream and software will need to prime tables using these scripts.
An alternative strategy would be to trickle in old data over a period of a few weeks, avoiding massive updates which could break the system if processing an update takes longer that the interval between update jobs.

Tasks

Tasks are identified for 3 types of actors:-

Database Updaters - people currently responsible for putting data into the database.
Site Librarians - people maintaining the software and database at an institution.
Table Czars - people with ultimate responsibility for all aspects of a database table.

Database Updaters

CVS Tables

People who are updating CVS Tables have work to do! They will need to work on a replacement system that will write data into the database with insert dates correctly set. The simplest way to achieve this would probably to modify their systems so that they still generate MySQL scripts but set the insert date to the far futures, as described in Rules on SEQNO, INSERTDATE and CREATIONDATE They have to ensure that they adhere to the other rules described in that section. During Phases I and II of the Schedule they need to maintain updates in CVS but could also be testing out their replacement system ready for Phase III when the transition is made and no further CVS updates are made.

Note that there is a partial exemption for PLEX* and UGLI*.. See CVS Tables.

Non-CVS Tables

With luck people responsible for updating Non-CVS Tables have nothing to do. Certainly if they use the Dbi to write the data, it will correctly set the insert dates. If no then they have to set the insert dates correctly. In all cases the rules described in Rules on SEQNO, INSERTDATE and CREATIONDATE have to be followed.

Site Librarians

Currently site librarians maintain local clones of the database either by using SRT or msrt to play the updating scripts or by executing them directly with mysql.

To switch to the new system they will need to follow the Slave: Preparation instructions.

Table Czars

Table czars are responsible for every aspect of their tables including, but not limited to:-

Documentation: explaining content, form and use.
Preparation: ensuring systems are in place to generate and validate the data.
Production: ensuring that new versions of the data are produced as dictated by the detector history and that these are correctly entered into the distribution system
Use: ensuring that the necessary interface software is part of the framework code so that applications can use the data.

Czars done actually have to do anything. They can delegate everything except the final responsibility for ensuring that all of the above get done!

Disaster Recovery

Currently, for CVS Tables tables at least, we have the protection of CVS. If anyone makes a change that corrupts the database it is possible to back up in CVS and undo the damage. Once we have left CVS behind we loose that safety line. However, the updates produced by the system to provide an audit trail which together with regular backups of the minos-db1 database would allow us to recover. So the proposed scheme is as follows:-

Have regular (at least weekly backups of the database on minos-db1.
Keep updates produced by DBMauto from that database for at least 2 months

To recover from some serious error:-

Locate the last good backup and restore that on minos-db1
Identify the updates produced since that backup
Remove the offending entries from these updates and then feed the remainder back in to the database to bring it up to date.

Schedule

Phase I: Final Testing

Proposed start date:Now
Updating continues in CVS. A pilot scheme supplying CVS Tables and DBUSUBRUNSUMMARYs via DBMauto is available for people to try.

Database Updaters Review how their systems currently work and plan how they must be updated.
Site Librarians Look to see what is involved with being a Slave node and can try things out if they want to into a sand-pit database.
Table Czars Ensure that migration programs are being developed for all the tables for which they are responsible.

Phase II: Transition

Proposed start date: Monday 10 March
Distribution via DBMauto officially starts but CVS continues to be updated.

Database Updaters Implement changes to their systems so that updates get written into minos-db1 with correct insert dates although all CVS tables continue to go to the Repository.
Site Librarians Switch to the new updating system
Table Czars Confirm that all tables for which they are responsible are on track for CVS phase out.

Phase III: CVS phase out

Proposed start date:Monday 7 April
Updating of CVS updates ends, except for PLEX* and UGLI* CVS Tables Database updates come via DBMauto. Databases at CalDet and Soudan now produces their own updates that get propagated to FNAL for redistribution.

Before this final transfer we have to ensure:-

The Disaster Recovery is in place.
All database tables are now being propagate through the new system and that the propagation has been verified.

Beyond this phased transition program there are events that will occur:-

Synchronising CalDet Database

Currently the CalDet database is out of sync with CVS and has valuable data not yet in the Repository.. It has to be brought into line before it can be part of the new system. If this happens before CVS is turned off, then useful additions it contains will be added to the Repository. If not then that data will be distributed from CalDet by updating insert dates.

Bringing Other Tables on Stream

Some tables, e.g. DBUVACHIPPEDS are not currently being distributed due to problems with some of the data they contain. As they are fixed, hey will come on stream priming files will be provided rather that generate massive updates that would be slow to process.

Return to the top-level Database Distribution document
Last Modified: $Date: 2006/06/06 07:02:46 $