[firebird-support] Database as own security database

[p62tpaxc3tdvxtc4d5si2g2...@yahoo.com [firebird-support]]

I am upgrading from 2.1 to 3.02 and I want a database to be its own security 
database. If I register the database as:

ABZ = {path to database file}
{
 SecurityDatabase = ABZ
}

then I cannot restore from the backup because there is no security context. If 
I restore as a normal database I cannot connect as there is no login.
 

 How do I go about this?
 

 Dave

Re: [firebird-support] Using Firebird v3.0.2 fbclient.dll to access previous versions, specifically 1,56

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 2017-12-06 13:14, OB1 oldbasf...@googlemail.com [firebird-support] 
wrote:
> We're making the jump from v1.56 to v3.0.2 (gulp!) and to make the
> transition easier for our clients we wondered if v3 client libs
> (fbclient.dll) can access 1.56 servers.  We've tested this and it
> appears to work, but wanted to confirm if it was officially supported.

Technically yes, the wire protocol is versioned and fbclient.dll of 
Firebird 3 supports the older versions of the protocol (if it didn't you 
wouldn't even be able to connect with older versions of the protocol). 
However given that Firebird 2.1 and older are no longer supported, I'd 
say that 'officially' it isn't ;).

Mark

RE: [firebird-support] Re: Slow execution of Stored Procedure after 20 min. inactivity

[hv...@users.sourceforge.net [firebird-support]]

> ---In firebird-support@yahoogroups.com,  wrote :
 > > With “idle” I mean no connections at all. 
> I already found out the problem is related to a custom UDF dll, but it’s 
> unclear to me why this only happens after 20 minutes. 
> I added some logging to the UDF so I could see when the UDF was loaded and 
> unloaded and the memory usage at those moments. 
> The UDF is properly loaded and unloaded without any difference in memory 
> usage after the UDF has been used. 
 

   I.e. sometimes load of UDF took 18 sec ? Really ? Wow ;)

 

 Regards,
 Vlad

  

 
  
  

Re: [firebird-support] Re: lock denied...

[ehmmm.fireb...@seznam.cz [firebird-support]]

Hi,
here is the link:
https://ulozto.net/!wM4fFP0Kpnuq/fb-lock-table-dump
There are options "Fast download" and "Slow download".
If you don't want to register then you must use "Slow download" and type 
captcha code.

Jakub


-- Původní e-mail --
Od: peshk...@mail.ru [firebird-support] 
Komu: firebird-support@yahoogroups.com
Datum: 6. 12. 2017 10:48:27
Předmět: Re: [firebird-support] Re: lock denied... 
" 
  



First of all regarding yesterday file. Unfortunately "Non-text portions of 
thAT message have been removed". So please upload that file to any public 
file-sharing you better like (except yandex & mail.ru). Sorry for 
inconveniences - that's not me who invited yahoo interface...

> But you should write me what and where must be configured to obtain some 
error dumps when it falls down.

Install appropriate debug info (in our builds it's always placed as a pair 
for binary package). After it adding BugcheckAbort=1 to firebird.conf should
be enough. And get ready that this may be a long process - may be I will 
have to send you some special builds with additional debugging.

> Because firebird didn't restarted itself since yesterday afternoon then I'
ll have to restart it manually to enable legacy authentication.

That's bad solution but that's your solution. That backward compatibility 
feature was kept in firebird for such cases.

>Together with this change I'm thinking about moving firebird temp dir to 
tmpfs because I've read on some IBSurgeon pages that it might improve 
perfomance.
>(In my Debian Stretch directory /run seems suitable for this.)
>Do you think it's really worth it?

That highly depends upon RAM usage on your box. If you have enough RAM - 
that's useful, if not - may cause performance degradation.

Also please provide info about your kernel, libc and libstdc++ versions.




"

[firebird-support] Using Firebird v3.0.2 fbclient.dll to access previous versions, specifically 1,56

[OB1 oldbasf...@googlemail.com [firebird-support]]

We're making the jump from v1.56 to v3.0.2 (gulp!) and to make the
transition easier for our clients we wondered if v3 client libs
(fbclient.dll) can access 1.56 servers.  We've tested this and it appears
to work, but wanted to confirm if it was officially supported.

Many thanks.

RE: [firebird-support] Re: Slow execution of Stored Procedure after 20 min. inactivity

[Marcel Brink mbr...@xploration.onmicrosoft.com [firebird-support]]

Hello,

With “idle” I mean no connections at all.
I already found out the problem is related to a custom UDF dll, but it’s 
unclear to me why this only happens after 20 minutes.
I added some logging to the UDF so I could see when the UDF was loaded and 
unloaded and the memory usage at those moments.
The UDF is properly loaded and unloaded without any difference in memory usage 
after the UDF has been used.

