MySQL Error 1786 (HY000) case

1.1.1. ERROR 1786 (HY000)

"Environment description"

msyql5.6.14

"Error Message"

Executing the CREATE TABLE ... an error was encountered while 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 the error"

ERROR1786 is due to the opening of the Enforce_gtid_consistency=true function, MySQL official explained that when the Enforce_gtid_consistency feature is enabled, MySQL only allows security of the transaction, and the SQL statements that can be logged are executed, like CREATE TABLE ... select and create temporarytable statements, and SQL statements or transactions that update both transaction 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 |

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

"Workaround"

Because the enforce_gtid_consistency parameter is read-only, the MySQL service must be restarted to be configured to take effect.

Error when attempting to dynamically modify online:

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

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

This column more highlights: http://www.bianceng.cn/database/MySQL/