990590 - i broke bugzilla stage's database

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[:glob ✱]

so, there i was, casually committing bug 981756 to finally make bugzilla aware of the BIGINT schema changes that were implemented during "the database exploded week".

unfortunately things did not go to plan:

[01:20:19] Updating column id in table attach_data ... 
[01:20:19] Old: mediumint PRIMARY KEY NOT NULL 
[01:20:19] New: bigint unsigned 
[03:20:19] DBD::mysql::db do failed: Lost connection to MySQL server during query [for Statement "ALTER TABLE attach_data CHANGE COLUMN 
[03:20:19] id id bigint unsigned"] at Bugzilla/DB.pm line 739.

uh oh.

and now checksetup says:

Adding foreign key: attach_data.id -> attachments.attach_id...
DBD::mysql::db do failed: Cannot add foreign key constraint [for Statement "ALTER TABLE attach_data ADD
     CONSTRAINT fk_attach_data_id_attachments_attach_id FOREIGN KEY (id)
     REFERENCES attachments(attach_id)
      ON UPDATE CASCADE ON DELETE CASCADE"] at Bugzilla/DB.pm line 664.


:(

Comment 1

[:glob ✱]

more context:

[01:20:18] Dropping foreign key: attach_data.id -> attachments.attach_id...
[01:20:18] Dropping foreign key: autoland_attachments.attach_id ->
[01:20:18] attachments.attach_id...
[01:20:18] Dropping foreign key: bugs_activity.attach_id ->
[01:20:18] attachments.attach_id...
[01:20:19] Dropping foreign key: flag_state_activity.attachment_id ->
[01:20:19] attachments.attach_id...
[01:20:19] Dropping foreign key: flags.attach_id -> attachments.attach_id...
[01:20:19] Updating column id in table attach_data ...
[01:20:19] Old: mediumint PRIMARY KEY NOT NULL
[01:20:19] New: bigint unsigned
[03:20:19] DBD::mysql::db do failed: Lost connection to MySQL server during query [for Statement "ALTER TABLE attach_data CHANGE COLUMN [03:20:19] id id bigint unsigned"] at Bugzilla/DB.pm line 739.

it's entirely possible those dropped foreign keys will need to be recreated.

Comment 2

[Sheeri Cabral [:sheeri]]

Looking at:
https://bugzilla.mozilla.org/show_bug.cgi?id=981714#c16

ALTER TABLE attach_data CHANGE COLUMN id id bigint unsigned;
alter table attachments modify attach_id bigint unsigned not null;
alter table autoland_attachments modify attach_id bigint unsigned not null;
 alter table bugs_activity modify attach_id bigint unsigned not null;
alter table flag_state_activity modify attachment_id  bigint unsigned not null;
alter table flags modify attach_id bigint unsigned not null;
alter table sanitized_bugs_activity modify attach_id bigint unsigned not null;

For this last one, I got:
ERROR 1347 (HY000): 'bugzilla_allizom_org.sanitized_bugs_activity' is not BASE TABLE

Everything else went fine. I haven't done dev, and I haven't added back in the foreign keys.

Comment 3

[Sheeri Cabral [:sheeri]]

Adding foreign keys back into stage (comparing to fkeys in dev):
alter table autoland_attachments add CONSTRAINT `fk_autoland_attachments_attach_id_attachments_attach_id` FOREIGN KEY (`attach_id`) REFERENCES `attachments` (`attach_id`) ON DELETE CASCADE ON UPDATE CASCADE;

 alter table flag_state_activity add CONSTRAINT `fk_flag_state_activity_attachment_id_attachments_attach_id` FOREIGN KEY (`attachment_id`) REFERENCES `attachments` (`attach_id`) ON DELETE CASCADE ON UPDATE CASCADE;


 alter table flags add  CONSTRAINT `fk_flags_attach_id_attachments_attach_id` FOREIGN KEY (`attach_id`) REFERENCES `attachments` (`attach_id`) ON DELETE CASCADE ON UPDATE CASCADE;

alter table bugs_activity add CONSTRAINT `fk_bugs_activity_attach_id_attachments_attach_id` FOREIGN KEY (`attach_id`) REFERENCES `attachments` (`attach_id`) ON DELETE CASCADE ON UPDATE CASCADE;

alter table attach_data add CONSTRAINT `fk_attach_data_id_attachments_attach_id` FOREIGN KEY (`id`) REFERENCES `attachments` (`attach_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Comment 4

[Sheeri Cabral [:sheeri]]

Altering tables on dev now (with set foreign_key_checks=OFF so I don't have to disable and reenable...)

Comment 5

[Sheeri Cabral [:sheeri]]

Those result in:
 ERROR 1025 (HY000): Error on rename of './bugzilla_dev_allizom_org/#sql-2b2b_2fe43b' to './bugzilla_dev_allizom_org/attachments' (errno: 150 - Foreign key constraint is incorrectly formed)

So I do have to drop constraints.

alter table autoland_attachments drop FOREIGN KEY `fk_autoland_attachments_attach_id_attachments_attach_id`;

 alter table flag_state_activity drop FOREIGN KEY `fk_flag_state_activity_attachment_id_attachments_attach_id`;


 alter table flags drop  FOREIGN KEY `fk_flags_attach_id_attachments_attach_id` ;

alter table bugs_activity drop FOREIGN KEY `fk_bugs_activity_attach_id_attachments_attach_id`;

alter table attach_data drop FOREIGN KEY `fk_attach_data_id_attachments_attach_id`;

Comment 6

[Sheeri Cabral [:sheeri]]

alters in comment 2 are now made on dev.

Comment 7

[Sheeri Cabral [:sheeri]]

and added an alter for attach_id in attachment_cf_fixed_in on dev.

All the dev and stage alters and foreign key re-adds have been completed.

Please test to verify!r

Comment 8

[:glob ✱]

unfortunately it looks like something has gone extremely wonky (most likely bugzilla's code is trying to fix things itself, badly).

checksetup.pl is running, and it's taking a very long time.
so i poked at the process list:

> DELETE FROM bugs_activity WHERE attach_id IN ('0')

odd

> select count(*) from bugs_activity where attach_id = 0;
> 10047724

oh.  that isn't good.



i don't think there's anything in the staging database that we need, and i apologise that i didn't think of this before, but i think it would be simplest at this point to start that db afresh with the most recent dump from production.

dkl - can you think of any reason to not refresh the staging database?

(we should leave the development database alone for now).

Comment 9

[David Lawrence [:dkl]]

(In reply to Byron Jones ‹:glob› from comment #8)
> dkl - can you think of any reason to not refresh the staging database?

No objections/concerns from my side.

dkl

Comment 10

[:glob ✱]

great :)

sheeri - can you please refresh the staging database with a recent(ish) unsanitised dump from production?

thanks

Comment 11

[Sheeri Cabral [:sheeri]]

Will do!

Comment 12

[Sheeri Cabral [:sheeri]]

Importing into the "bugzilla_refresh_stage" database - plenty of space on that machine. Once the import is complete, I'll drop the old db and rename this one.

mysql bugzilla_refresh_stage < bugs.2014.04.07.sql

Comment 13

[Sheeri Cabral [:sheeri]]

stage refresh is done, old db is at bugzilla_old, I can delete it whenever, if bugzilla_allizom_org works fine now that it's refreshed.

Comment 14

[:glob ✱]

the good news is the database refresh went perfectly, thank you!
the bad news is checksetup.pl managed to bork it again :(

i've reverted the code in question and will have to pour time into determining the root cause of this issue and why we didn't see it locally.  that's being tracking in bug 981756.


i'm sorry to consume even more of your time, but can you please perform another refresh of the stage database?

thanks.

Comment 15

[Sheeri Cabral [:sheeri]]

No worries! Starting that now.

Comment 16

[Sheeri Cabral [:sheeri]]

stage refresh complete.