Mysql – GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context

google-cloud-platformMySQL

My Magento version is 2.2.3. We are using a Google Cloud SQL 2nd gen instance.

Facing error while run indexing command

General error: 1787 Statement violates GTID consistency: CREATE
TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside
transactional context. These statements are also not allowed in a
function or trigger because functions and triggers are also considered
to be multi-statement transactions., query was: CREATE TEMPORARY TABLE
IF NOT EXISTS catalog_product_index_eav_temp LIKE
catalog_product_index_eav_tmp

Best Answer

Seems Magento does use forbidden statements for GTID to do the indexing.

Because Google Cloud Second Generation instances intrinsically do use GTID, Magento cannot work with this kind of instance. There is a post in Github in which many users are experiencing your same problem (probably because an update?).

In that post, it suggests you might want to use SQL First Generation instead. The user called "klarre1" commented this change solved the same problem for him.

Related Solutions

Mysql: allow create/insert/delete for temporary tables only

I understand this doesn't exactly answer your request but...

You may want to handle it using an ACL (Access Control List) of permissions for this user. You want to specify by table what this user can SELECT. Next, create a database specifically for this user's purpose with SELECT,CREATE permissions.

E.g.

GRANT SELECT ON dbname.tablename1 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';
GRANT SELECT ON dbname.tablename2 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';
GRANT SELECT ON dbname.tablename3 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';

GRANT CREATE,SELECT,DROP ON tmpdb.tablename1 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';
GRANT CREATE,SELECT,DROP ON tmpdb.tablename2 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';
GRANT CREATE,SELECT,DROP ON tmpdb.tablename3 TO 'username'@'ip-or-hostname' IDENIFIED BY 'password';

MySQL: Log only drop/create commands

There is no sophisticated way to log queries in mysql. The only thing you can do is the following:

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

http://dev.mysql.com/doc/refman/5.1/en/query-log.html

Best Answer

Related Solutions

Mysql: allow create/insert/delete for temporary tables only

MySQL: Log only drop/create commands

Related Topic