Execute Stored Procedure

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:

1. We use a Query Database node to truncate the database staging table.
2. We use an Execute Stored Procedure node to call a stored procedure which (only) contains an INSERT INTO ... SELECT ... statement.
3. We use an Export Data Store to Excel node to dump the contents of the staging table into a new Excel file.

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?

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:

1. We use a Query Database node to truncate the database staging table.
2. We use an Execute Stored Procedure node to call a stored procedure which (only) contains an INSERT INTO ... SELECT ... statement.
3. We use an Export Data Store to Excel node to dump the contents of the staging table into a new Excel file.

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?