• State Not Answered
  • Replies 3 replies
  • Subscribers 7 subscribers
  • Views 5174 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Plug-Ins

Execute Stored Procedure Smart Service & 18.3

paulc919

Last week we had upgraded our Development environment to 18.3. We quickly found out that our implementation of certain Stored Procedures on MySQL no longer worked - we were getting a GTID enforcement message that we hadn't seen before. We've now rolled back to 18.2 and are exploring options for addressing this issue. 

It seems we were abusing the CREATE TEMPORARY TABLE aspect of Stored Procs in MySQL when invoked via the plug in Smart Service. 18.2 and lower the stored procs worked correctly, post 18.3 they errored. 

I've reached out to the Plug In author to see what he suggests but wondered if anyone else out there has already encountered this issue and if they have what solutions were used ? 

We've got a variety of options on the table, some seem straight forward and others quite complicated - so I'm trying to find that happy balance of what works but more importantly what is right. 

Many thanks 

Paul.

  Discussion posts and replies are publicly visible

Parents
  • 0
    TLDR; you might need to create a copy of the plugin that does not wrap the stored procedure call in a transaction. Below covers more details:

    Later versions of Appian required GTID to be enabled. This has a few restrictions on what you can do in MySQL. This document covers most of the restrictions:

    dev.mysql.com/.../replication-gtids-restrictions.html

    To handle these, a refactor of the stored procedure is needed. The two major changes aren't too difficult to handle. For any CREATE TABLE ... SELECT statements, just refactor to create and define the table first then populate it. For the CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements, move those statements out of the transactions.

    In order to address your Smart Service issue, you'll either need to refactor the stored proc to not use temp tables (may not be feasible), or create a copy of the plugin that does not wrap the stored procedure call in a transaction. My understanding is that the smart service automatically wraps the stored procedure calls into a transaction, which is why you are getting the GTID violation error.

    I /think/ another option would be to disable GTID on your database if that is an available option for you. This is typically not available for Cloud managed deployments.
Reply
  • 0
    TLDR; you might need to create a copy of the plugin that does not wrap the stored procedure call in a transaction. Below covers more details:

    Later versions of Appian required GTID to be enabled. This has a few restrictions on what you can do in MySQL. This document covers most of the restrictions:

    dev.mysql.com/.../replication-gtids-restrictions.html

    To handle these, a refactor of the stored procedure is needed. The two major changes aren't too difficult to handle. For any CREATE TABLE ... SELECT statements, just refactor to create and define the table first then populate it. For the CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements, move those statements out of the transactions.

    In order to address your Smart Service issue, you'll either need to refactor the stored proc to not use temp tables (may not be feasible), or create a copy of the plugin that does not wrap the stored procedure call in a transaction. My understanding is that the smart service automatically wraps the stored procedure calls into a transaction, which is why you are getting the GTID violation error.

    I /think/ another option would be to disable GTID on your database if that is an available option for you. This is typically not available for Cloud managed deployments.
Children
No Data

© 2024 Appian. All rights reserved.