981986 - invalid entries for attach_id in the flags table, due to maxing out the mediumint value

Status: RESOLVED FIXED

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

Description

[:glob ✱]

something very weird is happening with the attachments and flags on bug 969117.

querying the slaves i get two different attach_ids for njn's review:

> select attach_id,status from flags where id=826519;
> 8388607 	?
> select attach_id,status from flags where id=826519;
> 8388676 	?

first, attachment 8388607 [details] is unrelated to this bug, which worries me.


second, njn set this flag to + on [2014-03-10 15:17:49 PDT] however i don't see this change when querying the flags (as per above, they are still returning ?).

Comment 1

[:glob ✱]

> select count(*) from flags where attach_id=8388607;
> 0
> select count(*) from flags where attach_id=8388607;
> 13

that attachment is from bug 860711.

show_bug is displaying all those flags against that attachment, which indicates to me that there's incorrect rows in the master (that attachment shouldn't have any flags set).

Comment 2

[:glob ✱]

nagios is currently reporting zero lag for all slaves (except bugzilla4, which is currently offline), so those different results for the queries is where i'm mostly concerned.

as 8388607 is the max value of mediumint, that's probably why it's being reused, and we'll probably have to manually tidy up those rows.  i'll work through the list and try to determine the correct attach_id's.

Comment 3

[:glob ✱]

as far as i can tell:

flags.id  correct attach_id
826516    8388607
826521    8388680
826511    8388663
826517    8388671
826514    8388667
826519    8388676
826512    8388665
826505    8388649
826508    8388660
826520    8388677
826506    8388650
826518    8388675
826522    8388681

Comment 4

[Brandon Johnson [:cyborgshadow]]

I'll dig into these further and fix.

Comment 5

[Sheeri Cabral [:sheeri]]

The flags table has 18 differences among the slaves. Fixing those now, setting the attach_id to the value that isn't 8388607, when there's a difference among the slaves.

Changed:
mysql> select flags.id,bug_id,attach_id from flags where id in (826505,826508,826506,826511,826512,826514,826516,826517,826518,826519,826520,826521,826522);
+--------+--------+-----------+
| id     | bug_id | attach_id |
+--------+--------+-----------+
| 826505 | 979489 |   8388649 |
| 826506 | 981481 |   8388650 |
| 826508 | 979489 |   8388660 |
| 826511 | 935219 |   8388663 |
| 826512 | 976082 |   8388665 |
| 826514 | 960517 |   8388667 |
| 826516 | 739959 |   8388670 |
| 826517 | 959430 |   8388671 |
| 826518 | 981636 |   8388675 |
| 826519 | 969117 |   8388676 |
| 826520 | 980456 |   8388677 |
| 826521 | 739959 |   8388680 |
| 826522 | 981728 |   8388681 |
+--------+--------+-----------+
13 rows in set (0.00 sec)

Comment 6

[Sheeri Cabral [:sheeri]]

Entries stable, resolving.