Engagement backup crc differences.

RESOLVED INVALID

Status

RESOLVED INVALID
5 years ago
4 years ago

People

(Reporter: bjohnson, Assigned: bjohnson)

Tracking

Details

(Whiteboard: [2013q4] November)

(Assignee)

Description

5 years ago
<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.
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.
Whiteboard: [2013q4] November
(Assignee)

Comment 2

5 years ago
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.
Assignee: server-ops-database → bjohnson
Status: NEW → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → INVALID
(Assignee)

Comment 3

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

Comment 4

5 years ago
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.
Product: mozilla.org → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.