Omeka S & MySQL 8.0

[ckarpinski]

Hi,
My hosting updated to MySQL 8.0 and now the admin of my Omeka S is not working.
I have updated to the latest version of Omeka S

When i click on an item in the admin I get this error

Doctrine\DBAL\Exception\SyntaxErrorException
An exception occurred while executing ‘SELECT y.position FROM ( SELECT x.id_0 AS id, @rownum := @rownum + 1 AS position FROM ( SELECT r0_.id AS id_0, r0_.title AS title_1, r0_.is_public AS is_public_2, r0_.created AS created_3, r0_.modified AS modified_4, r0_.resource_type AS resource_type_5, r0_.owner_id AS owner_id_6, r0_.resource_class_id AS resource_class_id_7, r0_.resource_template_id AS resource_template_id_8, r0_.thumbnail_id AS thumbnail_id_9 FROM item i1_ INNER JOIN resource r0_ ON i1_.id = r0_.id GROUP BY r0_.id ORDER BY r0_.created DESC, r0_.id DESC ) AS x JOIN (SELECT @rownum := 0) AS row ) AS y WHERE y.id = ?;’ with params [12617]: SQLSTATE[42000]: Syntax error or access violation: 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 ‘row ) AS y WHERE y.id = ‘12617’’ at line 7

The site itself is visible, and I can see the items on the front end and I can navigate the rest of the admin. Is there anything I can do to fix this?

Christy

[Daniel_KM]

It doesn’t look for an issue in omeka, but in a module. Have you an uncommon module?

[ckarpinski]

OH thank you so much! It is the “Next” plugin that is causing the issue. I was using it to create previous and next buttons.

[Daniel_KM]

Since version 5.7, mysql changed a default option “only_full_group_by”, so when your hosting changed its version, the issue occurred.

I updated nearly all my requests in my modules to support the new options, but some queries are still using the old option.

And here the issue is different: “row” became a reserved word in version 8.0.2. So you can try the last version of the module (not released yet, download master from gitlab/github) or fix the two lines here.

[ckarpinski]

That worked. thank you so much for this, I really appreciate your plugin