Marcel Brink

[cid:image001.png@01D1D8FF.6BD77F00]



Van: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Verzonden: woensdag 6 december 2017 10:50
Aan: firebird-support@yahoogroups.com
Onderwerp: [firebird-support] Re: Slow execution of Stored Procedure after 20 
min. inactivity



> ---In 
> firebird-support@yahoogroups.com, 
> > wrote :
>
> Hello,
>
> After spending more than a week trying to resolve the following issue we hope 
> someone has an answer on this.
>
> We have a database with about 230 tables and 200 stored procedures which is 
> being used by our customers.
> Each customer have a dedicated installation with it's own database varying in 
> size from 50MB up to 2GB.
> At the moment they use Firebird 2.5.1.26351 Super-Classic Win32 on different 
> platforms (Win 7 Pro, Win 10 Pro, Windows server 2012 R2).
>
> The issue occurs when we execute a complex (recursive) stored procedure after 
> the database engine has been idle for about 20 minutes.

  Define "idle": no active connections ? no connections at all ? What about 
other activities on this host ?

> Executing the stored procedure after those 20 minutes can take around 18-20 
> seconds to complete, while it normally takes around 0,1 seconds.
> Even after restarting the Firebird service executing the stored procedure 
> immediately never takes longer then 3-4 seconds (unless the 20 minutes are 
> passed).

  Looks like database file is removed from system cache after 20 min, probably 
due to load by another
processes. You may use Sysinternals RamMap utility to check this guess.

> Things we tried:
>
> * Restarting the Firebird service.
> Even if we restart the Firebird service between those 20 minutes, once the 20 
> minute mark is reached it simply takes around 20 seconds again.
> * Installed firebird and the database on an SSD (Samsung Evo 500GB) under 
> Windows 10 Pro (Fall Creators update) with default configuration.
> - No difference.
> * Turned off inactivity settings in Windows for the HDD, so Windows doesn't 
> turn off the HDD.
> - No difference.
> * Upgraded Firebird to version 2.5.7.27050.
> - No difference.
> * Upgraded Firebird to version 3.0.2 using the migration guide 
> (backup/restore, etc.).
> - No difference.
>
> When we insert a record into a random table within the same connection and 
> transaction before executing the stored procedure,
> after those 20 minutes the insert is fast and executing the stored procedure 
> takes around 20 seconds again.
>
>
>This is the trace of what happens when we execute the stored procedure after 
>20 minutes (watch the prepare of statement 70).
...

  Trace show that prepare call takes 18 sec, execute is fast. Prepare could be 
slow if
- there is one or more huge tables affected by the query - optimizer should 
estimate cardinality of tables
  and it should read all Pointer Pages of the given table(s)
