ERROR 1047 (08S01): WSREP has not yet prepared node for application use when I create database or use database

Question

Before ask the question, I searched the stackoverflow, found a similar post:
mariadb galera - Error when a node shutdown ERROR 1047 WSREP has not yet prepared node for application use

But however the post is two-node cluster, mine is three-node, and in every node the mariadb is running as below:

controller1:

[root@controller1 mysql]# systemctl status mariadb
● mariadb.service - MariaDB 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: active (running) since Tue 2017-07-04 19:34:22 CST; 19h ago
 Main PID: 3785 (mysqld)
   Status: "Taking your SQL requests now..."
   CGroup: /system.slice/mariadb.service
           └─3785 /usr/sbin/mysqld --wsrep_start_position=de05f291-60a9-11e7-a641-13dca5e08098:0

Jul 05 14:52:54 controller1 mysqld[3785]: 2017-07-05 14:52:54 139817407719168 [Note] WSREP: applier thread exiting (code:6)
Jul 05 14:52:54 controller1 mysqld[3785]: 2017-07-05 14:52:54 139817408489216 [Note] WSREP: New cluster view: global state: 00000000-0000-0000-0000-000000000000:0, v... version -1
Jul 05 14:52:54 controller1 mysqld[3785]: 2017-07-05 14:52:54 139817408489216 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Jul 05 14:52:54 controller1 mysqld[3785]: 2017-07-05 14:52:54 139817408489216 [Note] WSREP: applier thread exiting (code:6)
Jul 05 14:55:41 controller1 mysqld[3785]: 2017-07-05 14:55:41 139817330894592 [Warning] WSREP: Setting parameter 'pc.bootstrap' to 'YES' failed: Setting 'pc.bootstra...tion abort)
Jul 05 14:55:41 controller1 mysqld[3785]: at galera/src/galera_gcs.hpp:param_set():223
Jul 05 14:55:41 controller1 mysqld[3785]: 2017-07-05 14:55:41 139817330894592 [ERROR] WSREP: Set options returned 7
Jul 05 15:04:39 controller1 mysqld[3785]: 2017-07-05 15:04:39 139817330894592 [Warning] WSREP: Setting parameter 'pc.bootstrap' to 'YES' failed: Setting 'pc.bootstra...tion abort)
Jul 05 15:04:39 controller1 mysqld[3785]: at galera/src/galera_gcs.hpp:param_set():223
Jul 05 15:04:39 controller1 mysqld[3785]: 2017-07-05 15:04:39 139817330894592 [ERROR] WSREP: Set options returned 7
Hint: Some lines were ellipsized, use -l to show in full.

controller2:

[root@controller2 ~]# systemctl status mariadb
● mariadb.service - MariaDB 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: active (running) since Tue 2017-07-04 19:32:33 CST; 19h ago
 Main PID: 3181 (mysqld)
   Status: "Taking your SQL requests now..."
   CGroup: /system.slice/mariadb.service
           └─3181 /usr/sbin/mysqld --wsrep_start_position=de05f291-60a9-11e7-a641-13dca5e08098:0

Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140476124489472 [Note] WSREP: Closing replication queue.
Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140476124489472 [Note] WSREP: Closing slave action queue.
Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140476124489472 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 0)
Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140476124489472 [Note] WSREP: RECV thread exiting -103: Software caused connection abort
Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140478689122048 [Note] WSREP: applier thread exiting (code:6)
Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140478689654528 [Note] WSREP: applier thread exiting (code:6)
Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140478690424576 [Note] WSREP: applier thread exiting (code:6)
Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140478689388288 [Note] WSREP: New cluster view: global state: 00000000-0000-0000-0000-000000000000:0, v... version -1
Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140478689388288 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Jul 05 14:52:54 controller2 mysqld[3181]: 2017-07-05 14:52:54 140478689388288 [Note] WSREP: applier thread exiting (code:6)
Hint: Some lines were ellipsized, use -l to show in full.

controller3:

