Hello!

I have just migrated a site from local to live server - IONOS (ex 1and1). I encountered the problem described in this topic https://www.couchcms.com/forum/viewtopic.php?f=4&t=12149&hilit=Statement+violates+GTID+consistency%3A - so I followed KK's advice and used phpMyAdmin to export/import the database.

That all appears to have worked successfully . However, when I try to delete a cloned page from within the Admin Panel it fails with the following error :

Could not successfully run query: Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

The site is using MySQL 5.5 and PHP 7.4 - I tried PHP 7.2 and same problem (that is the oldest version of PHP that IONOS will support without charging a silly amount each month)... any suggestions gratefully received.

P.S. same thing happened when I tried to create a new cloned page!

[KK]

Hi,

The problem has nothing to do with the version of PHP in use; it is a specific configuration of MySQL server (Replication using GTIDs).

Strange thing is that Ionos seems to be only hosting provider out there using this particular configuration (as none of the reports mention any other host).

Unfortunately, there is no solution for it in Couch at the moment, I regret to say.

I have a number of sites with IONOS/1and1 with earlier versions of Couch that have and still do work fine. I have no idea what this actually means - GTIDs? IONOS are such a big hosting provider I would expect a number of other Couch users must be experiencing this too? So does it mean I have to get the client to change hosting provider to enable the site to function?

[KK]

I have a number of sites with IONOS/1and1 with earlier versions of Couch that have and still do work fine.

I think only MySQL 5.6+ supports the configuration in question. Any lesser version should still work well on Ionos.

I would expect a number of other Couch users must be experiencing this too?

So far, the only reports we have had (apart for yours) is from the thread you have already seen - viewtopic.php?f=4&t=12149

So does it mean I have to get the client to change hosting provider to enable the site to function?

Please try asking Ionos if you can switch to MySQL 5.5. That should solve the issue.
Else, I am sorry, switching host would be the only available alternative currently.

Update: the response from IONOS - "We regret to inform you that on 6 February 2020, the option to create MySQL 5.5 databases in Linux Shared Hosting was removed. All new databases will now be created with MySQL 5.7."

As it happens this particular client has another site/database using MySQL 5.5 and I can use it for their new site.

I also use TSOHost for hosting some clients and I asked them about the MySQL version - it is 10.2.22-MariaDB. And I checked with GoDaddy and it is MySQL 5.6.47. Neither could go into the details concerning "Replication using GTIDs". They referred me back to the developer of the CMS to ensure compatibility!

I'm not sure what the implications are of this ... how will a Couch user know if their chosen shared hosting is compatible or not? This is the first such issue I've had. Presumably the troublesome option "Replication using GTIDs" must be used for a reason?

[KK]

how will a Couch user know if their chosen shared hosting is compatible or not?

As I mentioned before, Ionos seems to be the only host that is using the GTID configuration for its shared hosting.
Haven't had any such report from any other hosting provider.

So, I think. it'd be safe to assume that except for Ionos shared hosting, there is no incompatibility with any other hosting.

[trendoman]

I believe that a software is a vulnerable child. Maybe even a new form of existence, and is easily abused, not fed enough, hardly respected. Also, I believe that people should be introduced to better experience when it exists.

My implications are -
1. a patch to Couch can be done to mitigate that problem.
2. Ionos kicked our CouchCMS in the butt, really bad, so it stopped working.

Can we bomb them or something?

[KK]

a patch to Couch can be done to mitigate that problem

Yes, ideally that is what needs to be done. The patch, however, entails some very major changes to the way queries are being formulated currently. This is making me kinda reluctant to get going with it.

For now the problem is pretty limited. In case, things escalate then there would be no option but to bite the bullet.

[trendoman]

I have a patch that can also be installed without touching Couch code (using my addon "dbxoid" viewtopic.php?f=8&t=12757) but I don't have access to Ionos hosting to test how it works and tweak.
Of course, I am not interested in requesting their demo or otherwise go extra mile, so if anyone wants to offer their hosting account for a thourough test for a day, contact me tony.smirnov@gmail.com or PM.

[KK]

SOLUTION:

If the MySQL version in use happens to be 5.6.4 or higher. following is a way of rectifying the issue under discussion.

I'll use phpMyAdmin to show how to implement the solution but you are free to use any other way you are comfortable with while dealing with MySQL.

Caution: As with all operations involving manipulating the database directly, it is strongly advised to take a backup of your database at this point.

1. Log into your phpMyAdmin and you should see a list of tables your installation of Couch uses -

Untitled-1.png (41.24 KiB) Viewed 1650 times

Please note that there is only *one* table in the list that is of type 'MyISAM'; all the rest are of type 'InnoDB'.
Note down the name of this table (by default it is 'couch_fulltext' but it could be slightly different if you have configured your Couch installation to use a 'prefix' for tables).

The solution is simply to change the type of this table to 'InnoDB' so that it uses the same engine as the rest of the tables.

2. Before we make this change, let us make sure that the database version is indeed 5.6.4 or higher.
To do that, click on the 'SQL' tab in the right-side pane -

Untitled-3.png (17.73 KiB) Viewed 1650 times

Paste SELECT VERSION(); into the text box and then press the 'GO' button at bottom-right -

Untitled-4.png (17.89 KiB) Viewed 1650 times

Take a look at the result - it should be something greater or equal to '5.6.4'.

3. Now that we have ensured that the MySQL version is right, let us finally go ahead and change the table's type.
To do that, again click the same 'SQL' tab we used in step 2 above.
This time, paste the following in the textbox -

Code: Select all

ALTER TABLE couch_fulltext ENGINE=InnoDB; 

Please make sure to use the correct name for the table (we found this name out in step 1).

Click 'GO' and that should change the table type. Confirm this by taking a look at the table list again.

With this change, the issue under discussion should get resolved.
Hope this helps.