mysqld initiated with wsrep is not restarted and cluster size decrements although the previous mysql process remains active

# ps aux | grep -v grep | grep mysql

root     15204  0.0  0.2 161624  5136 pts/4    S+   16:36   0:00 mysql -u root -px

mysql    15426  0.2  2.5 655544 51116 ?        Sl   17:15   0:00 /usr/sbin/mysqld --user=mysql --wsrep_recover --disable-log-error

mysql    29409  0.0  8.7 2269580 176244 ?      Ssl  May16   2:47 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=3c15149f-5766-11e8-9a99-22bc53d4

MariaDB [(none)]> quit

Bye

# service mysql restart

\Restarting mysql (via systemctl):  Job for mariadb.service failed because a timeout was exceeded. See "systemctl status mariadb.service" and "journalctl -xe" for details.

                                                           [FAILED]

[root@t4w6 ~]# systemctl status mariadb.service

● mariadb.service - MariaDB 10.3.6 database server

   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)

  Drop-In: /etc/systemd/system/mariadb.service.d

           └─migrated-from-my.cnf-settings.conf

   Active: failed (Result: timeout) since Fri 2018-05-18 16:43:41 EEST; 9min ago

     Docs: man:mysqld(8)

           https://mariadb.com/kb/en/library/systemd/

  Process: 29446 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

  Process: 15238 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=killed, signal=TERM)

  Process: 15236 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

 Main PID: 29409

   CGroup: /system.slice/mariadb.service

           └─29409 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=3c15149f-5766-11e8-9a99-22bc53d40581:26771

May 18 16:42:10 t4w6.xentio.lan systemd[1]: Starting MariaDB 10.3.6 database server...

May 18 16:43:41 t4w6.xentio.lan systemd[1]: mariadb.service start-pre operation timed out. Terminating.

May 18 16:43:41 t4w6.xentio.lan systemd[1]: Failed to start MariaDB 10.3.6 database server.

May 18 16:43:41 t4w6.xentio.lan systemd[1]: Unit mariadb.service entered failed state.

May 18 16:43:41 t4w6.xentio.lan systemd[1]: mariadb.service failed.

[root@t4w6 ~]#

[root@t4w6 ~]#

[root@t4w6 ~]#

[root@t4w6 ~]#

[root@t4w6 ~]# systemctl restart  mariadb.service

Job for mariadb.service failed because a timeout was exceeded. See "systemctl status mariadb.service" and "journalctl -xe" for details.

[root@t4w6 ~]#

[root@t4w6 ~]#

[root@t4w6 ~]#

[root@t4w6 ~]#

[root@t4w6 ~]#

[root@t4w6 ~]# systemctl status mariadb.service

● mariadb.service - MariaDB 10.3.6 database server

   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)

  Drop-In: /etc/systemd/system/mariadb.service.d

           └─migrated-from-my.cnf-settings.conf

   Active: failed (Result: timeout) since Fri 2018-05-18 17:17:14 EEST; 10min ago

     Docs: man:mysqld(8)

           https://mariadb.com/kb/en/library/systemd/

  Process: 29446 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

  Process: 15371 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=killed, signal=TERM)

  Process: 15369 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

 Main PID: 29409

   CGroup: /system.slice/mariadb.service

           └─29409 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=3c15149f-5766-11e8-9a99-22bc53d40581:26771

May 18 17:15:44 t4w6.xentio.lan systemd[1]: Starting MariaDB 10.3.6 database server...

May 18 17:17:14 t4w6.xentio.lan systemd[1]: mariadb.service start-pre operation timed out. Terminating.

May 18 17:17:14 t4w6.xentio.lan systemd[1]: Failed to start MariaDB 10.3.6 database server.

May 18 17:17:14 t4w6.xentio.lan systemd[1]: Unit mariadb.service entered failed state.

May 18 17:17:14 t4w6.xentio.lan systemd[1]: mariadb.service failed.

[root@t4w6 ~]#

[root@t4w6 ~]#

[root@t4w6 ~]#

[root@t4w6 ~]# mysql -u root -p1

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

2018-05-18 16:39:10 0 [Note] /usr/sbin/mysqld (initiated by: unknown): Normal shutdown

2018-05-18 16:39:10 0 [Note] WSREP: Stop replication

2018-05-18 16:39:10 0 [Note] WSREP: Closing send monitor...

2018-05-18 16:39:10 0 [Note] WSREP: Closed send monitor.

2018-05-18 16:39:10 0 [Note] WSREP: gcomm: terminating thread

2018-05-18 16:39:10 0 [Note] WSREP: gcomm: joining thread

2018-05-18 16:39:10 0 [Note] WSREP: gcomm: closing backend

2018-05-18 16:39:10 47 [Warning] Aborted connection 47 to db: 'unconnected' user: 'root' host: 'localhost' (Deadlock: wsrep aborted transaction)

