484842 - Run SQL for bandwagon staging

Status: VERIFIED FIXED

(mozilla.org Graveyard :: Server Operations, task)

Description

[Justin Scott [:fligtar]]

Please run the following SQL on the bandwagon staging DB. It should not be run in production yet.

From https://wiki.mozilla.org/Update:Developers/Database_Changes#Bandwagon :

alter table collections drop foreign key collections_addontype_ibfk_1;
alter table collections drop column addontype_id;
ALTER TABLE `collections` ADD `uuid` CHAR(36) NOT NULL DEFAULT '';
UPDATE `collections` SET `uuid`=uuid() WHERE `uuid`='';
ALTER TABLE collections ADD UNIQUE KEY `uuid` (`uuid`);
ALTER TABLE `collections` ADD `collection_type` int(11) unsigned NOT NULL DEFAULT '0';

Thanks

Comment 1

[Jeremy Orem [:oremj]]

Bandwagon is currently sharing a database with preview. Will this SQL break anything on preview?

Comment 2

[Justin Scott [:fligtar]]

I don't think so, but let's find out!

Comment 3

[Jeremy Orem [:oremj]]

The first two alter tables failed:

mysql> alter table collections drop foreign key collections_addontype_ibfk_1;
ERROR 1025 (HY000): Error on rename of './addons_reskin/collections' to './addons_reskin/#sql2-6df8-14b571' (errno: 152)

mysql> alter table collections drop column addontype_id;
ERROR 1091 (42000): Can't DROP 'addontype_id'; check that column/key exists

mysql> ALTER TABLE `collections` ADD `uuid` CHAR(36) NOT NULL DEFAULT '';
Query OK, 10 rows affected (0.20 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> UPDATE `collections` SET `uuid`=uuid() WHERE `uuid`='';
Query OK, 10 rows affected (0.02 sec)
Rows matched: 10  Changed: 10  Warnings: 0

mysql> ALTER TABLE collections ADD UNIQUE KEY `uuid` (`uuid`);
Query OK, 10 rows affected (0.09 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `collections` ADD `collection_type` int(11) unsigned NOT NULL
    -> DEFAULT '0';
Query OK, 10 rows affected (0.07 sec)
Records: 10  Duplicates: 0  Warnings: 0

SHOW CREATE TABLE collections:
CREATE TABLE `collections` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` int(11) unsigned NOT NULL,
  `nickname` varchar(30) default NULL,
  `description` int(11) unsigned NOT NULL,
  `access` tinyint(1) NOT NULL default '0',
  `listed` tinyint(1) NOT NULL default '1',
  `password` varchar(255) NOT NULL,
  `subscribers` int(11) unsigned 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',
  `uuid` char(36) NOT NULL default '',
  `collection_type` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `listed` (`listed`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8

Comment 4

[Les Orchard [:lorchard]]

Weird.  The UUIDs in links are still empty, even after the update.  I can't reproduce this on my dev machine.  Not sure how to diagnose this

https://bandwagon.stage.mozilla.com/en-US/firefox/collections/

Comment 5

[Jeremy Orem [:oremj]]

I can turn on DEBUG or insert error_log statements wherever.

Comment 6

[Les Orchard [:lorchard]]

The only two things I can think of to poke at this thing are:

1) Is this for sure the database bandwagon.stage is using?  Because the "all
collections" list shows only 7 - which implies that 3 of the "10 rows affected"
up there are private maybe?

2) What's SELECT `uuid`, `access` FROM `collections` look like?

Turning on debug might help, but not sure what error_log statements would help

Comment 7

[Les Orchard [:lorchard]]

Or.. maybe make that SELECT `uuid`, `listed` FROM `collections`

Comment 8

[Jeremy Orem [:oremj]]

I verified that the correct db was updated.

SELECT `uuid`, `access` FROM `collections`;
+--------------------------------------+--------+
| uuid                                 | access |
+--------------------------------------+--------+
| 787690d4-692f-102c-9a23-001e0bd9232c |      0 | 
| 78769390-692f-102c-9a23-001e0bd9232c |      0 | 
| 78769412-692f-102c-9a23-001e0bd9232c |      0 | 
| 78769480-692f-102c-9a23-001e0bd9232c |      0 | 
| 787694ee-692f-102c-9a23-001e0bd9232c |      0 | 
| 78769552-692f-102c-9a23-001e0bd9232c |      0 | 
| 787695b6-692f-102c-9a23-001e0bd9232c |      0 | 
| 7876961a-692f-102c-9a23-001e0bd9232c |      0 | 
| 7876982c-692f-102c-9a23-001e0bd9232c |      0 | 
| 7876989a-692f-102c-9a23-001e0bd9232c |      0 | 
|                                      |      0 | 
+--------------------------------------+--------+
11 rows in set (0.00 sec)

Comment 9

[Jeremy Orem [:oremj]]

SELECT `uuid`, `listed` FROM `collections`
    -> ;
+--------------------------------------+--------+
| uuid                                 | listed |
+--------------------------------------+--------+
| 787690d4-692f-102c-9a23-001e0bd9232c |      0 | 
| 78769390-692f-102c-9a23-001e0bd9232c |      1 | 
| 78769412-692f-102c-9a23-001e0bd9232c |      0 | 
| 78769480-692f-102c-9a23-001e0bd9232c |      1 | 
| 787694ee-692f-102c-9a23-001e0bd9232c |      1 | 
| 78769552-692f-102c-9a23-001e0bd9232c |      0 | 
| 787695b6-692f-102c-9a23-001e0bd9232c |      1 | 
| 7876961a-692f-102c-9a23-001e0bd9232c |      0 | 
| 7876982c-692f-102c-9a23-001e0bd9232c |      1 | 
| 7876989a-692f-102c-9a23-001e0bd9232c |      1 | 
|                                      |      1 | 
+--------------------------------------+--------+
11 rows in set (0.00 sec)

Comment 10

[Les Orchard [:lorchard]]

Oh!  Maybe the cakephp model caches need clearing?

Comment 11

[Les Orchard [:lorchard]]

That's my last idea, because the UUIDs apparently work for looking up collections:

https://bandwagon.stage.mozilla.com/en-US/firefox/collections/view/78769480-692f-102c-9a23-001e0bd9232c

Comment 12

[Jeremy Orem [:oremj]]

Model cache cleared and it appears to be working.

Comment 13

[Les Orchard [:lorchard]]

Awesome - works for me, too!  Thank you, sir.

Comment 14

[Les Orchard [:lorchard]]

Oh, one last potential issue - while poking at this thing, I think I managed to add a collection with a blank UUID.  If you have a chance, could you run this:

DELETE FROM `collections` where `uuid`=''

The value is technically unique, so it doesn't fail the unique index, but the PHP code refuses to accept a blank UUID for deletion or viewing

Comment 15

[Jeremy Orem [:oremj]]

Deleting breaks some constraint:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`addons_reskin/collections_users`, CONSTRAINT `collections_users_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`))

Comment 16

[Les Orchard [:lorchard]]

Ack, hmm in that case, maybe try this again:

UPDATE `collections` SET `uuid`=uuid() WHERE `uuid`='';

That will at least give it a UUID that the app can use.

Comment 17

[Jeremy Orem [:oremj]]

Done.

Comment 18

[Les Orchard [:lorchard]]

This bug is mega resolved fixed and gets extra credit