:An error is reported when you execute a create table XXX select XXX statement in apsaradb for MySQL

Problem description

When you execute the create table XXX select XXX SQL statement in apsaradb for MySQL to create a table and copy the old table data, the following error occurs.

1786-Statement violates GTID consistency:create table ... select  

Possible cause

Apsaradb for MySQL does not support this method.

Solution

Alibaba Cloud reminds you that:

• Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
• You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
• If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.

1. Connect to the RDS instance. For more information, see connect to an RDS instance.
2. Run the following SQL statement to create a new table:

   create table [$Table_Name1] like [$Table_Name2];

   Note:
   

   • [$Table_Name1]: select the new table name.
   • [$Table_Name2]: the name of the old table.

3. Run the following SQL statement to insert data from the old table into the new table:

   insert into [$Table_Name1] select * from [$Table_Name2];

Scope

• ApsaraDB RDS for MySQL