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

Several firebird servers crash during a daily maintenance / set statistics index [CORE6433] #6670

Closed
firebird-issue-importer opened this issue Oct 30, 2020 · 15 comments

Comments

@firebird-issue-importer

Submitted by: Alexander Muylaert (alexander_gonline.be)

Hi

I'm still investigating, but we're having some issues with our daily cleanup procedure since 3.0.7.

This method causes the server to crash completely.

SET TERM ^ ;

CREATE OR ALTER procedure SYS_RESET_INDEX_STATS (
IN_RELATION_NAME type of column RDB$INDICES.RDB$RELATION_NAME = null)
as
declare variable STMT varchar(1024);
declare variable IDX varchar(100);
begin
begin
for select 'set statistics index "' || trim(trailing from I.RDB$INDEX_NAME) || '"',
trim(trailing from I.RDB$INDEX_NAME)
from RDB$INDICES I
where I.RDB$INDEX_NAME not containing 'rdb$' and
(I.RDB$RELATION_NAME = coalesce(:IN_RELATION_NAME, I.RDB$RELATION_NAME))
into STMT,
IDX
do
begin
begin
execute statement STMT
with autonomous transaction
as user 'SYSDBA' password 'masterkey';
end
when any do
begin
DLLLOG('Set statistics "' || IDX || '" failed SQLSTATE=' || sqlstate);
end
end
end
end^

SET TERM ; ^

FAST-SERVER Fri Oct 30 09:05:42 2020
Access violation.
The code attempted to access a virtual
address without privilege to do so.
This exception will cause the Firebird server
to terminate abnormally.

Client file logs

09:05:42.915 5 ****************************** exception ******************************
09:05:42.915 5 CancelEvents
09:05:42.915 5 FbException
09:05:42.915 5 Unable to complete network request to host "
No message for error code 335544721 found.
09:05:42.915 5 IOException
09:05:42.915 5 Kan geen gegevens schrijven naar de transportverbinding: De externe host heeft een verbinding verbroken.
09:05:42.915 5 SocketException
09:05:42.915 5 De externe host heeft een verbinding verbroken
09:05:42.915 5 ErrorCode = 10054 SocketErrorCode = ConnectionReset NativeErrorCode = 10054
09:05:42.915 5 bij FirebirdSql.Data.FirebirdClient.FbRemoteEvent.CancelEvents()

Commits: c1179d7 2e092bf

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2020

Modified by: Alexander Muylaert (alexander_gonline.be)

summary: Several firebird servers crash during a backup / restore cycle or "CancelEvents" => Several firebird servers crash during a daily maintenance / set statistics index

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2020

Commented by: @AlexPeshkoff

Can you add DDL statement(s) for function DLLLOG()?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2020

Commented by: @hvlad

Crash dump (with full memory) could help to investigate issue.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2020

Commented by: Alexander Muylaert (alexander_gonline.be)

Hi Vladd

DLLLog is a UDF in delphi that actually writes a line of text to a logfile.
I'll work on the crash.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2020

Commented by: Alexander Muylaert (alexander_gonline.be)

FYI Removing the dlllog from the code still causes the server to crash.
I always have it on the same "index", a primary key. BUT if I update that index alone, I don't have the problem.

If you have a discrete upload, I can send you and fbk that fails?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2020

Commented by: @hvlad

Alexander,

could you upload it somewhere and send link to me privately ?
If you concerned about security, you may protect archive (7z or RAR, please) with password and send it to me in the same message.

Thanks.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2020

Commented by: Alexander Muylaert (alexander_gonline.be)

Hi Vlad, Yes I will send you a wetransfer link.

I will upload our database and win64 database version with c#⁠ UDR and delphi UDL included.

Jiri Cincura will help me on creating the crashdump as well.

kind regards and many thanks for your swift response.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2020

Commented by: @hvlad

Alexander,

wetransfer is OK.
As for the crash dump this link could help:

https://docs.microsoft.com/windows/win32/wer/collecting-user-mode-dumps

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 30, 2020

Commented by: @hvlad

I reproduced the crash, thanks.
Fix will follow soon.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 2, 2020

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 3, 2020

Commented by: @hvlad

Patch is committed, you may try next snapshot build to verify it.

https://firebirdsql.org/en/snapshot-builds/

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 3, 2020

Commented by: Alexander Muylaert (alexander_gonline.be)

Dear Vlad

I can confirm the issue is resolved.

But that brings me to another question, is this kind of firebird 1.5 hack still relevant?

kind regards

Alexander

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 3, 2020

Commented by: @hvlad

> But that brings me to another question, is this kind of firebird 1.5 hack still relevant?

Could you explain - what do you mean ?
If it is about usage of EXECUTE STATEMENT with SET STATISTICS - it is fully legal and not a hack, especially if combined with autonomous transaction as in your code.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 3, 2020

Commented by: Alexander Muylaert (alexander_gonline.be)

Hi

to not go off topic too much: my apologies, I indeed mean the set statistics.
Since that code was there (fb2.1) so many things are changed that I was wondering if it still adds something.

If http://www.firebirdfaq.org/faq167/ is still valid, please just close ticket and thank you very much.

"Index selectivity should be recalculated periodically because inserting, updating and deleting records changes it. Firebird calculates it only when index is created and when database is restored from backup."

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 13, 2020

Modified by: @AlexPeshkoff

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

Fix Version: 3.0.8 [ 10960 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants