Backdrop encounters transient MySQL/MariaDB errors under high concurrency

[swampopus]

Description of the bug

This is something D7 did too, at least on my projects.

On sites with a lot of user activity, Backdrop can sometimes encounter transient MySQL errors such as:

• Deadlocks (err 1213)
• Lock wait timeouts (err 1205)
• Table def changes (err 1412)

See some recent screenshots I got while working:

This is obviously from watchdog, but in the user experience, I actually get an error screen, which is no good.

This can be fixed by simply retrying the query/transaction. I intend to submit a PR which does this optionally.

Steps To Reproduce

This is a little hard to reproduce on demand-- when it happened to me, it was because I was creating a new content type, and loaded another content type in a new tab at the same time as the first tab was trying to save.

But, I've seen similar "deadlock" errors occur in other situations in D7, especially when a lot of users were all doing things at the same time.

I have noticed this especially happens when performing long batch operations while other users are on the site doing things.

Expected behavior / My plan for the PR

Before throwing an exception, we should retry up to a couple of times, with a random short delay between attempts, to avoid further collisions.

My plan in the PR is going to be that this has to be enabled in the settings.php file with something similar to the following:

$settings['database_error_retry'] = [
  'enabled' => TRUE,
  'max_attempts' => 3,
  'min_delay_ms' => 100,
  'max_delay_ms' => 800,
  'retry_error_codes' => [1213, 1205, 1412],
];

Your average user need never bother messing with this, but power users would be able to enable it if they run into similar problems as I have.

Ideally, if accepted, this could be commented-out in the settings.php file in a future release, with an explanation for devs as to what it is doing and why.

[indigoxela]

Hi @swampopus, that's indeed a bit tricky to reproduce.

Seems like you skipped the Database Server and version info our issue template asks for. Mind to complement that?

And while you're at it - some info whether you have access to DB server settings or at least to a mysql/mariadb shell.

This may seem odd, but the explanation is, that a specific server setting has some influence there (or might have, reading your report): Transation isolation level.

show variables like '%isolation%';

I guess, it's REPEATABLE-READ - is that correct? Because if that's the case and you have access to server settings, it might just be a matter of changing that server setting. On the other hand, if you haven't...

[swampopus]

@indigoxela You're right-- sorry-- the DB info where I just got these errors is MariaDB 10.11.11. I have also gotten it in the past on MySQL 8.0.42. For simplicity, I'll focus on the MariaDB info first.

I have full access to the server settings.

Okay-- I checked variables and got this:

+---------------------------+-----------------+
| Variable_name             | Value           |
+---------------------------+-----------------+
| innodb_snapshot_isolation | OFF             |
| tx_isolation              | REPEATABLE-READ |
+---------------------------+-----------------+

Interestingly, I got transaction_isolation = REPEATABLE-READ on the MySQL 8x server too.

Sooo-- should that instead be READ COMMITTED? And if so, is there a downside to making that the standard for all of my backdrop sites?

It appears that REPEATABLE-READ is the default setting for both MySQL and MariaDB.

[indigoxela]

Oh, forgot to ask: you mentioned that it happened to you while creating a new content type. Interesting in that context: does your site save config in files or in the database?

In settings.php that would be, for example $settings['config_active_class'] = 'ConfigDatabaseStorage';

[swampopus]

Configs are in files

[indigoxela]

should that instead be READ COMMITTED? And if so, is there a downside to making that the standard for all of my backdrop sites?

I'd feel uncomfortable recommending something in this case, as I'm no database specialist. 😉 Only know, that this server variable may have influence re deadlocks.

[swampopus]

Hm. I appreciate the advice. I am still debating about the original suggestion I posed-- adding "on error, retry" logic to core before throwing an exception. I know altering core is not something to be done lightly, but I know for my specific situation, I absolutely cannot have users seeing errors when they attempt to save work or run batch operations where a lot of nodes are being worked on (where I've seen this before).

The users actually end up losing their work. Or, in one example I can name, I had a batch operation which handled mass emails that every now and then would die with a "deadlock" error, and the users would email me angry, and we'd have to figure out where it failed so they can repeat the mass email without sending twice to some recipients.

Anyway-- what's your thoughts on that (the "retry" PR proposal)? Do you think such a PR would be dismissed out of hand (ie, it isn't worth it make the attempt) because it's logic that wraps around such a core function as database queries?

[swampopus]

I guess another option would be to make a new custom database driver (eg 'mysql_with_retries') that extends DatabaseConnection just like /core/includes/database/mysql/database.inc, but the only thing I do different is override the query() method. That way I wouldn't be touching core. But I am not sure how to do that in a module, if such is even possible.

[swampopus]

Sorry for all the chatter-- I think I have figured out how to do it in a contrib module. If I can do that instead, I'll do that.

UPDATE EDIT: Nah... it doesn't look like I can register a database driver from a contrib module. Backdrop assumes all database drivers (mysql is only one at the moment) are located in core/includes/database/* Even with a symlink to my attempt to extend the mysql driver, it's a mess and way too many errors to bother with.

My options would be to either (a) add my "retry" code to a core PR, or (b) write a completely new driver as a new project, and instruct users to manually copy it into the right spot (or create a symlink) in core, but of course they'd have to copy it back into place every time they update core.

[swampopus]

Okay-- I created a project (in the form of a module) which gives instructions on how to get this working. I'm still going to submit a PR to core I think, but this module (hopefully) will help in the meantime: https://github.com/backdrop-contrib/mysql_retry_on_errors

I am using it on a live site where this problem occurs, and I am logging when it attempts a retry, so I'll see early on if it works.