508718 - [AMO] Update addons.mozilla.org on August 6th

Status: VERIFIED FIXED

(Infrastructure & Operations Graveyard :: WebOps: Other, task)

Description

[Wil Clouser [:clouserw]]

Attachment: 5.0.9 SQL

[AMO] Update addons.mozilla.org on August 6th

This is a production AMO push for 5.0.8.  Steps are below, please paste the output of all commands into this bug.  There should be no downtime but the database updates may take a while so your call on the maintenance page.

1) Add two new defines to config/config.php.  They are JSHYDRA_PATH and PYTHON_PATH and can both be copied from config.php.default.  PYTHON_PATH should point to the python 2.6 binary on the servers (it was installed a couple weeks ago).  JSHYDRA_PATH can be blank for now.

2) Run the attached SQL.  This took me 9 minutes on our dev box.

3) Update addons.mozilla.org.  There are several changes that are local which need to be reverted/removed.  Please double check this, but I think these commands should be all you need, run from /site/app/:
	svn revert -R .
	rm models/blacklisted_guid.php views/admin/guids.thtml
	svn up

3b) Bonus step:  Looks like there are a bunch of .orig files sitting around in the tree.  These aren't helping anyone and are just confusing the output of svn status.  Please remove them.

4) Update services.addons.mozilla.org.  This has minor local changes also.  All that needs to be done:
	svn revert -R .
	svn up

4b) More bonus: site/app/controllers/api_controller.php.orig should be removed

5) Restart apache to pick up gettext changes

6) Clear all caches (Zeus, memcache, local cake cache) on both AMO and SAMO.

7) Add two new cron jobs.  Below are my suggestions for times, feel free to change if you like.  If you do, please make sure preview.amo matches.  The ADU job can take 10+ minutes to run, user_ratings around 2.

    # Bug 505557
    5,35 * * * * root cd /data/amo/www/addons.mozilla.org-preview/bin ; php -f maintenance.php ADU

    # Bug 508699
    16,36,56 * * * * root cd /data/amo/www/addons.mozilla.org-preview/bin ; php -f maintenance.php user_ratings

8) One more SQL statement.  This should be run after we're sure the new AMO code is running on all the boxes so we don't get any stray lines in the db:

	TRUNCATE stats_contributions;

I still need to tag the site so this bug is serving as advanced notice for now.  Please wait for a comment from me here before updating.  Thanks.

Comment 1

[Wil Clouser [:clouserw]]

Attachment: 5.0.9.sql

Adding some additional SQL since this bug is open.  I just got the r+ for a new stats table for metrics (bug 507221) and rather than filing a new bug I just rolled it into this sql.

Comment 2

[Wil Clouser [:clouserw]]

I haven't seen an announcement for this yet - can someone send one out?

Comment 3

[Jeremy Orem [:oremj]]

curl 'https://bug508718.bugzilla.mozilla.org/attachment.cgi?id=392963' | ssh tm-amo01-master01 mysql -v addons_remora

DROP TABLE IF EXISTS `test_groups`;
CREATE TABLE `test_groups` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `category` varchar(255) NOT NULL,
  `tier` tinyint(4) NOT NULL default '2',
  `critical` tinyint(1) NOT NULL default '0',
  `types` tinyint(7) NOT NULL default '0',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `test_cases`;
CREATE TABLE `test_cases` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `test_group_id` int(11) unsigned NOT NULL default '0',
  `help_link` varchar(255) default NULL,
  `function` varchar(255) NOT NULL,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `test_group_id` (`test_group_id`),
  CONSTRAINT `test_cases_ibfk_1` FOREIGN KEY (`test_group_id`) REFERENCES `test_groups` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test_results`;
CREATE TABLE `test_results` (   
  `id` int(11) unsigned NOT NULL auto_increment,
  `file_id` int(11) unsigned NOT NULL default '0',
  `test_case_id` int(11) unsigned NOT NULL default '0',
  `result` tinyint(2) NOT NULL default '0',
  `line` int(11) NOT NULL default '0',
  `filename` varchar(255) NOT NULL default '',
  `message` text default NULL,  
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `file_id` (`file_id`),
  KEY `test_case_id` (`test_case_id`),
  CONSTRAINT `test_results_ibfk_1` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`),
  CONSTRAINT `test_results_ibfk_2` FOREIGN KEY (`test_case_id`) REFERENCES `test_cases` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `test_groups`
--

INSERT INTO `test_groups` (`id`, `category`, `tier`, `critical`, `types`) VALUES
(1,'general',1,1,127),(2,'security',2,0,127),
(11,'general',2,0,1),(12,'security',3,0,1),
(21,'general',2,0,4),(22,'security',3,0,4),
(31,'general',2,0,16),(32,'security',3,0,16),
(41,'general',2,0,2),(42,'security',3,0,2),
(51,'general',2,0,8),(52,'security',3,0,8);

--
-- Dumping data for table `test_cases`
--

INSERT INTO `test_cases` (`id`, `test_group_id`, `help_link`, `function`) VALUES
(11,1,NULL,'all_general_verifyExtension'),(12,1,NULL,'all_general_verifyInstallRDF'),
(13,1,NULL,'all_general_verifyFileTypes'),
(21,2,NULL,'all_security_filterUnsafeJS'),(22,2,NULL,'all_security_filterUnsafeSettings'),
(23,2,NULL,'all_security_filterRemoteJS'),
(121,12,NULL,'extension_security_checkGeolocation'),(122,12,NULL,'extension_security_checkConduit'),
(211,21,NULL,'dictionary_general_verifyFileLayout'),(212,21,NULL,'dictionary_general_checkExtraFiles'),
(213,21,NULL,'dictionary_general_checkSeaMonkeyFiles'),
(221,22,NULL,'dictionary_security_checkInstallJS'),
(311,31,NULL,'langpack_general_verifyFileLayout'),(312,31,NULL,'langpack_general_checkExtraFiles'),
(321,32,NULL,'langpack_security_filterUnsafeHTML'),(322,32,NULL,'langpack_security_checkRemoteLoading'),
(323,32,NULL,'langpack_security_checkChromeManifest'),
(411,41,NULL,'theme_general_verifyFileLayout'),
(421,42,NULL,'theme_security_checkChromeManifest');

INSERT INTO `config` (`key`, `value`) VALUES ('validation_disabled', '0');
INSERT INTO `test_groups` (`id`, `category`, `tier`, `critical`, `types`) VALUES (3,'l10n',2,0,3);

INSERT INTO `test_cases` (`id`, `test_group_id`, `help_link`, `function`) VALUES (14,1,NULL,'all_general_checkJSPollution'),(24,2,NULL,'all_security_libraryChecksum');

ALTER TABLE addons
ADD COLUMN `average_daily_downloads` int(11) unsigned not null default '0' AFTER `totaldownloads`,
ADD COLUMN `average_daily_users` int(11) unsigned not null default '0' AFTER `average_daily_downloads`;


ALTER TABLE stats_contributions 
    ADD COLUMN uuid VARCHAR(255) default NULL;

ALTER TABLE users
  ADD COLUMN `averagerating` varchar(255) default NULL;

ALTER TABLE stats_contributions 
  ADD COLUMN `is_suggested` tinyint(1) unsigned NOT NULL default '0',
  ADD COLUMN `suggested_amount` varchar(255) default NULL;

INSERT INTO `test_cases` (`id`, `test_group_id`, `help_link`, `function`) VALUES
(511,51,NULL,'search_general_checkFormat'),
(521,52,NULL,'search_security_checkUpdateURL');

-- Special request
truncate tshirt_requests;

-- Bug 507221
DROP TABLE IF EXISTS `download_sources`;
CREATE TABLE `download_sources` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL, 
  `type` varchar(255) NOT NULL, 
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Source list for add-on downloads. Bug 507221.';

