Create decoy tables - Revision history

Frodo: /* Build on Fastest DB Method */

2015-03-04T05:18:48Z

Build on Fastest DB Method

← Older revision		Revision as of 05:18, 4 March 2015
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;"

Frodo at 17:32, 14 May 2014

2014-05-14T17:32:45Z

← Older revision		Revision as of 17:32, 14 May 2014
Line 48:		Line 48:
	[[Category:Sysadmin]]		[[Category:Sysadmin]]
	[[Category:Developer]]		[[Category:Developer]]
			[[Category:Curator]]
			[[Category:Decoys]]

Frodo at 00:05, 12 March 2014

2014-03-12T00:05:44Z

← Older revision		Revision as of 00:05, 12 March 2014
Line 21:		Line 21:
	* 2: move the decoyprot.* files copied above into /var/lib/mysql/zinc8/		* 2: move the decoyprot.* files copied above into /var/lib/mysql/zinc8/
	* 3: Start mysqld		* 3: Start mysqld

	~~[[Category:Internal]]~~


Line 47:		Line 45:
	select count(*) from zinc8.decoyprot;		select count(*) from zinc8.decoyprot;
	select * from zinc8.decoyprot limit 10;		select * from zinc8.decoyprot limit 10;

			[[Category:Sysadmin]]
			[[Category:Developer]]

Teague Sterling at 20:33, 24 October 2012

2012-10-24T20:33:29Z

← Older revision		Revision as of 20:33, 24 October 2012
Line 1:		Line 1:
			=== Build on Fastest DB Method ===
			These commands are available on zincdb1 with corresponding readme file.

			# mysql zinc_stage -p -e "DROP TABLE IF EXISTS decoyprot_tmp;"
			# 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));"
			# 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"
			# '''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		Create Decoy Tables

Line 14:		Line 25:


	March 20, 2010 version from Pascal (unformatted)		=== March 20, 2010 version from Pascal (unformatted) ===

	Procedure is as follows:		Procedure is as follows:

Frodo at 19:17, 23 October 2012

2012-10-23T19:17:34Z

← Older revision		Revision as of 19:17, 23 October 2012
Line 1:		Line 1:
	Create Decoy Tables		Create Decoy Tables

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

	On each ~~zincdb2~~, 3:		On each zincdb1, 4, 6:
	* 1: Shutdown mysqld		* 1: Shutdown mysqld
	* 2: move the decoyprot.* files copied above into /var/lib/mysql/zinc8/		* 2: move the decoyprot.* files copied above into /var/lib/mysql/zinc8/

Therese: 2 revisions

2012-10-08T18:10:32Z

2 revisions

← Older revision

Revision as of 18:10, 8 October 2012

Frodo at 18:11, 21 March 2011

2011-03-21T18:11:26Z

← Older revision		Revision as of 18:11, 21 March 2011
Line 12:		Line 12:

	[[Category:Internal]]		[[Category:Internal]]


			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;

Frodo at 20:17, 19 October 2010

2010-10-19T20:17:51Z

New page

Create Decoy Tables

* 1: On zincdb4, 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 zincdb2, 3:
* 1: Shutdown mysqld
* 2: move the decoyprot.* files copied above into /var/lib/mysql/zinc8/
* 3: Start mysqld

[[Category:Internal]]