Not the answer you need?
Register and ask your own question!

[Note] WSREP: cleanup transaction for LOCAL_STATE: COMMIT

XibuXibu ContributorInactive User Role Beginner
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

Comments

  • umarzukiumarzuki Contributor Current User Role Beginner
    build a new node, bootstrap the one that still functioning.
    "mysqldump --all-databases > alldb.sql" first ;)
  • wagnerbianchiwagnerbianchi Remote DBA Current User Role Patron
    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/
  • XibuXibu Contributor Inactive User Role Beginner
    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/

    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
  • XibuXibu Contributor Inactive User Role Beginner
    Even restoring the dump on a fresh installation of mysql server ... provide me with the same results
  • XibuXibu Contributor Inactive User Role Beginner
    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
  • XibuXibu Contributor Inactive User Role Beginner
    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'

    ...
  • sergeymsergeym Entrant Current User Role Beginner
    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/


    Hi, I have the same issue with "
  • jason.ijason.i Entrant Current User Role Beginner
    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'
  • jason.ijason.i Entrant Current User Role Beginner
    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
Sign In or Register to comment.

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