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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.