In T107254#2509147, @Nemo_bis wrote:

I don't think it's acceptable that now users (well, one user) have to manually re-mark as duplicate every single duplicated bug

Closed duplicates are a special case. https://secure.phabricator.com/T12234#225298 provides information about what would be required in the database.

In T107254#3238369, @Aklapper wrote:

And of course we'd need to have a list of the tasks to actually manipulate.

Not covering tasks resolved as duplicates:

SELECT maniphest_task.id, maniphest_task.phid, maniphest_task.status 
FROM maniphest_task 
WHERE (maniphest_task.status != "open" AND maniphest_task.status != "stalled" AND maniphest_task.status != "duplicate") 
AND maniphest_task.id > 2000 AND maniphest_task.id < 75682 
AND maniphest_task.phid NOT IN 
(SELECT maniphest_task.phid 
FROM maniphest_task 
JOIN maniphest_transaction 
WHERE maniphest_task.phid = maniphest_transaction.objectPHID 
AND maniphest_transaction.dateModified > 1416697200 
AND maniphest_transaction.transactionType = "status" 
AND (maniphest_transaction.oldValue = "\"open\"" OR maniphest_transaction.oldValue = "\"stalled\"") 
AND (maniphest_transaction.newValue != "\"open\"" AND maniphest_transaction.newValue != "\"stalled\""));

The query explicitly excludes resolved tasks with status set to duplicate in the maniphest_task table, as proper fiddling with "resolved as dup" tasks requires inserting two rows in the maniphest_transaction table (one transactionType value is core:edge and the other one is mergedinto instead of status) and also changes in the edge table. See my previous comment.
1416697200 is the epoch when the first task T75682 was created after migration, as we only want resolved tasks which did not see any status change after 2014-11-23 and which are still resolved (but not resolved as a duplicate).

For the records, the latest Phab version introduced the rows phabricator_maniphest.maniphest_task.closedEpoch and phabricator_maniphest.maniphest_task.closerPHID (which are NULL by default).

In T107254#4000910, @Aklapper wrote:

For the records, the latest Phab version introduced the rows phabricator_maniphest.maniphest_task.closedEpoch and phabricator_maniphest.maniphest_task.closerPHID (which are NULL by default).

Now we have a good place for the data, do we have the data easily extractable?

In T107254#4005100, @greg wrote:

Now we have a good place for the data, do we have the data easily extractable?

Quoting @chasemp from T107254#1782299:

TLDR: we don't seem to have the metadata in our migration db to provide this

However if we are only after fixing T107254#3235431, mass-setting phabricator_maniphest.maniphest_task.closedEpoch to something slightly smaller than 1416697200 wherever missing (see SQL query above) might be sufficient? Some bits and pieces from T107254#3238369 and T107254#3297161, basically.

Seems like Phabricator has a migration script to populate the closedEpoch closerPHID fields: resources/sql/autopatches/20180208.maniphest.02.populate.php

@bd808 eventually noticed it had a faulty SQL to create the closerPHID field and proposed a patch to the script {D1134}. Maybe it is sufficient to populate them.

In T107254#4848280, @hashar wrote:

Seems like Phabricator has a migration script to populate the closedEpoch closerPHID fields: resources/sql/autopatches/20180208.maniphest.02.populate.php

@bd808 eventually noticed it had a faulty SQL to create the closerPHID field and proposed a patch to the script {D1134}. Maybe it is sufficient to populate them.

That migration only makes the column available in a new Phabricator deployment. It doesn't do anything to populate the column.

I assumed it populates it due to the presence of 'UPDATE %T SET closedEpoch = %d, closerPHID = %ns WHERE id = %d',. Then there are few conditions that skips that update (task already closed for example). At least the script has some interesting logic for this task :)

Uhm. Is this still a requested thing in 2023? 🤔

@valerio.bozzolan Yes

I wonder who could contribute to this, since nowadays this is just a redirect:

https://bugzilla.wikimedia.org/createaccount.cgi

That's off-topic. This ticket is about DB content, not some websites.

I still need an help to understand if also these are off-topic :)

1. the description mentions a broken link http://korma.wmflabs.org/browser/maniphest.html
2. this is tagged Bugzilla-Migration but it's unclear how to obtain the necessary data from the old bugzilla since it seems a redirect to Phabricator to me
3. we are mentioning a database, but it's not clear to me who has access to that

1. That's https://wikimedia.biterg.io/app/kibana#/dashboard/Maniphest now
2. Either scrape https://static-bugzilla.wikimedia.org/ ; or workaround without the necessary data in T107254#3238369 and T107254#3297161
3. phabricator_maniphest.maniphest_task

Wow! nice dashboard! In this case it would be useful to understand what Kibana is reading now.

But probably we can start inspecting what we have.

SELECT * FROM phabricator_maniphest.maniphest_task WHERE id = 2002;

If we are lucky, we have just the column closedEpoch to NULL by mistake.

In T107254#8962942, @valerio.bozzolan wrote:

Wow! nice dashboard! In this case it would be useful to understand what Kibana is reading now.

It does not matter what Kibana does. See T107254#3235431.

But probably we can start inspecting what we have.

We already know it's empty. See T107254#3238369 why.

Ah I see the point! Thanks

I can try to scrape Bugzilla to generate this export:

BugZillaTaskID1,DateClosed1
BugZillaTaskID2,DateClosed2
BugZillaTaskID3,DateClosed3

We can probably update the column at this stage, without generating the corresponding transaction. The risk otherwise is to generate a thousand fake notifications.

Here the repo

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/

In this exact moment I arrived at Task ~4166 going very slowly to be nice with the servers. Let's wait, keeping my laptop online until tonight.

I reached bug 4.321 🎉 but I have to stop for a while.

If you have 1 minute, feel free to inspect some random entries:

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/master/data/bugzilla.json

Where you see NULL, we should probably don't care since it was never formally closed in Bugzilla.

It looks like you have no special handling for bugs that were closed and then reopened, so e.g. bug 1 (T2001) is listed as having been resolved in 2006. I don't know if these events should be migrated or not?

In T107254#8999820, @valerio.bozzolan wrote:

Fortunately the Glorious Bugzilla Archivers, also archived these pages:

https://static-bugzilla.wikimedia.org/show_activity.cgi?id=73

Woah, I kind of remember doing this and thinking something like "better save the activity as well". It's cool to read years later unexpectedly in another context that it did matter.

In T107254#9006822, @matmarex wrote:

It looks like you have no special handling for bugs that were closed and then reopened, so e.g. bug 1 (T2001) is listed as having been resolved in 2006

Indeed, the JSON file says

"73": {
    "lastStatus": "RESOLVED",
    "lastDate": "2005-03-23 21:58:11 UTC",
    "lastActor": "erik"
},

and that's the first event, https://static-bugzilla.wikimedia.org/show_activity.cgi?id=73 says that it got ultimately resolved on 2005-05-22 00:46:39 UTC.

Uh, thanks for the bug report

Another funny thing:

Sometime the column "What" can contain "Assignee", "Blocks", "CC". And that is nice.

But... sometime it contains this :D

..
                <a href="attachment.cgi?id=58">
                Attachment #58</a>
                Attachment is obsolete

Why is that in the logical Heading than in the value? Uh? UH? Only Bugzilla knows.

Does this make sense now (CSV)?

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/bef42057090f29ab76ba2a7d48d6a320c6e32a4a/data/bugzilla.csv

Also, do you somehow like the full history exported in this sommehow machine-readable format, for the glory of future data-miners?

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/bef42057090f29ab76ba2a7d48d6a320c6e32a4a/data/bugzilla.json

Interestingly the reported bug seems fixed now (?)

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/master/data/bugzilla.csv

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/master/data/bugzilla.json

It also skips the WORKSFORME status, since it's not a Phabric-able status...

Interesting indicative stats so far

[FIXED] => 2948
[DUPLICATE] => 1100
[INVALID] => 813
[WONTFIX] => 638
[RESOLVED] => 484
[] => 218
[CLOSED] => 177
[REOPENED] => 131

Edited: where the [] means "no status" and so "open"

Not sure how to read this...
"Resolved", "Reopened", "Verified", "Closed" were statuses; "Invalid", "Worksforme", "Duplicate", "Wontfixed", "Fixed" were resolutions of the status "Resolved":
https://upload.wikimedia.org/wikipedia/commons/archive/5/57/20141207044352%21Bug_Life_Cycle_Diagram.png .
Also see the "Status (and related Resolution) options" table on https://www.mediawiki.org/wiki/Phabricator/versus_Bugzilla#Bugzilla_data_migrated

OK so from a Phabricator perspective these are the most phab-nonsense "status changes":

• ---, this is just NULL, nonsense
• ASSIGNED: changed the assignee, not the status
• LATER, changed when, not the status
• NEW, this just means I'm new, and the status never changed
• PATCH_TO_REVIEW, this is a corner-case for "in progress" but better to ignore
• REMIND, this does not indicate a status change
• UNCONFIRMED, this indicates reproducibility, not status change
• VERIFIED, this indicates reproducibility, not status change
• WORKSFORME, this indicates reproducibility, not status change

The script skipped all of these.

It takes just the very last (→ the most meaningful) status. Here available for download:

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/raw/5dd57479ca6bd565f1110f9ed974041a5657fb21/data/bugzilla.csv?inline=false (permalink)

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/raw/master/data/bugzilla.csv (last)

In T107254#9026619, @valerio.bozzolan wrote:

The script skipped all of these. It takes just the very last (→ the most meaningful) status.

Statuses were only {UNCONFIRMED, NEW, ASSIGNED, PATCH_TO_REVIEW, REOPENED, RESOLVED, VERIFIED, CLOSED} (not sure we had or used the last one).
{FIXED, LATER, REMIND, WORKSFORME, WONTFIX, INVALID, DUPLICATE} were resolutions of one of the non-open statuses {RESOLVED, VERIFIED, CLOSED}, except for the {---} resolution in case of an open status {UNCONFIRMED, NEW, ASSIGNED, PATCH_TO_REVIEW, REOPENED}.

IMO the underlying problem is that in 2014 we did not import into the Phab DB the calendar date when a ticket's status was changed to RESOLVED for the last time.

In T107254#9026648, @valerio.bozzolan wrote:

Chapter II: understanding what should be updated, from what

Simplified, imported closed tickets with closedEpoch === null and no status changes since the import. [1] That's currently 57035 tickets.
closedEpoch should be updated to say when the ticket's status was changed to RESOLVED (Bugzilla status) for the last time.

Phabricator ID	Phabricator Closed Epoch	Phabricator Closed PHID	Bugzilla ID
numeric	numeric / null	string / null	numeric

To obtain that, it seems we can look at how Bugzilla redirects to Phabricator.