INSERT INTO `download_sources` VALUES
(1, 'category', 'full', NOW()), 
(2, 'search', 'full', NOW()),   
(3, 'collection', 'full', NOW()),
(4, 'recommended', 'full', NOW()),
(5, 'homepagebrowse', 'full', NOW()),
(6, 'homepagepromo', 'full', NOW()),
(7, 'api', 'full', NOW()),
(8, 'sharingapi', 'full', NOW()),
(9, 'addondetail', 'full', NOW()),
(10, 'external-', 'prefix', NOW());

[oremj@boris ~]$ curl 'https://bug508718.bugzilla.mozilla.org/attachment.cgi?id=392963' | ssh tm-amo01-master01 mysql -v addons_remora
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  5180  100  5180    0     0  84455      0 --:--:-- --:--:-- --:--:--     0
--------------
DROP TABLE IF EXISTS `test_groups`
--------------

--------------
CREATE TABLE `test_groups` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `category` varchar(255) NOT NULL,
  `tier` tinyint(4) NOT NULL default '2',
  `critical` tinyint(1) NOT NULL default '0',
  `types` tinyint(7) NOT NULL default '0',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--------------

--------------
DROP TABLE IF EXISTS `test_cases`
--------------

--------------
CREATE TABLE `test_cases` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `test_group_id` int(11) unsigned NOT NULL default '0',
  `help_link` varchar(255) default NULL,
  `function` varchar(255) NOT NULL,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `test_group_id` (`test_group_id`),
  CONSTRAINT `test_cases_ibfk_1` FOREIGN KEY (`test_group_id`) REFERENCES `test_groups` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--------------

--------------
DROP TABLE IF EXISTS `test_results`
--------------

