NAME DBIx::Class::Storage::DBI::mysql::Retryable - MySQL-specific DBIC storage engine with retry support VERSION version v1.0.2 SYNOPSIS package MySchema; # Recommended DBIx::Class::Storage::DBI::mysql::Retryable->_use_join_optimizer(0); __PACKAGE__->storage_type('::DBI::mysql::Retryable'); # Optional settings (defaults shown) my $storage_class = 'DBIx::Class::Storage::DBI::mysql::Retryable'; $storage_class->parse_error_class('DBIx::ParseError::MySQL'); $storage_class->timer_class('Algorithm::Backoff::RetryTimeouts'); $storage_class->timer_options({}); # same defaults as the timer class $storage_class->aggressive_timeouts(0); $storage_class->retries_before_error_prefix(1); $storage_class->warn_on_retryable_error(0); $storage_class->enable_retryable(1); DESCRIPTION This storage engine for DBIx::Class is a MySQL-specific engine that will explicitly retry on MySQL-specific transient error messages, as identified by DBIx::ParseError::MySQL, using Algorithm::Backoff::RetryTimeouts as its retry algorithm. This engine should be much better at handling deadlocks, connection errors, and Galera node flips to ensure the transaction always goes through. How Retryable Works A DBIC command triggers some sort of connection to the MySQL server to send SQL. First, Retryable makes sure the connection mysql_*_timeout values (except mysql_read_timeout unless "aggressive_timeouts" is set) are set properly. (The default settings for RetryTimeouts will use half of the maximum duration, with some jitter.) If the connection was successful, a few SET SESSION commands for timeouts are sent first: wait_timeout # only with aggressive_timeouts=1 lock_wait_timeout innodb_lock_wait_timeout net_read_timeout net_write_timeout If the DBIC command fails at any point in the process, and the error is a recoverable failure (according to the error parsing class), the retry process starts. The timeouts are only checked during the retry handler. Since DB operations are XS calls, Perl-style "safe" ALRM signals won't do any good, and the engine won't attempt to use unsafe ones. Thus, the engine relies on the server to honor the timeouts set during each attempt, and will give up if it runs out of time or attempts. If the DBIC command succeeds during the process, program flow resumes as normal. If any re-attempts happened during the DBIC command, the timeouts are reset back to the original post-connection values. STORAGE OPTIONS parse_error_class Class used to parse MySQL error messages. Default is DBIx::ParseError::MySQL. If a different class is used, it must support a similar interface, especially the is_transient method. timer_class Algorithm class used to determine timeout and sleep values during the retry process. Default is Algorithm::Backoff::RetryTimeouts. If a different class is used, it must support a similar interface, including the dual return of the failure method. timer_options Options to pass to the timer algorithm constructor, as a hashref. Default is an empty hashref, which would retain all of the defaults of the algorithm module. aggressive_timeouts Boolean that controls whether to use some of the more aggressive, query-unfriendly timeouts: mysql_read_timeout Controls the timeout for all read operations. Since SQL queries in the middle of sending its first set of row data are still considered to be in a read operation, those queries could time out during those circumstances. If you're confident that you don't have any SQL statements that would take longer than R/2 (or at least returning results before that time), you can turn this option on. Otherwise, you may experience longer-running statements going into a retry death spiral until they finally hit the Retryable timeout for good and die. wait_timeout Controls how long the MySQL server waits for activity from the connection before timing out. While most applications are going to be using the database connection pretty frequently, the MySQL default (8 hours) is much much longer than the mere seconds this engine would set it to. Default is off. Obviously, this setting only makes sense with "retryable_timeout" turned on. retries_before_error_prefix Controls the number of retries (not tries) needed before the exception message starts using the statistics prefix, which looks something like this: Failed dbh_do coderef: Out of retries, attempts: 5 / 4, timer: 34.5 / 50.0 sec The default is 1, which means a failed first attempt (like a non-transient failure) will show a normal exception, and the second attempt will use the prefix. You can set this to 0 to always show the prefix, or a large number like 99 to keep the exception clean. warn_on_retryable_error Boolean that controls whether to warn on retryable failures, as the engine encounters them. Many applications don't want spam on their screen for recoverable conditions, but this may be useful for debugging or CLI tools. Unretryable failures always generate an exception as normal, regardless of the setting. This is functionally equivalent to "PrintError" in DBI, but since "RaiseError" is already the DBIC-required default, the former option can't be used within DBI. Default is off. enable_retryable Boolean that enables the Retryable logic. This can be turned off to temporarily disable it, and revert to DBIC's basic "retry once if disconnected" default. This may be useful if a process is already using some other retry logic (like DBIx::OnlineDDL). Messing with this setting in the middle of a database action would not be wise. Default is on. METHODS dbh_do my $val = $schema->storage->dbh_do( sub { my ($storage, $dbh, @binds) = @_; $dbh->selectrow_array($sql, undef, @binds); }, @passed_binds, ); This is very much like "dbh_do" in DBIx::Class::Storage::DBI, except it doesn't require a connection failure to retry the sub block. Instead, it will also retry on locks, query interruptions, and failovers. Normal users of DBIC typically won't use this method directly. Instead, any ResultSet or Result method that contacts the DB will send its SQL through here, and protect it from retryable failures. However, this method is recommended over using $schema->storage->dbh directly to run raw SQL statements. txn_do my $val = $schema->txn_do( sub { # ...DBIC calls within transaction... }, @misc_args_passed_to_coderef, ); Works just like "txn_do" in DBIx::Class::Storage, except it's now protected against retryable failures. Calling this method through the $schema object is typically more convenient. throw_exception $storage->throw_exception('It failed'); Works just like "throw_exception" in DBIx::Class::Storage, but also reports attempt and timer statistics, in case the transaction was tried multiple times. CAVEATS Transactions without txn_do Retryable is transaction-safe. Only the outermost transaction depth gets the retry protection, since that's the only layer that is idempotent and atomic. However, transaction commands like txn_begin and txn_scope_guard are NOT granted retry protection, because DBIC/Retryable does not have a defined transaction-safe code closure to use upon reconnection. Only txn_do will have the protections available. For example: # Has retry protetion my $rs = $schema->resultset('Foo'); $rs->delete; # This effectively turns off retry protection $schema->txn_begin; # NOT protected from retryable errors! my $result = $rs->create({bar => 12}); $result->update({baz => 42}); $schema->txn_commit; # Retry protection is back on # Do this instead! $schema->txn_do(sub { my $result = $rs->create({bar => 12}); $result->update({baz => 42}); }); # Still has retry protection $rs->delete; All of this behavior mimics how DBIC's original storage engines work. (Ab)using $dbh directly Similar to txn_begin, directly accessing and using a DBI database or statement handle does NOT grant retry protection, even if they are acquired from the storage engine via $storage->dbh. Instead, use "dbh_do". This method is also used by DBIC for most of its active DB calls, after it has composed a proper SQL statement to run. SEE ALSO DBIx::Connector::Retry::MySQL - A similar engine for DBI connections, using DBIx::Connector::Retry as a base. DBIx::Class::Storage::BlockRunner - Base module in DBIC that controls how transactional coderefs are ran and retried AUTHOR Grant Street Group COPYRIGHT AND LICENSE This software is Copyright (c) 2021 - 2022 by Grant Street Group. This is free software, licensed under: The Artistic License 2.0 (GPL Compatible)