Note: There are a few cases of duplicates in user autocompletion which are being worked on.

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

VERIFIED FIXED

Status

Infrastructure & Operations
WebOps: Other
VERIFIED FIXED
8 years ago
4 years ago

People

(Reporter: clouserw, Assigned: oremj)

Tracking

other
All
Other
Bug Flags:
needs-downtime +

Details

(Whiteboard: 08/27/2009, URL)

Attachments

(2 attachments)

(Reporter)

Description

8 years ago
This is a production AMO push for 5.0.9.  Steps are below, please paste the output of all commands into this bug.  There should be no downtime but your call on the maintenance page.  Steps to follow:

1) Run the attached 5.0.9 SQL (I'll attach it soon) - it requires SUPER privileges!  This took me less than a minute on our dev box.

2) Update addons.mozilla.org.  There are a couple local changes which need to be reverted.  Double check this but I think you should only need to run from /site/app/:
    svn revert -R .
    svn up

3) Update services.addons.mozilla.org with `svn up`

4) Restart apache to pick up gettext changes

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

6) This is a script that needs to be run once.  It shouldn't take too long:
	cd /bin/run_once/
	php -f collection_stats_history.php

7) This script also needs to be run once.  When I tested this it took 58 minutes.  The CPU was at 100% the whole time but everything still seemed responsive and worked fine and justdave didn't think it would hurt to run in the middle of the day.  You can run this in parallel with the rest of the steps:
	cd /bin/run_once/
	php -f site_stats_history.php

8) There is a maintenance.php cron job named "tag_totals".  Please rename it to "category_totals".

9) Add three 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. 

*/15 * * * * root cd /data/amo/www/addons.mozilla.org-preview/bin; php -f maintenance.php share_count_totals

*/8 * * * * root cd /data/amo/www/addons.mozilla.org-preview/bin; php -f maintenance.php collection_stats

11,41 * * * * root cd /data/amo/www/addons.mozilla.org-preview/bin; python26 build-recommendations.py


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.
(Reporter)

Comment 1

8 years ago
Created attachment 397019 [details] [diff] [review]
5.0.9.sql

Here is the 5.0.9.sql to run.  We've actually merged one of the scripts into this file so there is no need to run step 6 any longer.  Thanks.
Created attachment 397026 [details] [diff] [review]
SQL file for sphinx views, and alterations.

Adding a step 10,11

10. In run_once run:
python upgrade_versions.py

11. Run the attached sphinx.sql file.
(Reporter)

Updated

8 years ago
Attachment #397026 - Attachment is patch: true
Attachment #397026 - Attachment mime type: application/octet-stream → text/plain
(Reporter)

Comment 3

8 years ago
Thanks Dave.  We actually need those run before the caches are cleared.  IT, please ignore comment #2 but run these after step 1:

1.1) Upgrade the schema:
    cd /bin/run_once/
    python26 upgrade_versions.py

1.2) Run the second attachment on this bug on the database, attachment 397026 [details] [diff] [review]

Thanks
(Reporter)

Comment 4

8 years ago
An additional cron job to add in step 9:

*/11 * * * * root cd /data/amo/www/addons.mozilla.org/bin; php -f
maintenance.php collections_ratings

I also noticed all my cron jobs still have -preview in them because I copied them from preview.amo's cron so please make sure to remove that.  Really my only goal with crons is to have the production jobs match the preview ones so when in doubt, do that.  Thanks. :)

Updated

8 years ago
Assignee: server-ops → jeremy.orem+bugs
Flags: needs-downtime+
Whiteboard: 08/27/2009
(Assignee)

Comment 5

8 years ago
CREATE TABLE `stats_share_counts_totals` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `addon_id` int(10) unsigned NOT NULL default '0',
    ->   `service` varchar(255) not null default '',
    ->   `count` int(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`id`),
    ->   KEY `addon_id` (`addon_id`),
    ->   KEY `count` (`count`),
    ->   CONSTRAINT `stats_share_counts_totals_ibfk_1` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
_id;
  WHEN -1 THEN
    UPDATE collections SET downvotes=(downvotes + 1) WHERE id=NEW.collection_id;
  END CASE;
|

DROP TRIGGER IF EXISTS collection_vote_delete|
CREATE TRIGGER collection_vote_delete
  AFTER DELETE ON collections_votes
  FOR EACH ROW
  CASE OLD.vote
  WHEN 1 THEN
    UPDATE collections SET upvotes=(upvotes - 1) WHERE id=OLD.collection_id;
  WHEN -1 THEN
    UPDATE collections SET downvotes=(downvotes - 1) WHERE id=OLD.collection_id;
  END CASE;
|
DELIMITER ;
CREATE TABLE `test_results_cache` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `date` datetime NOT NULL,
  `key` varchar(255) NOT NULL,
  `value` text default NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Creating stats_collections table
--
DROP TABLE IF EXISTS `stats_collections`;
CREATE TABLE `stats_collections` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `collection_id` int(11) unsigned NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `count` int(10) unsigned NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  UNIQUE KEY `collectionnamedate` (`collection_id`, `name`, `date`),
  KEY `collection_id` (`collection_id`),
  CONSTRAINT `stats_collections_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`),
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Initializing collection stats with approximate collection subscription history
--
REPLACE INTO `stats_collections` (`name`, `collection_id`, `date`, `count`) 
    SELECT 'new_subscribers', `collection_id`, DATE(`created`) AS `the_date`, COUNT(*)
    FROM `collection_subscriptions`
    GROUP BY `collection_id`, `the_date`;
INSERT INTO config VALUES('cron_debug_enabled',0);
ALTER TABLE collections
  ADD COLUMN `rating` float NOT NULL DEFAULT '0';
ALTER TABLE `stats_contributions`
    ADD COLUMN `transaction_id` varchar(255) default NULL,  
    ADD COLUMN `final_amount` varchar(10) default '0.00',
    ADD COLUMN `post_data` text default NULL;ALTER TABLE `stats_contributions` 
    DROP COLUMN `final_amount`;
