[Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT

MySQL & MariaDB Percona XtraDB Cluster 5.x
#1

Percona-Server-shared-51.x86_64 5.1.73-rel14.12.624.rhel6 @percona-release-x86_64
Percona-XtraDB-Cluster-56.x86_64 1:5.6.22-25.8.978.el6 @percona-release-x86_64
Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.22-25.8.978.el6 @percona-release-x86_64
Percona-XtraDB-Cluster-galera-3.x86_64 3.9-1.3494.rhel6 @percona-release-x86_64
Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.22-25.8.978.el6 @percona-release-x86_64
Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.22-25.8.978.el6 @percona-release-x86_64
percona-release.noarch 0.1-3 @percona
percona-toolkit.noarch 2.2.14-1 @percona-release-noarch
percona-xtrabackup.x86_64 2.2.10-1.el6 @percona-release-x86_64
percona-zabbix-templates.noarch 1.1.4-1 @percona-release-noarch

Following a crash of my cluster ( 2 nodes corrupted on the cluster of 3 nodes ) after a double hard disk failure
(No luck this time … the I am not able to restore a copy of the database at the right point in time)

So I turn to community to seek for advice with this issue

My mysql error log get filled with such message … and I wonder if there is a way to clean this.

2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT dv.mandatory,dv.optional FROM dbversion dv
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT n.* FROM nodes n WHERE n.nodetype=1 ORDER BY n.nodeid
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT p.* FROM profiles p WHERE p.userid=4 ORDER BY p.userid,p.profileid
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT p.* FROM profiles p WHERE p.userid=4 ORDER BY p.userid,p.profileid
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT c.* FROM config c
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT p.* FROM profiles p WHERE p.userid=4 ORDER BY p.userid,p.profileid
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT p.* FROM profiles p WHERE p.userid=4 ORDER BY p.userid,p.profileid
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT p.* FROM profiles p WHERE p.userid=4 ORDER BY p.userid,p.profileid
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT COUNT(DISTINCT t.triggerid) AS cnt,t.status,t.value FROM triggers t WHERE NOT EXISTS (SELECT f.functionid FROM functions f JOIN items i ON f.itemid=i.itemid JOIN hosts h ON i.hostid=h.hostid WHERE f.triggerid=t.triggerid AND (i.status<>0 OR h.status<>0)) AND t.flags IN (0,4) GROUP BY t.status,t.value
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT
2015-05-19 17:13:40 253025 [Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT

#2

build a new node, bootstrap the one that still functioning.
“mysqldump --all-databases > alldb.sql” first :wink:

#3

Have you checked if SHOW PROCESSLIST showed up threads in “pre-commit” state? Have you built the failed node? Next time, try to check if it’s a problems related to the Flow-Control, https://www.percona.com/blog/2013/05/02/galera-flow-control-in-percona-xtradb-cluster-for-mysql/

#4

Thank you for your comment

unfortunately SHOW PROCESSLIST does not show any process in pre-commit state
mysql> SHOW PROCESSLIST;
±-------±------------±----------±-----±--------±-------±-------------------±-----------------±----------±--------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
±-------±------------±----------±-----±--------±-------±-------------------±-----------------±----------±--------------+
| 1 | system user | | NULL | Sleep | 321889 | wsrep aborter idle | NULL | 0 | 0 |
| 2 | system user | | NULL | Sleep | 321889 | NULL | NULL | 0 | 0 |
| 3 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 4 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 5 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 6 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 7 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 8 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 9 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 10 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 11 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 12 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 13 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 14 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 15 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 16 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 17 | system user | | NULL | Sleep | 321884 | NULL | NULL | 0 | 0 |
| 326157 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0 | 0 |
±-------±------------±----------±-----±--------±-------±-------------------±-----------------±----------±--------------+

The failed node is finally built, but when I start the service, the second node give me the same problem …
WSREP: cleanup transaction for LOCAL_STATE: COMMIT

#5

Even restoring the dump on a fresh installation of mysql server … provide me with the same results

#6

Yesterday I enabled wsrep_debug
" SET GLOBAL wsrep_debug=1;"

Today one of my server crashed with this error messages

2015-05-27 04:05:20 311567 [Note] WSREP: MDL conflict
request: (10 seqno 25968 wsrep (1, 1, 0) cmd 0 144 (null))
granted: (64954 seqno -1 wsrep (0, 0, 0) cmd 0 34 (null))
2015-05-27 04:05:20 311567 [Note] WSREP: MDL ticket: type: shared space: UNKNOWN db: name:
2015-05-27 04:05:20 311567 [Note] WSREP: MDL conflict-> BF abort
request: (10 seqno 25968 wsrep (1, 1, 0) cmd 0 144 (null))
granted: (64954 seqno -1 wsrep (0, 0, 0) cmd 0 34 (null))
2015-05-27 04:05:20 311567 [Note] WSREP: MDL ticket: type: shared space: UNKNOWN db: name:
2015-05-27 04:05:20 311567 [Note] WSREP: wsrep_abort_thd, by: 140646226708224, victim: 140646221924096
2015-05-27 04:05:20 311567 [Note] WSREP: abort transaction: BF: (null) victim: (null)
2015-05-27 04:05:20 311567 [Note] WSREP: victim does not have transaction
2015-05-27 04:05:20 311567 [Note] WSREP: MDL conflict
request: (10 seqno 25968 wsrep (1, 1, 0) cmd 0 144 (null))
granted: (64954 seqno -1 wsrep (0, 0, 1) cmd 0 34 (null))
2015-05-27 04:05:20 311567 [Note] WSREP: MDL ticket: type: shared space: UNKNOWN db: name:
2015-05-27 04:05:20 311567 [Note] WSREP: MDL conflict-> BF abort
request: (10 seqno 25968 wsrep (1, 1, 0) cmd 0 144 (null))
granted: (64954 seqno -1 wsrep (0, 0, 1) cmd 0 34 (null))
2015-05-27 04:05:20 311567 [Note] WSREP: MDL ticket: type: shared space: UNKNOWN db: name:
2015-05-27 04:05:20 311567 [Note] WSREP: wsrep_abort_thd, by: 140646226708224, victim: 140646221924096
2015-05-27 04:05:20 311567 [Note] WSREP: abort transaction: BF: (null) victim: (null)
2015-05-27 04:05:20 311567 [Note] WSREP: victim does not have transaction
2015-05-27 04:05:20 311567 [Note] WSREP: MDL victim killed at add_ticket
2015-05-27 04:05:23 311567 [Note] WSREP: client rollback due to BF abort for (64954), query: (null)
2015-05-27 04:05:23 311567 [Note] WSREP: Deadlock error for: (null)
02:05:23 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona XtraDB Cluster better by reporting any
bugs at https://bugs.launchpad.net/percona-xtradb-cluster

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=4
max_threads=2050
thread_count=18
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 826591 K bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.

Thread pointer: 0x2bd37a60
Attempting backtrace. You can use the following information to find out

#7

This is produced during the backup phase 1

150527 04:05:23 innobackupex: Finished backing up non-InnoDB tables and files

150527 04:05:23 innobackupex: Executing LOCK BINLOG FOR BACKUP…
DBD::mysql::db do failed: Deadlock found when trying to get lock; try restarting transaction at /usr/bin/innobackupex line 3044.
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3047
main::mysql_query(‘HASH(0x2abd178)’, ‘LOCK BINLOG FOR BACKUP’) called at /usr/bin/innobackupex line 3529
main::mysql_lock_binlog(‘HASH(0x2abd178)’) called at /usr/bin/innobackupex line 2008
main::backup() called at /usr/bin/innobackupex line 1601
innobackupex: Error:
Error executing ‘LOCK BINLOG FOR BACKUP’: DBD::mysql::db do failed: Deadlock found when trying to get lock; try restarting transaction at /usr/bin/innobackupex line 3044.
150527 04:05:23 innobackupex: Waiting for ibbackup (pid=416149) to finish
05/27/2015 04:06:09

but that’s strange as the configuration file state

inno-backup-opts=’–no-backup-locks’

…

#8

Hi, I have the same issue with "

#9

Me too . Our 3 node Mariadb also more “” in our log , how do ?

e.g
160402 12:47:32 [Note] WSREP: cleanup transaction for LOCAL_STATE: select barcodeID, scanType, couponVer from coupon_log_2016 where deviceToke n = ‘XXXXXXXXXX’ and barcodeID = ‘214’ and scanType = ‘0’

#10

Still have many warning abut the cleanup transaction show up , any body can help me know what problem ,

[Note] WSREP: cleanup transaction for LOCAL_STATE: SELECT b.title FROM daily_hl_news AS a INNER JOIN news_main_2016 AS b ON a.XXXXX = b.XXXXXX INNER JOIN news_XXXXXX AS c ON a.newsCat = c.newsCatID WHERE a.XXXXXXX = ‘1’ AND b.XXXXXX<= ‘2016-04-05 10:40:43’ AND a.XXXXXXXX <= ‘2016-04-05 10:40:43’ AND a.year = ‘2016’

Many thanks

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.