UPDATE metrics m, submission_file_settings sfs SET m.assoc_id = sfs.file_id WHERE m.assoc_type = 531 AND sfs.setting_name = 'old-supp-id' AND sfs.setting_value = m.assoc_id
Our metrics table has 3,3 million rows and submission_files_settings has 160 000 rows so the inner join command there seems to take for ages? It ran for 2 hours with no result.
The thing is that our metrics table has 0 hits for assoc_type 531, so two questions (@asmecher@bozana)
Should I be worried that there are no 531 rows? Why is that?
Any way of checking the query and making it faster? Of course I can just remove it, but probably others have large metrics tables as well.
Ok, so it seems that in ojs/3.1.0_update.xml at ojs-stable-3_1_0 · pkp/ojs · GitHub the script tries to add those 531 assoc_types. However, in the 3.1.0-1 upgrade script the 3.1.0_update_a.xml file is called before 3.1.0_update.xml, so basically the UPDATE query in 3.1.0_update_a.xml never has any hits, it is just a query that takes very very long to finish?
So basically the 3.1.0-1 package is flawed in the way that it does not update the metrics table as expected???
That would maybe make more sense because now the 531’s are added in 3.1.0_update.xml and that conversion is then ran in 3.1.1_update.xml.
So I am basically thinking of removing those sql’s from the 3.1.0_update_a script, because I know that there will be no hits, and maybe run a conversion if needed after the actual upgrade.
That sounds like a reasonable solution, and I believe the metrics can be re-migrated and processed after an upgrade (though you’d need to make sure you were merging new with old data if your upgrade went live in the meantime).
Regards,
Alec Smecher
Public Knowledge Project Team
Hmmm… I think the following query would be needed instead of ojs/3.1.0_update_a.xml at ojs-stable-3_1_0 · pkp/ojs · GitHub UPDATE metrics m, submission_file_settings sfs SET assoc_type = 531, m.assoc_id = sfs.file_id WHERE m.assoc_type = 515 AND sfs.setting_name = 'old-supp-id' AND sfs.setting_value = m.assoc_id.
Does that make sense? Do/did you have the entries with m.assoc_type = 515 AND sfs.setting_name = 'old-supp-id' AND sfs.setting_value = m.assoc_id in your DB table metrics? If so, I can make a PR with the fix…
Thanks a lot and sorry for the late response!
Bozana
No problem @bozana, I just had to proceed with the upgrade because there were so many important features we had promised the journals would have by the beginning of this year.
I have to check the details, but I removed the mentioned sql altogether simply because the WHERE clause it included did not return any hits in our database. I will check this again and get back to you.
Yes, ok so before the update from 3.0.2. to 3.1.0.1. we had no hits for assoc_type 531, so the query did not find anything. That is why I felt that I could remove it and maybe handle this later.
Edit: I tried this sql in my database: SELECT m.assoc_id FROM metrics m, submission_file_settings sfs WHERE m.assoc_type = 515 AND sfs.setting_name = 'old-supp-id' AND sfs.setting_value = m.assoc_id
edit: @bozana, that query does return 190 000 hits (after 30 minutes)
OK, yes – those are the views of the supp files from OJS 2.4.x and they should have the new assoc_type = 531 (i.e. ASSOC_TYPE_SUBMISSION_FILE_COUNTER_OTHER, because they should not be considered in the COUNTER article or journal report/numbers) instead of assoc_type = 515 (i.e. ASSOC_TYPE_SUBMISSION_FILE, that is considered in the COUNTER article or journal report/numbers). Also the assoc_id should be the new file_id.
I will provide a patch…
Hmmm… @ajnyga, now I do not understand why this code was not applied for you when you were updating to 3.0.2: ojs/3.0.0_postupgrade_metrics.xml at ojs-3_0_2-0 · pkp/ojs · GitHub
I.e. the SQL SELECT m.assoc_id FROM metrics m, submission_file_settings sfs WHERE m.assoc_type = 515 AND sfs.setting_name = 'old-supp-id' AND sfs.setting_value = m.assoc_id
should not return any row, because the assoc_ids were already set to file_ids in the 3.0.2 update.
When I see it well, with the upgrade to 3.0.2 you should have assoc_type = 515 and the assoc_id = file_id. Then when upgrading to 3.1.0-1 the code in 3.1.0_update.xml shold change 515 to 531. Thus, after the upgrade to 3.1.0-1 you should have all correct
Hmmm… I am so confused… :-\
I am so confused
I have to look this through in detail. I am not sure you should do any changes based on our database. When I figure out what the situation is, I can always do a custom upgrade script for 3.2. or whatever comes next.
So I basically have I slightly modified upgrade script. If I was able to follow this old discussion correctly, that should sort things out in our metrics database.
(edit: realize that this could take a considerable amount of time, maybe around 30-40 hours, but this is hopefully the last of the metrics table migrations at least for a while )
)