[MySQL Case] ERROR 1786 (HY000)

1.1.1. ERROR 1786 (HY000)

[Environment description]

Msyql5.6.14

[Error message]

An error occurred while executing create table... 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

[Error cause]

ERROR1786 is because enforce_gtid_consistency = true is enabled. MySQL officially explains that when enforce_gtid_consistency is enabled, MySQL only allows transaction security, SQL statements that can be logged are executed, such as create table... select and create temporarytable statements, and SQL statements or transactions that update both the transaction table and non-transaction table are not allowed to be executed.

 

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 to make the configuration take effect.

An error occurs when dynamic online modification is attempted:

Db1 [test] [23:37:56]> set globalenforce_gtid_consistency = true;

ERROR 1238 (HY000): Variable 'enforce _ gtid_consistency 'is a read only variable