unlcms / project-herbie

Drupal 10 implementation at the University of Nebraska–Lincoln
https://cms.unl.edu
GNU General Public License v2.0
5 stars 6 forks source link

ProxySQLbug on production causes site installations to fail #366

Open ericras opened 11 months ago

ericras commented 11 months ago

Two symptoms that involve menu rebuilding that creates MySQL transactions:

ericras commented 11 months ago

Issue occurs at https://git.drupalcode.org/project/drupal/-/blob/10.0.7/core/modules/mysql/src/Driver/Database/mysql/Connection.php#L412

Drupal tries to release a savepoint that no longer exists because mysql cleaned it up. (This is detailed in the comments in the Core code.) This causes an error that Drupal handles gracefully but ProxySQL does not.

ericras commented 11 months ago

Bug report filed: https://github.com/sysown/proxysql/issues/4181

tsteiner commented 11 months ago

https://github.com/sysown/proxysql/issues/4181

As a workaround, you could try specifying the backend database servers directly. Though high-availability/failover would be lost.

ericras commented 11 months ago

What I was going to do was just kind of skip over releasing savepoints when doing a site install. Specifying the backend database server directly during install would probably be better though from testing installations it looks like only the non-existing savepoints are trying to be released.... so it doesn't really matter in practice.

I'm going to log when we hit https://git.drupalcode.org/project/drupal/-/blob/10.0.7/core/modules/mysql/src/Driver/Database/mysql/Connection.php#L414 in production dat-to-day use and see if its a problem. Of course, with the proxysql problem, its potentially not just that one spot, but other SQL errors (just transaction related?) that could cause issues. Hopefully if there are SQL errors we would be fixing those, but that one savepoint release spot in Drupal is where errors are anticipated/expected to occur.

ericras commented 5 months ago

2.5.5 of proxysql which has been deployed to production appears to solve the problem

https://github.com/sysown/proxysql/releases/tag/v2.5.5

Following an error returned by the backend, COMMIT and ROLLBACK were incorrectly filtered and not sent to the right backend connection because of the unknown transaction status