[MDEV-31064] I_S.parameters not immediatly changed updated after procedure change Created: 2023-04-17  Updated: 2023-12-05  Resolved: 2023-07-24

Status: Closed
Project: MariaDB Server
Component/s: Information Schema
Affects Version/s: 10.6.11, 10.11.1
Fix Version/s: 10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2, 11.2.1

Type: Bug Priority: Major
Reporter: Diego Dupin Assignee: Lawrin Novitsky
Resolution: Fixed Votes: 0
Labels: CONNECTOR_RELATED

Issue Links:
Blocks
blocks CONJ-1069 DatabaseMetaData#getProcedureColumns ... Closed

 Description   

If having use a procedure, then in another connection, this procedure is changed (removing one parameter), a call to INFORMATION_SCHEMA.parameters will not reflect the changes in the the first connection.

example to reproduce :
create procedure :

create or replace procedure metaChange(p1 int, p2 int)
 
BEGIN
 
  select p1 as 'p1';
 
END

The failing scenario:

  • connection 1 : execute 

    call metaChange(1, 2)

  • connection 1 : execute 

    SELECT * FROM information_schema.parameters where SPECIFIC_NAME='metaChange';

    => return the p1 and p2 parameters as expected

  • connection 2 : change procedure: 

    create or replace procedure metaChange(p1 int)
    		 
    BEGIN
    		 
      select p1 as 'p1';
    		 
    END

    procedure has now only one parameter

  • connection 2 : execute

    SELECT * FROM information_schema.parameters where SPECIFIC_NAME='metaChange';

    => return the p1 parameters as expected

  • connection 1 : execute

    SELECT * FROM information_schema.parameters where SPECIFIC_NAME='metaChange';

    => still return the p1 and p2 parameters

 Comments   
Comment by Julian Bui [ 2023-04-17 ]

I think the step "connection 2 : change procedure" should have removed the "p2 int" parameter from the procedure's inputs.

Comment by Julian Bui [ 2023-04-24 ]

Here is the original ticket I created that spawned this one: CONJ-1069

As far as I know, the title of the ticket here is a little misleading and makes the problem seem less severe than it is? It's not that it doesn't "immediately" change, it just doesn't seem to change.

Comment by Lawrin Novitsky [ 2023-05-22 ]

It possibly makes more sense to check if cache entry is not obsolete right in the sp_cache_lookup. In this way we ensure, that the similar situation won't happen under any circumstances. I don't think there can be any need to use obsolete object anywhere.

Comment by Lawrin Novitsky [ 2023-06-01 ]

The new fix version doesn't remove obsolete sphead object from the cache entry, as it can be expected to exist by procedure being executed, but only does not take if for the forming of query results and compiles new object.

In the fix sphead's version is compared with global cache version to decide if it's not obsolete and can be used. But probaibly it would better to add the method to the cache, that will tell if object is obsolete. As it looks like cache's job to tell that

Comment by Lawrin Novitsky [ 2023-06-05 ]

I've pushed the new version. I've checked all test failures - they also fail in other branches, and do not really look like smth that I could cause. Could you please review the patch?

Comment by Oleksandr Byelkin [ 2023-07-06 ]

OK to puh

Comment by Lawrin Novitsky [ 2023-07-24 ]

Looks it's making to the 10.6.15

Generated at Thu Feb 08 10:21:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.