Andrii Nikitin (Inactive) added a comment - 2017-07-15 10:48 - edited EDIT: see also alternative instructions in later comments. Use following script to setup environment : set -e MDBVER=${ENVIRON:-10.2.6}   # just use current directory if called from framework if [ ! -f common.sh ] ; then [ -d mariadb-environs ] || git clone http: //github .com /AndriiNikitin/mariadb-environs cd mariadb-environs fi   . /get_plugin .sh galera # set up cluster _template /plant_cluster .sh cluster1 echo m0 > cluster1 /nodes .lst echo m1 >> cluster1 /nodes .lst echo m2 >> cluster1 /nodes .lst echo m3 >> cluster1 /nodes .lst cluster1 /replant .sh ${MDBVER}   # download tar if needed . /build_or_download .sh m0 # workaround MDEV-13283 [ ! -d _depot /m-tar/ ${MDBVER} ] || \ sed -i "s/Distrib 10.1/Distrib 10/g" _depot /m-tar/ ${MDBVER} /bin/wsrep_sst_mysqldump Commands below will initialize and verify four nodes cluster on local machine on ports 3306 - 3309 cluster1 /cleanup .sh cluster1 /gen_cnf .sh general_log=1 cluster1 /install_db .sh cluster1 /galera_setup_acl .sh cluster1 /galera_start_new .sh # sleep a while to let sst finish or let nodes shut down if initialization failed sleep 45 cluster1 /sql .sh select 1 from mysql.user limit 1 cluster1 /galera_cluster_size .sh Last command will show wsrep_cluster_size on each node. When it is 4 on every node, it should be a sign that cluster has been initialized properly. Optionally - select from any table of the nodes and observe no error: mysql --defaults- file =m0-${MDBVER} /my .cnf -e 'select 1 from mysql.user limit 1' mysql --defaults- file =m1-${MDBVER} /my .cnf -e 'select 1 from mysql.user limit 1' mysql --defaults- file =m2-${MDBVER} /my .cnf -e 'select 1 from mysql.user limit 1' mysql --defaults- file =m3-${MDBVER} /my .cnf -e 'select 1 from mysql.user limit 1' Optionally try whole sequence several times to confirm that it works reliably. Now configure MaxScale to monitor one or more ports 3307 3308 3309 on 127.0.0.1 ( user/password : galera/galera ) e.g. with following maxscale.cnf : [server1] type=server address=127.0.0.1 port=3307 protocol=MySQLBackend   [MySQL Monitor] type=monitor module=galeramon servers=server1 user=galera passwd=galera monitor_interval=500   [Read-Write Service] type=service router=readwritesplit servers=server1 user=galera passwd=galera   [Read-Write Listener] type=listener service=Read-Write Service protocol=MySQLClient port=4006 Now the sequence of cluster commands will leave monitored nodes in state 'WSREP has not yet prepared node for application use' forever, while the other ones will work properly. In particular, with example cnf log above output will be: $ cluster1 /sql .sh select 1 from mysql.user limit 1 m0 :1 m1 :ERROR 1047 (08S01) at line 1: WSREP has not yet prepared node for application use   m2 :1 m3 :1 $ cluster1 /galera_cluster_size .sh m0 :wsrep_cluster_size 4 m1 :wsrep_cluster_size 2 m2 :wsrep_cluster_size 4 m3 :wsrep_cluster_size 4 General log has only commands like 'SHOW STATUS' and I wasn't able reproduce the problem when executing such commands against nodes directly (i.e. without MaxScale).

