Error
Error Code:
1786
MySQL Error 1786: GTID Unsafe CREATE TABLE SELECT
Description
This error indicates that a `CREATE TABLE ... SELECT` statement was executed on a MySQL server configured with GTIDs enabled. MySQL considers this type of statement unsafe for GTID-based replication, as its execution on different replicas might not produce identical results, jeopardizing data consistency across the replication topology.
Error Message
Statement violates GTID consistency: CREATE TABLE ... SELECT.
Known Causes
4 known causesGTID Mode Enabled
The MySQL server or cluster is running with GTID mode enabled (e.g., `gtid_mode=ON`), which enforces strict GTID-safe operations for replication.
Unsafe `CREATE TABLE ... SELECT`
An attempt was made to create a new table and populate it directly from a `SELECT` query in a single statement, which MySQL explicitly flags as GTID-unsafe.
Replication Context
The server is likely a primary or a replica in a GTID-based replication topology, requiring strict transactional consistency across all nodes.
Implicit Non-Determinism
The `CREATE TABLE ... SELECT` statement's behavior might not be perfectly deterministic (e.g., due to implicit ordering or concurrent operations), making it unsuitable for consistent GTID assignment across replicas.
Solutions
4 solutions available1. Disable GTID for the Statement easy
Temporarily disable GTID for the specific 'CREATE TABLE ... SELECT' statement.
1
Execute the `SET SESSION gtid_domain_id = 0;` command before your `CREATE TABLE ... SELECT` statement. This temporarily disables GTID enforcement for the current session.
SET SESSION gtid_domain_id = 0;
CREATE TABLE new_table AS SELECT col1, col2 FROM existing_table WHERE condition;2
Immediately after the statement, re-enable GTID by setting `gtid_domain_id` back to its original value if you know it, or by restarting the session if it's a one-off operation. In many cases, simply running the statement with `gtid_domain_id = 0` is sufficient as the setting reverts at session end.
SET SESSION gtid_domain_id = <your_original_domain_id>; -- If known, otherwise not strictly needed for temporary use.2. Create Table First, Then Insert easy
Separate the table creation from the data insertion to avoid the GTID violation.
1
Create the new table structure first without any data.
CREATE TABLE new_table (col1 datatype, col2 datatype, ...);2
Then, insert the data from the existing table into the newly created table.
INSERT INTO new_table (col1, col2, ...) SELECT col1, col2, ... FROM existing_table WHERE condition;3. Use `mysqldump` for Data Transfer medium
Export the data from the source table and import it into a newly created table on the target.
1
On the source MySQL server, dump the structure and data of the table you want to copy.
mysqldump -u your_user -p --no-create-info --skip-triggers your_database existing_table > existing_table_data.sql2
On the target MySQL server, create the new table with the desired structure. You can get the `CREATE TABLE` statement from `SHOW CREATE TABLE existing_table;` on the source.
CREATE TABLE new_table (col1 datatype, col2 datatype, ...);3
Import the dumped data into the new table.
mysql -u your_user -p your_database < existing_table_data.sql4. Reconfigure GTID Mode (Advanced) advanced
Adjust the server's GTID configuration if this is a persistent issue.
1
Understand the implications of changing `gtid_mode` and `enforce_gtid_consistency`. This is a server-level configuration and requires careful planning, especially in production environments.
2
Edit your MySQL configuration file (`my.cnf` or `my.ini`).
3
Consider setting `gtid_mode = OFF` if you don't strictly need GTID-based replication or if you are migrating away from it. Alternatively, if you want to keep GTID enabled, ensure `enforce_gtid_consistency = ON` is set, and then use the other solutions to work within its constraints.
[mysqld]
gtid_mode = OFF
enforce_gtid_consistency = OFF4
Restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql