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 https://github.com/pkp/pkp-lib/issues/5133.

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

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]

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