Andrii Nikitin (Inactive) added a comment - 2017-11-01 10:46 - edited I am able to reliably reproduce the problem on system with 10.0 Galera packages installed, which has suitable maxscale packages from list https://downloads.mariadb.com/MaxScale/2.1.10/ . (E.g. xenial) Using this script https://github.com/AndriiNikitin/bugs/blob/master/MDEV-13329.sh I confirm that following patch solves the problem: --- a/sql/wsrep_mysqld.cc +++ b/sql/wsrep_mysqld.cc @@ -285,7 +285,7 @@ wsrep_view_handler_cb ( void * app_ctx, if (!wsrep_before_SE()) { WSREP_DEBUG( "[debug]: closing client connections for PRIM" ); - wsrep_close_client_connections(TRUE); + wsrep_close_client_connections(FALSE); } ssize_t const req_len= wsrep_sst_prepare (sst_req); output before patch is always like below: $ bash ~/bugs/MDEV-13329.sh Already up-to-date. Already up-to-date. GENERATE TEMPLATES Process 17861 still exists, sleeping 1 sec Process 18115 still exists, sleeping 1 sec     Shutting down MaxScale   DOWNLOAD PACKAGES (if needed) GENERATE CONFIG FILES m7 : m8 : INITIALIZE DATADIRs m7 :171101 11:26:00 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 20866 ... 171101 11:26:07 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 20893 ... calling /usr/local/mysql/scripts/mysql_install_db Installing MariaDB/MySQL system tables in '/home/a/env1/m7-system2/dt' ... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/local/mysql/bin/mysqladmin' -u root password 'new-password' '/usr/local/mysql/bin/mysqladmin' -u root -h UBINTI password 'new-password' Alternatively you can run: '/usr/local/mysql/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/home/a/env1/m7-system2/dt' You can test the MariaDB daemon with mysql-test-run.pl cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/ m8 :171101 11:26:12 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 20959 ... 171101 11:26:20 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 20988 ... calling /usr/local/mysql/scripts/mysql_install_db Installing MariaDB/MySQL system tables in '/home/a/env1/m8-system2/dt' ... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/local/mysql/bin/mysqladmin' -u root password 'new-password' '/usr/local/mysql/bin/mysqladmin' -u root -h UBINTI password 'new-password' Alternatively you can run: '/usr/local/mysql/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/home/a/env1/m8-system2/dt' You can test the MariaDB daemon with mysql-test-run.pl cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/ STARTUP SERVERS TO SETUP ACL m7 :calling /usr/local/mysql/bin/mysqld_safe 171101 11:26:24 mysqld_safe Logging to '/home/a/env1/m7-system2/dt/error.log'. 171101 11:26:24 mysqld_safe Starting mysqld daemon with databases from /home/a/env1/m7-system2/dt mysqld is alive m8 :calling /usr/local/mysql/bin/mysqld_safe 171101 11:26:28 mysqld_safe Logging to '/home/a/env1/m8-system2/dt/error.log'. 171101 11:26:28 mysqld_safe Starting mysqld daemon with databases from /home/a/env1/m8-system2/dt mysqld is alive m7 : m8 : STARTUP Galera and MaxScale m7 :calling /usr/local/mysql/bin/mysqld_safe 171101 11:26:47 mysqld_safe Logging to '/home/a/env1/m7-system2/dt/error.log'. 171101 11:26:47 mysqld_safe Starting mysqld daemon with databases from /home/a/env1/m7-system2/dt mysqld is alive m8 :calling /usr/local/mysql/bin/mysqld_safe 171101 11:26:51 mysqld_safe Logging to '/home/a/env1/m8-system2/dt/error.log'. 171101 11:26:51 mysqld_safe Starting mysqld daemon with databases from /home/a/env1/m8-system2/dt mysqld is alive CREATING TABLE in MAXSCALE MONITOR Nodes' OUTPUT m7 :5 m8 :ERROR 1047 (08S01) at line 1: WSREP has not yet prepared node for application use   m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 m7 :5 m8 :ERROR 1047 (08S01) at line 1: WSREP has not yet prepared node for application use   m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 m7 :5 m8 :ERROR 1047 (08S01) at line 1: WSREP has not yet prepared node for application use   m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 m7 :5 m8 :ERROR 1047 (08S01) at line 1: WSREP has not yet prepared node for application use   m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 m7 :5 m8 :ERROR 1047 (08S01) at line 1: WSREP has not yet prepared node for application use   m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 m7 :5 m8 :ERROR 1047 (08S01) at line 1: WSREP has not yet prepared node for application use   m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 Output after patch: $ bash ~/bugs/MDEV-13329.sh Already up-to-date. Already up-to-date. GENERATE TEMPLATES Process 21818 still exists, sleeping 1 sec Process 22073 still exists, sleeping 1 sec     Shutting down MaxScale   DOWNLOAD PACKAGES (if needed) GENERATE CONFIG FILES m7 : m8 : INITIALIZE DATADIRs m7 :171101 11:30:48 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 24883 ... 171101 11:31:01 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 24910 ... calling /usr/local/mysql/scripts/mysql_install_db Installing MariaDB/MySQL system tables in '/home/a/env1/m7-system2/dt' ... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/local/mysql/bin/mysqladmin' -u root password 'new-password' '/usr/local/mysql/bin/mysqladmin' -u root -h UBINTI password 'new-password' Alternatively you can run: '/usr/local/mysql/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/home/a/env1/m7-system2/dt' You can test the MariaDB daemon with mysql-test-run.pl cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/ m8 :171101 11:31:05 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 24976 ... 171101 11:31:15 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.0.33-MariaDB-wsrep) starting as process 25003 ... calling /usr/local/mysql/scripts/mysql_install_db Installing MariaDB/MySQL system tables in '/home/a/env1/m8-system2/dt' ... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/local/mysql/bin/mysqladmin' -u root password 'new-password' '/usr/local/mysql/bin/mysqladmin' -u root -h UBINTI password 'new-password' Alternatively you can run: '/usr/local/mysql/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '/usr/local/mysql' ; /usr/local/mysql/bin/mysqld_safe --datadir='/home/a/env1/m8-system2/dt' You can test the MariaDB daemon with mysql-test-run.pl cd '/usr/local/mysql/mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/ STARTUP SERVERS TO SETUP ACL m7 :calling /usr/local/mysql/bin/mysqld_safe 171101 11:31:19 mysqld_safe Logging to '/home/a/env1/m7-system2/dt/error.log'. 171101 11:31:19 mysqld_safe Starting mysqld daemon with databases from /home/a/env1/m7-system2/dt mysqld is alive m8 :calling /usr/local/mysql/bin/mysqld_safe 171101 11:31:23 mysqld_safe Logging to '/home/a/env1/m8-system2/dt/error.log'. 171101 11:31:23 mysqld_safe Starting mysqld daemon with databases from /home/a/env1/m8-system2/dt mysqld is alive m7 : m8 : STARTUP Galera and MaxScale m7 :calling /usr/local/mysql/bin/mysqld_safe 171101 11:31:43 mysqld_safe Logging to '/home/a/env1/m7-system2/dt/error.log'. 171101 11:31:43 mysqld_safe Starting mysqld daemon with databases from /home/a/env1/m7-system2/dt mysqld is alive m8 :calling /usr/local/mysql/bin/mysqld_safe 171101 11:31:47 mysqld_safe Logging to '/home/a/env1/m8-system2/dt/error.log'. 171101 11:31:47 mysqld_safe Starting mysqld daemon with databases from /home/a/env1/m8-system2/dt mysqld is alive CREATING TABLE in MAXSCALE MONITOR Nodes' OUTPUT m7 :5 m8 :5 m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 m7 :5 m8 :5 m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 m7 :5 m8 :5 m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 m7 :5 m8 :5 m7 :wsrep_cluster_size 2 m8 :wsrep_cluster_size 2 sachin.setiya.007 seppo could you confirm that the patch above is reasonable? Node is ongoing sst, so no need to try wait forever until all clients are gracefully disconnected. I believe current galera_sst_mysqldump test is suffering the same problem, where Node is hopelessly waiting mtr to disconnect - see stack in MDEV-14069

