Replacing old tables with new ones

When you are updating a track that already exists on the public site with new data or a new assembly, follow the steps below sequentially to make sure that the old data and new data are not simultaneously on the public site (RR). This scenario assumes that you have finished QAing the track and have both the old and the new data tracks on hgwbeta.

1. Find and drop old tables from hgwbeta.

2. Make strict on hgwbeta (Now only the new track should be visible on hgwbeta).

3. Push both trackDb and the new tables to mysqlrr (Now the RR should be displaying the new track even though the old tables are there because trackDb points to the new tables).

4. Drop old tables from RR after testing the new track.

Here is an example in which a chain/net track was updated from danRer5 to danRer6 on mm9:

1. Find and drop tables from hgwbeta:

• hgwbeta: hgsql mm9
• mysql> show tables like "%anRer5%";

  +-------------------------------+
  | Tables_in_mm9 (%anRer5%)      |
  +-------------------------------+
  | chr10_chainDanRer5            | 
  | chr10_chainDanRer5Link        | 
  | chr11_chainDanRer5            | 
  | chr11_chainDanRer5Link        | 
  | ....                          | 
  | chrY_chainDanRer5Link         | 
  | chrY_random_chainDanRer5      | 
  | chrY_random_chainDanRer5Link  | 
  | netDanRer5                    | 
  +-------------------------------+

71 rows in set (0.05 sec)

• mysql> exit
• Bye
  1. We want to create a file with all the tables to drop, in command form for mysql
• hgwdev: hgsql -e "show tables like '%anRer5%'" mm9 > dropList
• hgwdev: vi dropList
• vi: :%s/^/DROP TABLE mm9./ (this means: for the whole file, substitute, at the beginning of the line, this text)
• vi: :%s/$/;/ (this means: for the whole file, substitute, at the end of the line, this text)
• vi: :wq
• hgwdev: hgsql mm9
• mysql> (copy and paste contents of dropList and hit enter)

2. Make strict on hgwbeta.

• hgwbeta - trackDb: make strict DBS=mm9

3. Push both trackDb and the new tables to mysqlrr.

• email: Please push the following from mm9: chainDanRer6, chainDanRer6Link, netDanRer6 from mysqlbeta to mysqlrr

4. Drop old tables from RR after testing the new track.

• email: Please drop the following tables from the mm9 database from mysqlrr: mm9.chr10_chainDanRer5, mm9.chr10_chainDanRer5Link, mm9.chr11_chainDanRer5 ...