898509 - Datazilla Data Integrity Issue

Status: RESOLVED FIXED

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

Description

[Jonathan Eads ( :jeads )]

It appears that all objects in datazilla have been marked as "ready" to process. This has occurred across all projects, there is no explanation for this that I can think of, other than an explicit query being run or some kind of data corruption. I'm going to need help troubleshooting.

1.) I'm not sure what is available in the transaction log on datazilla-rw-vip.db.scl3.mozilla.com but I would be looking for some kind of update query executed without an appropriate where clause on any database name matching *_objectstore_1.


2.) Could we determine how much disk space is currently available and how much has been used on datazilla-rw-vip.db.scl3.mozilla.com

3.) Are there any outstanding queries running? If there is a query killer running that terminates long running queries does it keep a log anywhere that I can examine?

4.) How do the system resources look on datazilla-rw-vip.db.scl3.mozilla.com? Are there any outstanding resource consumers in the process list?

We can tell which objects have been processed by the ones that have a test_run_id defined so this is straightforward to fix but I can think of no explanation for how it occurred so I would like to investigate a bit before setting things straight.

Comment 1

[Jonathan Eads ( :jeads )]

Ok I've determined from an application log what the cause of the issue is. There are jobs in the talos objectstore that have excessive numbers of replicates that are causing a number of problems for all projects. To temporarily fix this we need someone to execute the following SQL query:


UPDATE `talos_objectstore_1`.`objectstore`
SET processed_flag = 'complete'
WHERE processed_flag = 'ready' OR processed_flag = 'loading';


There will be some subsequent data clean up required but this will allow other projects to begin processing objects for now.

Comment 2

[Chris Turra [:cturra]]

mysql> update `talos_objectstore_1`.`objectstore` SET processed_flag = 'complete' WHERE processed_flag = 'ready' OR processed_flag = 'loading';
Query OK, 70612 rows affected (1 min 4.74 sec)
Rows matched: 70612  Changed: 70612  Warnings: 0

Comment 3

[Brandon Johnson [:cyborgshadow]]

Thanks Chris. You beat me to it.

jeads,

does that fix everything?

Btw,

no resources were in use and no extra running queries. I was actively combing through the binlogs and found lots of statements, but no generic statement missing a where or targeted like you originally mentioned.

Also, there's 617 GB free space and 252 GB in use.

Comment 4

[Jonathan Eads ( :jeads )]

Thanks Brandon, that's useful information. We're back up and running with object processing in datazilla. We worked out a fix in bug 898492, I will be watching the data processing over the weekend and make sure things look good.

Looks like the source of the problems was a lot of corrupted data sent from talos. We're going to need to truncate some tables in talos_perftest_1 to deal with the situation in the long term but we have a short term fix for now.

I will file a separate bug for the data clean up.