MySQL Learning note 16 Several common problems and solutions to group replication

MySQL Packet Replication provides a powerful feature, but sometimes there are some problems, or the use of some limitations mainly include:

(1) restrictions for group replication.

(a) the storage engine can only be InnoDB .

(b) binary log format only supports ROW format.

(c) only supported with GTID mode.

(d) each group supports up to 9 member nodes.

(2) The data table must have a primary key.

Mysql> CREATE TABLE Test (name varchar (100));

Query OK, 0 rows affected (0.05 sec)

mysql> INSERT into Test values (now ()), (now ());

ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.

To view logs:

2017-08-19t06:23:22.253181z [ERROR] Plugin group_replication reported: ' Table test does not has any PRIMARY KEY. This isn't compatible with Group Replication '

2017-08-19t06:24:18.493848z [ERROR] Plugin group_replication reported: ' Table test does not has any PRIMARY KEY. This isn't compatible with Group Replication '

Workaround:

Create a data table with a primary key.

Mysql> CREATE TABLE Test (name varchar primary key);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT into Test (name) VALUES (' 001 ');

Query OK, 1 row affected (0.02 sec)

mysql> INSERT into Test (name) VALUES (' 002 ');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT into Test (name) VALUES (' 003 ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into Test (name), values (now ());

Query OK, 1 row affected (0.01 sec)

(3) The database already exists.

There is an error in the log that the database already exists and the database cannot be created.

2017-08-19t06:51:50.784471z [ERROR] Slave SQL for channel ' group_replication_recovery ': Error ' Can ' t create database ' t EST '; Database exists ' on query. Default database: ' Test '. Query: ' Create Database Test ', error_code:1007

2017-08-19t06:51:50.784523z [Warning] slave:can ' t create database ' test '; Database exists error_code:1007

2017-08-19t06:51:50.784530z [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "Slave START". We stopped at log ' binlog.000001 ' position 1082

Workaround:

mysql> Stop group_replication;

Query OK, 0 rows affected (9.43 sec)

mysql> drop database test;

ERROR 1290 (HY000): The MySQL server is running with the--super-read-only option so it cannot execute this statement

mysql> set global super_read_only=0;

Query OK, 0 rows Affected (0.00 sec)

mysql> drop database test;

Query OK, 0 rows Affected (0.00 sec)

mysql> set global Super_read_only=1;

Query OK, 0 rows Affected (0.00 sec)

mysql> set global group_replication_allow_local_disjoint_gtids_join=on;

Query OK, 0 rows Affected (0.00 sec)

mysql> start group_replication;

Query OK, 0 rows affected (3.19 sec)

in order to prevent This problem occurs when the MySQL node is started, and the following configuration can be added to the mysql configuration file.

Loose-group_replication_allow_local_disjoint_gtids_join=on

MySQL Learning note 16 Several common problems and solutions to group replication