I’m having some problems to upgrade. The previous problems was solved. They are listed here:
But, now I have a new problem, when I run:
php tools/upgrade.php upgrade
In the middle of processing I have a connection error with postgres.
Postgres log:
LOG: statement: INSERT INTO review_round_files (submission_id, review_round_id, stage_id, file_id, revision) SELECT afm.article_id, rr.review_round_id, 3, afm.file_id, afm.revision FROM article_files_migration afm, articles_migration am, review_rounds rr WHERE am.review_file_id = afm.file_id AND rr.submission_id = afm.article_id AND rr.round = afm.round
LOG: statement: UPDATE submission_files SET file_stage=2 FROM submission_files sf, articles_migration am WHERE am.submission_file_id=sf.file_id
LOG: server process (PID 11416) was terminated by signal 9: Killed
DETAIL: Failed process was running: UPDATE submission_files SET file_stage=2 FROM submission_files sf, articles_migration am WHERE am.submission_file_id=sf.file_id
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2016-11-01 21:47:20 BRST
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at AA/ACC09E10
LOG: unexpected pageaddr A9/B5BF2000 in log segment 00000001000000AA000000B4, offset 12525568
LOG: redo done at AA/B4BEF7B8
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
During the processing server uses only 3GB of 4 reserved for him.
When executing this command:
LOG: server process (PID 11416) was terminated by signal 9: Killed
DETAIL: Failed process was running: UPDATE submission_files SET file_stage=2 FROM submission_files sf, articles_migration am WHERE am.submission_file_id=sf.file_id
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
Postgres uses all memory and swap available on my computer. Then the process is killed by OOM killer.
It’s crazier than I can understand.
Can someone help me?
The query that’s being executed is fairly innocuous: (see dbscripts/xml/upgrade/3.0.0_update.xml)
<query driver="postgres7">UPDATE submission_files SET file_stage=2 FROM submission_files sf, articles_migration am WHERE am.submission_file_id=sf.file_id</query><!-- SUBMISSION_FILE_SUBMISSION -->
I suspect you might need to work a little with your PostgreSQL configuration to limit the amount of memory it expects to use.
Regards,
Alec Smecher
Public Knowledge Project Team
Yeah, I tested it again, with 16gb of RAM and 135GB of swap. OOM Killer killed postgres again, after 6 hours.
It’s insane, I’m shure about the limit of 4gb but I will double check postgresql.conf.
Maybe try running that query directly in the PostgreSQL client after a failed attempt to run an upgrade – just to ensure that there’s not something else going on?
Regards,
Alec Smecher
Public Knowledge Project Team
Take a look:
ojs3=# UPDATE submission_files SET file_stage = 2 where file_id in (select (file_id) FROM submission_files sf, articles_migration am WHERE am.submission_file_id=sf.file_id);
UPDATE 36354
Time: 860.080 ms
Is that ok? Can I change it inside xml and upgrade? It’s insane…
While this may work for Postgres, I’m pretty sure mysql will choke on an update of a table which is referenced in a subquery. The cross-platform statement might be better as:
UPDATE submission_files SET file_stage=2 WHERE submission_file_id IN (SELECT am.file_id FROM articles_migration am)
Yeah, because subquery will do a lot of ‘or’ and ‘or’ and ‘or’ and ‘or’ and ‘or’ and ‘or’ and ‘or’ and ‘or’…
Same problem with the rest of the same XML. 4 querys.
I rewrote them…
Testing…
Everything working in my test installation.
My institution will test and I will upgrade the real instalation in some moment in the future.
Thank you both.