error: 1785 Statement violates GTID consistency

[dmajwool]

Hello Eugenio,

My previously working DaDaBik installation has just started to give error messages like this when I try to save changes to a record. Immediately before this failing I had deleted and replaced the table being queried with one having what I think is the same structure but newer data rows.

The phpbb_profile_fields_data table is MyISAM utf8_general_ci

Can you suggest what might be wrong to give an error like this?

[08] Error: during query execution. UPDATE `phpbb_profile_fields_data` SET `pf_title` = 'Mrs', `pf_firstname` = 'D', `pf_lastname` = 'zzzWool', `pf_address_one` = 'fldAddress1', `pf_address_two` = 'fldAddress2b', `pf_address_three` = 'fldAddress3', `pf_city` = 'fldCity', `pf_county` = 'fldCounty', `pf_postcode` = 'xxxxxx', `pf_emailsalutation` = 'David', `pf_postalsalutation` = 'David', `pf_telephone` = 111111111', `pf_yearjoined` = '2005', `pf_phpbb_interests` = 'Admin interests new_Edited', `pf_commsbypost` = '1', `pf_newsletterbypost` = '1', `pf_emailactive` = '2', `pf_showemail` = '2', `pf_showphone` = '2', `pf_showaddress` = '2', `pf_show_interests` = '2', `pf_gdpr_consent` = '2', `pf_cardorcards` = NULL, `pf_partnerto` = NULL where `user_id` = '2'
The DBMS server said: SQLSTATE[HY000]: General error: 1785 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.

Very many thanks,
David.
*************************************
Here's my config.
You are using DaDaBIK version 9.2-Monterosso pro, installed on 12-14-2018 (installation code: 141875c142df44e364), the latest version of DaDaBIK is 10.1-Manarola released on 03-09-2020
You are not running the last release of DaDaBIK, the release you are running might have bugs and security holes, see the official change log for further information. You can upgrade DaDaBIK here.
PHP Version: 7.2.30
mysql version: 5.7.30-log
Web server: Apache
Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.102 Safari/537.36 Edge/18.18363

 

[eugenio]

Hello,
did you or your hosting provider update MySQL recently? That could be the only reason I would say.

Starting from a particular version of MySQL, as the error suggests, you might have problem if you mix transactional and non-transactional tables if they are used in the same transaction.
Probably you have some MyISAM and some innodb tables in your db?

Is there any reason why you are using MyISAM? My suggestion is to move to innodb; here you can find some recommendations:

https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html

Best,

 

[dmajwool]

Hello Eugenio,

This is most curious, but there's a happy ending

I think your explanation that my issue is caused by a change in the MySQL environment is most probably correct.

Looking at recent backups of my phpbb database (which is quite old and has gone through a number of upgrades/revisions over the years) does indeed have a mixture of different tables as MyISAM and InnoDB . DaDABik has worked successfully with this database for a few years.

Then, a couple of days ago I started receiving the error message when trying to commit an edit in DaDaBik.

This evening I sat down to try & trace what was going on and I saw that all my tables are now InnoDB and that DaDaBik can commit edits without a problem.

I haven't run any ALTER TABLE statements in my database and so I suppose its possible that my shared hosting provider has adopted some new MySQL environment policy and it has taken them a few days to modify my databases to be completely consistent with the new environment.

I'll keep a close eye on it...

Many thanks for your support, again.

David.

 

[eugenio]

You are welcome!