Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Selecting from MON$CALL_STACK within a Trigger or SP sometimes return 0 rows. [CORE2286] #2712

Closed
firebird-issue-importer opened this issue Jan 21, 2009 · 20 comments

Comments

@firebird-issue-importer

Submitted by: Nunzio Modesto (nunzio)

Votes: 1

Selecting from MON$CALL_STACK within a Trigger or SP sometimes return 0 rows especially when the trigger is fired by a Foreign Key with cascade on update.

Metadata of the trigger :

Note:
If uncomment the first select in the trigger body it will return rows, but sometimes when the trigger is fired frequently in the same transaction it keeps returning the same set of rows instead the real call stack.)

CREATE OR ALTER TRIGGER ART_COLORI_AIUD0 FOR ART_COLORI
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0
AS
DECLARE VARIABLE STK_COUNT INTEGER;
BEGIN
--SELECT COUNT(*) FROM MON$CALL_STACK INTO :STK_COUNT;

INSERT INTO CALL_STACK (MON$OBJECT_NAME) VALUES ('***************** begin ------');
INSERT INTO CALL_STACK (MON$CALL_ID,
MON$STATEMENT_ID,
MON$CALLER_ID,
MON$OBJECT_NAME,
MON$OBJECT_TYPE,
MON$TIMESTAMP,
MON$SOURCE_LINE,
MON$SOURCE_COLUMN,
MON$STAT_ID)
SELECT
CS.* FROM MON$CALL_STACK CS
LEFT JOIN MON$STATEMENTS ST ON (CS.MON$STATEMENT_ID = ST.MON$STATEMENT_ID)
WHERE
ST.MON$TRANSACTION_ID = CURRENT_TRANSACTION
ORDER BY MON$CALL_ID ASC;
INSERT INTO CALL_STACK (MON$OBJECT_NAME) VALUES ('************** end -----');
END

Commits: d6c745b ed31717 606f56c

====== Test Details ======

Abandon from implementing test after discuss with dimitr, see letter 03-mar-18 15:11.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 21, 2009

Commented by: @dyemanov

The monitoring information snapshot is created upon the first reference to the MON$ tables and cached until the transaction completion. So any subsequent queries are expected to return the same set of rows.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 22, 2009

Commented by: Sean Leyne (seanleyne)

Nunzio,

My sense of the example you provided is that you really are looking for database/SQL traceing which is a different function from what the MON$ tables are for. The SQL traceing functionality (SQL Trace) is scheduled to be introduced in the v2.5 release.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 22, 2009

Commented by: Nunzio Modesto (nunzio)

I just made the example that write the content of the mon$table in a persistent table on the database to figure out what was in the mon$call_stack table.

according to what Dimitry said the snapshot of the mon$ table is generated when I first reference the table itself, that's ok it make sense now why I always got the same set of records.

I'm using FB 2.1.2 so to solve that problem I wrote a UDF that allow me to check the mon$ table in a separate transaction/connection and it works ok (I'll drop that when the v2.5 will be released and I'll use the AUTONOMOUS TRANSACTION feature)

But the problem that sometimes the mon$call_stack returns 0 rows remains, and since the query is done inside a trigger there should be at least one row.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 22, 2009

Commented by: @dyemanov

Can you be sure that the failing query is always the first monitoring request in that transaction? Maybe zero rows is an already cached result?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 22, 2009

Commented by: Nunzio Modesto (nunzio)

Here's my test scenario

a UDF with a function name "udf_StackStatusOK" that does only the following in the given order:
- open a new connection to the database
- start a new transaction
- execute this query : SELECT MON$CALL_ID, MON$CALLER_ID, MON$OBJECT_NAME FROM MON$CALL_STACK
- dump the content to a log file
- close transaction
- close connection
- return a integer value always 1

Table TABLE1 with the following metadata:
- ID INTEGER (Primary Key)
- TEST VARCHAR(10)

Trigger TABLE1_AIUD0 on TABLE1 (after inser,update,delete):
- It only call the UDF Function (SELECT udf_StackStatusOK('TEST') FROM RDB$DATABASE INTO....)

Stored Procedure PERFORM_TEST which execute the following:
- UPDATE TABLE1 SET TEST = 'OK' WHERE ID = 1;
- UPDATE TABLE1 SET TEST = 'OK' WHERE ID = 2;

