909778 - Engagement backup crc differences.

Status: RESOLVED INVALID

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

Description

[Brandon Johnson [:cyborgshadow]]

<nagios-phx1> (IRC) Tue 07:08:55 PDT [1243] backup2.db.phx1.mozilla.com:MySQL Backups Table Checksum is WARNING: OK: bouncer has no crc differences in the last 24 hours AND db NOT IN (mysql,performance_schema,information_schema) OK: bugzilla has no crc differences in the last 24 hours AND db NOT IN (mysql,performance_schema,information_schema) WARNING: engagement has crc differences: affiliates_mozilla_org.badges_leaderboard,aff

Check out soon.

Comment 1

[Sheeri Cabral [:sheeri]]

For now we are ignoring those tables in the checksum script, but this should be checked out and figured out why the data seems to be nondeterministic.

Comment 2

[Brandon Johnson [:cyborgshadow]]

Although this was recurring for a while, I dug through as best I could. Nothing in the slow logs, error logs, or elsewhere that points to non-deterministic data. What is of note: these were occurring during a period of known network problems prior to our big overhaul.

I've removed the ignore table lines for these, refreshed engagements, and am closing this as resolved for now unless it pops up again, but I don't think it will.

Comment 3

[Brandon Johnson [:cyborgshadow]]

Forgot to mention in comment 2 that I also ran checksums after the refresh. Everything was as expected (no differences).

Comment 4

[Brandon Johnson [:cyborgshadow]]

Post refresh, I've been able to pinpoint why the badges_leaderboard table has crc differences.

It's a 3 column table that's actually dropped and recreated (found a drop table statement in the slow log).

The create table statement is: 

CREATE TABLE `badges_leaderboard` (
  `ranking` int(10) unsigned NOT NULL,
  `user_id` int(11) NOT NULL,
  `clicks` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ranking`),
  KEY `badges_leaderboard_fbfc09f1` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

This table is dropped and recreated, being populated by a query that gathers user_id and click count and orders by click count only.

By technicality, an "order by count(click), user_id" instead of "order by count(click)" would solve this, but in seeing the nature of this table more clearly, we really don't need to be checksumming this table, and it's easier to solve this by simply not checksumming it rather than making the badges folks have to make a code change.

I will however let them know and decide if they'd like to fix it, as someone's order may change slightly daily if there's lots of people with that # of clicks.