Phabricator task ID = Bugzilla ticket ID + 2000. I do not know what "Phabricator Closed PHID" means here, sorry. :(

[1] SELECT maniphest_task.id, maniphest_task.phid, maniphest_task.status, closedEpoch FROM maniphest_task WHERE (maniphest_task.status != "open" AND maniphest_task.status != "stalled" AND maniphest_task.status != "duplicate" AND maniphest_task.status != "progress") AND maniphest_task.id > 2000 AND maniphest_task.id < 75682 AND maniphest_task.phid NOT IN (SELECT maniphest_task.phid FROM maniphest_task JOIN maniphest_transaction WHERE maniphest_task.phid = maniphest_transaction.objectPHID AND maniphest_transaction.dateModified > 1416697200 AND maniphest_transaction.transactionType = "status" AND (maniphest_transaction.oldValue = "\"open\"" OR maniphest_transaction.oldValue = "\"stalled\"" OR maniphest_transaction.oldValue = "\"progress\"") AND (maniphest_transaction.newValue != "\"open\"" AND maniphest_transaction.newValue != "\"stalled\"" AND maniphest_transaction.newValue != "\"progress\"")) AND closedEpoch IS NULL;

Phabricator task ID = Bugzilla ticket ID + 2000

Whoa! thanks.

SELECT ...

Whoa! Can I somehow surf that lovely data? 🤩 maybe in a pastebin? Also please with the closerPHID column

(With "Phabricator Closed PHID" I just mean the "string identified of the person who closed that Task" - it's the column closerPHID in maniphest_task table)

Whoa! Can I somehow surf that lovely data? 🤩 maybe in a pastebin?

P49618

Also please with the closerPHID column

There are no tasks WHERE closedEpoch IS NULL AND closerPHID IS NOT NULL. Both columns are NULL in the Paste above.

Yeah having closerPHID as NULL has sense since you already filtered by maniphest_task.status != "open" AND maniphest_task.status != "stalled" AND maniphest_task.status != "duplicate" AND maniphest_task.status != "progress" and so there is no closer.

Thanks for the paste 👍

Let's join this lovely pastebin with our scraped stuff

Got it. In this case I'll just untag Collab.

To summarize: Would need to perform 57008 times the following steps:

• take the $PhabID
• subtract 2000 from the $PhabID to get the $BzID
• pull https://static-bugzilla.wikimedia.org/show_activity.cgi?id=$BzID (e.g. $data = file_get_contents('https://fooo');)
• scrape the last (!!) line where What==Statusand Added==RESOLVED to get the When value from that line - maybe https://nyamsprod.com/blog/extracting-data-from-html-table-in-php/ or such could be handy, because need to also handle HTML table rowspan etc
• convert the When value to epoch to get $epoch (e.g. echo strtotime("2014-11-21 00:15:49 UTC");)
• UPDATE phabricator_maniphest.maniphest_task SET closedEpoch = $epoch WHERE closedEpoch IS NULL AND id = $PhabID;

I obviously do not care who set the closed status (we cannot match non-existing accounts!), and I obviously do not care about creating fake transactions in the DB, but would only set that one closedEpoch column value, if at all.

Note: we have dump of Bugzilla data at https://dumps.wikimedia.org/other/bugzilla/ , as static html file and a database dump (without emails)

@Aklapper

git clone https://gitlab.wikimedia.org/repos/sre/miscweb/bugzilla.git

cd html-compressed

tar xzf static-bugzilla.gz

cd static-bugzilla

grep bz_status_RESOLVED activity2.html

bz_status_RESOLVED  bz_closed"

In T107254#9702401, @Dzahn wrote:

git clone https://gitlab.wikimedia.org/repos/sre/miscweb/bugzilla.git

Thanks, that might turn out helpful. Note to myself: Requires installing git-lfs beforehand.

grep bz_status_RESOLVED activity2.html

bz_status_RESOLVED  bz_closed"

That's about other tickets linked via "Depends on" etc. It is unrelated to the ticket status itself.

Had another quick look and managed to get down to have only <table>...</table> left in the local bug activity page. But then interesting problem is dealing with the rowspan: No date cell in the Status change row when several actions were performed at once and 'Status change' is not listed as first row)

Small note

This already takes in consideration the rowspan thing:

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser

So it could parse the website hosted locally correctly I think

@valerio.bozzolan I admit I didn't try to fully understand those 400 lines which seem to cover way way more stuff than needed here. :D Like why there is an array with statuses when we only care about last Status=Resolved etc. Or why care about ACTION_WITH_LINKS etc. (Also, stuff like WORKSFORME and REMIND and LATER and --- are no statuses but resolutions.) And if it would work on a static HTML dump like ours, instead of a real BZ instance.

P.S.

Have anybody already checked the file mentioned in the description, that is, maybe an useful starting point?

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/master/data/bugzilla.csv

stuff like WORKSFORME and REMIND and LATER and --- are no statuses but resolutions

Yep. That is exactly why WORKSFORME etc. are stored in a constant called NONSENSE_STATUSES - so we can ignore them in a strict and stable way 👍

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/5dd57479ca6bd565f1110f9ed974041a5657fb21/bugzilla-scraper.php#L43

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/5dd57479ca6bd565f1110f9ed974041a5657fb21/bugzilla-scraper.php#L89

BTW the real software starts at line 78 and ends at line 157 - that are 79 cute lines of code (comments included) without any import and without any alien library. Hoping to have shared something useful.

Anyway a required step is to take P49618 and make a nice phabricator.csv.

Where to upload that? pull request welcome in the data/ directory here:

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/

In T107254#9706866, @valerio.bozzolan wrote:

Have anybody already checked the file mentioned in the description, that is, maybe an useful starting point?
https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/master/data/bugzilla.csv

@valerio.bozzolan: I am sorry, I should have first checked that more thoroughly, indeed. :-( That could be quite useful!
Most data in the CSV file seems correct (yay). Code seems not to ignore VERIFIED and CLOSED statuses though, so e.g. bug 814 and bug 1924 have wrong data.

In T107254#9706654, @Aklapper wrote:

And if it would work on a static HTML dump like ours, instead of a real BZ instance.

Yeah. Since PHP is a lovely+scaring piece of glue that considers HTTP URLs as files that can be opened... (yes - file_get_contents() supports URLs lol) now our local dump is supported ✨

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/commit/d98b2aaff20e85db66d6172b82c9e117fcdec8e6

In T107254#9700462, @hashar wrote:

Note: we have dump of Bugzilla data at https://dumps.wikimedia.org/other/bugzilla/ , as static html file and a database dump (without emails)

OH YEAH - thanks again @hashar for reporting the existence

And... the script now takes seconds instead of weeks. Now we have enough extra Watts to shutdown a small nuclear plant somewhere 🌈

In T107254#9706981, @Aklapper wrote:

Most data in the CSV file seems correct (yay) [...] Code seems not to ignore VERIFIED and CLOSED statuses though, so e.g. bug 814 and bug 1924 have wrong data.

If you say that CLOSED should be ignored, let's ignore that 👌

Run again. Completed in 10 seconds instead of 10 weeks 🎇

Is this example change correct to you, @Aklapper ? If yes, I will commit the dataset again.

diff --git a/data/bugzilla.json b/data/bugzilla.json
index 195b7ba..629fee1 100644
--- a/data/bugzilla.json
+++ b/data/bugzilla.json
@@ -2500,9 +2500,9 @@
             ]
         },
         "3": {
-            "lastStatus": "CLOSED",
-            "lastDate": "2005-05-22 16:11:13 UTC",
-            "lastActor": "river",
+            "lastStatus": "FIXED",
+            "lastDate": "2004-08-13 23:46:18 UTC",
+            "lastActor": "brion",
             "history": [
                 [
                     "timwi",
@@ -4394,9 +4394,9 @@
             ]
         },
         "12": {
-            "lastStatus": "CLOSED",
-            "lastDate": "2004-08-31 00:54:34 UTC",
-            "lastActor": "hashar",
+            "lastStatus": "FIXED",
+            "lastDate": "2004-08-31 00:34:05 UTC",
+            "lastActor": "brion",
             "history": [
                 [
                     "timwi",
@@ -5059,9 +5059,9 @@
             ]
         },
         "18": {
-            "lastStatus": "CLOSED",
-            "lastDate": "2005-05-22 16:56:27 UTC",
-            "lastActor": "river",
+            "lastStatus": "FIXED",
+            "lastDate": "2004-08-14 08:34:41 UTC",
+            "lastActor": "brion",



....



         "814": {
-            "lastStatus": "CLOSED",
-            "lastDate": "2007-03-04 06:57:13 UTC",
+            "lastStatus": "FIXED",
+            "lastDate": "2007-03-04 06:55:33 UTC",
             "lastActor": "ryan.lane",


...


         "1924": {
-            "lastStatus": "CLOSED",
-            "lastDate": "2007-01-20 08:14:09 UTC",
+            "lastStatus": "WONTFIX",
+            "lastDate": "2007-01-20 08:13:33 UTC",
             "lastActor": "rotemliss",
             "history": [

....

Note: we have dump of Bugzilla data at https://dumps.wikimedia.org/other/bugzilla/ , as static html file and a database dump (without emails)

And... the script now takes seconds instead of weeks. Now we have enough extra Watts to shutdown a small nuclear plant somewhere 🌈

I had a good laugh on that one!! I am quite happy to see my finding served you well and dramatically sped up the script.

As for CLOSED, I guess we did that to prevent further comments and/or to indicate the task resolution got verified. Then.. I cant quite remember how we used Bugzilla 15/20 years ago :D I have looked at the activities of each of the bug listed in the diff (example: https://static-bugzilla.wikimedia.org/show_activity.cgi?id=1924 ) and the new lastActor looks fine to me.

@valerio.bozzolan The SQL statements and comments appear in contradiction. For every comment that says "Skipped bug X - state was REOPENED" there is in fact a real update statement that does exactly what the comment says it doesn't. Is this intentional?

In T107254#9710628, @Krinkle wrote:

@valerio.bozzolan The SQL statements and comments appear in contradiction. For every comment that says "Skipped bug X - state was REOPENED" there is in fact a real update statement that does exactly what the comment says it doesn't. Is this intentional?

Whooopsie, thanks! fixed :)

https://gitlab.wikimedia.org/valeriobozzolan/yet-another-bugzilla-parser/-/blob/eb6b9048c874c73d2eea1b550de7c92e72ebef2b/data/T107254-migration-commands.sql

In T107254#9710082, @hashar wrote:

I cant quite remember how we used Bugzilla 15/20 years ago

https://upload.wikimedia.org/wikipedia/commons/archive/5/57/20141207044352%21Bug_Life_Cycle_Diagram.png (with another "CLOSED" status reachable form "VERIFIED" which likely got switch off many many years ago so it wasn't switched on anymore when I made that diagram).

In T107254#9710082, @hashar wrote:

As for CLOSED, I guess we did that to prevent further comments and/or to indicate the task resolution got verified. Then.. I cant quite remember how we used Bugzilla 15/20 years ago :D

Of course we had a task about removing the "CLOSED" status from Bugzilla and that was T30121: Remove "CLOSED" status. Although the removal happened outside of that task, there is an indication the status got removed between March 2011 (when the task got filed) and June 2011 (Peachey88 comment indicates that got removed).