**NOTE: This plug-in is deprecated and no longer recommended for use for sites running on Appian 22.1 or later. The functionality of this component is available in Appian without installing the plug-in. You can use a!executeStoredProcedureForQuery(), a!executeStoredProcedureOnSave(), or the Execute Stored Procedure Smart Service in Appian 22.1 or later.**
Overview
This plug-in provides a Smart Service and Function for executing stored procedures. The Smart Service can be used to execute stored procedures that modify data. Result sets are returned as CDTs. As custom functions cannot have side-effects, it must not be used to execute stored procedures that modify data. It is only safe to use the Smart Service with stored procedures that modify data. Following SQL Statements should not be used by the stored procedures executed with the function:
Key Features & Functionality
Hi James.
I'm seeing periodic issues in our High Availability Appian Cloud production environment when using the Execute Stored Procedure plugin. Our database is MySQL. Our Appian version is 19.3.
We have an automated process which triggers every night at midnight. This process is responsible for generating an Excel file which contains human-readable details for every member of a specific Record type. The process is fairly simple:
There should be roughly 32,000 rows of data in this table. The generated Excel file should be approximately 18.5MB in size. The process successfully completes every time, with no errors.
On a handful of occasions (and only in our production environment), however, we have noticed that the process will successfully run, but will generate an Excel file which is blank. Upon further investigation, we noticed that, on these occasions, the database staging table has not been populated, even though the stored procedure node ran & completed successfully, with no errors. When we manually re-run this process to try to generate the Excel file correctly, it sometimes will work as expected. Other times, the process will, again, complete with no errors, but will generate a blank Excel file (due to the database staging table still not being populated). Still other times, the Execute Stored Procedure node will "hang", sometimes for upwards of 30 minutes. In this last scenario, we observe in PHPMyAdmin that the database process which contains the execution of the stored procedure is "Waiting for table metadata lock".
I have a theory as to what may be going on, but I have no way to really confirm or prove it. Perhaps you can shed some light on this for me. I believe that longer-running stored procedures, executed through the Execute Stored Procedure plugin, may sometimes lose or otherwise fail to close their implicit transactions.
You have previously stated that "The smart service (will) only proceed when the stored procedure has finished executing." In our case, however, we have seen that the Smart Service node will sometimes complete when the result of the stored procedure has not yet been COMMIT-ted. Adding a wait timer after the Execute Stored Procedure node does not help, either. I believe that the implicit COMMIT statement, called after the execution of the stored procedure completes, might occasionally be "killed" mid-flight by the closing of the database connection at the time the Smart Service node completes, especially if the modified data set is sufficiently large.
This issue only occurs in our production environment, where we have roughly 8,000 more rows of data than in our lower environments. I've already created an Appian Support case to get help investigating this issue, to no avail, but I was advised to reach out to you, the plugin author, directly. Do you have any thoughts or insight into what I've described above?