Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.13
-
None
Description
A typical use case is to use mariabackup to take a live backup from a running galera node and use that to build an async slave to be used for backup or reporting purposes.
After restoring such backup and running mariabackup --prepare, it appears that when mariadb is started some stray internal XA transactions from WSREP remain in the prepared state and will never go away. These can also take row locks and conflict with replication or other legit traffic.
Only way to have them disappear is to run XA ROLLBACK on each of them.
When server is started, if wsrep is OFF, these should be cleaned up automatically to avoid issues.
Log from node startup below.
Thanks
Rick
2020-07-03 9:03:16 0 [Note] mysqld: Aria engine: starting recovery
|
recovered pages: 0% 100% (0.0 seconds); tables to flush: 1 0
|
(0.0 seconds);
|
2020-07-03 9:03:16 0 [Note] mysqld: Aria engine: recovery done
|
2020-07-03 9:03:16 0 [Warning] option 'innodb-print-all-deadlocks': boolean value 'yes' wasn't recognized. Set to OFF.
|
2020-07-03 9:03:16 0 [Note] InnoDB: Using Linux native AIO
|
2020-07-03 9:03:16 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
|
2020-07-03 9:03:16 0 [Note] InnoDB: Uses event mutexes
|
2020-07-03 9:03:16 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
|
2020-07-03 9:03:16 0 [Note] InnoDB: Number of pools: 1
|
2020-07-03 9:03:16 0 [Note] InnoDB: Using SSE2 crc32 instructions
|
2020-07-03 9:03:16 0 [Note] InnoDB: Initializing buffer pool, total size = 10G, instances = 4, chunk size = 128M
|
2020-07-03 9:03:16 0 [Note] InnoDB: Completed initialization of buffer pool
|
2020-07-03 9:03:16 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
|
2020-07-03 9:03:16 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
|
2020-07-03 9:03:16 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes
|
2020-07-03 9:03:16 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
|
2020-07-03 9:03:16 0 [Note] InnoDB: New log files created, LSN=39006842777638
|
2020-07-03 9:03:16 0 [Note] InnoDB: Transaction 36671586166 was in the XA prepared state.
|
2020-07-03 9:03:16 0 [Note] InnoDB: Transaction 36671586169 was in the XA prepared state.
|
2020-07-03 9:03:16 0 [Note] InnoDB: Transaction 36671586170 was in the XA prepared state.
|
2020-07-03 9:03:16 0 [Note] InnoDB: 4 transaction(s) which must be rolled back or cleaned up in total 1 row operations to undo
|
2020-07-03 9:03:16 0 [Note] InnoDB: Trx id counter is 36671586173
|
2020-07-03 9:03:16 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
|
2020-07-03 9:03:16 0 [Note] InnoDB: Starting in background the rollback of recovered transactions
|
2020-07-03 9:03:16 0 [Note] InnoDB: Creating shared tablespace for temporary tables
|
2020-07-03 9:03:16 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
|
2020-07-03 9:03:16 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
|
2020-07-03 9:03:16 0 [Note] InnoDB: Waiting for purge to start
|
2020-07-03 9:03:16 0 [Note] InnoDB: Rolled back recovered transaction 36671586172
|
2020-07-03 9:03:16 0 [Note] InnoDB: Rollback of non-prepared transactions completed
|
2020-07-03 9:03:17 0 [Note] InnoDB: 10.4.13 started; log sequence number 39006842778124; transaction id 36671586176
|
2020-07-03 9:03:17 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
|
2020-07-03 9:03:17 0 [Note] Plugin 'FEEDBACK' is disabled.
|
2020-07-03 9:03:17 0 [Note] InnoDB: Starting recovery for XA transactions...
|
2020-07-03 9:03:17 0 [Note] InnoDB: Transaction 36671586170 in prepared state after recovery
|
2020-07-03 9:03:17 0 [Note] InnoDB: Transaction contains changes to 1 rows
|
2020-07-03 9:03:17 0 [Note] InnoDB: Transaction 36671586166 in prepared state after recovery
|
2020-07-03 9:03:17 0 [Note] InnoDB: Transaction contains changes to 1 rows
|
2020-07-03 9:03:17 0 [Note] InnoDB: Transaction 36671586169 in prepared state after recovery
|
2020-07-03 9:03:17 0 [Note] InnoDB: Transaction contains changes to 1 rows
|
2020-07-03 9:03:17 0 [Note] InnoDB: 3 transactions in prepared state after recovery
|
2020-07-03 9:03:17 0 [Note] Found 3 prepared transaction(s) in InnoDB
|
2020-07-03 9:03:17 0 [Warning] Found 3 prepared XA transactions
|
2020-07-03 9:03:17 0 [Note] Server socket created on IP: '::'.
|
2020-07-03 9:03:17 0 [Warning] 'user' entry 'root@te-db01.test.local' ignored in --skip-name-resolve mode.
|
2020-07-03 9:03:17 0 [Warning] 'proxies_priv' entry '@% root@te-db01.test.local' ignored in --skip-name-resolve mode.
|
2020-07-03 9:03:17 0 [Note] Reading of all Master_info entries succeeded
|
2020-07-03 9:03:17 0 [Note] Added new Master_info '' to hash table
|
2020-07-03 9:03:17 0 [Note] /usr/sbin/mysqld: ready for connections.
|
Version: '10.4.13-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
|