mySQL Replication Issue on Sign In

[jeffsikes]

I have been using the SAML-SP plugin in my QA environment, where it's a basic LAMP configuration. Everything has been working fine.

Our production environment uses a mySQL Server that is replicated to a backup mySQL database.

Specifically, we are using Rackspace with a mySQL Cloud Database, with their default config, which includes replication to another mySQL instance.

Now, when I try to log in a user, I am getting the message below.

It seems to be related to the replication process.

---

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. The SQL being executed was: INSERT INTO searchindex (keywords, elementId, attribute, fieldId, siteId) VALUES (' 979939 ', 5, 'username', 0, 1) ON DUPLICATE KEY UPDATE keywords=' 979939 ' Error Info: Array ( [0] => HY000 [1] => 1785 [2] => 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. ) ↵ Caused by: PDOException 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. n /var/www/vhosts/www.aanp.org/vendor/yiisoft/yii2/db/Command.php at line 1258

in /var/www/vhosts/www.aanp.org/vendor/flipboxfactory/saml-sp/src/services/login/User.php at line 119 – craft\services\Elements::saveElement(craft\elements\User) Database Exception – yii_db_Exception.pdf

[dsmrt]

Hi Jeff,

It looks like the username is an id of some sort with a space. Is the QA environment the same? Are the usernames numbers? That might be problematic for craft in general.

Also, it looks like there is a space which, when I try and save a user in the admin directly like that, gives me an error.

[AANP-ITSupport]

It's the same setup as on QA, but I can switch the username to something alpahnumeric. I'll give it a shot.

[dsmrt]

What versions of craft and saml-sp are you using?

[AANP-ITSupport]

Craft Pro 3.0.27.1 SAM SSO Service Provider 1.0.3.1

[nateiler]

I don't believe this issue is related to SAML. Search indexes are typically driven from Field Types. Is there a chance a new field type has been added recently?

You could also try to rebuild the search index as well and try again. Utilities -> Search Index - Update search indexes (button)

[dsmrt]

Possibly relevant links due to this: https://stackoverflow.com/questions/40724046/mysql-gtid-consistency-violation https://craftcms.stackexchange.com/questions/18996/internal-server-error-in-admin-after-migration

[AANP-ITSupport]

This is a fresh install with only Redactor and SAML plugins installed. I just installed today, it's still in the default configuration right now. The passing of the username is embedded deep in the plugin on the IDP side of things so it may take me a bit to try that out.

Really the only difference between the QA and Production server is that the mySQL Server isn't living on the same box and it's being replicated to another instance.

[AANP-ITSupport]

@dsmrt , that was it. The issue disappeared after I ran this statement:

ALTER TABLE searchindex ENGINE = InnoDB;

My eye twitches a little bit when I go running statements on core app tables. Makes me wonder what else in core Craft is going to fail with this replicated server setup.

Thanks for the quick reply! Much appreciated.

[dsmrt]

That is frustrating. If you see issues with search in the admin with the InnoDB change, you may be able to tell rackspace to ignore replication on that table. It's not like you need a backup of that. But I haven't used that service, so take this with a grain of salt. Glad to help!