That's what I expected when I execute the procedure :
-- first update in SP with ID = 1 --
Trigger TABLE1_AIUD0
called by Stored Proc PERFORM_TEST
-- second update SP with ID = 1 --
Trigger TABLE1_AIUD0
called by Stored Proc PERFORM_TEST

That's what I get when I execute the procedure :
-- first update in SP with ID = 1 --
Trigger TABLE1_AIUD0
called by Stored Proc PERFORM_TEST
-- second update SP with ID = 1 --
< no records in mon$call_stack>

I also noted that the value of MON$CALLER_ID refers to a value not present
in table MON$CALL_STACK when a FOREIGN KEY with CASCADE on update attempt to
update the value of child tables.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 22, 2009

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 22, 2009

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 26, 2009

Commented by: Riccardo Fiorenza (ricfio)

Hi, I have the same problem with monitoring table MON$CALL_STACK and this configuration:
Firebird 2.1.1 on Windows XP SP3.

I have two simple tables ("TAB_H" and "TAB_R") with trigger ("TAB_H_AI0" and "TAB_R_AI0") after insert to capture the records from MON$CALL_STACK.

If I execute two INSERT queries inside the "SHOW_BUG" procedure on the tables "TAB_H" and "TAB_R", the first INSERT query on "TAB_H" returning a right call_stack while the second INSERT query on "TAB_R" show the same call_stack registered at the previous INSERT query on "TAB_H".
(show query C)

If I execute separatly the two INSERT queries then it's all ok without the bug.
(show queries A and B)

To reproduce this bug please create the following db structure (4 tables + 1 procedure),
and then exec these queries:
A) SELECT * FROM TEST_BUG('TAB_H');
B) SELECT * FROM TEST_BUG('TAB_R');
C) SELECT * FROM TEST_BUG(NULL);

The queries A) and B) are ok (NO BUG), while the query C) is wrong (BUG SHOWED).

Thank you,
Riccardo Fiorenza

-------------

CREATE TABLE CALL_STACK_H (
MON$CALL_ID INTEGER,
MON$STATEMENT_ID INTEGER,
MON$CALLER_ID INTEGER,
MON$OBJECT_NAME CHAR(31) CHARACTER SET UNICODE_FSS,
MON$OBJECT_TYPE SMALLINT,
MON$TIMESTAMP TIMESTAMP,
MON$SOURCE_LINE INTEGER,
MON$SOURCE_COLUMN INTEGER,
MON$STAT_ID INTEGER
);

CREATE TABLE CALL_STACK_R (
MON$CALL_ID INTEGER,
MON$STATEMENT_ID INTEGER,
MON$CALLER_ID INTEGER,
MON$OBJECT_NAME CHAR(31) CHARACTER SET UNICODE_FSS,
MON$OBJECT_TYPE SMALLINT,
MON$TIMESTAMP TIMESTAMP,
MON$SOURCE_LINE INTEGER,
MON$SOURCE_COLUMN INTEGER,
MON$STAT_ID INTEGER
);

