Amir, I am not sure what you want #DBAs to do here, as I do not have a patch to apply or review. In any case, this doesn't seem like a Schema-change-in-production ticket, but you are asking for help with a review (that only requires the DBA tag). If that is the case, please say so explicitly, and add me to such review, I cannot understand well what do you want from us yet (I am not involved with ORES on a day-to-day basis except on the database side).

I am happy to help, just tell me how.

I'm trying to follow https://wikitech.wikimedia.org/wiki/Schema_changes#Workflow_of_a_schema_change. This is the article 3: "Once the solution has been agreed among all developers (usually that means merged to HEAD including the schema change, but disabled on configuration), and you need to apply that to the live databases, create a separate specific task (subtask of the main issue) for the Schema-change-in-production, owned by DBA, providing the following specific information, one per batch (group of alters to be done at the same time)"
I copied the title from the example given there T119752: Review schema changes for T119495
What I need is applying these index changes in the live database and that's it :)

Ladsgroup, this is reverted https://gerrit.wikimedia.org/r/#/c/309818/ . Even if you did that, it is not "merged to HEAD", as far as I can see. So this is not ready for deploy yet.

We need to be sure they don't exist in production tables too before applying the schema change

Which means we have to check for that BEFORE we apply any further change. So this is not a schema change (yet), it is a regular DBA ticket, then if we are successful, we can do the schema change with no issue.

However, you did not include any details on how to check for data corruption. Please tell me so I can check. What was the issue on beta? You did not add those details.

And that revert got reverted later on:
https://gerrit.wikimedia.org/r/#/c/309837/

So It' in HEAD now. We should wait for a few days until wmf.19 gets deployed in production and deploy these changes afterwards.

Regarding the corrupt data. We are adding two unique indexes. By corrupt, I mean data that violate these unique constraints. Just thinking out loud: Something like this SELECT oresc_rev, oresc_model, oresc_class, count(*) FROM ores_classification GROUP BY oresc_rev, oresc_model, oresc_class HAVING count(*) > 1; I want to check that but I was thinking that it might be better to be done before the schema update because we might get some corrupt data between now and the next week (or any time that we want to apply this schema change). If you tell me to check it now, I do it now.

@Ladsgroup the title I added would have been more descriptive for me to understand the issue. I will be checking this now.

Thanks.

There are multiple rows with issue on production, here is the list I got some minutes ago:

Okay, this is too big to be done manually. I think, I should write a maintenance script to do this job.

I blocked myself to wait for your input, I am only standing by, but let me know when you need me again.

I will ask you soon. Thanks.

Assigning this to @Ladsgroup because it looks like the last thing to do is to run the maintenance script. We can do that once the change for T145503 is merged and deployed. So I'm moving this to the backlog for now.

Can I have a look at it before it is run? It is very easy to create lag by accident, and I could maybe give some tips if that was a possibility?

@jcrespo yes please. I'm confirming now what should be reviewed.

@jcrespo see T145503#2659669 which references https://gerrit.wikimedia.org/r/#/c/312286/

Sorry, I checked and there is less than 1000 rows to delete per wiki; things should be done really badly to create lag- and you already did well by separating the reads on a separate replica and deleting using the primary key, so everything is ok. If at some point you do other maintenance ping me, as we should be otherwise more careful.

The only thing I would ask you is to do it during a scheduled deployment time (this should not take more than 5 minutes) in case something else goes wrong, notice it immediately.

Mentioned in SAL (#wikimedia-operations) [2016-10-11T17:25:13Z] <Amir1> ladsgroup@terbium:~$ mwscript extensions/ORES/maintenance/CleanDuplicateScores.php on eight wikis (T145356)

@jcrespo: I ran the maintenance script and then this:

Is it okay now? Thanks