Andrii Nikitin (Inactive) added a comment - 2017-11-01 10:47 sachin.setiya.007 please review one line patch in previous comment

markus makela added a comment - 2017-11-01 11:11 Workaround would be to stop the monitor in MaxScale to force the closing of the connections.

Andrii Nikitin (Inactive) added a comment - 2017-11-14 12:35 The problem happens because the node waits all connections to gracefully disconnect before starting sst. So if some existing connection is just idle - sst will never initialize. E.g. following command will attempt to connect and then just remain idle. If command like this runs (e.g. by some monitoring software or just broken connection waits for some timeout to be detected) on joining node, then node remains in 'Ininitalized' state. ( while : ; do mysql -e 'show variables like "wsrep_on"; system sleep 10000' 2>>log.log || : ; done ) & See also https://github.com/AndriiNikitin/bugs/blob/master/MDEV-13329-simple.sh . which gives in result: m1 :wsrep_cluster_size 3 m2 :wsrep_cluster_size 2 m3 :wsrep_cluster_size 3 m1 :wsrep_local_state_comment Synced m2 :wsrep_local_state_comment Initialized m3 :wsrep_local_state_comment Synced m1 :wsrep_local_state_comment Synced m2 :wsrep_local_state_comment Initialized m3 :wsrep_local_state_comment Synced m1 :wsrep_local_state_comment Synced m2 :wsrep_local_state_comment Initialized m3 :wsrep_local_state_comment Synced m1 :wsrep_local_state_comment Synced m2 :wsrep_local_state_comment Initialized m3 :wsrep_local_state_comment Synced ...