KB-1380 "Statement violates GTID consistency" error thrown when trying to execute stored procedures or run database queries in MySQL through Appian

Symptoms

When attempting to execute a stored procedure in MySQL through Appian or execute a database query through Appian, the following error is returned:

com.appiancorp.suiteapi.process.framework.ActivityExecutionException: There was a problem executing the SQL query. SQL Message : Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

Cause

This error is thrown when at least one of the tables involved is configured to use a nontransactional storage engine such as MyISAM.

According to the MySQL documentation here, "when using GTIDs, updates to tables using nontransactional storage engines such as MyISAM cannot be made in the same statement or transaction as updates to tables using transactional storage engines such as InnoDB."

Action

Use InnoDB instead of MyISAM. Steps on converting MyISAM to InnoDB can be found here.

Workaround

Update the design of your query/procedure to avoid this issue. For example:

• If using temporary tables in the stored procedure, try using subqueries in a single SQL statement instead.
• If using the Query Database smart service, try splitting the query into multiple nodes in the process model.

For more tailored suggestions on how to bypass this issue, Appian Support recommends contacting your DBA for more information.

Affected Versions

This article applies to all versions of Appian using MySQL as a business database.

Last Reviewed: June 2017