Error upgrade from OJS 2.4.8 to OJS 3.1.2 with postgres11

marcus1 · June 7, 2019, 11:15am

Hi all,
I upgraded OJS from 2.3.7 to OJS 2.4.8 without any problem.
Upgrading from 2.4.8 to 3.1.2 first failed with “static” and installed the patch for that:
https://gist.github.com/asmecher/d1013624ff5325eae97384ae5adc88c4/raw/c636dded71570c195078c68fe2608f0efc552bdd/postgresql-serials.diff
That worked fine, but then I got stuck with the error: ojs2: DB Error: ERROR: relation “articles” does not exist
I read the post: Upgrade from 2.4 to 3.0.2 and 3.1 failed - #7 by gustavov
and applied patch: https://github.com/pkp/ojs/commit/4d3455f1f56b4a2d21d86456b6d3e84b00f6b87c.diff
I got a Hunk:

patching file dbscripts/xml/upgrade/3.0.0_update.xml
Hunk #1 FAILED at 93.
1 out of 1 hunk FAILED -- saving rejects to file dbscripts/xml/upgrade/3.0.0_update.xml.rej

3.0.0_update.xml.rej looks like this:

--- dbscripts/xml/upgrade/3.0.0_update.xml                                                                                                                                                                                                    
+++ dbscripts/xml/upgrade/3.0.0_update.xml                                                                                                                                                                                                    
@@ -93,13 +93,13 @@
        <sql>                                                                                                                                                                                                                                 
                <query driver="mysql">UPDATE submission_files sf, genres g, submissions s, article_galleys_migration agm SET sf.genre_id = g.genre_id WHERE g.entry_key = 'STYLE' AND g.context_id = s.context_id AND s.submission_id = sf.su\
bmission_id AND sf.file_id = agm.style_file_id</query>                                                                                                                                                                                        
                <query driver="mysqli">UPDATE submission_files sf, genres g, submissions s, article_galleys_migration agm SET sf.genre_id = g.genre_id WHERE g.entry_key = 'STYLE' AND g.context_id = s.context_id AND s.submission_id = sf.s\
ubmission_id AND sf.file_id = agm.style_file_id</query>                                                                                                                                                                                       
-               <query driver="postgres7">UPDATE submission_files SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = 'STYLE' AND g.context_id = s.context_id AND s.submission_id = sub\
mission_files.submission_id AND sf.file_id = agm.style_file_id</query>                                                                                                                                                                        
+               <query driver="postgres7">UPDATE submission_files sf SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = 'STYLE' AND g.context_id = s.context_id AND s.submission_id = \
sf.submission_files.submission_id AND sf.file_id = agm.style_file_id</query>                                                                                                                                                                  
        </sql>                                                                                                                                                                                                                                
        <!-- issue #2758: set assoc_type = 515 (ASSOC_TYPE_SUBMISSION_FILE) and the appropriate assoc_id for HTML galley CSS files -->                                                                                                        
        <sql>                                                                                                                                                                                                                                 
                <query driver="mysql">UPDATE submission_files sf, article_galleys_migration agm SET sf.assoc_type = 515, sf.assoc_id = agm.file_id WHERE sf.file_id = agm.style_file_id</query>                                               
                <query driver="mysqli">UPDATE submission_files sf, article_galleys_migration agm SET sf.assoc_type = 515, sf.assoc_id = agm.file_id WHERE sf.file_id = agm.style_file_id</query>                                              
-               <query driver="postgres7">UPDATE submission_files SET assoc_type = 515, assoc_id = agm.file_id FROM article_galleys_migration agm WHERE sf.file_id = agm.style_file_id</query>                                                
+               <query driver="postgres7">UPDATE submission_files sf SET assoc_type = 515, assoc_id = agm.file_id FROM article_galleys_migration agm WHERE sf.file_id = agm.style_file_id</query>                                             
        </sql>                                                                                                                                                                                                                                
        <!-- Bug #7745: Change no_NO to nb_NO -->                                                                                                                                                                                             
        <sql>

I tried to replace the two rows manually but I still got the error: ojs2: DB Error: ERROR: relation “articles” does not exist

I guess that the problem is that I use postgresql 11.

Thanks alot!

Marcus

gustavov · June 7, 2019, 12:12pm

Could you try the patch i’ve posted on the " Upgrade from 2.4 to 3.0.2 and 3.1 failed" thread?

If you use postgresql, apply the second patch too.

The patch on github didnt work for me also.

marcus1 · June 7, 2019, 6:38pm

Hi gustavov,
I tried both of your patches but I still get the error: ojs2: DB Error: ERROR: relation “articles” does not exist

marcus1 · June 9, 2019, 6:24pm

I installed and restored database in pg 9.6.
I applied the patches and run the upgrade again.
Still the same error: ojs2: DB Error: ERROR: relation “articles” does not exist
Should I first upgrade to 3.0 before I upgrade to 3.1?

marcus1 · June 9, 2019, 8:24pm

I am so sorry!! I did the rookie misstake and did not restore db between tests.
Now I get php errors, the error I get with the offical patches is:

PHP Warning:  Declaration of SubmissionLanguageEntryDAO::getByControlledVocabId($controlledVocabId, $rangeInfo = NULL) should be compatible with ControlledVocabEntryDAO::getByControlledVocabId($controlledVocabId, $rangeInfo = NULL, $filter = NULL) in /var/www/html/ojs/lib/pkp/classes/submission/SubmissionLanguageEntryDAO.inc.php on line 20
PHP Warning:  Declaration of SubmissionKeywordEntryDAO::getByControlledVocabId($controlledVocabId, $rangeInfo = NULL) should be compatible with ControlledVocabEntryDAO::getByControlledVocabId($controlledVocabId, $rangeInfo = NULL, $filter = NULL) in /var/www/html/ojs/lib/pkp/classes/submission/SubmissionKeywordEntryDAO.inc.php on line 45
PHP Warning:  Declaration of SubmissionAgencyEntryDAO::getByControlledVocabId($controlledVocabId, $rangeInfo = NULL) should be compatible with ControlledVocabEntryDAO::getByControlledVocabId($controlledVocabId, $rangeInfo = NULL, $filter = NULL) in /var/www/html/ojs/lib/pkp/classes/submission/SubmissionAgencyEntryDAO.inc.php on line 44
[data: dbscripts/xml/upgrade/3.0.0_update.xml]
ERROR: Upgrade failed: DB: ERROR:  missing FROM-clause entry for table "submission_files"
LINE 1: ...g.context_id = s.context_id AND s.submission_id = sf.submiss...

And when I run upgrade with gustavov patches, I get one warning:

PHP Warning: Element '<ahref|target><cite><code><ul><ol><li><dl><dt><dd><imgsrc|alt> ' is not supported (for information on implementing this, see the support forums) in /var/www/html/ojs/lib/pkp/lib/vendor/ezyang/htmlpurifier/library/HTMLPurifier/HTMLDefinition.php on line 311

and one error:

<h1>DB Error: ERROR:  syntax error at or near &quot;metrics&quot;
LINE 1: INSERT metrics SELECT * FROM metrics_supp
               ^</h1>ojs2: DB Error: ERROR:  syntax error at or near "metrics"
LINE 1: INSERT metrics SELECT * FROM metrics_supp

marcus1 · June 10, 2019, 12:44pm

I managed to upgrade to 3.0, when upgrading to 3.1.2 I first got the “static” error.
I applied patch: https://gist.github.com/asmecher/d1013624ff5325eae97384ae5adc88c4/raw/c636dded71570c195078c68fe2608f0efc552bdd/postgresql-serials.diff

After that I got the same php syntax error as before:

[code: Installer Installer::updateSuppFileMetrics]
<h1>DB Error: ERROR:  syntax error at or near &quot;metrics&quot;
LINE 1: INSERT metrics SELECT * FROM metrics_supp
               ^</h1>ojs2: DB Error: ERROR:  syntax error at or near "metrics"
LINE 1: INSERT metrics SELECT * FROM metrics_supp
               ^

asmecher · June 10, 2019, 4:59pm

Hi @marcus1,

Thanks for reporting – that’s a bug. Some PostgreSQL-incompatible SQL crept into the upgrade script. I’ve filed it here: PostgreSQL-incompatible SQL on upgrade: INSERT metrics · Issue #4823 · pkp/pkp-lib · GitHub

I think you can resolve this by editing the line in classes/install/Upgrade.inc.php (approx. line 2891) that reads…

$metricsDao->update('INSERT metrics SELECT * FROM metrics_supp');

…to…

$metricsDao->update('INSERT INTO metrics SELECT * FROM metrics_supp');

Please confirm whether that works for you and I’ll commit it for the next release.

Regards,
Alec Smecher
Public Knowledge Project Team

marcus1 · June 10, 2019, 6:39pm

Hi asmecher,
Thank you for the help, it works perfectly!!
I am happy to help!
Thank you for a great platform that suits our organization well!

Regards
Marcus

asmecher · June 10, 2019, 11:39pm

Hi @marcus1,

Excellent, thanks for confirming! That’ll be included in the next release of OJS.

Regards,
Alec Smecher
Public Knowledge Project Team