Derby
  1. Derby
  2. DERBY-5578

Provide a way to invalidate stored prepared statements

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.10.1.1
    • Component/s: Services
    • Labels:
      None
    • Issue & fix info:
      High Value Fix, Newcomer, Release Note Needed

      Description

      In various support situations I have seen problems with JDBC metadata stored prepared statements or trigger stored prepared statements that need to be invalidated. It would be nice to have a way to do this in the field. For 10.9 a stored procedure would make most sense, but it would be good to have something available in the release branches too.

      1. releaseNote.html
        3 kB
        Rick Hillegas
      2. derby-5578-upgrade.sql
        1 kB
        Rick Hillegas
      3. derby-5578-create.sql
        0.8 kB
        Rick Hillegas
      4. DERBY5578_patch2_diff.txt
        15 kB
        Mamta A. Satoor
      5. DERBY5578_patch1_diff.txt
        4 kB
        Mamta A. Satoor

        Activity

        Knut Anders Hatlen made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Hide
        Knut Anders Hatlen added a comment -

        [bulk update] Close all resolved issues that haven't been updated for more than one year.

        Show
        Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
        Gavin made changes -
        Workflow jira [ 12649889 ] Default workflow, editable Closed status [ 12802081 ]
        Rick Hillegas made changes -
        Attachment releaseNote.html [ 12573878 ]
        Hide
        Rick Hillegas added a comment -

        Adding a release note based on the Reference Manual's description of the procedure.

        Show
        Rick Hillegas added a comment - Adding a release note based on the Reference Manual's description of the procedure.
        Mamta A. Satoor made changes -
        Issue & fix info High Value Fix,Newcomer [ 10422, 10423 ] High Value Fix,Newcomer,Release Note Needed [ 10422, 10423, 10101 ]
        Hide
        Rick Hillegas added a comment -

        The following script demonstrates what the Reference Manual says:

        o By default, only the DBO can run SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS.

        o However, the DBO can grant other users the privilege to run this procedure.

        connect 'jdbc:derby:memory:db;create=true;user=test_dbo';

        – turn on NATIVE authentication. won't take effect until you bounce the database.
        call syscs_util.syscs_create_user( 'test_dbo', 'test_dbopassword' );
        call syscs_util.syscs_create_user( 'fred', 'fredpassword' );

        – bounce the database in order to turn on NATIVE authentication
        connect 'jdbc:derby:memory:db;shutdown=true';
        connect 'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword' as dbo;

        – the DBO can run this procedure
        call syscs_util.syscs_invalidate_stored_statements();

        connect 'jdbc:derby:memory:db;user=fred;password=fredpassword' as fred;

        – should fail because privilege has not been granted
        call syscs_util.syscs_invalidate_stored_statements();

        set connection dbo;

        --grant privilege to fred;
        grant execute on procedure syscs_util.syscs_invalidate_stored_statements to fred;

        set connection fred;

        – should work now that fred has been granted privilege
        call syscs_util.syscs_invalidate_stored_statements();

        Show
        Rick Hillegas added a comment - The following script demonstrates what the Reference Manual says: o By default, only the DBO can run SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS. o However, the DBO can grant other users the privilege to run this procedure. connect 'jdbc:derby:memory:db;create=true;user=test_dbo'; – turn on NATIVE authentication. won't take effect until you bounce the database. call syscs_util.syscs_create_user( 'test_dbo', 'test_dbopassword' ); call syscs_util.syscs_create_user( 'fred', 'fredpassword' ); – bounce the database in order to turn on NATIVE authentication connect 'jdbc:derby:memory:db;shutdown=true'; connect 'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword' as dbo; – the DBO can run this procedure call syscs_util.syscs_invalidate_stored_statements(); connect 'jdbc:derby:memory:db;user=fred;password=fredpassword' as fred; – should fail because privilege has not been granted call syscs_util.syscs_invalidate_stored_statements(); set connection dbo; --grant privilege to fred; grant execute on procedure syscs_util.syscs_invalidate_stored_statements to fred; set connection fred; – should work now that fred has been granted privilege call syscs_util.syscs_invalidate_stored_statements();
        Rick Hillegas made changes -
        Attachment derby-5578-create.sql [ 12550970 ]
        Attachment derby-5578-upgrade.sql [ 12550971 ]
        Hide
        Rick Hillegas added a comment -

        Attaching derby-5578-create.sql and derby-5578-upgrade.sql. These are scripts which I used to buddy-test this feature. The first script relies on the wrapper DatabaseMetaData functions attached to DERBY-3973. Here's how you run these scripts:

        o First run derby-5578-create.sql using an earlier release like 10.8.2.2. This script creates a trigger and registers functions to wrap the DatabaseMetaData methods.

        o Then run derby-5578-upgrade.sql using the 10.10 trunk. This script hard-upgrades the database, adding the new SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS procedure.

        The scripts verify that SYSCS_INVALIDATE_STORED_STATEMENTS marks all stored prepared statements as invalid in SYS.SYSSTATEMENTS. The scripts verify that invoking the trigger after the invalidation causes it to be marked as valid and updates its lastcompiled timestamp. Those columns in SYS.SYSSTATEMENTS are similarly updated for a DatabaseMetaData method when you invoke it after the bulk invalidation.

        This indicates to me that SYSCS_INVALIDATE_STORED_STATEMENTS behaves as described in the Reference Manual.

        Show
        Rick Hillegas added a comment - Attaching derby-5578-create.sql and derby-5578-upgrade.sql. These are scripts which I used to buddy-test this feature. The first script relies on the wrapper DatabaseMetaData functions attached to DERBY-3973 . Here's how you run these scripts: o First run derby-5578-create.sql using an earlier release like 10.8.2.2. This script creates a trigger and registers functions to wrap the DatabaseMetaData methods. o Then run derby-5578-upgrade.sql using the 10.10 trunk. This script hard-upgrades the database, adding the new SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS procedure. The scripts verify that SYSCS_INVALIDATE_STORED_STATEMENTS marks all stored prepared statements as invalid in SYS.SYSSTATEMENTS. The scripts verify that invoking the trigger after the invalidation causes it to be marked as valid and updates its lastcompiled timestamp. Those columns in SYS.SYSSTATEMENTS are similarly updated for a DatabaseMetaData method when you invoke it after the bulk invalidation. This indicates to me that SYSCS_INVALIDATE_STORED_STATEMENTS behaves as described in the Reference Manual.
        Mamta A. Satoor made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Mamta A. Satoor made changes -
        Fix Version/s 10.10.0.0 [ 12321550 ]
        Hide
        Mamta A. Satoor added a comment -

        Knut, thanks for reviwing the patch. I have made the changes suggested by you and committed the changes to trunk(10.10)

        Show
        Mamta A. Satoor added a comment - Knut, thanks for reviwing the patch. I have made the changes suggested by you and committed the changes to trunk(10.10)
        Hide
        Knut Anders Hatlen added a comment -

        The upgrade test should probably go into Changes10_10, since that's the current version of trunk. And maybe the new helper methods in TriggerTest should close the ResultSets. Otherwise, the patch looks good to me.

        Nits: Some small typos in the comments in TriggerTest that you might want to fix:

        • "Creat the required tables" - creat -> create
        • "It's trigger action" - It's -> Its
        • "trigger action with have" - with -> will
        Show
        Knut Anders Hatlen added a comment - The upgrade test should probably go into Changes10_10, since that's the current version of trunk. And maybe the new helper methods in TriggerTest should close the ResultSets. Otherwise, the patch looks good to me. Nits: Some small typos in the comments in TriggerTest that you might want to fix: "Creat the required tables" - creat -> create "It's trigger action" - It's -> Its "trigger action with have" - with -> will
        Hide
        Dag H. Wanvik added a comment -

        > I don't see any reason why we'd want to deny the DBO's granting the execute permission on this procedure, so +1 to keeping it in SYSCS_UTIL.

        +1

        Show
        Dag H. Wanvik added a comment - > I don't see any reason why we'd want to deny the DBO's granting the execute permission on this procedure, so +1 to keeping it in SYSCS_UTIL. +1
        Mamta A. Satoor made changes -
        Attachment DERBY5578_patch2_diff.txt [ 12530592 ]
        Hide
        Mamta A. Satoor added a comment -

        Attaching patch DERBY5578_patch2_diff.txt which is ready for commit. This patch has required code changes to add a new procedure which will allow users to invalidate all the stored statements inside SYS.SYSSTATEMENTS. At this point, there are two types of stored statements in that system table - statements for metadata calls and statements for trigger action plans. I have also added test cases including the regression tests and upgrade tests. Upgrade tests show that this procedure is available only after hard upgrade. Regression test show how the procedure can be executed only by dba unless dba grants execute permission to other users. Additionally, it has test cases showing statements getting invalidated by the procedure call and subsequent execution of metadata call or trigger causing their corresponding statements to become valid.

        Please let me know if there is any feedback on the code changes or any other test cases I should add.

        The changes for this jira can only go in as part of a major release since it fiddles around wit system tables. This jira can't be backported to released branches.

        If Rick sees a need to cut another 10.9 release jars, we can backport it to 10.9 but otherwise it will need to go into next major release.

        Show
        Mamta A. Satoor added a comment - Attaching patch DERBY5578_patch2_diff.txt which is ready for commit. This patch has required code changes to add a new procedure which will allow users to invalidate all the stored statements inside SYS.SYSSTATEMENTS. At this point, there are two types of stored statements in that system table - statements for metadata calls and statements for trigger action plans. I have also added test cases including the regression tests and upgrade tests. Upgrade tests show that this procedure is available only after hard upgrade. Regression test show how the procedure can be executed only by dba unless dba grants execute permission to other users. Additionally, it has test cases showing statements getting invalidated by the procedure call and subsequent execution of metadata call or trigger causing their corresponding statements to become valid. Please let me know if there is any feedback on the code changes or any other test cases I should add. The changes for this jira can only go in as part of a major release since it fiddles around wit system tables. This jira can't be backported to released branches. If Rick sees a need to cut another 10.9 release jars, we can backport it to 10.9 but otherwise it will need to go into next major release.
        Hide
        Knut Anders Hatlen added a comment -

        I don't see any reason why we'd want to deny the DBO's granting the execute permission on this procedure, so +1 to keeping it in SYSCS_UTIL.

        I think you're right that only meta-data queries and triggers are stored in SYSSTATEMENTS. It looks like there used to be syntax for CREATE STATEMENT, which allowed user-defined queries to be stored there too, but that syntax is disabled now.

        Show
        Knut Anders Hatlen added a comment - I don't see any reason why we'd want to deny the DBO's granting the execute permission on this procedure, so +1 to keeping it in SYSCS_UTIL. I think you're right that only meta-data queries and triggers are stored in SYSSTATEMENTS. It looks like there used to be syntax for CREATE STATEMENT, which allowed user-defined queries to be stored there too, but that syntax is disabled now.
        Hide
        Mamta A. Satoor added a comment -

        As per my understanding, SYSSTATEMENTS will only has two kinds stored prepared statements - for metadata queries and for trigger action plan. Please let me know if there are other possibilities. I am working on writing tests and want to make sure that I provide test cases covering different kinds of stored statements. Thanks

        Show
        Mamta A. Satoor added a comment - As per my understanding, SYSSTATEMENTS will only has two kinds stored prepared statements - for metadata queries and for trigger action plan. Please let me know if there are other possibilities. I am working on writing tests and want to make sure that I provide test cases covering different kinds of stored statements. Thanks
        Hide
        Mamta A. Satoor added a comment -

        While writing one of the tests for this procedure, I saw that, as a dba, I was able to grant the execute permission on this procedure to another user.
        grant execute on procedure syscs_util.SYSCS_INVALIDATE_STORED_STATEMENTS to sam;

        But a similar grant on sysibm.sqlprocedures by dbo to another user fails
        grant execute on procedure sysibm.sqlprocedures to sam;
        ERROR 42509: Specified grant or revoke operation is not allowed on object 'SYSIBM.SQLPROCEDURES'.

        Looking at the code, I found that SchemaDescriptor.isSchemaWithGrantableRoutines() identifies SQLJ and SYSCS_UTIL as schemas which allows grant on it's routines. And that is why we were able to grant execute on syscs_util.SYSCS_INVALIDATE_STORED_STATEMENTS but not on sysibm.sqlprocedures.

        So, the question is should the new procedure for invalidating stored statements have grantable execute permission? If not, then should we move it to some other schema like sysibm which does not allow grant execute permission on it's procedures?

        Show
        Mamta A. Satoor added a comment - While writing one of the tests for this procedure, I saw that, as a dba, I was able to grant the execute permission on this procedure to another user. grant execute on procedure syscs_util.SYSCS_INVALIDATE_STORED_STATEMENTS to sam; But a similar grant on sysibm.sqlprocedures by dbo to another user fails grant execute on procedure sysibm.sqlprocedures to sam; ERROR 42509: Specified grant or revoke operation is not allowed on object 'SYSIBM.SQLPROCEDURES'. Looking at the code, I found that SchemaDescriptor.isSchemaWithGrantableRoutines() identifies SQLJ and SYSCS_UTIL as schemas which allows grant on it's routines. And that is why we were able to grant execute on syscs_util.SYSCS_INVALIDATE_STORED_STATEMENTS but not on sysibm.sqlprocedures. So, the question is should the new procedure for invalidating stored statements have grantable execute permission? If not, then should we move it to some other schema like sysibm which does not allow grant execute permission on it's procedures?
        Hide
        Mamta A. Satoor added a comment -

        Knut, I think that's a good idea to include STORED in the procedure name to indicate that we are only going to invalidate stored prepared statements. I will make the procedure name change.

        Show
        Mamta A. Satoor added a comment - Knut, I think that's a good idea to include STORED in the procedure name to indicate that we are only going to invalidate stored prepared statements. I will make the procedure name change.
        Hide
        Knut Anders Hatlen added a comment -

        Maybe we should call it something like SYSCS_INVALIDATE_STORED_STATEMENTS, just so that it's clear from the name that it doesn't invalidate ordinary statements?

        Show
        Knut Anders Hatlen added a comment - Maybe we should call it something like SYSCS_INVALIDATE_STORED_STATEMENTS, just so that it's clear from the name that it doesn't invalidate ordinary statements?
        Mamta A. Satoor made changes -
        Attachment DERBY5578_patch1_diff.txt [ 12530233 ]
        Hide
        Mamta A. Satoor added a comment -

        Attaching a patch which adds the new stored procedure which will invalidate all the stored statements. Next execution of the statement will cause it to recompile. I will work next on adding regression tests and upgrade tests.

        Show
        Mamta A. Satoor added a comment - Attaching a patch which adds the new stored procedure which will invalidate all the stored statements. Next execution of the statement will cause it to recompile. I will work next on adding regression tests and upgrade tests.
        Mamta A. Satoor made changes -
        Field Original Value New Value
        Assignee Mamta A. Satoor [ mamtas ]
        Hide
        Dag H. Wanvik added a comment -

        For field work-arounds I would imagine DBO access would suffice.

        Show
        Dag H. Wanvik added a comment - For field work-arounds I would imagine DBO access would suffice.
        Hide
        Mamta A. Satoor added a comment -

        Was wondering, would only a dba have permissions to invalidate the prepared statements?

        Show
        Mamta A. Satoor added a comment - Was wondering, would only a dba have permissions to invalidate the prepared statements?
        Kathey Marsden created issue -

          People

          • Assignee:
            Mamta A. Satoor
            Reporter:
            Kathey Marsden
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development