[root@controller3 mysql]# systemctl status mariadb
● mariadb.service - MariaDB 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: active (running) since Wed 2017-07-05 04:32:36 CST; 10h ago
 Main PID: 3176 (mysqld)
   Status: "Taking your SQL requests now..."
   CGroup: /system.slice/mariadb.service
           └─3176 /usr/sbin/mysqld --wsrep_start_position=de05f291-60a9-11e7-a641-13dca5e08098:0

Jul 05 15:11:12 controller3 mysqld[3176]: 2017-07-05 15:11:12 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 77...empt 1080
Jul 05 15:11:36 controller3 mysqld[3176]: 2017-07-05 15:11:36 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 7c...empt 1110
Jul 05 15:11:43 controller3 mysqld[3176]: 2017-07-05 15:11:43 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 77...empt 1110
Jul 05 15:12:06 controller3 mysqld[3176]: 2017-07-05 15:12:06 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 7c...empt 1140
Jul 05 15:12:13 controller3 mysqld[3176]: 2017-07-05 15:12:13 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 77...empt 1140
Jul 05 15:12:39 controller3 mysqld[3176]: 2017-07-05 15:12:39 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 7c...empt 1170
Jul 05 15:12:44 controller3 mysqld[3176]: 2017-07-05 15:12:44 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 77...empt 1170
Jul 05 15:13:09 controller3 mysqld[3176]: 2017-07-05 15:13:09 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 7c...empt 1200
Jul 05 15:13:15 controller3 mysqld[3176]: 2017-07-05 15:13:15 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 77...empt 1200
Jul 05 15:13:39 controller3 mysqld[3176]: 2017-07-05 15:13:39 140609394308864 [Note] WSREP: (e95d1384, 'tcp://0.0.0.0:4567') reconnecting to 7c...empt 1230
Hint: Some lines were ellipsized, use -l to show in full.

As the title I log into every mariadb, I use or create database I will get the error:

ERROR 1047 (08S01): WSREP has not yet prepared node for application use when I create database or use database.

I don't know how to deal with it.

Answer 1

Check cat /var/lib/mysql/grastate.dat on all your nodes. When a cluster starts, seqno is set to -1 (i.e. unknown) and safe_to_bootstrap is set to 0. When mariadb is shut down gracefully, seqno is set to the last transaction known to the node. If all nodes shut down uncleanly, all of them will have seqno: -1 and safe_to_bootstrap: 0, making it impossible for galera to select a donor for any joiner. If that is your situation, this is the solution:

1. Stop mariadb on all nodes. Kill it brutally if necessary.
2. Add wsrep_recover=1 to your galera configuration file on all nodes, then start them up. They will run briefly and exit with an error.
3. Check systemctl status mariadb -l or your log. You will find an entry like "WSREP: Recovered position: 01234567-89ab-cdef-0123-456789abcdef:999". The UUID should be the same on all nodes. The number after the colon is that node's last known transaction.
4. Change wsrep_recover to =0 on all nodes.
5. Edit each node's grastate.dat to contain the correct cluster UUID and the correct seqno for that node (in this example 999). On the node with the highest seqno, change also safe_to_bootstrap to 1.
6. Use galera_new_cluster to bootstrap the node with the highest seqno. Tail the log and see that it is up and stable. Then start normally (systemctl start mariadb) the node with the next highest seqno. Tail its log and verify that it syncronises with the bootstrapped node.
7. Log on to the mysql database on each of these two nodes and run show global status like 'wsrep_cluster_size'; and select user from user limit 10;. If the first result is 2 and the second is not an error, you have a working cluster.
8. Start normally the remaning nodes in descending order of their respective seqno.

If the UUID in step 3 is not the same on all nodes, fix grastate.dat only on the node with the highest seqno. Delete everything in the /var/lib/mysql/ directory on the other nodes, including the hidden .sst directory if it exists. Continue with steps 6-8. Finally, spare a thought for your guardian angel who had made a full backup of your databases just before this mess happened.