title: Mysql group replication: stuck on RECOVERING forever
body: <p>I have two MySQL server which are running on same group replication.
  The setup had been done by below steps:</p>
  <ol>
  <li>The first server is production server with lot of data.</li>
  <li>I set it readonly and dump the data, then restore it on the fallback MySQL server</li>
  <li>After restored, I executed &quot;START GROUP_REPLICATION&quot; and it success join to the group. All data are sync well between two server.</li>
  </ol>
  <p>But I found an other error: Every time I reload the fallback MySQL (by restart services) it will auto join to group but stuck at RECOVERING forever, I waited for 3 days but it still in RECOVERING.
  I checked on the log and don't see any error on both server, everything look good except the fallback is running as readonly and stay at RECOVERING.</p>
  <p>What step did I missed?</p>
  <p>My group configuration is (I followed the instruction from DigitalOcean help  page at <a href="https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04" rel="nofollow noreferrer">https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04</a>):</p>
  <blockquote>
  <h1>BINARY LOGGING # log_bin                        = /data/databases/mysql_bin199 expire_logs_days               = 14</h1>
  <p>sync_binlog                    = 1 binlog_format                  =
  ROW</p>
  <h1>General replication settings gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON</h1>
  <h1>log_bin = binlog</h1>
  <h1>binlog_format = ROW transaction_write_set_extraction = XXHASH64 loose-group_replication_bootstrap_group = OFF</h1>
  <p>loose-group_replication_start_on_boot = ON
  loose-group_replication_ssl_mode = REQUIRED
  loose-group_replication_recovery_use_ssl = 1</p>
  <h1>Shared replication group configuration loose-group_replication_group_name =</h1>
  <p>&quot;9dc4ae01-6664-437a-83f8-80546d58e025&quot;
  loose-group_replication_ip_whitelist =
  &quot;172.AAA.BBB.166,138.AAA.BBB.199&quot; loose-group_replication_group_seeds
  = &quot;172.AAA.BBB.166:33061,138.AAA.BBB.199:33061&quot;</p>
  <h1>Single or Multi-primary mode? Uncomment these two lines</h1>
  <h1>for multi-primary mode, where any host can accept writes loose-group_replication_single_primary_mode = OFF</h1>
  <p>loose-group_replication_enforce_update_everywhere_checks = ON</p>
  <h1>Host specific replication configuration server_id = 2 report_host = &quot;138.AAA.BBB.199&quot; loose-group_replication_local_address =</h1>
  <p>&quot;138.AAA.BBB.199:33061&quot;</p>
  </blockquote>
  <p>Below is MySQL log on first server:</p>
  <blockquote>
  <p>2018-06-08T06:10:12.167400Z 0 [Warning] Plugin group_replication</p>
  <p>reported: 'Members removed from the group: 138.AAA.BBB.199:3306'</p>
  <p>2018-06-08T06:10:12.167475Z 0 [Note] Plugin group_replication</p>
  <p>reported: 'Group membership changed to 172.AAA.BBB.166:3306 on view</p>
  <p>15271181169364149:11.' 2018-06-08T06:11:59.032666Z 0 [Note] Plugin</p>
  <p>group_replication reported: 'Members joined the group:</p>
  <p>138.AAA.BBB.199:3306' 2018-06-08T06:11:59.032722Z 0 [Note] Plugin group_replication reported: 'Group membership changed to</p>
  <p>172.AAA.BBB.166:3306, 138.AAA.BBB.199:3306 on view 15271181169364149:12.'</p>
  </blockquote>
  <p>Below is the MySQL log on fallback server:</p>
  <blockquote>
  <p>2018-06-11T09:22:57.490896Z 0 [Warning] option 'max_allowed_packet':</p>
  <p>unsigned value 3221225472 adjusted to 1073741824</p>
  <p>2018-06-11T09:22:57.490942Z 0 [Warning] The use of InnoDB is mandatory</p>
  <p>since MySQL 5.7. The former options like '--innodb=0/1/OFF/ON' or</p>
  <p>'--skip-innodb' are ignored. 2018-06-11T09:22:57.491057Z 0 [Warning]</p>
  <p>The syntax '--log_warnings/-W' is deprecated and will be removed in a</p>
  <p>future release. Please use '--log_error_verbosity' instead.</p>
  <p>2018-06-11T09:22:57.491098Z 0 [Warning] TIMESTAMP with implicit</p>
  <p>DEFAULT value is deprecated. Please use</p>
  <p>--explicit_defaults_for_timestamp server option (see documentation for more details). 2018-06-11T09:22:57.492972Z 0 [Note] /usr/sbin/mysqld</p>
  <p>(mysqld 5.7.22-log) starting as process 31633 ...</p>
  <p>2018-06-11T09:22:57.500063Z 0 [Warning] InnoDB: Using</p>
  <p>innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be</p>
  <p>removed in future releases. Please use READ COMMITTED transaction</p>
  <p>isolation level instead; Please refer to</p>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html</a></p>
  <p>2018-06-11T09:22:57.500175Z 0 [Note] InnoDB: PUNCH HOLE support</p>
  <p>available 2018-06-11T09:22:57.500191Z 0 [Note] InnoDB: Mutexes and</p>
  <p>rw_locks use GCC atomic builtins 2018-06-11T09:22:57.500200Z 0 [Note]</p>
  <p>InnoDB: Uses event mutexes 2018-06-11T09:22:57.500205Z 0 [Note]</p>
  <p>InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier</p>
  <p>2018-06-11T09:22:57.500209Z 0 [Note] InnoDB: Compressed tables use</p>
  <p>zlib 1.2.3 2018-06-11T09:22:57.500213Z 0 [Note] InnoDB: Using Linux</p>
  <p>native AIO 2018-06-11T09:22:57.500430Z 0 [Note] InnoDB: Number of</p>
  <p>pools: 1 2018-06-11T09:22:57.500575Z 0 [Note] InnoDB: Using CPU crc32</p>
  <p>instructions 2018-06-11T09:22:57.501015Z 0 [ERROR] InnoDB: Failed to</p>
  <p>create check sector file, errno:13 Please confirm O_DIRECT is</p>
  <p>supported and remove the file /data/check_sector_size if it exists.</p>
  <p>2018-06-11T09:22:57.502305Z 0 [Note] InnoDB: Initializing buffer pool,</p>
  <p>total size = 4G, instances = 8, chunk size = 128M</p>
  <p>2018-06-11T09:22:57.799065Z 0 [Note] InnoDB: Completed initialization</p>
  <p>of buffer pool 2018-06-11T09:22:57.857325Z 0 [Note] InnoDB: If the</p>
  <p>mysqld execution user is authorized, page cleaner thread priority can</p>
  <p>be changed. See the man page of setpriority().</p>
  <p>2018-06-11T09:22:57.870317Z 0 [Note] InnoDB: Highest supported file</p>
  <p>format is Barracuda. 2018-06-11T09:22:58.081570Z 0 [Note] InnoDB:</p>
  <p>Creating shared tablespace for temporary tables</p>
  <p>2018-06-11T09:22:58.081656Z 0 [Note] InnoDB: Setting file</p>
  <p>'/data/databases/ibtmp1' size to 12 MB. Physically writing the file</p>
  <p>full; Please wait ... 2018-06-11T09:22:58.116190Z 0 [Note] InnoDB:</p>
  <p>File '/data/databases/ibtmp1' size is now 12 MB.</p>
  <p>2018-06-11T09:22:58.117279Z 0 [Note] InnoDB: 96 redo rollback</p>
  <p>segment(s) found. 96 redo rollback segment(s) are active.</p>
  <p>2018-06-11T09:22:58.117293Z 0 [Note] InnoDB: 32 non-redo rollback</p>
  <p>segment(s) are active. 2018-06-11T09:22:58.117670Z 0 [Note] InnoDB:</p>
  <p>Waiting for purge to start 2018-06-11T09:22:58.168094Z 0 [Note]</p>
  <p>InnoDB: 5.7.22 started; log sequence number 51745666191</p>
  <p>2018-06-11T09:22:58.168309Z 0 [Note] InnoDB: Loading buffer pool(s)</p>
  <p>from /data/databases/ib_buffer_pool 2018-06-11T09:22:58.168558Z 0</p>
  <p>[Note] Plugin 'FEDERATED' is disabled. 2018-06-11T09:22:58.183268Z 0</p>
  <p>[Warning] CA certificate /etc/mysql/mysql-ssl/ca-cert.pem is self</p>
  <p>signed. 2018-06-11T09:22:58.184615Z 0 [Note] Server hostname</p>
  <p>(bind-address): '138.AAA.BBB.199'; port: 3306</p>
  <p>2018-06-11T09:22:58.184636Z 0 [Note]   - '138.AAA.BBB.199' resolves to</p>
  <p>'138.AAA.BBB.199'; 2018-06-11T09:22:58.184668Z 0 [Note] Server socket</p>
  <p>created on IP: '138.AAA.BBB.199'. 2018-06-11T09:22:58.186203Z 0</p>
  <p>[Warning] 'user' entry 'mysql.session@localhost' ignored in</p>
  <p>--skip-name-resolve mode. 2018-06-11T09:22:58.186220Z 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve</p>
  <p>mode. 2018-06-11T09:22:58.186238Z 0 [Warning] 'user' entry</p>
  <p>'phpmadsys@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.186260Z 0 [Warning] 'user' entry</p>
  <p>'phpmyadmin@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.186308Z 0 [Warning] 'db' entry 'performance_schema</p>
  <p>mysql.session@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.186313Z 0 [Warning] 'db' entry 'sys</p>
  <p>mysql.sys@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.186318Z 0 [Warning] 'db' entry 'phpmyadmin</p>
  <p>phpmadsys@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.186322Z 0 [Warning] 'db' entry 'performance_schema</p>
  <p>datadog@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.186327Z 0 [Warning] 'db' entry 'phpmyadmin</p>
  <p>phpmyadmin@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.186340Z 0 [Warning] 'proxies_priv' entry '@</p>
  <p>root@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.188628Z 0 [Warning] 'tables_priv' entry 'user</p>
  <p>mysql.session@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.188649Z 0 [Warning] 'tables_priv' entry</p>
  <p>'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.</p>
  <p>2018-06-11T09:22:58.192624Z 0 [Warning] Neither --relay-log nor</p>
  <p>--relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use</p>
  <p>'--relay-log=dvm02-relay-bin' to avoid this problem.</p>
  <p>2018-06-11T09:22:58.206545Z 0 [Note] Event Scheduler: Loaded 0 events</p>
  <p>2018-06-11T09:22:58.206745Z 0 [Note] /usr/sbin/mysqld: ready for</p>
  <p>connections. Version: '5.7.22-log'  socket:</p>
  <p>'/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server</p>
  <p>(GPL) 2018-06-11T09:22:58.207175Z 2 [Note] Plugin group_replication</p>
  <p>reported: 'Group communication SSL configuration:</p>
  <p>group_replication_ssl_mode: &quot;REQUIRED&quot;; server_key_file:</p>
  <p>&quot;/etc/mysql/mysql-ssl/server-key.pem&quot;; server_cert_file:</p>
  <p>&quot;/etc/mysql/mysql-ssl/server-cert.pem&quot;; client_key_file:</p>
  <p>&quot;/etc/mysql/mysql-ssl/server-key.pem&quot;; client_cert_file:</p>
  <p>&quot;/etc/mysql/mysql-ssl/server-cert.pem&quot;; ca_file:</p>
  <p>&quot;/etc/mysql/mysql-ssl/ca-cert.pem&quot;; ca_path: &quot;&quot;; cipher: &quot;&quot;;</p>
  <p>tls_version: &quot;TLSv1,TLSv1.1&quot;; crl_file: &quot;&quot;; crl_path: &quot;&quot;'</p>
  <p>2018-06-11T09:22:58.207378Z 2 [Warning] Plugin group_replication</p>
  <p>reported: '[GCS] Automatically adding IPv4 localhost address to the</p>
  <p>whitelist. It is mandatory that it is added.'</p>
  <p>2018-06-11T09:22:58.207820Z 2 [Note] Plugin group_replication</p>
  <p>reported: 'Initialized group communication with configuration:</p>
  <p>group_replication_group_name: &quot;9dc4ae01-6664-437a-83f8-80546d58e025&quot;;</p>
  <p>group_replication_local_address: &quot;138.AAA.BBB.199:33061&quot;;</p>
  <p>group_replication_group_seeds:</p>
  <p>&quot;172.AAA.BBB.166:33061,138.AAA.BBB.199:33061&quot;;</p>
  <p>group_replication_bootstrap_group: false;</p>
  <p>group_replication_poll_spin_loops: 0;</p>
  <p>group_replication_compression_threshold: 1000000;</p>
  <p>group_replication_ip_whitelist: &quot;172.AAA.BBB.166,138.AAA.BBB.199&quot;'</p>
  <p>2018-06-11T09:22:58.207853Z 2 [Note] Plugin group_replication</p>
  <p>reported: '[GCS] Configured number of attempts to join: 0'</p>
  <p>2018-06-11T09:22:58.207859Z 2 [Note] Plugin group_replication</p>
  <p>reported: '[GCS] Configured time between attempts to join: 5 seconds'</p>
  <p>2018-06-11T09:22:58.207878Z 2 [Note] Plugin group_replication</p>
  <p>reported: 'Member configuration: member_id: 2; member_uuid:</p>
  <p>&quot;822868f9-52a0-11e8-aa0e-1e45f9551f27&quot;; single-primary mode: &quot;false&quot;;</p>
  <p>group_replication_auto_increment_increment: 7; '</p>
  <p>2018-06-11T09:22:58.209024Z 3 [Note] 'CHANGE MASTER TO FOR CHANNEL</p>
  <p>'group_replication_applier' executed'. Previous state</p>
  <p>master_host='', master_port= 0, master_log_file='',</p>
  <p>master_log_pos= 4, master_bind=''. New state master_host='',</p>
  <p>master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.</p>
  <p>2018-06-11T09:22:58.216904Z 6 [Note] Slave SQL thread for channel</p>
  <p>'group_replication_applier' initialized, starting replication in log</p>
  <p>'FIRST' at position 0, relay log</p>
  <p>'./dvm02-relay-bin-group_replication_applier.000071' position: 4</p>
  <p>2018-06-11T09:22:58.216931Z 2 [Note] Plugin group_replication</p>
  <p>reported: 'Group Replication applier module successfully initialized!'</p>
  <p>2018-06-11T09:22:58.241357Z 0 [Note] Plugin group_replication</p>
  <p>reported: 'XCom protocol version: 3' 2018-06-11T09:22:58.241397Z 0</p>
  <p>[Note] Plugin group_replication reported: 'XCom initialized and ready</p>
  <p>to accept incoming connections on port 33061'</p>
  <p>2018-06-11T09:22:59.213826Z 0 [Note] InnoDB: Buffer pool(s) load</p>
  <p>completed at 180611 11:22:59 2018-06-11T09:23:00.316791Z 0 [Note]</p>
  <p>Plugin group_replication reported: 'Group membership changed to</p>
  <p>172.AAA.BBB.166:3306, 138.AAA.BBB.199:3306 on view 15271181169364149:16.'</p>
  </blockquote>