ERROR: Upgrade failed: DB: Statement violates GTID consistency

Software Support
,
1

I’m trying to upgrade from 2.4.8.4 to the latest version, and I’m getting the following error:

[data: dbscripts/xml/upgrade/3.0.0_preupdate.xml]
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
PHP Notice:  Only variables should be assigned by reference in /usr/home/luizborges/www/public_html/lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122
ERROR: Upgrade failed: DB: Statement violates GTID consistency: CREATE TABLE ... SELECT.

The client uses MySQL 8.0 and the server is using MySQL 5.7 with GTID enabled (and according to the admins, it has to remain enabled). A few google searches suggested that the problem is the SQL used and the best solution is to rewrite it.

What can I do?

2

Digging a little deeper I’ve found the culprit in [data: dbscripts/xml/upgrade/3.1.0_preupdate_review_assignments.xml]

Here is the problematic query:
CREATE TABLE review_assignments_tmp AS (SELECT review_id FROM review_assignments WHERE cancelled = 1)

There is also the PHP Notice regarding variables as reference in /lib/pkp/classes/db/DBDataXMLParser.inc.php on line 122

What to do?

3

Hi all,

This has also been posted at Upgrade fails when database server has GTID enabled · Issue #5133 · pkp/pkp-lib · GitHub.

Regards,
Alec Smecher
Public Knowledge Project Team

4

Hi there,

I know there’s another post about upgrading from 2.x to 3.x and getting this error but I’m upgrading from 3.1.1.4 to 3.2.1.1 and get the following error

Errors occurred during installation:

A database error has occurred: Statement violates GTID consistency: CREATE TABLE ... SELECT.

I’m on shared hosting, so doubt there’s anything I can do about replication settings. Any workarounds?

Thanks!

Matt

5

Hi @matthewbarr,

Here’s the work-around I’d recommend if you’re unable to temporarily disable replication: download the database, code etc. to your local machine (with a WAMP/XAMPP/etc. stack running there), run the upgrade script locally, and move the upgraded database etc. back to the server afterwards.

Regards,
Alec Smecher
Public Knowledge Project Team

6

Thank you @asmecher! I’ll give that a try this weekend.

7

Well I tried it. After hours of fixing problems (e.g. ’ ERROR: Upgrade failed: DB: Column 'publication_id' cannot be null', ‘Duplicate column name ‘email_id’’ and more) I thought it had worked but now there’s something up with the themes: https://press-start.gla.ac.uk/index.php?journal=press-start&page=index

Trying to change the theme to the Default (or the child theme I had previously created and used pre-update) then hitting Save on the Appearance tab of the Website Settings page gives this error:

The requested URL was not recognized.

Right now, I don’t think it’s picking up any theme.

8

Looking at the PHP error log, I see this:

[01-Aug-2020 12:52:42 UTC] PHP Fatal error: Uncaught Error: Call to a member function getOption() on null in /press-start/cache/t_compile/686e039e7ee567f4c8f82663c4275f0d0ddefe5f^e64e579388e5615e11e939b12134d6ae8679eba6_0.app.frontendpagesindexJournal.php:36

Just in case my old theme was outdated, I installed the Bootstrap theme from within OJS plugin settings but when I try to save the settings, I still get the ‘The requested URL was not recognized.’ error.

As before the upgrade, our htaccess has a rewrite rule as follows:

RewriteEngine On
RewriteCond %{HTTP_HOST} ^www.press-start.gla.ac.uk [NC]
RewriteCond %{SERVER_PORT} 80
RewriteRule ^(.*)$ https://press-start.gla.ac.uk/$1 [R,L]

Not sure if it’s to do with that.

9

Hi @matthewbarr,

It looks like you’re using the disable_path_info setting in config.inc.php. I’d suggest disabling that.

Regards,
Alec Smecher
Public Knowledge Project Team

10

Thanks @asmecher

Turning that off results in the site becoming unavailable. In Firefox, the error is:

The page isn't redirecting properly

An error occurred during a connection to press-start.gla.ac.uk.

    This problem can sometimes be caused by disabling or refusing to accept cookies.
11

Think I’m going to revert back to the old version of the site (just in case anyone clicking on the URL above sees that it’s ‘fixed’ - it isn’t!)

12

Hi @matthewbarr,

If there site isn’t working with that option disabled, you’ll have to tweak the web server configuration so that path_info URLs are properly supported. You’re so close to finishing the upgrade and this is a bridge that you’ll have to cross sooner or later!

Regards,
Alec Smecher
Public Knowledge Project Team

13

Is there a guide to tweaking the server? I did a bit of googling based on the comment in the config file about disable_path_info being deprecated but I’m not sure how path info is supposed to work here/how to fix the broken config.

14

Hi @matthewbarr,

It’s pretty web server dependent, I’m afraid, so hard to give specific advice – but see this topic/post: About Restful Url with PHP CGI/FCGI - #2 by asmecher

Regards,
Alec Smecher
Public Knowledge Project Team

15

Thanks @asmecher

I managed to get PATH_INFO set using the php.ini prepend hack here: [OJS3.0.2] Webserver delivers [ORIG_PATH_INFO] instead of [PATH_INFO] - #3 by twa

So, now when I go to a path like info.php/testing/matt and do print_r($_SERVER) I see [PATH_INFO] => /testing/matt at the end of the array.

But, when I set disable_path_info = On after that, I get nothing and setting restful_urls = On gives a 404. So, I’ve reverted back to the old version of the site again.

Grasping at straws but maybe there’s something missing from my .htaccess?

RewriteEngine On
RewriteCond %{HTTP_HOST} ^www.press-start.gla.ac.uk [NC]
RewriteCond %{SERVER_PORT} 80
RewriteRule ^(.*)$ https://press-start.gla.ac.uk/$1 [R,L]

Think it may be time to give up - I can’t keep knocking the site offline or asking users to hold off using it!

16

Hi @matthewbarr,

Did you mean setting disable_path_info to Off? That’s what you should be trying for.

I’d recommend starting with disable_path_info set to Off, restful_urls set to Off, and removing your rewrite rules. If that works, try re-adding your rewrite rules.

Regards,
Alec Smecher
Public Knowledge Project Team