Query OK, 0 rows affected (0.23 sec)

mysql> CREATE TABLE `addon_recommendations` (
    ->   `addon_id` int(11) unsigned NOT NULL default '0',
    ->   `other_addon_id` int(11) unsigned NOT NULL default '0',
    ->   `score` float default NULL,
    ->   KEY `addon_id` (`addon_id`),
    ->   KEY `addon_recommendations_ibfk_2` (`other_addon_id`),
    ->   CONSTRAINT `addon_recommendations_ibfk_1` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`id`),
    ->   CONSTRAINT `addon_recommendations_ibfk_2` FOREIGN KEY (`other_addon_id`) REFERENCES `addons` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.15 sec)

mysql> ALTER TABLE collections
    ->   ADD COLUMN `upvotes` int(11) unsigned NOT NULL DEFAULT '0',
    ->   ADD COLUMN `downvotes` int(11) unsigned NOT NULL DEFAULT '0';
Query OK, 28928 rows affected (3.16 sec)
Records: 28928  Duplicates: 0  Warnings: 0

mysql> 
mysql> DROP TABLE IF EXISTS `collections_votes`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `collections_votes` (
    ->   `collection_id` int(11) unsigned NOT NULL default '0',
    ->   `user_id` int(11) unsigned NOT NULL default '0',
    ->   `vote` tinyint(2) NOT NULL default '0',
    ->   `created` datetime NOT NULL default '0000-00-00 00:00:00',
    ->   PRIMARY KEY  (`collection_id`,`user_id`),
    ->   KEY `user_id` (`user_id`),
    ->   CONSTRAINT `collections_votes_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`),
    ->   CONSTRAINT `collections_votes_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> -- You need SUPER privileges for these triggers!  Check it.
mysql> 
mysql> -- Collection voting triggers
mysql> DELIMITER |
mysql> DROP TRIGGER IF EXISTS collection_vote_insert|
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER collection_vote_insert
    ->   AFTER INSERT ON collections_votes
    ->   FOR EACH ROW
    ->   CASE NEW.vote
    ->   WHEN 1 THEN
    ->     UPDATE collections SET upvotes=(upvotes + 1) WHERE id=NEW.collection_id;
    ->   WHEN -1 THEN
    ->     UPDATE collections SET downvotes=(downvotes + 1) WHERE id=NEW.collection_id;
    ->   END CASE;
    -> |
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> DROP TRIGGER IF EXISTS collection_vote_delete|
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER collection_vote_delete
    ->   AFTER DELETE ON collections_votes
    ->   FOR EACH ROW
    ->   CASE OLD.vote
    ->   WHEN 1 THEN
    ->     UPDATE collections SET upvotes=(upvotes - 1) WHERE id=OLD.collection_id;
    ->   WHEN -1 THEN
    ->     UPDATE collections SET downvotes=(downvotes - 1) WHERE id=OLD.collection_id;
    ->   END CASE;
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CREATE TABLE `test_results_cache` (
    ->   `id` int(11) unsigned NOT NULL auto_increment,
    ->   `date` datetime NOT NULL,
    ->   `key` varchar(255) NOT NULL,
    ->   `value` text default NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> --
mysql> -- Creating stats_collections table
mysql> --
mysql> DROP TABLE IF EXISTS `stats_collections`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `stats_collections` (
    ->   `id` int(11) unsigned NOT NULL auto_increment,
    ->   `collection_id` int(11) unsigned NOT NULL default '0',
    ->   `name` varchar(255) NOT NULL default '',
    ->   `count` int(10) unsigned NOT NULL default '0',
    ->   `date` date NOT NULL default '0000-00-00',
    ->   UNIQUE KEY `collectionnamedate` (`collection_id`, `name`, `date`),
    ->   KEY `collection_id` (`collection_id`),
    ->   CONSTRAINT `stats_collections_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`),
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

mysql> 
mysql> --
mysql> -- Initializing collection stats with approximate collection subscription history
mysql> --
mysql> REPLACE INTO `stats_collections` (`name`, `collection_id`, `date`, `count`) 
    ->     SELECT 'new_subscribers', `collection_id`, DATE(`created`) AS `the_date`, COUNT(*)
    ->     FROM `collection_subscriptions`
    ->     GROUP BY `collection_id`, `the_date`;
Query OK, 21654 rows affected (1.55 sec)
Records: 21654  Duplicates: 0  Warnings: 0

mysql> INSERT INTO config VALUES('cron_debug_enabled',0);
Query OK, 1 row affected (0.05 sec)

mysql> ALTER TABLE collections
    ->   ADD COLUMN `rating` float NOT NULL DEFAULT '0';
Query OK, 28928 rows affected (2.19 sec)
Records: 28928  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `stats_contributions`
    ->     ADD COLUMN `transaction_id` varchar(255) default NULL,  
    ->     ADD COLUMN `final_amount` varchar(10) default '0.00',
    ->     ADD COLUMN `post_data` text default NULL;ALTER TABLE `stats_contributions` 
Query OK, 144738 rows affected (4.83 sec)
Records: 144738  Duplicates: 0  Warnings: 0

    ->     DROP COLUMN `final_amount`;
Query OK, 144738 rows affected (4.19 sec)
Records: 144738  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE addons ADD CONSTRAINT UNIQUE(name);
Query OK, 12502 rows affected (2.49 sec)
Records: 12502  Duplicates: 0  Warnings: 0

mysql> -- We should also create a autoid field (autoincremented unique primary key)
mysql> -- for the translations table
mysql> 
mysql> ALTER TABLE translations 
    -> DROP PRIMARY KEY, 
    -> ADD COLUMN autoid INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST, 
    -> ADD CONSTRAINT UNIQUE(`id`, `locale`);
ERROR 1060 (42S21): Duplicate column name 'autoid'
mysql> 
mysql> -- Query OK, 481606 rows affected (22.80 sec)
mysql> 
mysql> 
mysql> -- This is the main view that will seed our Sphinx index.
mysql> CREATE OR REPLACE VIEW translated_addons 
    -> AS
    -> SELECT 
    ->     name.autoid AS id,
    ->     a.id AS addon_id, 
    ->     a.addontype_id AS addontype, 
    ->     a.status, 
    ->     name.locale, 
    ->     CRC32(name.locale) AS locale_ord,
    ->     a.averagerating,
    ->     a.weeklydownloads,
    ->     a.totaldownloads,
    ->     a.inactive,
    ->     name.localized_string AS name,
    ->     (SELECT localized_string FROM translations WHERE id = a.homepage AND locale = name.locale) AS homepage,
    ->     (SELECT localized_string FROM translations WHERE id = a.description AND locale = name.locale) AS description,
    ->     (SELECT localized_string FROM translations WHERE id = a.summary AND locale = name.locale) AS summary,
    ->     (SELECT localized_string FROM translations WHERE id = a.developercomments AND locale = name.locale) AS developercomments,
    ->     (SELECT max(version_int) FROM versions v, applications_versions av, appversions max WHERE v.addon_id = a.id AND av.version_id = v.id AND av.max = max.id) AS max_ver,
    ->     (SELECT min(version_int) FROM versions v, applications_versions av, appversions min WHERE v.addon_id = a.id AND av.version_id = v.id AND av.min = min.id) AS min_ver,
    ->     UNIX_TIMESTAMP(a.created) AS created,
    ->     (SELECT MAX(IFNULL(f.datestatuschanged, f.created)) FROM versions AS v INNER JOIN files AS f ON f.status = 4 AND f.version_id = v.id WHERE v.addon_id=a.id) AS modified
    -> FROM 
    ->     translations name, 
    ->     addons a
    -> WHERE a.name = name.id;
ERROR 1054 (42S22): Unknown column 'version_int' in 'field list'
mysql> 
mysql> -- This view is used to extract some version-related data
mysql> 
mysql> -- versions (we use this for joins)
mysql> 
mysql> CREATE OR REPLACE VIEW versions_summary_view AS
    -> 
    -> SELECT DISTINCT 
    ->     t.autoid AS translation_id, 
    ->     v.addon_id, 
    ->     v.id, 
    ->     av.application_id, 
    ->     v.created, 
    ->     v.modified, 
    ->     min.version_int AS min, 
    ->     max.version_int AS max, 
    ->     MAX(v.created)
    -> FROM versions v, addons a, translations t, applications_versions av, appversions max, appversions min
    -> WHERE 
    ->     a.id = v.addon_id AND a.name = t.id AND av.version_id = v.id
    ->     AND av.min = min.id AND av.max=max.id
    -> GROUP BY 
    ->     translation_id, 
    ->     v.addon_id, 
    ->     v.id, 
    ->     av.application_id, 
    ->     v.created, 
    ->     v.modified, 
    ->     min.version_int, 
    ->     max.version_int;
ERROR 1054 (42S22): Unknown column 'min.version_int' in 'field list'
mysql>
oremj,

Run the upgrade_versions.py first.  I put an alter table statement in there.
(Assignee)

Comment 7

8 years ago
 U   .

Fetching external item into 'site'
U    site/app/locale/sv_SE/LC_MESSAGES/messages.mo
U    site/app/locale/sv_SE/LC_MESSAGES/messages.po
U    site/app/locale/uk/LC_MESSAGES/messages.mo
U    site/app/locale/uk/LC_MESSAGES/messages.po
U    site/app/locale/sq/LC_MESSAGES/messages.mo
U    site/app/locale/sq/LC_MESSAGES/messages.po
U    site/app/locale/da/LC_MESSAGES/messages.mo
U    site/app/locale/da/LC_MESSAGES/messages.po
U    site/app/locale/fa/LC_MESSAGES/messages.mo
U    site/app/locale/fa/LC_MESSAGES/messages.po
U    site/app/locale/de/LC_MESSAGES/messages.mo
U    site/app/locale/de/LC_MESSAGES/messages.po
U    site/app/locale/ja/LC_MESSAGES/messages.mo
U    site/app/locale/ja/LC_MESSAGES/messages.po
U    site/app/locale/he/LC_MESSAGES/messages.mo
U    site/app/locale/he/LC_MESSAGES/messages.po
U    site/app/locale/es_ES/LC_MESSAGES/messages.mo
U    site/app/locale/es_ES/LC_MESSAGES/messages.po
U    site/app/locale/fi/LC_MESSAGES/messages.mo
U    site/app/locale/fi/LC_MESSAGES/messages.po
U    site/app/locale/extract-po.sh
U    site/app/locale/pt_BR/LC_MESSAGES/messages.mo
U    site/app/locale/pt_BR/LC_MESSAGES/messages.po
U    site/app/locale/fr/LC_MESSAGES/messages.mo
U    site/app/locale/fr/LC_MESSAGES/messages.po
U    site/app/locale/nl/LC_MESSAGES/messages.mo
U    site/app/locale/nl/LC_MESSAGES/messages.po
A    site/app/locale/pl/pages/validation.thtml
U    site/app/locale/pl/LC_MESSAGES/messages.mo
U    site/app/locale/pl/LC_MESSAGES/messages.po
U    site/app/locale/hu/pages/statistics_help.thtml
U    site/app/locale/hu/pages/faq.thtml
A    site/app/locale/hu/pages/collector_faq.thtml
A    site/app/locale/hu/pages/privacy.thtml
U    site/app/locale/hu/pages/error404.thtml
A    site/app/locale/hu/pages/collector_firstrun.thtml
A    site/app/locale/hu/pages/collector_features.thtml
A    site/app/locale/hu/pages/collector.thtml
U    site/app/locale/hu/LC_MESSAGES/messages.mo
U    site/app/locale/hu/LC_MESSAGES/messages.po
U    site/app/locale/vi/LC_MESSAGES/messages.mo
U    site/app/locale/vi/LC_MESSAGES/messages.po
U    site/app/locale/ro/LC_MESSAGES/messages.mo
U    site/app/locale/ro/LC_MESSAGES/messages.po
U    site/app/locale/ca/LC_MESSAGES/messages.mo
U    site/app/locale/ca/LC_MESSAGES/messages.po
U    site/app/locale/tr/LC_MESSAGES/messages.mo
U    site/app/locale/tr/LC_MESSAGES/messages.po
U    site/app/locale/ru/LC_MESSAGES/messages.mo
U    site/app/locale/ru/LC_MESSAGES/messages.po
U    site/app/locale/af/LC_MESSAGES/messages.mo
U    site/app/locale/af/LC_MESSAGES/messages.po
U    site/app/locale/pt_PT/LC_MESSAGES/messages.mo
U    site/app/locale/pt_PT/LC_MESSAGES/messages.po
U    site/app/locale/zh_TW/LC_MESSAGES/messages.mo
U    site/app/locale/zh_TW/LC_MESSAGES/messages.po
U    site/app/locale/id/LC_MESSAGES/messages.mo
U    site/app/locale/id/LC_MESSAGES/messages.po
U    site/app/locale/el/LC_MESSAGES/messages.mo
U    site/app/locale/el/LC_MESSAGES/messages.po
U    site/app/locale/zh_CN/LC_MESSAGES/messages.mo
U    site/app/locale/zh_CN/LC_MESSAGES/messages.po
U    site/app/locale/ar/LC_MESSAGES/messages.mo
U    site/app/locale/ar/LC_MESSAGES/messages.po
U    site/app/locale/ga_IE/LC_MESSAGES/messages.mo
U    site/app/locale/ga_IE/LC_MESSAGES/messages.po
A    site/app/locale/.gitignore
U    site/app/locale/cs/LC_MESSAGES/messages.mo
U    site/app/locale/cs/LC_MESSAGES/messages.po
U    site/app/locale/fy_NL/LC_MESSAGES/messages.mo
U    site/app/locale/fy_NL/LC_MESSAGES/messages.po
U    site/app/locale/ko/pages/nomination.thtml
U    site/app/locale/ko/pages/sandbox.thtml
U    site/app/locale/ko/pages/submission_help.thtml
U    site/app/locale/ko/LC_MESSAGES/messages.mo
U    site/app/locale/ko/LC_MESSAGES/messages.po
U    site/app/locale/en_US/pages/validation.thtml
U    site/app/locale/en_US/LC_MESSAGES/messages.mo
U    site/app/locale/en_US/LC_MESSAGES/messages.po
U    site/app/locale/eu/LC_MESSAGES/messages.mo
U    site/app/locale/eu/LC_MESSAGES/messages.po
U    site/app/locale/mn/LC_MESSAGES/messages.mo
U    site/app/locale/mn/LC_MESSAGES/messages.po
U    site/app/locale/cy/LC_MESSAGES/messages.mo
U    site/app/locale/cy/LC_MESSAGES/messages.po
A    site/app/locale/it/pages/about.thtml
U    site/app/locale/it/LC_MESSAGES/messages.mo
U    site/app/locale/it/LC_MESSAGES/messages.po
U    site/app/locale/sk/LC_MESSAGES/messages.mo
U    site/app/locale/sk/LC_MESSAGES/messages.po
A    site/app/tmp/cache/persistent/.gitignore
A    site/app/tmp/cache/models/.gitignore
A    site/app/tmp/cache/views/.gitignore
U    site/app/webroot/services/install.php
A    site/app/webroot/services/paypal.php
U    site/app/webroot/css/developers.css
A    site/app/webroot/css/validation.css
U    site/app/webroot/css/amo2009/main-mozilla.css
U    site/app/webroot/css/amo2009/ie.css
U    site/app/webroot/css/amo2009/style.min.css
U    site/app/webroot/css/amo2009/main.css
U    site/app/webroot/css/stats/stats.css
U    site/app/webroot/css/stats/dropdowns.css
A    site/app/webroot/img/amo2009/icons/thumbs.png
U    site/app/webroot/js/simile/ajax/ajax-api-amo-bundle.js
U    site/app/webroot/js/simile/timeplot/timeplot-bundle.js
U    site/app/webroot/js/simile/timeplot/scripts/geometry.js
U    site/app/webroot/js/simile/amo-bundle.js
U    site/app/webroot/js/addons.js
U    site/app/webroot/js/developers.js
U    site/app/webroot/js/jquery.addons.min.js
U    site/app/webroot/js/editors.js
U    site/app/webroot/js/amo2009/addons.js
U    site/app/webroot/js/amo2009/amo2009.min.js
U    site/app/webroot/js/amo2009/collections.js
A    site/app/webroot/js/stats/collection-stats.js
A    site/app/webroot/js/stats/plot-data-table.js
A    site/app/webroot/js/stats/site-stats.js
U    site/app/webroot/js/stats/plot-selection.js
U    site/app/webroot/js/stats/plots.js
D    site/app/tests/AdvancedSearch-KeywordSelection.html
D    site/app/tests/AdvancedSearch-platformSelection.html
D    site/app/tests/testSuite.html
D    site/app/tests/amo-selenium.html
D    site/app/tests/LoginAndRegisterLinksPaths.html
D    site/app/tests/categories link.html
D    site/app/tests/Login.html
D    site/app/tests/AdvancedSearch-LastUpdatedSelection.html
D    site/app/tests/MostPopVAll.html
D    site/app/tests/AdvancedSearch-perPageSelection .html
D    site/app/tests/NoEmail.html
D    site/app/tests/AdvancedSearch-addonTypeSelection.html
D    site/app/tests/Profile.html
D    site/app/tests/verifyreg.html
D    site/app/tests/AdvancedSearch-applicationSelection.html
D    site/app/tests/NoPass.html
D    site/app/tests/categories link-second list.html
D    site/app/tests/Logout.html
A    site/app/tests/selenium
A    site/app/tests/selenium/Verify button state for FF3.5.2-WinXp.html
A    site/app/tests/selenium/Verify_login_fails_with_no_credentials.html
A    site/app/tests/selenium/MostPopVAll.html
A    site/app/tests/selenium/AdvancedSearch-perPageSelection .html
A    site/app/tests/selenium/AdvancedSearch-KeywordSelection.html
A    site/app/tests/selenium/AdvancedSearch-platformSelection.html
A    site/app/tests/selenium/NoEmail.html
A    site/app/tests/selenium/AdvancedSearch-addonTypeSelection.html
A    site/app/tests/selenium/Verify_simple_login_to AMO_firefox.html
A    site/app/tests/selenium/testSuite.html
A    site/app/tests/selenium/amo-selenium.html
A    site/app/tests/selenium/incorrect_email.html
A    site/app/tests/selenium/Profile.html
A    site/app/tests/selenium/LoginAndRegisterLinksPaths.html
A    site/app/tests/selenium/AdvancedSearch-applicationSelection.html
A    site/app/tests/selenium/NoPass.html
A    site/app/tests/selenium/categories link.html
A    site/app/tests/selenium/Login.html
A    site/app/tests/selenium/categories link-second list.html
A    site/app/tests/selenium/Logout.html
A    site/app/tests/selenium/AdvancedSearch-LastUpdatedSelection.html
A    site/app/tests/selenium/incorrect_password.html
U    site/app/tests/models/collection.test.php
A    site/app/tests/views/files
A    site/app/tests/views/files/browse.test.php
U    site/app/tests/views/reviews/display.test.php
U    site/app/tests/views/reviews/add.test.php
U    site/app/tests/views/search/index.test.php
U    site/app/tests/views/addons/searchengines.test.php
U    site/app/tests/views/addons/plugins.test.php
U    site/app/tests/views/addons/previews.test.php
U    site/app/tests/views/addons/display.test.php
U    site/app/tests/views/addons/home.test.php
U    site/app/tests/views/addons/versions.test.php
A    site/app/tests/views/admin/tags.test.php
U    site/app/tests/views/elements/install.test.php
U    site/app/tests/test_helper_web.php
A    site/app/tests/searchapi.html
U    site/app/tests/installation.test.php
U    site/app/tests/controllers/editors_controller.test.php
A    site/app/tests/controllers/collections_controller.test.php
A    site/app/tests/controllers/components/stats.test.php
U    site/app/tests/controllers/components/opensearch.test.php
U    site/app/tests/controllers/components/developers.test.php
U    site/app/tests/controllers/components/validation.test.php
U    site/app/tests/controllers/sharing_api_controller.test.php
U    site/app/tests/controllers/tags_controller.test.php
U    site/app/tests/data/remora-test-data.sql
U    site/app/models/global_stat.php
U    site/app/models/collection.php
U    site/app/models/test_case.php
U    site/app/models/addon.php
U    site/app/models/addontype.php
U    site/app/models/test_group.php
U    site/app/models/reviews_moderation_flag.php
U    site/app/models/dbo/dbo_amo_mysql.php
U    site/app/models/user.php
U    site/app/models/license.php
A    site/app/config/sphinx.conf-dist
U    site/app/config/sql/remora.sql
A    site/app/config/sql/sphinx.sql
U    site/app/config/bootstrap.php
U    site/app/config/revisions.php
U    site/app/config/routes.php
U    site/app/config/language.php
A    site/app/config/migrations/49669-collection-stats.sql
A    site/app/config/migrations/49660-collection-ratings.sql
U    site/app/config/migrations/settings.py
A    site/app/config/migrations/49665-verification-on-upload.sql
A    site/app/config/migrations/49280-share-count-totals.sql
A    site/app/config/migrations/49661-collection-vote-triggers.sql
A    site/app/config/migrations/49080-collections-downloads.sql
A    site/app/config/migrations/49780-collection-rating-column.sql
A    site/app/config/migrations/50118-remove-final-amount.sql
A    site/app/config/migrations/49385-addon-recommendations.sql
A    site/app/config/migrations/50112-stats-notify-url.sql
A    site/app/config/migrations/49732-cron-debug.sql
U    site/app/config/config.php.default
U    site/app/config/core.php
U    site/app/app_model.php
G    site/app/controllers/admin_controller.php
U    site/app/controllers/statistics_controller.php
U    site/app/controllers/downloads_controller.php
U    site/app/controllers/components/validation.php
U    site/app/controllers/components/stats.php
U    site/app/controllers/components/audit.php
U    site/app/controllers/components/collections_listing.php
U    site/app/controllers/components/simple_acl.php
U    site/app/controllers/components/developers.php
U    site/app/controllers/components/editors.php
U    site/app/controllers/components/rdf.php
U    site/app/controllers/components/amo.php
U    site/app/controllers/components/paypal.php
U    site/app/controllers/components/opensearch.php
U    site/app/controllers/tags_controller.php
U    site/app/controllers/pages_controller.php
U    site/app/controllers/users_controller.php
U    site/app/controllers/files_controller.php
U    site/app/controllers/previews_controller.php
U    site/app/controllers/search_controller.php
U    site/app/controllers/localizers_controller.php
U    site/app/controllers/developers_controller.php
Conflict discovered in 'site/app/controllers/addons_controller.php'.
Select: (p) postpone, (df) diff-full, (e) edit,
        (mc) mine-conflict, (tc) theirs-conflict,
        (s) show all options: tc
G    site/app/controllers/addons_controller.php
U    site/app/controllers/versions_controller.php
U    site/app/controllers/editors_controller.php
U    site/app/controllers/compatibility_controller.php
U    site/app/controllers/api_controller.php
U    site/app/controllers/collections_controller.php
U    site/app/controllers/groups_controller.php
A    site/app/controllers/api15_controller.php
U    site/app/controllers/reviews_controller.php
U    site/app/controllers/sharing_api_controller.php
U    site/app/app_controller.php
A    site/app/views/api15
A    site/app/views/api15/search.thtml
U    site/app/views/sharing_api/collection_detail.thtml
U    site/app/views/sharing_api/collections.thtml
U    site/app/views/sharing_api/addon.thtml
U    site/app/views/sharing_api/service_doc.thtml
U    site/app/views/reviews/add.thtml
U    site/app/views/reviews/flag.thtml
U    site/app/views/reviews/delete.thtml
U    site/app/views/reviews/review_added.thtml
U    site/app/views/reviews/display.thtml
U    site/app/views/admin/logs.thtml
Conflict discovered in 'site/app/views/admin/users_edit.thtml'.
Select: (p) postpone, (df) diff-full, (e) edit,
        (mc) mine-conflict, (tc) theirs-conflict,
        (s) show all options: tc
G    site/app/views/admin/users_edit.thtml
U    site/app/views/admin/addons_status.thtml
U    site/app/views/admin/flagged_queue.thtml
U    site/app/views/admin/config.thtml
U    site/app/views/statistics/json.thtml
U    site/app/views/statistics/csv.thtml
A    site/app/views/statistics/collections.thtml
U    site/app/views/statistics/rss/summary.thtml
U    site/app/views/statistics/settings.thtml
U    site/app/views/statistics/addon.thtml
U    site/app/views/statistics/index.thtml
U    site/app/views/tags/top.thtml
U    site/app/views/tags/display.thtml
U    site/app/views/pages/credits.thtml
U    site/app/views/pages/policy.thtml
U    site/app/views/pages/appversions.thtml
A    site/app/views/pages/developer_agreement.thtml
U    site/app/views/pages/collector_faq.thtml
U    site/app/views/pages/collector_firstrun.thtml
U    site/app/views/pages/js_constants.js.thtml
U    site/app/views/pages/collector_features.thtml
U    site/app/views/pages/robots.txt.thtml
U    site/app/views/pages/collector.thtml
U    site/app/views/layouts/amo2009.thtml
U    site/app/views/layouts/mozilla.thtml
U    site/app/views/users/activatefirst.thtml
U    site/app/views/users/login.thtml
U    site/app/views/users/pwreset.thtml
U    site/app/views/users/info.thtml
U    site/app/views/users/register_complete.thtml
U    site/app/views/users/emailchange.thtml
U    site/app/views/users/email/pwreset_plain.thtml
U    site/app/views/users/email/confirm_plain.thtml
U    site/app/views/users/email/emailchange_plain.thtml
U    site/app/views/users/delete.thtml
U    site/app/views/users/register.thtml
U    site/app/views/users/edit.thtml
U    site/app/views/files/browse.thtml
U    site/app/views/helpers/statistics.php
U    site/app/views/helpers/addons_html.php
U    site/app/views/helpers/link.php
U    site/app/views/helpers/localization.php
U    site/app/views/previews/add.thtml
U    site/app/views/previews/edit.thtml
U    site/app/views/search/collections.thtml
U    site/app/views/search/rss/index.thtml
U    site/app/views/search/index.thtml
D    site/app/views/localizers/tags.thtml
A    site/app/views/localizers/categories.thtml
U    site/app/views/developers/addon_status.thtml
U    site/app/views/developers/versions_delete.thtml
U    site/app/views/developers/addon_edit.thtml
U    site/app/views/developers/addon_edit_authors.thtml
U    site/app/views/developers/addon_edit_categories.thtml
U    site/app/views/developers/previews.thtml
U    site/app/views/developers/addon_status_confirm.thtml
U    site/app/views/developers/addon_edit_properties.thtml
U    site/app/views/developers/versions.thtml
U    site/app/views/developers/addon_edit_tags.thtml
U    site/app/views/developers/json.thtml
U    site/app/views/developers/addon_edit_profile.thtml
U    site/app/views/developers/uploader.thtml
U    site/app/views/developers/addon_edit_contributions.thtml
U    site/app/views/developers/discuss.thtml
U    site/app/views/developers/versions_edit.thtml
U    site/app/views/developers/addon_edit_descriptions.thtml
U    site/app/views/developers/versions_validate.thtml
U    site/app/views/developers/addon_status_nominate.thtml
U    site/app/views/developers/dashboard.thtml
D    site/app/views/addons/previews.thtml
U    site/app/views/addons/home.thtml
U    site/app/views/addons/policy.thtml
U    site/app/views/addons/browse.thtml
U    site/app/views/addons/display.thtml
U    site/app/views/addons/versions.thtml
U    site/app/views/addons/developers.thtml
U    site/app/views/addons/category_landing.thtml
A    site/app/views/addons/about.thtml
U    site/app/views/addons/dictionaries.thtml
U    site/app/views/addons/searchengines.thtml
U    site/app/views/addons/rss/versions.thtml
U    site/app/views/addons/themes_landing.thtml
U    site/app/views/addons/plugins.thtml
U    site/app/views/addons/recommended.thtml
U    site/app/views/addons/browse_thumbs.thtml
U    site/app/views/versions/update_info.thtml
U    site/app/views/versions/license.thtml
U    site/app/views/editors/performance_table.thtml
U    site/app/views/editors/reviews_queue.thtml
U    site/app/views/editors/summary.thtml
U    site/app/views/editors/featured.thtml
U    site/app/views/editors/email/notify_update_plain.thtml
U    site/app/views/editors/email/inforequest_reply_plain.thtml
U    site/app/views/editors/email/notify_version_comment_plain.thtml
U    site/app/views/editors/email/inforequest_plain.thtml
U    site/app/views/editors/review.thtml
U    site/app/views/editors/markdown.thtml
U    site/app/views/editors/reviewlog.thtml
U    site/app/views/editors/logs.thtml
U    site/app/views/editors/queue.thtml
U    site/app/views/editors/performance_charts.thtml
U    site/app/views/compatibility/developers.thtml
U    site/app/views/compatibility/report.thtml
U    site/app/views/compatibility/users.thtml
U    site/app/views/compatibility/dashboard.thtml
U    site/app/views/api/api_addon.thtml
U    site/app/views/api/collections_feed.thtml
U    site/app/views/errors/error401.thtml
U    site/app/views/elements/addon_categories.thtml
U    site/app/views/elements/feature.thtml
U    site/app/views/elements/addon_list_options.thtml
U    site/app/views/elements/footer.thtml
U    site/app/views/elements/addon_author_addons.thtml
U    site/app/views/elements/addon_listitem.thtml
U    site/app/views/elements/addon_tags.thtml
U    site/app/views/elements/search.thtml
U    site/app/views/elements/pitch.thtml
U    site/app/views/elements/translationbox.thtml
U    site/app/views/elements/app_compatibility.thtml
U    site/app/views/elements/addon_version_detail.thtml
U    site/app/views/elements/header.thtml
U    site/app/views/elements/pagination.thtml
U    site/app/views/elements/recaptcha.thtml
U    site/app/views/elements/stars.thtml
U    site/app/views/elements/amo2009/pagination.thtml
U    site/app/views/elements/amo2009/homepage_addon.thtml
U    site/app/views/elements/amo2009/stars.thtml
A    site/app/views/elements/amo2009/support_addon_in_listing.thtml
U    site/app/views/elements/amo2009/collection_listing_item.thtml
U    site/app/views/elements/amo2009/separated_list_items.thtml
U    site/app/views/elements/amo2009/search.thtml
U    site/app/views/elements/amo2009/install.thtml
U    site/app/views/elements/amo2009/results_addon.thtml
U    site/app/views/elements/amo2009/users/vcard.thtml
U    site/app/views/elements/amo2009/contribution.thtml
U    site/app/views/elements/amo2009/teaser_collections.thtml
U    site/app/views/elements/amo2009/addons/developers/other_addons.thtml
U    site/app/views/elements/amo2009/addons/developers/about_addon.thtml
U    site/app/views/elements/amo2009/addons/list_options.thtml
U    site/app/views/elements/amo2009/addons/version_detail.thtml
U    site/app/views/elements/amo2009/homepage_addon_listing.thtml
U    site/app/views/elements/amo2009/reviews.thtml
U    site/app/views/elements/amo2009/new_indicator.thtml
U    site/app/views/elements/amo2009/categories.thtml
U    site/app/views/elements/amo2009/collections/collector_info_secondary.thtml
A    site/app/views/elements/amo2009/collections/recently_viewed.thtml
U    site/app/views/elements/amo2009/collections/sort_form.thtml
U    site/app/views/elements/amo2009/collections/add_form.thtml
U    site/app/views/elements/amo2009/collections/js_init.thtml
A    site/app/views/elements/amo2009/collections/barometer.thtml
U    site/app/views/elements/amo2009/collections/collector_sidebar_download.thtml
U    site/app/views/elements/addon_discussionheader.thtml
U    site/app/views/elements/app_chooser.thtml
U    site/app/views/elements/install.thtml
U    site/app/views/elements/collections_interactive_addon.thtml
U    site/app/views/elements/developers/editors_review_history_item.thtml
U    site/app/views/elements/developers/editbox.thtml
U    site/app/views/elements/developers/editorsmenu.thtml
U    site/app/views/elements/developers/rolecheck.thtml
U    site/app/views/elements/developers/testresults.thtml
U    site/app/views/elements/developers/actionbar.thtml
U    site/app/views/elements/developers/additem.thtml
U    site/app/views/elements/developers/localebox.thtml
A    site/app/views/elements/developers/testresults_header.thtml
U    site/app/views/elements/developers/sidebar.thtml
U    site/app/views/elements/developers/performanceheader.thtml
U    site/app/views/elements/developers/testresults_group.thtml
U    site/app/views/elements/developers/myaddons.thtml
U    site/app/views/elements/developers/editorsqueue.thtml
U    site/app/views/elements/developers/license_picker.thtml
U    site/app/views/elements/developers/testresults_stats.thtml
U    site/app/views/elements/developers/statsbar.thtml
U    site/app/views/elements/developers/editors_comment_add_form.thtml
U    site/app/views/elements/search_mini.thtml
U    site/app/views/elements/categories.thtml
U    site/app/views/collections/add.thtml
U    site/app/views/collections/listing.thtml
U    site/app/views/collections/interactive.thtml
U    site/app/views/collections/subscribe.thtml
U    site/app/views/collections/detail.thtml
A    site/app/views/collections/share.thtml
U    site/app/views/collections/edit.thtml
A    site/vendors/sphinx
A    site/vendors/sphinx/addonsSearch.php
U    site/cake/libs/model/datasources/dbo_source.php
Updated external to revision 50348.


Fetching external item into 'bin'
D    bin/migration.py
D    bin/fix_ratings.php
A    bin/fabfile.py
A    bin/python-requirements.txt
U    bin/update-hashes.php
A    bin/connection.php
U    bin/compatibility_report.php
U    bin/database.class.php
A    bin/comparelocales_scripts
A    bin/comparelocales_scripts/amo-l10ncheck.py
A    bin/recommend.py
U    bin/update-search-views.php
U    bin/maintenance.php
A    bin/run_once/migration.py
A    bin/run_once/fix_ratings.php
A    bin/run_once/upgrade_versions.py
A    bin/run_once/site_stats_history.php
A    bin/jshydra_scripts/install_js_test.js
A    bin/build-recommendations.py
Updated external to revision 50348.


Fetching external item into 'site/vendors/product-details'
U    site/vendors/product-details/productDetails.class.php
U    site/vendors/product-details/history/thunderbirdHistory.class.php
U    site/vendors/product-details/thunderbirdDetails.class.php
Updated external to revision 50348.

Updated to revision 50349.
(Assignee)

Comment 8

8 years ago
mysql> -- We should also create a autoid field (autoincremented unique primary key)
mysql> -- for the translations table
mysql> 
mysql> ALTER TABLE translations 
    -> DROP PRIMARY KEY, 
    -> ADD COLUMN autoid INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST, 
    -> ADD CONSTRAINT UNIQUE(`id`, `locale`);
ERROR 1060 (42S21): Duplicate column name 'autoid'
mysql> 
mysql> -- Query OK, 481606 rows affected (22.80 sec)
mysql> 
mysql> 
mysql> -- This is the main view that will seed our Sphinx index.
mysql> CREATE OR REPLACE VIEW translated_addons 
    -> AS
    -> SELECT 
    ->     name.autoid AS id,
    ->     a.id AS addon_id, 
    ->     a.addontype_id AS addontype, 
    ->     a.status, 
    ->     name.locale, 
    ->     CRC32(name.locale) AS locale_ord,
    ->     a.averagerating,
    ->     a.weeklydownloads,
    ->     a.totaldownloads,
    ->     a.inactive,
    ->     name.localized_string AS name,
    ->     (SELECT localized_string FROM translations WHERE id = a.homepage AND locale = name.locale) AS homepage,
    ->     (SELECT localized_string FROM translations WHERE id = a.description AND locale = name.locale) AS description,
    ->     (SELECT localized_string FROM translations WHERE id = a.summary AND locale = name.locale) AS summary,
    ->     (SELECT localized_string FROM translations WHERE id = a.developercomments AND locale = name.locale) AS developercomments,
    ->     (SELECT max(version_int) FROM versions v, applications_versions av, appversions max WHERE v.addon_id = a.id AND av.version_id = v.id AND av.max = max.id) AS max_ver,
    ->     (SELECT min(version_int) FROM versions v, applications_versions av, appversions min WHERE v.addon_id = a.id AND av.version_id = v.id AND av.min = min.id) AS min_ver,
    ->     UNIX_TIMESTAMP(a.created) AS created,
    ->     (SELECT MAX(IFNULL(f.datestatuschanged, f.created)) FROM versions AS v INNER JOIN files AS f ON f.status = 4 AND f.version_id = v.id WHERE v.addon_id=a.id) AS modified
    -> FROM 
    ->     translations name, 
    ->     addons a
    -> WHERE a.name = name.id;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> -- This view is used to extract some version-related data
mysql> 
mysql> -- versions (we use this for joins)
mysql> 
mysql> CREATE OR REPLACE VIEW versions_summary_view AS
    -> 
    -> SELECT DISTINCT 
    ->     t.autoid AS translation_id, 
    ->     v.addon_id, 
    ->     v.id, 
    ->     av.application_id, 
    ->     v.created, 
    ->     v.modified, 
    ->     min.version_int AS min, 
    ->     max.version_int AS max, 
    ->     MAX(v.created)
    -> FROM versions v, addons a, translations t, applications_versions av, appversions max, appversions min
    -> WHERE 
    ->     a.id = v.addon_id AND a.name = t.id AND av.version_id = v.id
    ->     AND av.min = min.id AND av.max=max.id
    -> GROUP BY 
    ->     translation_id, 
    ->     v.addon_id, 
    ->     v.id, 
    ->     av.application_id, 
    ->     v.created, 
    ->     v.modified, 
    ->     min.version_int, 
    ->     max.version_int;
Query OK, 0 rows affected (0.01 sec)
For those following at home, I think we're still waiting on the script-run for the data behind https://addons.mozilla.org/en-US/statistics.
(Reporter)

Comment 10

8 years ago
What's the status?
(Reporter)

Comment 11

8 years ago
The stats migration script died.  I filed bug 513206 to figure that out.

Justdave helped figure out some external problems and also added a missing cron, collection_stats.  Thanks justdave.

We've got problems in production still, but nothing that we can't fix tomorrow instead of tonight.  I think this bug is done, but can't know for sure.
(Assignee)

Comment 12

8 years ago
Looks like step 7 failed.
    php -f site_stats_history.php
Processing addon_downloads_new stats... (465 rows affected)
Processing addon_count_new stats... (1092 rows affected)
Processing version_count_new stats... (1092 rows affected)
Processing user_count_new stats... (1092 rows affected)
Processing review_count_new stats... PHP Notice:  MySQL Error 2006: MySQL server has gone away
Query was: [SELECT IFNULL(MAX(`date`), '2006-09-01') AS latest FROM global_stats WHERE name = 'review_count_new'] in /data/amo/www/addons.mozilla.org-remora/bin/database.class.php on line 123
PHP Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data/amo/www/addons.mozilla.org-remora/bin/run_once/site_stats_history.php on line 68
(-14485 rows affected)
Processing collection_count_new stats... PHP Notice:  MySQL Error 2006: MySQL server has gone away
Query was: [SELECT IFNULL(MAX(`date`), '2006-09-01') AS latest FROM global_stats WHERE name = 'collection_count_new'] in /data/amo/www/addons.mozilla.org-remora/bin/database.class.php on line 123
PHP Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data/amo/www/addons.mozilla.org-remora/bin/run_once/site_stats_history.php on line 68
(-14485 rows affected)
done.
(Assignee)

Updated

8 years ago
Status: NEW → RESOLVED
Last Resolved: 8 years ago
Resolution: --- → FIXED
I'm calling this verified fixed; bug 513206 is verified FIXED, too, and I think that was the last remaining thing.
Status: RESOLVED → VERIFIED
Component: Server Operations: Web Operations → WebOps: Other
Product: mozilla.org → Infrastructure & Operations
You need to log in before you can comment on or make changes to this bug.