CREATE TABLE TAB_H (
TMS TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE TAB_R (
TMS TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

SET TERM ^ ;

/* Trigger: TAB_H_AI0 */
CREATE TRIGGER TAB_H_AI0 FOR TAB_H
ACTIVE AFTER INSERT POSITION 0
AS
begin
INSERT INTO CALL_STACK_H SELECT * FROM MON$CALL_STACK;
end
^

/* Trigger: TAB_R_AI0 */
CREATE TRIGGER TAB_R_AI0 FOR TAB_R
ACTIVE AFTER INSERT POSITION 0
AS
begin
INSERT INTO CALL_STACK_R SELECT * FROM MON$CALL_STACK;
end
^

CREATE OR ALTER PROCEDURE TEST_BUG (
TODO_INSERT_ONLY_ON CHAR(31) CHARACTER SET UNICODE_FSS)
RETURNS (
CALL_STACK_TABLE CHAR(31) CHARACTER SET UNICODE_FSS,
MON$CALL_ID INTEGER,
MON$STATEMENT_ID INTEGER,
MON$CALLER_ID INTEGER,
MON$OBJECT_NAME CHAR(31) CHARACTER SET UNICODE_FSS,
MON$OBJECT_TYPE SMALLINT,
MON$TIMESTAMP TIMESTAMP,
MON$SOURCE_LINE INTEGER,
MON$SOURCE_COLUMN INTEGER,
MON$STAT_ID INTEGER)
AS
begin
-- Empty Cloned CALL_STACK Tables
DELETE FROM CALL_STACK_H;
DELETE FROM CALL_STACK_R;
--
if ((:TODO_INSERT_ONLY_ON IS NULL) OR (TODO_INSERT_ONLY_ON='TAB_H')) then
begin
INSERT INTO TAB_H(TMS) VALUES(CURRENT_TIMESTAMP);
end
if ((:TODO_INSERT_ONLY_ON IS NULL) OR (TODO_INSERT_ONLY_ON='TAB_R')) then
begin
INSERT INTO TAB_R(TMS) VALUES(CURRENT_TIMESTAMP);
end
-- Show Cloned CALL_STACK Records
FOR
SELECT 'CALL_STACK_H', MON$CALL_ID, MON$STATEMENT_ID, MON$CALLER_ID, MON$OBJECT_NAME, MON$OBJECT_TYPE, MON$TIMESTAMP, MON$SOURCE_LINE, MON$SOURCE_COLUMN, MON$STAT_ID FROM CALL_STACK_H
UNION
SELECT 'CALL_STACK_R', MON$CALL_ID, MON$STATEMENT_ID, MON$CALLER_ID, MON$OBJECT_NAME, MON$OBJECT_TYPE, MON$TIMESTAMP, MON$SOURCE_LINE, MON$SOURCE_COLUMN, MON$STAT_ID FROM CALL_STACK_R
INTO :CALL_STACK_TABLE, :MON$CALL_ID, :MON$STATEMENT_ID, :MON$CALLER_ID, :MON$OBJECT_NAME, :MON$OBJECT_TYPE, :MON$TIMESTAMP, :MON$SOURCE_LINE, :MON$SOURCE_COLUMN, :MON$STAT_ID
DO
suspend;
end
^

SET TERM ; ^

COMMENT ON PARAMETER TEST_BUG.TODO_INSERT_ONLY_ON IS
'IF NULL => INSERT INTO TAB_H AND TAB_R TABLES';

/* Privileges of users */
GRANT ALL ON CALL_STACK_H TO PUBLIC;
GRANT ALL ON CALL_STACK_R TO PUBLIC;

/* Privileges of procedures */
GRANT SELECT, DELETE ON CALL_STACK_H TO PROCEDURE SHOW_BUG;
GRANT SELECT, DELETE ON CALL_STACK_R TO PROCEDURE SHOW_BUG;
GRANT INSERT ON TAB_H TO PROCEDURE SHOW_BUG;
GRANT INSERT ON TAB_R TO PROCEDURE SHOW_BUG;

-------------

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 26, 2009

Commented by: @dyemanov

Riccardo, as it has been explained above, this is not a bug but the expected behavior.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 26, 2009

Commented by: Riccardo Fiorenza (ricfio)

Hm... ok... sorry, but for my scenario I believe that it's a real bug (or a real problem).

Have you tested my reported steps writed to repeat the bug ?

Can you explain me why it's not a bug ?

It's better that I will open a new report bug for my specific issue.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 26, 2009

Commented by: @dyemanov

Yes, I've tested your example and it works exactly the way I expect it to. Please read my other comments in this ticket for the explanation.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 26, 2009

Commented by: Riccardo Fiorenza (ricfio)

Ok, thanks... I understand the problem.

I will try to request this feature to allow re-generation of monitoring snapshot during the same transaction life.

P.S.
I need to know at runtime (inside a my trigger) if a specified query is the "low-level item" (level 0) of the call stack.
There is a different strategy to make this, without MON$CALL_STACK ?

Thank for all.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 29, 2011

Commented by: @dyemanov

Finally, I can confirm that sometimes the call stack could be empty if selected from inside a procedure or trigger, even if it was the first access to the MON$ tables in this transaction. So the issue actually exists, except the per transaction persistence specifics commented above.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 29, 2011

Modified by: @dyemanov

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 14, 2012

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 14, 2012

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1.5 [ 10420 ]

Fix Version: 2.5.2 [ 10450 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 23, 2013

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2016

Modified by: @pavel-zotov

QA Status: No test

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 3, 2018

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: No test => Deferred

Test Details: Sent letter to dimitr, 03.03.18 13:04; waiting for reply.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 3, 2018

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Deferred => Cannot be tested

Test Details: Sent letter to dimitr, 03.03.18 13:04; waiting for reply.

=>

Abandon from implementing test after discuss with dimitr, see letter 03-mar-18 15:11.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment