Create decoy tables

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"
5. 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;