Create decoy tables: Difference between revisions

From DISI
Jump to navigation Jump to search
No edit summary
 
Line 6: Line 6:
# mysql zinc_state -p -e "INSERT INTO decoyprot_tmp (prot_id, smiles, sub_id_fk, net_charge, n_h_donors, n_h_acceptors, rb, mwt, xlogP) SELECT prot_id, smiles, p.sub_id_fk, net_charge, n_h_donors, n_h_acceptors, rb, mwt, xlogP FROM zinc8.protomer AS p RIGHT JOIN zinc8.catalog_item AS ci ON p.sub_id_fk=ci.sub_id_fk LEFT JOIN zinc8.catalog AS c ON ci.cat_id_fk=c.cat_id WHERE c.free = 1 AND c.purchasable in (1,2,4,5) ON DUPLICATE KEY UPDATE decoyprot_tmp.prot_id=decoyprot_tmp.prot_id;"
# mysql zinc_state -p -e "INSERT INTO decoyprot_tmp (prot_id, smiles, sub_id_fk, net_charge, n_h_donors, n_h_acceptors, rb, mwt, xlogP) SELECT prot_id, smiles, p.sub_id_fk, net_charge, n_h_donors, n_h_acceptors, rb, mwt, xlogP FROM zinc8.protomer AS p RIGHT JOIN zinc8.catalog_item AS ci ON p.sub_id_fk=ci.sub_id_fk LEFT JOIN zinc8.catalog AS c ON ci.cat_id_fk=c.cat_id WHERE c.free = 1 AND c.purchasable in (1,2,4,5) ON DUPLICATE KEY UPDATE decoyprot_tmp.prot_id=decoyprot_tmp.prot_id;"
# mysqldump -p<PASS> zincstage decoyprot_tmp | gzip -c | ssh zincdb6 "gzip -dc | mysql -p<PASS> zincstage"
# mysqldump -p<PASS> zincstage decoyprot_tmp | gzip -c | ssh zincdb6 "gzip -dc | mysql -p<PASS> zincstage"
new version:
# mysqldump -pxxx -h zincdb1 zincstage decoyprot_tmp | mysql -pxxxx -h zincdb6 zinc8
# '''On ZincDB6:''' mysql zinc8 -p -e "DROP TABLE IF EXISTS decoyprot;RENAME TABLE zincstage.decoyprot_tmp TO zinc8.decoyprot_tmp;"
# '''On ZincDB6:''' mysql zinc8 -p -e "DROP TABLE IF EXISTS decoyprot;RENAME TABLE zincstage.decoyprot_tmp TO zinc8.decoyprot_tmp;"



Latest revision as of 05:18, 4 March 2015

Build on Fastest DB Method

These commands are available on zincdb1 with corresponding readme file.

  1. mysql zinc_stage -p -e "DROP TABLE IF EXISTS decoyprot_tmp;"
  2. mysql zinc_state -p -e "CREATE TABLE decoyprot_tmp (smiles CHAR(255) NOT NULL, sub_id_fk INT(10) UNSIGNED NOT NULL, prot_id INT(10) UNSIGNED NOT NULL, net_charge INT(2) NOT NULL, n_h_donors INT(2) UNSIGNED NOT NULL, n_h_acceptors INT(2) UNSIGNED NOT NULL, rb INT(2) UNSIGNED NOT NULL, mwt DOUBLE NOT NULL, xlogP FLOAT(5,2), UNIQUE prot_id_idx (prot_id), INDEX decoyport_idx (net_charge, n_h_donors, n_h_acceptors, rb, mwt, xlogP));"
  3. mysql zinc_state -p -e "INSERT INTO decoyprot_tmp (prot_id, smiles, sub_id_fk, net_charge, n_h_donors, n_h_acceptors, rb, mwt, xlogP) SELECT prot_id, smiles, p.sub_id_fk, net_charge, n_h_donors, n_h_acceptors, rb, mwt, xlogP FROM zinc8.protomer AS p RIGHT JOIN zinc8.catalog_item AS ci ON p.sub_id_fk=ci.sub_id_fk LEFT JOIN zinc8.catalog AS c ON ci.cat_id_fk=c.cat_id WHERE c.free = 1 AND c.purchasable in (1,2,4,5) ON DUPLICATE KEY UPDATE decoyprot_tmp.prot_id=decoyprot_tmp.prot_id;"
  4. mysqldump -p<PASS> zincstage decoyprot_tmp | gzip -c | ssh zincdb6 "gzip -dc | mysql -p<PASS> zincstage"

new version:

  1. mysqldump -pxxx -h zincdb1 zincstage decoyprot_tmp | mysql -pxxxx -h zincdb6 zinc8
  1. On ZincDB6: mysql zinc8 -p -e "DROP TABLE IF EXISTS decoyprot;RENAME TABLE zincstage.decoyprot_tmp TO zinc8.decoyprot_tmp;"

Old Manual Migration Method (In Case of Errors):

Create Decoy Tables

  • 1: On zincdb4 (or zincdb6), in a screen session, run ~root/generate_decoyprot.sh (takes many hours)
  • 2: Shutdown zincdb4 mysqld
  • 3: Copy /var/lib/mysql/zinc8/decoyprot.* to zincdb2, zincdb3 somewhere
  • 4: Turn zincdb4 back on (This part may be possible without mysqld shutdown/startup, I'm just doing it for saftey)

On each zincdb1, 4, 6:

  • 1: Shutdown mysqld
  • 2: move the decoyprot.* files copied above into /var/lib/mysql/zinc8/
  • 3: Start mysqld


March 20, 2010 version from Pascal (unformatted)

Procedure is as follows:

Run the decoyprot generation script zincdb4:/root/scripts/generate_decoyprot.sh (wait many hours) Shutdown mysql on zincdb4 copy /var/lib/mysql/zinc8/decoyprot.* to zincdb2, zincdb3, to a temporary location. Copy takes 3-5minutes currently Restart mysql on zincdb4

On zincdb2, 3: Stop mysqld Move the decoyprot.* copies into /var/lib/mysql/zinc8/ (May take a few minutes even though its a move) Make sure the copied decoyprot.* files are owned by mysql:mysql Start mysqld

Validate that the tables work:

select count(*) from zinc8.decoyprot; select * from zinc8.decoyprot limit 10;