- there is a lot of different privieges granted to the objects affected by the 
query (stored procedure itself,
  all tables\views\SP's it uses and all objects of whole call tree)
- even if there is not much provileges but execution tree is big - engine 
should load all corresponding metadata
  objects and it could take time

Regards,
Vlad



[Non-text portions of this message have been removed]

[firebird-support] info

[Рустам Муса-Ахунов rusta...@ukr.net [firebird-support]]

select
  gen_id(GEN_XXX, 1)
from RDB$DATABASE


Where GEN_XXX - generator for your A.ID column

[firebird-support] Re: Slow execution of Stored Procedure after 20 min. inactivity

[hv...@users.sourceforge.net [firebird-support]]

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Hello,
 > 
> After spending more than a week trying to resolve the following issue we hope 
> someone has an answer on this.
 > 
> We have a database with about 230 tables and 200 stored procedures which is 
> being used by our customers.
> Each customer have a dedicated installation with it's own database varying in 
> size from 50MB up to 2GB.
> At the moment they use Firebird 2.5.1.26351 Super-Classic Win32 on different 
> platforms (Win 7 Pro, Win 10 Pro, Windows server 2012 R2).
 > 
> The issue occurs when we execute a complex (recursive) stored procedure after 
> the database engine has been idle for about 20 minutes.
 

   Define "idle": no active connections ? no connections at all ? What about 
other activities on this host ?

 

 > Executing the stored procedure after those 20 minutes can take around 18-20 
 > seconds to complete, while it normally takes around 0,1 seconds.
> Even after restarting the Firebird service executing the stored procedure 
> immediately never takes longer then 3-4 seconds (unless the 20 minutes are 
> passed).
 
 

   Looks like database file is removed from system cache after 20 min, probably 
due to load by another
 processes. You may use Sysinternals RamMap utility to check this guess.

 
 > Things we tried:
 > 
 > * Restarting the Firebird service.
 > Even if we restart the Firebird service between those 20 minutes, once the 
 > 20 minute mark is reached it simply takes around 20 seconds again.
 > * Installed firebird and the database on an SSD (Samsung Evo 500GB) under 
 > Windows 10 Pro (Fall Creators update) with default configuration.
 > - No difference.
 > * Turned off inactivity settings in Windows for the HDD, so Windows doesn't 
 > turn off the HDD.
 > - No difference.
 > * Upgraded Firebird to version 2.5.7.27050.
 > - No difference.
 > * Upgraded Firebird to version 3.0.2 using the migration guide 
 > (backup/restore, etc.).
 > - No difference.
 > 
 > When we insert a record into a random table within the same connection and 
 > transaction before executing the stored procedure,
 
 > after those 20 minutes the insert is fast and executing the stored procedure 
 > takes around 20 seconds again.
 
 >

 >
>This is the trace of what happens when we execute the stored procedure after 
>20 minutes (watch the prepare of statement 70).

 ...

   

   Trace show that prepare call takes 18 sec, execute is fast. Prepare could be 
slow if
 - there is one or more huge tables affected by the query - optimizer should 
estimate cardinality of tables
   and it should read all Pointer Pages of the given table(s)
 - there is a lot of different privieges granted to the objects affected by the 
query (stored procedure itself,
   all tables\views\SP's it uses and all objects of whole call tree)

 - even if there is not much provileges but execution tree is big - engine 
should load all corresponding metadata
   objects and it could take time


 

 Regards,
 Vlad

Re: [firebird-support] Re: lock denied...

[peshk...@mail.ru [firebird-support]]

First of all regarding yesterday file. Unfortunately "Non-text portions of thAT 
message have been removed". So please upload that file to any public 
file-sharing you better like (except yandex & mail.ru). Sorry for 
inconveniences - that's not me who invited yahoo interface...

> But you should write me what and where must be configured to obtain some 
> error dumps when it falls down.

Install appropriate debug info (in our builds it's always placed as a pair for 
binary package). After it adding BugcheckAbort=1 to firebird.conf should be 
enough. And get ready that this may be a long process - may be I will have to 
send you some special builds with additional debugging.

> Because firebird didn't restarted itself since yesterday afternoon then I'll 
> have to restart it manually to enable legacy authentication.

That's bad solution but that's your solution. That backward compatibility 
feature was kept in firebird for such cases.

 >Together with this change I'm thinking about moving firebird temp dir to 
 >tmpfs because I've read on some IBSurgeon pages that it might improve 
 >perfomance.
>(In my Debian Stretch directory /run seems suitable for this.)
 >Do you think it's really worth it?

That highly depends upon RAM usage on your box. If you have enough RAM - that's 
useful, if not - may cause performance degradation.

Also please provide info about your kernel, libc and libstdc++ versions.

Re: [firebird-support] windows server 2016 as server for firebird 2.5.7

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hello Nils,

No other issues.

Regards,
Alexey Kovyazin
IBSurgeon


On 05.12.2017 14:52, Nils Boedeker nilsboede...@t-online.de 
[firebird-support] wrote:


Hi

I want to use windows server 2016 as server for

firebird 2.5.7 (Classic Server)

are there any known issus with using firebird with this system?

with best regareds

Nils

PS: The power plan ist on "High Performance"

[firebird-support] info

[Nico Speleers nico.spele...@carfac.com [firebird-support]]

Hello,

Ex.
Table A
Id (AutoInc)
Description

Id = 1 / Description = Test
Id = 2 / Description = Test2

I want to know the next Id number in my table without an insert. In my example 
it will be 3. I want to reserve (without an insert) Id number 3 because someone 
else can also ask the next Id number. In that case it must be id  number = 4, 
because you've got Number 1 and 2 in the table and number 3 is reserved.

How can I do that ?

Thanks.

Nico Speleers
Analyst





Carfac bvba

[Beschrijving: Beschrijving: Beschrijving: CarfacAS 
(klein)]Driving on experience


Ambachtstraat 8 | 9700 Oudenaarde
Tel. +32 55 23 00 00 | Fax +32 55 31 00 95
nico.spele...@carfac.com | 
www.carfac.be

[Facebook][LinkedIn][Twitter]






[Non-text portions of this message have been removed]

Re: [firebird-support] Re: lock denied...

[ehmmm.fireb...@seznam.cz [firebird-support]]

It's me again.
FB3 in SuperServer mode seems to run without any trouble for almost the 
whole day.

Anyway, if you are interested I offer you that I might prepare second test 
server with almost the same SW and very similar load and FB in SuperClient 
mode.
But you should write me what and where must be configured to obtain some 
error dumps when it falls down.
I would like to give you enough information to fix the "lock denied" 
problems.

And another question:
Because firebird didn't restarted itself since yesterday afternoon then I'll
have to restart it manually to enable legacy authentication.
(I have to enable this due to some old clients which I'm not going to 
upgrade.)
Together with this change I'm thinking about moving firebird temp dir to 
tmpfs because I've read on some IBSurgeon pages that it might improve 
perfomance.
(In my Debian Stretch directory /run seems suitable for this.)
Do you think it's really worth it?

Best regards,
Jakub



-- Původní e-mail --
Od: ehmmm.fireb...@seznam.cz [firebird-support] 
Komu: firebird-support@yahoogroups.com
Datum: 5. 12. 2017 13:50:30
Předmět: Re: [firebird-support] Re: lock denied... 
" 
  



Well, I'll try to answer myself about the C and SuperServer.
It seems that the only problem is in:
EXEC SQL SET DATABASE DB = '/var/lib/firebird/2.5/data/xxx.fdb';
When add ip adress and I change it to:
EXEC SQL SET DATABASE DB = '127.0.0.1:/var/lib/firebird/2.5/data/xxx.fdb';
then it seems to work even in SuperServer (if Firebird really runs in 
SuperServer after changing /opt/firebird/firebird.conf)

This brings another question - how can I recognize in which mode is Firebird
running?

Best regards,
Jakub



-- Původní e-mail --
Od: ehmmm.fireb...@seznam.cz [firebird-support] 
Komu: firebird-support@yahoogroups.com
Datum: 5. 12. 2017 10:44:48
Předmět: Re: [firebird-support] Re: lock denied... 
" 
  



Thank you for your response.
Compared to you I'm just a beginner so I have other questions.

> i see no relation between such services and requirement to use 
SuperClassic

I din't investigate it much but I think it is becuase the C programs it is 
historically written something like this:

int dbConnect()
{
    EXEC SQL
    SET DATABASE DB = '/var/lib/firebird/2.5/data/xxx.fdb';
    EXEC SQL
    CONNECT DB USER 'xxx' PASSWORD 'xxx';
    ...

(Don't get confused by the "2.5" subdirectory, I did "gbak -r" to 3.0 
version but I put it in the old dir.)
I think that in this case the C programs access somehow more directly and 
they can't cooperate with SuperServer.
But I promise I'll lokk at it.

> Try to lower load on lock manager.
Please how? Make less connections?

> Switch to Super
Discussed above.

> or try to increase linger on security3.fdb(it is 60 sec by default).
Please how?
I've found some: "ALTER DATABASE SET LINGER TO"
I guess I'll have to do it via isql and not by Flamerobin. (?)
How can I check how much is it right now?
(I've found some tip "gstat -h" which I use for sweeping monitor. But I can'
t see there any "linger".)
I've found some presentation that this "linger functionality" has effect 
whene there are no connections.
But I think that I have there new attachments almost every second. (?)

> If there was coredump produced by crash, you may provide us with 
backtraces of all stacks. 
How can I get this dump?
I've found some document from 2008 mentioning BugcheckAbort=1 in the 
firebird.conf.
That's it?





Jakub


-- Původní e-mail --
Od: hv...@users.sourceforge.net [firebird-support] 
Komu: firebird-support@yahoogroups.com
Datum: 5. 12. 2017 10:15:00
Předmět: [firebird-support] Re: lock denied... 
" 
  



---In firebird-support@yahoogroups.com,  wrote :




> last weekend we switched from FB 2.5.x to  FB V3.0.3.32796
> 
> We have couple databases, the biggest has around 12 GB.
> About 100-200 clients are connecting to this DB via PHP approximately 
twice per minute to see some data.
> Some services on server are writing data into this DB.


> We are using FB in SuperClassic mode, because some services are written in
C and compiled via gpre/gcc.





  Hmm... i see no relation between such services and requirement to use 
SuperClassic.

Сould you try Super mode ? SuperClassic in FB3 is legacy and not recommended
mode.





> In firebird log I can see messages like this:
> 
> z3  Sun Dec  3 01:43:15 2017
>     Database: /opt/firebird/security3.fdb
>     page 0, page type 1 lock denied (216)


...
> Could you please explain me, what do this messages mean?



  It looks like bug due to race condition when lock manager initialized 
shared memory while another 


instance (attachment) is deinitialized it (it is simplified and far from 
exact description). It could be 


something else - i need to reproduce the issue to make correct conclusion.



> How can I avoid