482223 - SQL for AMO production and preview DB

Status: RESOLVED FIXED

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

Description

[Fred Wenzel [:wenzel]]

We need you to run some SQL on AMO's preview as well as production DBs please, as QA can't test our new code otherwise.

Please execute the following SQL snippets:
* attachment 363226 [details] [diff] [review] from bug 477343 (bayesian ratings)
* attachment 363800 [details] from bug 478348 (editor subscriptions to add-on updates) 
* attachment 364627 [details] from bug 400099 (editors: "more info" request)

as well as (from bug 478125):

 ALTER TABLE `addons_collections` ADD `user_id` int(11) unsigned default null;
 ALTER TABLE `addons_collections` ADD `modified` datetime default '0000-00-00 00:00:00';


Sorry for it being in several files.

Comment 1

[Wil Clouser [:clouserw]]

Thanks Fred, r=clouserw for preview.  I don't think anything bad would happen if we did production but let's hold off until we push the code changes as well.

Comment 2

[Fred Wenzel [:wenzel]]

Fine with me; I thought pushing both would cause less confusion but we can of course also do all of the changes in production at once on Thursday.

Comment 3

[Dave Miller [:justdave]]

There were errors in the run on preview....

mysql> ALTER TABLE `addons` ADD `bayesianrating` FLOAT NOT NULL DEFAULT '0' AFTER `averagerating` ;
Query OK, 8226 rows affected (1.66 sec)
Records: 8226  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `addons` ADD INDEX ( `bayesianrating` ) ;
Query OK, 8226 rows affected (1.31 sec)
Records: 8226  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `editor_subscriptions` (
    ->   `user_id` int(11) unsigned NOT NULL,
    ->   `addon_id` int(11) unsigned NOT NULL,
    ->   PRIMARY KEY  (`user_id`,`addon_id`),
    ->   KEY `user_id` (`user_id`),
    ->   KEY `addon_id` (`addon_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Editor subscriptions for add-on updates';
ERROR 1050 (42S01): Table 'editor_subscriptions' already exists
mysql> ALTER TABLE `editor_subscriptions`
    ->   ADD CONSTRAINT `editor_subscriptions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    ->   ADD CONSTRAINT `editor_subscriptions_ibfk_2` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`id`) ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table './addons_reskin/#sql-6df8_92c43.frm' (errno: 121)
mysql> ALTER TABLE `approvals` ADD `reply_to` INT( 11 ) UNSIGNED NULL DEFAULT NULL AFTER `created` ;
Query OK, 9310 rows affected (0.67 sec)
Records: 9310  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `approvals` ADD INDEX ( `reply_to` ) ;
Query OK, 9310 rows affected (0.47 sec)
Records: 9310  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `approvals` ADD FOREIGN KEY ( `reply_to` ) REFERENCES `approvals` (`id`) ON DELETE CASCADE ;
Query OK, 9310 rows affected (0.47 sec)
Records: 9310  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `approvals` CHANGE `file_id` `file_id` INT( 11 ) UNSIGNED NULL DEFAULT NULL;
Query OK, 9310 rows affected (0.55 sec)
Records: 9310  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `addons_collections` ADD `user_id` int(11) unsigned default null;
Query OK, 43 rows affected (0.19 sec)
Records: 43  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `addons_collections` ADD `modified` datetime default '0000-00-00 00:00:00';
Query OK, 43 rows affected (0.10 sec)
Records: 43  Duplicates: 0  Warnings: 0

Comment 4

[Dave Miller [:justdave]]

And for the record, here's the existing DDL for the table that already existed:

CREATE TABLE `editor_subscriptions` (
  `user_id` int(11) unsigned NOT NULL,
  `addon_id` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`user_id`,`addon_id`),
  KEY `user_id` (`user_id`),
  KEY `addon_id` (`addon_id`),
  CONSTRAINT `editor_subscriptions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `editor_subscriptions_ibfk_2` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Editor subscriptions for add-on updates'

Comment 5

[Dave Miller [:justdave]]

And that looks like an exact match for what was asked to be created, so it looks like that second attachment had already been run, and this all turned out okay. :)

Comment 6

[Fred Wenzel [:wenzel]]

(In reply to comment #5)
> And that looks like an exact match for what was asked to be created, so it
> looks like that second attachment had already been run, and this all turned out
> okay. :)

Thanks, I agree :)