Mysql 8 and ojs 3

Software Support
1

I am trying to upgrade from 2.4.8.3 to 3.1.1.4

I am using mysql 8.0.15
php 7.3.3
apache 2.4.6

Running on centos 7

During the upgrade of the database I got a DB Error:
Query: SELECT * FROM groups WHERE assoc_type = 256 AND assoc_id = 48 AND about_displayed = 1 ORDER BY context, seq failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
‘groups WHERE assoc_type = 256 AND assoc_id = 48 AND about_displayed = 1 ORDER BY’ at line 1
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘groups WHERE assoc_type = 256 AND assoc_id = 48 AND about_displayed = 1 ORDER BY’ at line 1

In mysql 8.0.2 GROUPS becomes a reserved word.

Does this mean that I will need to go to mysql 5.7?

Thank you, Lisa Blake
Library Systems Team
Richard J. Daley Library
University of Illinois in Chicago

2

Hi @loleary,

The groups table is only referred to during the OJS 2.x to 3.x upgrade process (it’s no longer present in OJS 3.x).

I’ve proposed a change at pkp/pkp-lib#4615 Quote reserved MySQL 8.x keyword by asmecher · Pull Request #2334 · pkp/ojs · GitHub that would properly quote the groups table name, so it should work with MySQL 8.0.3 or newer – could you try applying the patch, and confirming if it works as expected? If so, I can commit it for distribution in the next release.

Thanks,
Alec Smecher
Public Knowledge Project Team

3

Alec,

I believe that it worked. However, I cannot find the statement in the output that is generated during the update.

I looked for “ FROM groups” in the output. Or is that what the patch did. Does it just skip
around it now?

The job definitely got further but died on a duplicate key. Something that I will have to look into.

Thank you for your help.

Lisa

1 Like
4

If your duplicates are in the review_rounds table then here is a solution for cleaning up the database: Upgrade to 3.0.0: pkey violation on review_rounds · Issue #1921 · pkp/pkp-lib · GitHub

5

Thank you very much. :o)

6

Hi @loleary,

Thanks – I’ve committed that change for future releases!

Regards,
Alec Smecher
Public Knowledge Project Team