Execute Stored Procedure - Inconsistencies with function/Smart Service across architectures

Hi Evan, to answer your questions:

1. The function is only permitted to call read-only stored procedures. The function attempts to detect when it's calling a stored procedure that's modifying data and reject the request when it identifies one. In this case it's calling (or believes it is calling) a stored procedure that's modifying data. The Smart Service is permitted to call stored procedures that modify data, so this is why the stored procedure will work.
2. The function relies on the underlying database JDBC driver reporting whether an update occurred

I suggest simplifying the stored procedure to identify which part is causing the function (JDBC driver) to identify that data modification is happening. Once you know what statement in the stored procedure it is, there may be options to modify it

James,

As mariaDB is a fork of MySQL and has a different jbdc driver, has the plug-in been updated to support  MariaDB?

As they have different drivers, it is possible that MariaDB is seeing two prepared statements executing into a variable as a mod. However, MySQL doesn’t have this issue for the same stored proc. So, just want to make sure there is not a coming update to the plug-in that may solve the issue. Thanks!

Can you share an example of the stored procedure you're calling? As long as the statements are not causing updates (e.g. insert, update, create) and sticking to plain select statements I would expect the stored procedure to work.

I'm not aware of any other reports that the stored procedure function is incorrectly detecting updates under MariaDB. If this is a critical point, I recommend raising a support case also as that's the best channel for getting support for the plug-in

James,

We are facing a similar issue when we are calling the stored procedure which uses complex join queries, function call in select part of the query and subqueries to get the result. I believe the sub queries are implicitly creating temporary tables which may cause the driver to think that an update transaction is being applied.

Do we have any update to the plug-in which can resolve the issue?

We have observed that MariaDB report a stored procedure as modifying data when it executes any of
the following commands:
- INSERT
-  UPDATE
- DELETE
- TRUNCATE
- CREATE TEMPORARY
- SELECT … INTO … (where the INTO target is not an output variable)

Additionally we had to make change to functions using "INTO" which were called in a select statement within a stored proc.

We recently ran into a very similar issue calling the executeStoredProcedure function from an expression rule which, in turn, utilized an internal stored function. Unfortunately, after the 20.4 upgrade, the ESP function would fail due to a SELECT .. INTO statement located within the stored function (simply returns a YES/NO response): 

Where the former statement would fail (SELECT.. INTO):

BEGIN

DECLARE returnVal VARCHAR(20);
DECLARE c1 INT;

SELECT COUNT(*) INTO c1
FROM OG_TOOL_VW
WHERE TOOL_SERVICE_ID IN
    (SELECT TOOL_SERVICE_ID
     FROM OG_TOOL_SERVICE
     WHERE MOBILIZATION_ID = @mobilizationId
       AND IS_ACTIVE = 1);

 IF c1 <> 0 THEN
SET returnVal = 'Yes';

 ELSE
SET returnVal = 'No';

 END IF;

 RETURN returnVal;

END

And the refactored SQL would return a success:

BEGIN

DECLARE returnVal VARCHAR(20);
DECLARE c1 INT;

SET c1 = (SELECT COUNT(*)
FROM OG_TOOL_VW
WHERE TOOL_SERVICE_ID IN
    (SELECT TOOL_SERVICE_ID
     FROM OG_TOOL_SERVICE
     WHERE MOBILIZATION_ID = @mobilizationId
       AND IS_ACTIVE = 1));

 IF c1 <> 0 THEN
SET returnVal = 'Yes';

 ELSE
SET returnVal = 'No';

 END IF;

 RETURN returnVal;

END

Hope this helps others who may run into this unintended migration side-effect.

JT

In case MSSQL I just found out that "SET NOCOUNT ON;" will solve your problem. ;)

I just ran into the same issue when my prod environment was upgraded to MariaDB a few hours ago.  The strange part is, the same stored procedure (verified as the same SQL code) still worked in my Dev and Test environments which were both upgraded to Maria previously.  I also verified that the environments were using the same version of the plug-in.