2018-05-18 16:39:10 54 [Warning] Aborted connection 54 to db: 'unconnected' user: 'root' host: 'localhost' (Deadlock: wsrep aborted transaction)

2018-05-18 16:39:10 61 [Warning] Aborted connection 61 to db: 'unconnected' user: 'root' host: 'localhost' (Deadlock: wsrep aborted transaction)

2018-05-18 16:39:10 67 [Warning] Aborted connection 67 to db: 'unconnected' user: 'root' host: 'localhost' (Deadlock: wsrep aborted transaction)

2018-05-18 16:39:10 64 [Warning] Aborted connection 64 to db: 'unconnected' user: 'root' host: 'localhost' (Deadlock: wsrep aborted transaction)

2018-05-18 16:39:10 71 [Warning] Aborted connection 71 to db: 'unconnected' user: 'root' host: 'localhost' (Deadlock: wsrep aborted transaction)

2018-05-18 16:39:10 58 [Warning] Aborted connection 58 to db: 'unconnected' user: 'root' host: 'localhost' (Deadlock: wsrep aborted transaction)

2018-05-18 16:39:10 74 [Warning] Aborted connection 74 to db: 'unconnected' user: 'root' host: 'localhost' (Deadlock: wsrep aborted transaction)

2018-05-18 16:39:13 0 [Note] WSREP: (d09be1b9, 'tcp://0.0.0.0:4567') connection to peer 161afdad with addr tcp://192.168.104.195:4567 timed out, no messages seen in PT3S