--------------
CREATE TABLE `test_results` (   
  `id` int(11) unsigned NOT NULL auto_increment,
  `file_id` int(11) unsigned NOT NULL default '0',
  `test_case_id` int(11) unsigned NOT NULL default '0',
  `result` tinyint(2) NOT NULL default '0',
  `line` int(11) NOT NULL default '0',
  `filename` varchar(255) NOT NULL default '',
  `message` text default NULL,  
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `file_id` (`file_id`),
  KEY `test_case_id` (`test_case_id`),
  CONSTRAINT `test_results_ibfk_1` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`),
  CONSTRAINT `test_results_ibfk_2` FOREIGN KEY (`test_case_id`) REFERENCES `test_cases` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--------------

--------------
INSERT INTO `test_groups` (`id`, `category`, `tier`, `critical`, `types`) VALUES
(1,'general',1,1,127),(2,'security',2,0,127),
(11,'general',2,0,1),(12,'security',3,0,1),
(21,'general',2,0,4),(22,'security',3,0,4),
(31,'general',2,0,16),(32,'security',3,0,16),
(41,'general',2,0,2),(42,'security',3,0,2),
(51,'general',2,0,8),(52,'security',3,0,8)
--------------

--------------
INSERT INTO `test_cases` (`id`, `test_group_id`, `help_link`, `function`) VALUES
(11,1,NULL,'all_general_verifyExtension'),(12,1,NULL,'all_general_verifyInstallRDF'),
(13,1,NULL,'all_general_verifyFileTypes'),
(21,2,NULL,'all_security_filterUnsafeJS'),(22,2,NULL,'all_security_filterUnsafeSettings'),
(23,2,NULL,'all_security_filterRemoteJS'),
(121,12,NULL,'extension_security_checkGeolocation'),(122,12,NULL,'extension_security_checkConduit'),
(211,21,NULL,'dictionary_general_verifyFileLayout'),(212,21,NULL,'dictionary_general_checkExtraFiles'),
(213,21,NULL,'dictionary_general_checkSeaMonkeyFiles'),
(221,22,NULL,'dictionary_security_checkInstallJS'),
(311,31,NULL,'langpack_general_verifyFileLayout'),(312,31,NULL,'langpack_general_checkExtraFiles'),
(321,32,NULL,'langpack_security_filterUnsafeHTML'),(322,32,NULL,'langpack_security_checkRemoteLoading'),
(323,32,NULL,'langpack_security_checkChromeManifest'),
(411,41,NULL,'theme_general_verifyFileLayout'),
(421,42,NULL,'theme_security_checkChromeManifest')
--------------

--------------
INSERT INTO `config` (`key`, `value`) VALUES ('validation_disabled', '0')
--------------

--------------
INSERT INTO `test_groups` (`id`, `category`, `tier`, `critical`, `types`) VALUES (3,'l10n',2,0,3)
--------------


--------------
INSERT INTO `test_cases` (`id`, `test_group_id`, `help_link`, `function`) VALUES (14,1,NULL,'all_general_checkJSPollution'),(24,2,NULL,'all_security_libraryChecksum')
--------------

Comment 4

[Jeremy Orem [:oremj]]

ALTER TABLE addons
ADD COLUMN `average_daily_downloads` int(11) unsigned not null default '0' AFTER `totaldownloads`,
ADD COLUMN `average_daily_users` int(11) unsigned not null default '0' AFTE



^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[A^[[BR `average_daily_downloads`
--------------

--------------
ALTER TABLE stats_contributions
    ADD COLUMN uuid VARCHAR(255) default NULL
--------------

--------------
ALTER TABLE users
  ADD COLUMN `averagerating` varchar(255) default NULL
--------------

--------------
ALTER TABLE stats_contributions
  ADD COLUMN `is_suggested` tinyint(1) unsigned NOT NULL default '0',
  ADD COLUMN `suggested_amount` varchar(255) default NULL
--------------

--------------
INSERT INTO `test_cases` (`id`, `test_group_id`, `help_link`, `function`) VALUES
(511,51,NULL,'search_general_checkFormat'),
(521,52,NULL,'search_security_checkUpdateURL')
--------------

--------------
truncate tshirt_requests
--------------

--------------
DROP TABLE IF EXISTS `download_sources`
--------------

--------------
CREATE TABLE `download_sources` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Source list for add-on downloads. Bug 507221.'
--------------

--------------
INSERT INTO `download_sources` VALUES
(1, 'category', 'full', NOW()),
(2, 'search', 'full', NOW()),
(3, 'collection', 'full', NOW()),
(4, 'recommended', 'full', NOW()),
(5, 'homepagebrowse', 'full', NOW()),
(6, 'homepagepromo', 'full', NOW()),
(7, 'api', 'full', NOW()),
(8, 'sharingapi', 'full', NOW()),
(9, 'addondetail', 'full', NOW()),
(10, 'external-', 'prefix', NOW())
--------------

Comment 5

[Jeremy Orem [:oremj]]

I failed to capture the svn up, but:

svn status
?       497743
?       502472
X       site
?       507283
X       bin
?       504863
?       .htpasswd
?       502105

Performing status on external item at 'site'
?       site/app/webroot/googlef73fef82643bf21e.html
?       site/app/webroot/data
?       site/vendors/product-details

Performing status on external item at 'bin'

Performing status on external item at 'site/vendors/product-details'

svn info
Path: .
URL: http://svn.mozilla.org/addons/tags/prodexternal
Repository Root: http://svn.mozilla.org
Repository UUID: 4eb1ac78-321c-0410-a911-ec516a8615a5
Revision: 48745
Node Kind: directory
Schedule: normal
Last Changed Author: [e-mail]
Last Changed Rev: 48745
Last Changed Date: 2009-08-06 19:19:52 -0700 (Thu, 06 Aug 2009)

Comment 6

[Jeremy Orem [:oremj]]

mysql>     TRUNCATE stats_contributions;
Query OK, 67919 rows affected (0.07 sec)

Comment 7

[Wil Clouser [:clouserw]]

Thanks Jeremy!

Comment 8

[Stephen Donner [:stephend] Not actively reading bugmail]

Verified FIXED; tested a sampling of bugs, such as bug 450726, bug 484272, bug 506183, etc.