We should hear from Joseph, Dan, Marcel, and Aaron H on this I think, but from the little I know: Most analytical computations (for things like reverts, as you say) don’t have easy access to content, so computing SHAs on the fly is pretty hard. MediaWiki history reconstruction relies on the SHA to figure out what revisions revert other revisions, as there is no reliable way to know if something is a revert other than by comparing SHAs. See https://wikitech.wikimedia.org/wiki/Analytics/Data_Lake/Edits/Mediawiki_his… (particularly the *revert* fields). On Fri, Sep 15, 2017 at 1:49 PM, Erik Zachte <ezachte(a)wikimedia.org> wrote:

Hi! As a random idea - would it be possible to calculate the hashes when data is transitioned from SQL to Hadoop storage? I imagine that would slow down the transition, but not sure if it'd be substantial or not. If we're using the hash just to compare revisions, we could also use different hash (maybe non-crypto hash?) which may be faster. -- Stas Malyshev smalyshev(a)wikimedia.org

separately? Perhaps it be generated asynchronously via a job? Either stored in revision or a separate table. On Fri, Sep 15, 2017 at 4:06 PM, Andrew Otto <otto(a)wikimedia.org> wrote:

Alternatively, perhaps "hash" could be an optional part of an MCR chunk? We could keep it for the wikitext, but drop the hash for the metadata, and drop any support for a "combined" hash over wikitext + all-other-pieces. ...which begs the question about how reverts work in MCR. Is it just the wikitext which is reverted, or do categories and other metadata revert as well? And perhaps we can just mark these at revert time instead of trying to reconstruct it after the fact? --scott On Fri, Sep 15, 2017 at 4:13 PM, Stas Malyshev <smalyshev(a)wikimedia.org> wrote: -- (http://cscott.net)

Am 15.09.2017 um 19:49 schrieb Erik Zachte: We can always compute the hash when outputting XML dumps that contain the full content (it's already loaded, so no big deal), and then generate the XML dump with only meta-data from the full dump. -- Daniel Kinzler Principal Platform Engineer Wikimedia Deutschland Gesellschaft zur Förderung Freien Wissens e.V.

Ok, a little more detail here: For MCR, we would have to keep around the hash of each content object ("slot") AND of each revision. This makes the revision and content tables "wider", which is a problem because they grow quite "tall", too. It also means we have to compute a hash of hashes for each revision, but that's not horrible. I'm hoping we can remove the hash from both tables. Keeping the hash of each content object and/or each revision somewhere else is fine with me. Perhaps it's sufficient to generate it when generating XML dumps. Maybe we want it in hadoop. Maybe we want to have it in a separate SQL database. But perhaps we don't actually need it. Can someone explain *why* they want the hash at all? Am 15.09.2017 um 22:01 schrieb Stas Malyshev: -- Daniel Kinzler Principal Platform Engineer Wikimedia Deutschland Gesellschaft zur Förderung Freien Wissens e.V.

At a quick glance, EventBus and FlaggedRevs are the two extensions using the hashes. EventBust just puts them into the emitted data; FlaggedRevs detects reverts to the latest stable revision that way (so there is no rev_sha1 based lookup in either case, although in the case of FlaggedRevs I could imagine a use case for something like that). Files on the other hand use hash lookups a lot, and AIUI they are planned to become MCR slots eventually. For a quick win, you could just reduce the hash size. We have around a billion revisions, and probably won't ever have more than a trillion; square that for birthday effect and add a couple extra zeros just to be sure, and it still fits comfortably into 80 bits. If hashes only need to be unique within the same page then maybe 30-40.

Antoine Musso wrote: Yes. Brion started a thread about the use of SHA-1 in February 2017: https://lists.wikimedia.org/pipermail/wikitech-l/2017-February/087664.html https://lists.wikimedia.org/pipermail/wikitech-l/2017-February/087666.html Of note, we have <https://www.mediawiki.org/wiki/Manual:Hashing>. The use of base-36 SHA-1 instead of base-16 SHA-1 for revision.rev_sha1 has always perplexed me. It'd be nice to better(?) document that design decision. It's referenced here: https://lists.wikimedia.org/pipermail/wikitech-l/2012-September/063445.html MZMcBride

So, as things stand, rev_sha1 in the database is used for: 1. the XML dumps process and all the researchers depending on the XML dumps (probably just for revert detection) 2. revert detection for libraries like python-mwreverts [1] 3. revert detection in mediawiki history reconstruction processes in Hadoop (Wikistats 2.0) 4. revert detection in Wikistats 1.0 5. revert detection for tools that run on labs, like Wikimetrics ?. I think Aaron also uses rev_sha1 in ORES, but I can't seem to find the latest code for that service If you think about this list above as a flow of data, you'll see that rev_sha1 is replicated to xml, labs databases, hadoop, ML models, etc. So removing it and adding it back downstream from the main mediawiki database somewhere, like in XML, cuts off the other places that need it. That means it must be available either in the mediawiki database or in some other central database which all those other consumers can pull from. I defer to your expertise when you say it's expensive to keep in the db, and I can see how that would get much worse with MCR. I'm sure we can figure something out, though. Right now it seems like our options are, as others have pointed out: * compute async and store in DB or somewhere else that's central and easy to access from all the branches I mentioned * update how we detect reverts and keep a revert database with good references to wiki_db, rev_id so it can be brought back in context. Personally, I would love to get better revert detection, using sha1 exact matches doesn't really get to the heart of the issue. Important phenomena like revert wars, bullying, and stalking are hiding behind bad revert detection. I'm happy to brainstorm ways we can use Analytics infrastructure to do this. We definitely have the tools necessary, but not so much the man-power. That said, please don't strip out rev_sha1 until we've accounted for all its "data customers". So, put another way, I think it's totally fine if we say ok everyone, from date XYZ, you will no longer have rev_sha1 in the database, but if you want to know whether an edit reverts a previous edit or a series of edits, go *HERE*. That's fine. And just for context, here's how we do our revert detection in Hadoop (it's pretty fancy) [2]. [1] https://github.com/mediawiki-utilities/python-mwreverts [2] https://github.com/wikimedia/analytics-refinery-source/blob/1d38b8e4acfd10d… On Mon, Sep 18, 2017 at 9:19 AM, Daniel Kinzler &lt;daniel.kinzler(a)wikimedia.de

I am not a mediawiki developer, but shouldn't sha1 be moved instead of deleted/not deleted? Moved to the content table- so it is kept unaltered. That way it can be used for all the goals that have been discussed (detect reversions, XML dumps, etc.) and they are not altered, just moved away (being more compatible). And it is not like structure compatibility is going to be kept, as many fields are going to be "moved" there, so code using the tables directly has to change anyway; but if the actual content is not altered, the sha field can be kept unaltered with the same value as before. It would also allow to detect a "partial revertion", that means, mediawiki text is set to the same than a previous one, which is what I assume it is used now. However, now there will be other content that can be reverted individually. I do not know what exactly MCR is going to be used for, but if (silly idea), main text article and categories are 2 different contents of an article, if user A edits both, and user B reverts the text only, that would get a different revision sha1 value; however, most reasons here would want to detect the reversion by checking the sha of the text only (aka content). Equally, for backwards compatibility, storing it on content would allow to not have to recalculate it for all already existing values literally reducing it to a "trivial" code change, while keeping all old data valid. Keeping the field as is, on revision, will mean all historical data and old dumps are invalid. Full revision reversions, if needed, can be checked by checking each individual content sha or the linked content ids. If, on the other side, revision should be kept completely backwards compatible, some helper views can be created on the cloud wikireplicas, but other than that, MCR would not be possible. If at a later time, text with the same hash is detected (and content double checked), content could be normalized by assigning the same id to the same content? On Mon, Sep 18, 2017 at 8:25 PM, Danny B. &lt;Wikipedia.Danny.B(a)email.cz&gt; wrote: -- Jaime Crespo <http://wikimedia.org>

There are two important use cases; one where you want to identify previous reverts, and one where you want to identify close matches. There are other ways to do the first than to use a digest, but the digest opens up for alternate client side algorithms. The last would typically be done by some locally sensitive hashing. In both cases you don't want to download the content of each revision, that is exactly why you want to use some kind of hashes. If the hashes could be requested somehow, perhaps as part of the API, then it should be sufficient. Those hashes could be part of the XML dump too, but if you have the XML-dump and know the algorithm, then you don't need the digest. There are a specific use case when someone want to verify the content. In those cases you don't want to identify a previous revert, you want to check whether someone has tempered with the downloaded content. As you don't know who might have tempered with the content you should also question the digest delivered by WMF, thus the digest in the database isn't good enough as it is right now. Instead of a sha-digest each revision should be properly signed, but then if you can't trust WMF can you trust their signature? Signatures for revisions should probably be delivered by some external entity and not WMF itselves. On Fri, Sep 15, 2017 at 11:44 PM, Daniel Kinzler < daniel.kinzler(a)wikimedia.de&gt; wrote:

The revision hashes are also supposed to be used by at least some of the import tools for XML dumps. The dumps would be less valuable without some way to check their content. Generating hashes on the fly is surely not an option given exports can also need to happen within the time of a PHP request (Special:Export for instance). Nemo

Am 21.09.2017 um 11:24 schrieb Federico Leva (Nemo): While this is a typical use cacse for hashes in theory, i have never heard of any MediaWiki related tool actually doing this. Hashing is a lot faster than loading the content. Since Special:Export needs to load the content anyway, the extra cost of hashing is negligible. If we only need the hashes in contexts where we also need the full content, generating on the fly should work fine. But if we need revision hashes in a list of 500 revisions returned from the API, *that* we can't calculate on the fly. Similarly, database queries that need the hashes to detect revisions with the same content can't use on-the-fly hashes. -- Daniel Kinzler Principal Platform Engineer Wikimedia Deutschland Gesellschaft zur Förderung Freien Wissens e.V.

What is the current state, will some kind of digest be retained? On Thu, Sep 21, 2017 at 9:56 PM, Gergo Tisza &lt;gtisza(a)wikimedia.org&gt; wrote: