[OJS 3] Upgrade fail due to "out of sort memory"

Software Support
1

Hello all,

As I commented on the two posts I found on the issue (here, and here), we’re trying to upgrade our OJS 3 test and running into the “out of sort memory” problem:

[code: Installer Installer::convertEditorDecisionNotes]
PHP Fatal error: Uncaught Exception: DB Error: Out of sort memory, consider increasing server sort buffer size Query: SELECT sc.*, a.user_id FROM submission_comments sc, articles_migration a WHERE sc.submission_id = a.article_id AND sc.comment_type=2 ORDER BY sc.submission_id, sc.comment_id ASC in /var/www/ojs3/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /var/www/ojs3/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError()
#1 /var/www/ojs3/classes/install/Upgrade.inc.php(1660): DAO->retrieve()
#2 /var/www/ojs3/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->convertEditorDecisionNotes()
#3 /var/www/ojs3/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction()
#4 /var/www/ojs3/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#5 /var/www/ojs3/lib/pkp/classes/cliTool/UpgradeTool.inc.php(88): Installer->execute()
#6 /var/www/ojs3/lib/pkp/classes/cliTool/UpgradeTool.inc.php(64): UpgradeTool->upgrade()
#7 /var/www/ojs3/tools/upgrade.php(22): UpgradeTool->execute()
#8 {ma in /var/www/ojs3/lib/pkp/classes/db/DAO.inc.php on line 703

The question is that MySQL 8 doesn’t have a variable sort_buffer_size.
We added it to the conf. file to no effect.

Should we downgrade MySQL or use another DB server? Apparently MariaDB also has the same issue…

2

Hi @ramon,

MySQL 8 does indeed have a sort_buffer_size – see MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables for details.

Regards,
Alec Smecher
Public Knowledge Project Team

3

Hello all, @asmecher,

Our tests have revealed that it seems to be an incompatibility issue with MySQL 8.
We tested with MariaDB and it seems to have passed the upgrade problem.
Since downgrading the OS should not be a viable option, we’ll continue to test if possible.

There was also a serialization issue during the upgrade, related to OS locale configuration.
All locale MUST be compatible, especially if using languages with diacritics or other symbols not used in English. The database just doesn’t upgrade.

4

Hello all, @asmecher ,

Further testing has revealed that increasing the sort_buffer_size to 32M reduces MySQL 8 performance significantly.
Any suggestions as to how to best configure the server?
Should we downgrade to 5.7??

5

Hi @ramon,

The query associated with the error message is only executed during the upgrade process, so you could decrease the setting again after upgrading.

Regards,
Alec Smecher
Public Knowledge Project Team

6

Hello all, @asmecher,

We are glad to report that installation went fine on a testing server, with MariaDB and MySQL 8, although it took several hours.

We will begin testing on the production server environment.

7

Hi @ramon,

The slow upgrade will likely be much improved with the addition of Upgrade process performs slowly · Issue #5703 · pkp/pkp-lib · GitHub, which will be released in OJS 3.2.1, but can be applied quickly to 3.2.0-x as well using the commits at the link.

Regards,
Alec Smecher
Public Knowledge Project Team