"MySQL case" ERROR 1786 (HY000)

1.1.1. ERROR 1786 (HY000)

"Environment description"

msyql5.6.14

"Error Message"

Error encountered when 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

"Cause of error"

ERROR1786 is caused by the opening of the Enforce_gtid_consistency=true function, MySQL official explained that when the Enforce_gtid_consistency function is enabled, MySQL only allows to ensure that the transaction security, and SQL statements that can be logged are executed, like CREATE TABLE ... select and create temporarytable statements, and SQL statements or transactions that update both the transaction table and the non-transactional table are not allowed to execute.

DB1 [test] [23:28:28]> show variableslike ' enforce_gtid_consistency ';

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

| variable_name | Value |

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

| enforce_gtid_consistency | On |

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

"Workaround"

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

Error when trying online dynamic modification:

DB1 [Test] [23:37:56]> set globalenforce_gtid_consistency=true;

ERROR 1238 (HY000): Variable ' enforce_gtid_consistency ' is a read only Variable