2018-05-18 16:39:13 0 [Note] WSREP: (d09be1b9, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.104.195:4567

2018-05-18 16:39:15 0 [Note] WSREP: (d09be1b9, 'tcp://0.0.0.0:4567') reconnecting to 161afdad (tcp://192.168.104.195:4567), attempt 0

2018-05-18 16:39:16 0 [Note] WSREP: evs::proto(d09be1b9, LEAVING, view_id(REG,161afdad,88)) suspecting node: 161afdad

2018-05-18 16:39:16 0 [Note] WSREP: evs::proto(d09be1b9, LEAVING, view_id(REG,161afdad,88)) suspected node without join message, declaring inactive

2018-05-18 16:39:16 0 [Note] WSREP: view(view_id(NON_PRIM,161afdad,88) memb {

2018-05-18 16:39:16 0 [Note] WSREP: view((empty))

2018-05-18 16:39:16 0 [Note] WSREP: gcomm: closed

2018-05-18 16:39:16 0 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1

2018-05-18 16:39:16 0 [Note] WSREP: Flow-control interval: [16, 16]

2018-05-18 16:39:16 0 [Note] WSREP: Trying to continue unpaused monitor

2018-05-18 16:39:16 0 [Note] WSREP: Received NON-PRIMARY.

2018-05-18 16:39:16 0 [Note] WSREP: Shifting SYNCED -> OPEN (TO: 26923)

2018-05-18 16:39:16 0 [Note] WSREP: Received self-leave message.

2018-05-18 16:39:16 0 [Note] WSREP: Flow-control interval: [0, 0]

2018-05-18 16:39:16 0 [Note] WSREP: Trying to continue unpaused monitor

2018-05-18 16:39:16 0 [Note] WSREP: Received SELF-LEAVE. Closing connection.

2018-05-18 16:39:16 0 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 26923)

2018-05-18 16:39:16 0 [Note] WSREP: RECV thread exiting 0: Success

2018-05-18 16:39:16 0 [Note] WSREP: recv_thread() joined.

2018-05-18 16:39:16 0 [Note] WSREP: Closing replication queue.

2018-05-18 16:39:16 0 [Note] WSREP: Closing slave action queue.

2018-05-18 16:39:16 1 [ERROR] Slave SQL: Error 'Cannot delete or update a parent row: a foreign key constraint fails' on query. Default database: 'tpcc'. Query: 'drop table DISTRICT', Internal MariaDB error code: 1451

2018-05-18 16:39:16 1 [Warning] WSREP: RBR event 1 Query apply warning: 1, 26896

2018-05-18 16:39:16 1 [Warning] WSREP: Ignoring error for TO isolated action: source: 161afdad-5905-11e8-9248-4b34d681d2d4 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 15 trx_id: -1 seqnos (l: 347, g: 26896, s: 26895, d: 26895, ts: 704924715896420)

2018-05-18 16:39:18 0 [Note] WSREP: killing local connection: 39

2018-05-18 16:39:18 0 [Note] WSREP: killing local connection: 36

2018-05-18 16:39:18 0 [Note] WSREP: killing local connection: 33

2018-05-18 16:39:18 0 [Note] WSREP: killing local connection: 13

2018-05-18 16:39:18 0 [Warning] WSREP: Failed to report last committed 26898, -77 (File descriptor in bad state)

2018-05-18 16:39:18 1 [ERROR] Slave SQL: Error 'Can't create database 'aaa'; database exists' on query. Default database: 'eee'. Query: 'create database aaa', Internal MariaDB error code: 1007

2018-05-18 16:39:18 1 [Warning] WSREP: RBR event 1 Query apply warning: 1, 26905

2018-05-18 16:39:18 1 [Warning] WSREP: Ignoring error for TO isolated action: source: 161afdad-5905-11e8-9248-4b34d681d2d4 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 18 trx_id: -1 seqnos (l: 356, g: 26905, s: 26904, d: 26904, ts: 771281466189319)

2018-05-18 16:39:19 1 [ERROR] Slave SQL: Error 'Table `t` is already system-versioned' on query. Default database: 'bbb'. Query: 'ALTER TABLE t ADD SYSTEM VERSIONING', Internal MariaDB error code: 4135

2018-05-18 16:39:19 1 [Warning] WSREP: RBR event 1 Query apply warning: 1, 26916

2018-05-18 16:39:19 1 [Warning] WSREP: Ignoring error for TO isolated action: source: 161afdad-5905-11e8-9248-4b34d681d2d4 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 18 trx_id: -1 seqnos (l: 367, g: 26916, s: 26915, d: 26915, ts: 772556793082008)

2018-05-18 16:39:20 1 [Note] WSREP: New cluster view: global state: 3c15149f-5766-11e8-9a99-22bc53d40581:26923, view# -1: non-Primary, number of nodes: 1, my index: 0, protocol version 3

2018-05-18 16:39:20 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2018-05-18 16:39:20 1 [Note] WSREP: New cluster view: global state: 3c15149f-5766-11e8-9a99-22bc53d40581:26923, view# -1: non-Primary, number of nodes: 0, my index: -1, protocol version 3

2018-05-18 16:39:20 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2018-05-18 16:39:20 1 [Note] WSREP: applier thread exiting (code:0)

2018-05-18 16:39:20 2 [Note] WSREP: rollbacker thread exiting

2018-05-18 16:39:20 0 [Note] Event Scheduler: Purging the queue. 0 events

2018-05-18 16:39:20 0 [Note] InnoDB: FTS optimize thread exiting.

2018-05-18 16:39:40 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 13  user: 'root'

2018-05-18 16:39:40 0 [ERROR] mysqld: Got an error writing communication packets

wsrep_recovery log

2018-05-18 16:42:11 0 [Note] /usr/sbin/mysqld (mysqld 10.3.6-MariaDB) starting as process 15293 ...

2018-05-18 16:42:11 0 [Warning] Plugin 'handlersocket' is of maturity level beta while the server is gamma

handlersocket: initialized

2018-05-18 16:42:11 0 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Wi                        ll retry for 30 seconds

2018-05-18 16:42:42 0 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when tr                        ying to use aria control file '/var/lib/mysql/aria_log_control'

2018-05-18 16:42:42 0 [ERROR] Plugin 'Aria' init function returned error.

2018-05-18 16:42:42 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.

2018-05-18 16:42:42 0 [Note] InnoDB: For Galera, using innodb_lock_schedule_algorithm=fcfs

2018-05-18 16:42:42 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2018-05-18 16:42:42 0 [Note] InnoDB: Uses event mutexes

2018-05-18 16:42:42 0 [Note] InnoDB: Compressed tables use zlib 1.2.7

2018-05-18 16:42:42 0 [Note] InnoDB: Using Linux native AIO

2018-05-18 16:42:42 0 [Note] InnoDB: Number of pools: 1

2018-05-18 16:42:42 0 [Note] InnoDB: Using generic crc32 instructions

2018-05-18 16:42:42 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2018-05-18 16:42:42 0 [Note] InnoDB: Completed initialization of buffer pool

2018-05-18 16:42:42 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the                         man page of setpriority().

2018-05-18 16:42:42 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:42 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:42 0 [Note] InnoDB: Retrying to lock the first data file

2018-05-18 16:42:43 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:43 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:44 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:44 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:45 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:45 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:46 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:46 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:47 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:47 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:48 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:48 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:49 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:49 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:50 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:50 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:51 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:51 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:52 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:52 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:53 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:53 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:54 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:54 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:55 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:55 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:56 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:56 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:57 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:57 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:58 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:58 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:42:59 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:42:59 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:00 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:00 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:01 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:01 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:02 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:02 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:03 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:03 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:04 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:04 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:05 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:05 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:06 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:06 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:07 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:07 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:08 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:08 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:09 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:09 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:10 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:10 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:11 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:11 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:12 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:12 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:13 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:13 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:14 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:14 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

2018-05-18 16:43:15 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

2018-05-18 16:43:15 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log file                        s.

[root@t4w6 ~]#

# ps aux | grep -v grep | grep mysql

root     15204  0.0  0.2 161624  5136 pts/4    S+   16:36   0:00 mysql -u root -px

mysql    29409  0.0  8.7 2269580 176244 ?      Ssl  May16   2:46 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=3c15149f-5766-11e8-9a99-22bc53d40581:26771