mysql error handling for ERROR 1786 of HY000

ERROR 1786 (HY000)

[Environmental Description]

msyql5.6.14

[Error message]

Execute create table... An error was reported during select:

[

db1 [test] [23:01:58] > create tablelgmnr_bak select * from lgmnr;
ERROR 1786 (HY000): CREATE TABLE ... SELECTis forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1

]

[Reason for error]

ERROR1786 is due to enforce_gtid_consistency=true. MySQL explains that when enforce_gtid_consistency is enabled, MySQL only allows transactions to be secured and SQL statements that can be logged can be executed, such as create table... select and create temporarytable statements, and SQL statements or transactions that update both transactional and non-transactional tables are not allowed to execute.

db1 [test] [23:28:28]> show variableslike 'ENFORCE_GTID_CONSISTENCY';

+--------------------------+-------+

| Variable_name  | Value |

+--------------------------+-------+

| enforce_gtid_consistency | ON |

+--------------------------+-------+

【 Solution 】

Since the enforce_gtid_consistency parameter is read-only, you must restart the MySQL service for the configuration to take effect.

Error when attempting dynamic online modification:

[

db1 [test] [23:37:56] > set globalenforce_gtid_consistency=true;
ERROR 1238 (HY000): Variable'enforce_gtid_consistency' is a read only variable

]

Here's what others have added

mysql 5.7 Previous versions supported create table XXX as select * from XXX; This creates the table syntax, but es76EN5.7.ES77en version gtid is turned on and will report an error

ERROR 1786 (HY000):Statement violates GTID consistency: CREATE TABLE ... SELECT.

Official explanation: https: / / dev mysql. com/doc refman / 5.7 / en/replication - gtids - restrictions. html

[

CREATE TABLE... SELECT statements... SELECT is for statement replication this statement logged two separate one for creation table, and another for the insertion of rows from the source table into the new table just created. When this statement is executed within a There are a number of reasons for this, including the following: transaction, it possible in for, which that transaction containing inserts skipped slave... SELECT is supported when using GTID based replication.

]

Solution: Turn off GTID mode:

The modified parameters in my are:

[

gtid_mode = OFF
enforce_gtid_consistency = OFF

]

Restart MySQL and create again successfully:

mysql> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode   | OFF  |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like '%enforce_gtid_consistency%';
+--------------------------+-------+
| Variable_name      | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF  |
+--------------------------+-------+
1 row in set (0.01 sec)

mysql> create table t1 as select * from BS_CONT;
Query OK, 0 rows affected (0.12 sec)