[firebird-support] Re: AV in fbembed.dll_unloaded

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

You must call fb_shutdown before unload fbembed.dll

Regards,
Vlad

[firebird-support] Re: nbackup database made database inaccessible

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

Hugo,

  Switch -U[SER] set user name for autentification, i'm doubt it could address 
the problem.
If .delta file was really lost, nbackup can't help. Therefore i don't think it 
was lost. 

Regards,
Vlad

PS Sometimes it is risky even to cross the road... especially if you know no 
rules and see 
no details around you...

[firebird-support] Re: lock conversion denied/lock denied bug still exists in FB 2.5.3

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

Ideally, i would like to have reproducible test at my hands to analyze it.
It is possible for you to provide me with it ?

As for debug dumps you have - they can't help as produced *after* deadlock 
condition was found and cleaned by Lock Manager.

Thanks,
Vlad

[firebird-support] Re: lock conversion denied/lock denied bug still exists in FB 2.5.3

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

  No ths is not related to subject, this is another issue.
I have few such reports from customers but without test case to reproduce it.
I suspect this is related to garbage collection of a very long versions chain 
(ten's backversions for the same record).
gstat -r could confirm it.

Regards,
Vlad
 

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

 Also, possibly related to this (appears on same production servers) - 
sometimes all fb_inet_server.exe processes stop any disk i/o activity and wait 
for one process, which eats 100% of one CPU core. 

 Stack trace for hung thread look like this:
 fb_inet_server!down_grade+0x145
 fb_inet_server!down_grade+0x156

 

 tens of recursive down_grade calls
 

 fb_inet_server!down_grade+0x156

 fb_inet_server!blocking_ast_bdb+0x69
 fb_inet_server!Jrd::LockManager::blocking_action+0x170
 fb_inet_server!Jrd::LockManager::blocking_action_thread+0x11f
 fb_inet_server!Jrd::LockManager::blocking_action_thread+0x9
 fb_inet_server!`anonymous namespace'::threadStart+0x55
 msvcr80!_endthreadex+0x3b
 msvcr80!_endthreadex+0xc7
 kernel32!BaseThreadInitThunk+0xe
 ntdll_!__RtlUserThreadStart+0x23
 ntdll_!_RtlUserThreadStart+0x1b
 

 


 I has debug dump and lock print for hung process.
 
 

[firebird-support] Re: INET / inet_error: fork / CreateProcess errno = 193

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

  Win32 error 193 is:
 
 ERROR_BAD_EXE_FORMAT 193 (0xC1) %1 is not a valid Win32 application.
 
Very strange... probably issues with antivirus\firewall ?

Regards,
Vlad

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

 
 Hi,
 

 From a certain moment the message 'Connection rejected by remote interface' 
was given at one of our customers. In
firbird.log are many reports 'INET / inet_error: fork / CreateProcess errno = 
193'. Prior to this time one could work
 without problems, but suddenly, it was no more. No apparent cause
 

 Firebird 2.5.3 Classic server 64-bit is installed on Windows Small Business 
Server 2011 Standard SP 1 64-bit. Use is made of gds32.dll, there is
 verified that this is the correct version. Firewall is checked and the virus 
scanner off. That did not solve the
 problem. The message is also given on the server itself.
 

 The problem is solved by Firebird Super Server install.
 Cause was therefore that no child process fb_inet_server.exe could be created, 
such as notified.
 Does anyone have an idea what the cause of this problem could be? It is a 
Windows thing I assume.
 

 Regards,
Henk Cats

[firebird-support] Re: FB 2.5 and lock

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

John,

Upgrade to 2.5.3, and read Release Notes carefully

 
Regards,
Vlad

PS there is no such version as 2.5, there are five releases in 2.5.x series


  

[firebird-support] Re: FB 2.5.3 32bit and Db size limits test

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

  Alex,

what is  limit of 2.083.888 kb ? 
Is it size of database file on disk ? 
Is it memory usage by Firebird process (what exact memory counter) ?
Something else ?
How do you insert blobs into database ?

Regards,
Vlad

---In firebird-support@yahoogroups.com, af_123xy@... wrote :
 
 Hi all 
 i am new user of FB and i am making a  tests to check  a one db size limits to 
see if i can choose FB to support a new application.
 

 i will need to acquire in the DB only blob types to save images, jpg, pdf, 
tiff , etc of different dimension
 

 so i made a small application test on a W7/64 bit and FB 32bit  and after 
reaching almost 13.000 images and reached a limit of 2.083.888 kb i've go an 
out of memory message inserting one more Blob record
 

 i were reading on the official website that on some Platform the size limit 
is some terabytes a part some limit imposed by filesystem
 do i should use FB 64 bit version for handle it or i am doing something wrong 
in the configuration?
 

 thank you for any suggestions
 Alex
 

[firebird-support] Re: FB 2.5.3 32bit and Db size limits test

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

No

Regards,
Vlad
 

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

 out of memory inserting  the Blob
  
 is there any difference betwern 32 and 64 FB engine about db size limit 
(supposing that i am on a NTFs (5) filesystem) ?
 

Re: [firebird-support] Deadlock exception occurs but it shouldn't?

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

  Update, internally, consists of following steps (simplified) :

1. read record
2. evaluate new record
3. write new record into data page

really, it is a more complex, but for now we interesting only in steps above.

At step 1, read-committed (RC) transaction waits for commit or rollback of 
concurrent active transaction and thus reads only comitted record version.

At step 3, transaction re-reads original record version and must ensure that it 
is still the same record version as it was at step 1 (else we will silently 
replace other's work). If current record version was changed since step 1, 
update_conflict error will be reported - exactly as you see. 
Again, it is simplified description.

  Note, time slice between steps 3 and 1 usually very small. When you test 
concurrent updates in isql, you can't reproduce such scenario. But in real 
life, when you have a lot of concurrent updates by applications it becomes much 
more probably and really happens sometimes.

Hope it helps,
Vlad

[firebird-support] Re: Deadlock exception occurs but it shouldn't?

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

Walter,

there are few reasons:

a) MVCC engine never block records - it just can't :)

b) the only way to disable concurrent updates of the same record is... update 
:) 
  More exactly: engine must mark primary record version by current transaction 
and
it will make concurrent writes wait or fail.

c) therefore, i can't imagine how it is ever possible to block record *before* 
reading it

d) Firebird allows developer to avoid such problem (see below) but doesn't 
force developer to do it.

  To avoid exactly this kind of issues Firebird have ablity to lock record 
while reading (i enclosed 
lock into brackets because there is no true record locks in Firebird, see (a) 
above).

  So, to lock record engine must update it and there is special statement to 
read-and-update:
SELECT ... WITH LOCK

  So, if you really don't want to handle update conflicts and decided to wait 
for concurrent updates,
you should issue SELECT ... WITH LOCK before UPDATE (and, of course, use 
read-commited no
record version transaction isolation mode with non-zero wait timeout).
 
Regards,
Vlad

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

 Vlad, your explanation is very good, but I still don't understand something: 
Why before step 1 the engine does not blocks the record avoiding such problem? 

 Greetings.
 

 Walter.
 


 
 On Sat, Dec 27, 2014 at 5:19 AM, hvlad@... mailto:hvlad@... [firebird-support] 
firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com 
wrote:
 Update, internally, consists of following steps (simplified) :

1. read record
2. evaluate new record
3. write new record into data page

really, it is a more complex, but for now we interesting only in steps above.

At step 1, read-committed (RC) transaction waits for commit or rollback of 
concurrent active transaction and thus reads only comitted record version.

At step 3, transaction re-reads original record version and must ensure that it 
is still the same record version as it was at step 1 (else we will silently 
replace other's work). If current record version was changed since step 1, 
update_conflict error will be reported - exactly as you see. 
Again, it is simplified description.

  Note, time slice between steps 3 and 1 usually very small. When you test 
concurrent updates in isql, you can't reproduce such scenario. But in real 
life, when you have a lot of concurrent updates by applications it becomes much 
more probably and really happens sometimes.

Hope it helps,
Vlad

 

[firebird-support] Re: Bad surprise on performance

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

Hi, André !
 
 [Old]
  Server 2003 x86 no service packs
 Xeon with 4 GB RAM
 Classic 1.5.4 (x86, of course)
 Raid 0 on 2 * 500 GB SAS (though this is from memory, I should look it up...)
  
 [new]
 Server 2008 R2 x64 SP1
 Xeon with 8 GB RAM (I will shortly add +8)
 Classic 2.5.3 x64
 Raid 0 on 3 * 600 GB SAS
 
  Raid 0 ? Are you sure ? 

  In comparison to what I read from others, my databases are small.
  Biggest database is some 1 GB only.

  Agree, it is small database.

  Bad performance on updates and inserts and extremely bad performance
  on committing a big number of record changes (~ 150.000 updates).
 
  It points us to the issues with writes or with random writes. It could be 
interesting
to see results of disk IO benchmark (any you like which able to test random 
writes).
Also it is good to know some raid settings such as stripe\block size, write 
cache mode, 
presence of BBU (battery for internal cache).


Regards,
Vlad

Re: [firebird-support] Re: ON EXTERNAL DATA SOURCE

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

  Tim,

your question:

 (iii) I can't find any documentation of when the connection on database 
B get closed, and when and under what circumstances the transaction on 
database B gets committed and when and under what circumstances it gets 
rolled back


documentation 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-with-trans
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-with-trans
 
answer:
 WITH {AUTONOMOUS|COMMON} TRANSACTION ...
 Any new transactions started under the “COMMON” regime are committed or rolled 
back with the current transaction. 
 

 
Then you ask again:
 (ii) Each time the statement run in (b) completes the transaction in 
 database B will be committed, unless there was an error in which case it 
 will be rolled back
...
isn't it rather expensive to reopen the connection to database B every time?


Of course reopen connection is more expensive then reuse existing connection ;) 
But...

...read the docs
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-on-external
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-on-external
 

 Connection pooling: 
 
 External connections made by statements WITH COMMON TRANSACTION (the default) 
will remain open until the current transaction ends. They can be reused by 
subsequent calls to EXECUTE STATEMENT, but only if the connect string is 
exactly the same, including case. 
 External connections made by statements WITH AUTONOMOUS TRANSACTION are closed 
as soon as the statement has been executed. 
 Notice that statements WITH AUTONOMOUS TRANSACTION can and will reuse 
connections that were opened earlier by statements WITH COMMON TRANSACTION. If 
this happens, the reused connection will be left open after the statement has 
been executed. (It must be, because it has at least one uncommitted 
transaction!) 
 

So, please, read the documentation !

Regards,
Vlad


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

 Yes, I did, of course, you will see that not only do I quote the documentation 
but also that my questions are on points that the documentation doesn't answer, 
which is why I asked them here.
 
 On 27/02/2015 18:57, hvlad@... mailto:hvlad@... [firebird-support] wrote:

   Read the documentation first
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html
  
 Regards,
 Vlad
 
 
 -- 
Tim Ward

  

[firebird-support] Re: ON EXTERNAL DATA SOURCE

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

Read the documentation first
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html
 
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-psql-execstat.html
  
Regards,
Vlad

[firebird-support] Re: Incorrect behaviour of isc_start_multiple() ?

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

With CS, when you stop listener process all working processes are still works. 
Clients connections are not lost and alive.

Regards,
Vlad
 

[firebird-support] Re: Plan question, what is a stream and what isn't?

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

 ---In firebird-support@yahoogroups.com, tdw@... wrote :
   If SET STATISTICS is done on one connection, when does it take effect?
 
  As any DDL statement it actually runs at transaction commit.

  What about a transaction that was already running at the time someone 
 else did SET STATISTICS, does that use the new statistics for any new 
 statements it runs (where all the code running is in stored procedures)?
  
 What about a connection that was already in existence at the time 
 someone else did SET STATISTICS, does that use the new statistics for 
 any new transactions it runs (where all the code running is in stored 
 procedures)?

  Think in terms of statements, not transactions\connections. When statement
is prepared optimizer used most recent statistics to create execution plan. 
Note, stored procedures and triggers are prepared\optimized once when 
loaded into metadata cache.

Regards,
Vlad

Re: [firebird-support] Re: Plan question, what is a stream and what isn't?

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

---In firebird-support@yahoogroups.com, listas@... wrote :
 
 Re: [firebird-support] Re: Plan question, what is a stream and what isn't? 
 Note, stored procedures and triggers are prepared\optimized once when 
 loaded into metadata cache.
 
 Regards,
 Vlad 

 Just by curiosity, when are SPs/Triggers loaded into Metadata Cache? Right  
 before first execution?
 
  Exactly.

Regards,
Vlad
 http://www.FireBase.com.br

Re: [firebird-support] Re: Plan question, what is a stream and what isn't?

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

---In firebird-support@yahoogroups.com, tdw@... wrote :
 
  On 15/04/2015 18:50, hvlad@... mailto:hvlad@... [firebird-support] wrote:

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

 
 Re: [firebird-support] Re: Plan question, what is
a stream and what isn't? 
 Note, stored procedures and triggers are prepared\optimized once when 
 loaded into metadata cache.
 
 Regards,
 Vlad 
 
  Just by curiosity, when are SPs/Triggers loaded into Metadata Cache? Right  
  before first execution?
 
   Exactly.



 
 What does right before first execution mean? 

  Hmm... right before first execution mean *exactly* right before first 
execution ... what is confusing here ?

  Application prepare the statement which referenced some stored procedure. 
Engine must resolve all objects 
referenced by statement to create execution tree. Engine looks for object in 
metadata cache and, if not found,
reads system catalogue and construct necessary object. It involves creating 
execution tree for this object, of course. 
Then ready to use execution tree put into the metadata cache (with other 
necessary bits of information).

  So, about right before first execution - probably at first reference is 
more clear ?

 What is the lifecycle of metadata cache?
 
  Object (stored procedure\trigger) put into metadata cache at first reference. 
Object could be removed from metadata
cache when index or relation (table) is dropped. Object instance is marked as 
obsolete when user alter corresponding 
procedure\trigger. In this case new instance of object will be created and put 
into metadata cache. Old instance will 
not be used by newly prepared statements.

  In SS metadata cache is shared by all attachements. In CS\SC each attachment 
have its own private copy of 
metadata cache.

  It is not recommended (by docs, since IB times) to alter objects when there 
are other attachments present.

  SET STATISTICS doesn't invalidate any objects in metadata cache.

 So what's the answer to my question, re a process which keeps a connection 
 open permanently and repeatedly re-runs 
 the same procedures in different transactions? Does it need to drop and 
 recreate the connection in order to take account 
 of any SET STATISTICS done by other people, or not? Hope now you can 
 answer on questions above by yourself :)

Regards,
Vlad

[firebird-support] Re: Moving from FB 1.5x to 2.5x

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

---In firebird-support@yahoogroups.com, dressel@... wrote :
   We are considering moving from 1.5x to 2.5x. 95% of our user base is using 
   FB embedded (using Delphi and IBO) . We have a few thousand 
 installs on Windows and it could be quite painful if this doesn't go 
 smoothly--like sinking a boat in a storm.
 
 We have a few questions:
...
 3) Is there anything we should be aware of?

  Windows embedded before v2.5 was based on SS architecture. In v2.5 it is 
based on Classic architecture 
(SuperClassic to be precise). So, be ready - some default settings is changed. 
For example, page cache by 
default will be 75 pages, not 2048 as before. Also, your application will open 
database file in non-exclusive 
mode and another instance (or app) might attach at the same time.

Regards,
Vlad

  

Re: AW: AW: [firebird-support] Re: Memory usage excess / leak in FBServer 2.5.4

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

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

  Reproducible test case would be ideal
 
 Hello Vlad, hello Alexey, and other interested people!
  
 I have prepared a test case now showing the memory leak with a simple fresh 
 database and many many connect / select / disconnect sequences.
  
 Take the below script (also attached, but don’t know if group allows this), 
 place it in a file named connleak.cmd in a new empty directory and call it:
  
 1st: connleak init
 2nd: connleak run
  
 You can open the task manager and see fbserver.exe memory consumption growing.
  
 Hope this helps to investigate the problem.

  Thanks, it helps. I've reproduced and fixed it (not committed yet). Could you 
register it a tracker, please ?

Regards,
Vlad

Re: AW: AW: [firebird-support] Re: Memory usage excess / leak in FBServer 2.5.4

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

---In firebird-support@yahoogroups.com, hvlad@... wrote :
  Could you register it a tracker, please ?

  Done: http://tracker.firebirdsql.org/browse/CORE-4830 
http://tracker.firebirdsql.org/browse/CORE-4830  


Regards,
Vlad

  

Re: AW: [firebird-support] Re: Memory usage excess / leak in FBServer 2.5.4

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

 ---In firebird-support@yahoogroups.com, joja.lists@... wrote :
  
  Any hints how to investigate this problem?

  Reproducible test case would be ideal

Regards,
Vlad
 

Re: AW: AW: AW: [firebird-support] Re: Memory usage excess / leak in FBServer 2.5.4

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

---In firebird-support@yahoogroups.com, joja.lists@... wrote :
  
  Sorry, I didn’t check my mail till now. Thank you for creating the ticket. 
  And of course for fixing the bug.
 

  Please, verify it using recent snapshot build

Regards,
Vlad
 

Re: AW: AW: [firebird-support] Re: Memory usage excess / leak in FBServer 2.5.4

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

---In firebird-support@yahoogroups.com, ts@... wrote :
 
  Is this SuperServer only or also SuperClassic?
 
SuperServer only.

Regards,
Vlad

Re: [firebird-support] Re: Firebird 2.52 gbak fails to do a restore - error trigger (3)

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

---In firebird-support@yahoogroups.com, jackmason@... wrote :
  
  Thanks.  We do have both Interbase and Firebird running on our Win7 machine 
  because we have to access  both types of databases.  I will try your 
  suggestion.

  Any news on this issue ?

Regards,
Vlad

Re: [firebird-support] Re: Firebird 2.52 gbak fails to do a restore - error trigger (3)

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

---In firebird-support@yahoogroups.com, jackmason@... wrote :
  
  Is this what you needed?
  

  Hmm... i'd say it is impossible to obtain such results using FIrebird. See 
below
 
  C:\Program Files\Firebird\Firebird_2_5\binisql -user sysdba -pass masterkey
 Use CONNECT or CREATE DATABASE to specify a database
 SQL create database 'localhost:c:\temp\a.fdb';

Note, there is no error, i.e. database is created and isql have active 
connection

  SQL show version;
 ISQL Version: WI-V2.5.2.26540 Firebird 2.5
 Server version:
 Cannot get server version without database connection

  Impossible... here shouild be present 3 lines with reports from engine, 
network server and
network client subsystems:

SQL show version;
ISQL Version: WI-V2.5.5.26858 Firebird 2.5
Server version:
Firebird/x86/Windows NT (access method), version WI-V2.5.5.26885 Firebird 2.5
Firebird/x86/Windows NT (remote server), version WI-V2.5.5.26885 Firebird 
2.5/XNet (WIN7X64)/P12
Firebird/x86/Windows NT (remote interface), version WI-V2.5.5.26858 Firebird 
2.5/XNet (WIN7X64)/P12
on disk structure version 11.2

Instead, we have message about absent connection ! 


  SQL connect 'localhost:c:\temp\a.fdb';
  Commit current transaction (y/n)?y
  Committing.

  It is expected - isql have active connection and active transaction and ask 
to commit it before
disconnect.

  Database:  'localhost:c:\temp\a.fdb', User: sysdba
  SQL show version;
  ISQL Version: WI-V2.5.2.26540 Firebird 2.5
  Server version:
  Cannot get server version without database connection

  Same error...


  Could you run script below ?

isql -user sysdba -pass masterkey
create database 'localhost:c:\temp\a.fdb';
show version;
show database;
exit;

gstat -h c:\temp\a.fdb

Regards,
Vlad

  

Re: [firebird-support] Re: Firebird 2.52 gbak fails to do a restore - error trigger (3)

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

---In firebird-support@yahoogroups.com, hvlad@... wrote :
 ---In firebird-support@yahoogroups.com, jackmason@... wrote :
 
   Hmm... i'd say it is impossible to obtain such results using FIrebird. See 
  below
 
   C:\Program Files\Firebird\Firebird_2_5\binisql -user sysdba -pass 
 masterkey
  Use CONNECT or CREATE DATABASE to specify a database
  SQL create database 'localhost:c:\temp\a.fdb';

 Note, there is no error, i.e. database is created and isql have active 
 connection

  SQL show version;
  ISQL Version: WI-V2.5.2.26540 Firebird 2.5
  Server version
  Cannot get server version without database connection

  I looked at ISQL's source code and found that such error message is be shown 
if
isc_version() call returns error. ISQL assume that error means no connection. 
But
there will be error also if server can't recognize info items passed by 
isc_version()
into underlying call of isc_database_info(). In Firebird 2.5 isc_version() 
implementation
passed isc_info_firebird_version tag into isc_database_info(). I'm almost sure 
you have
run *not Firebird 2.5* server and it have no knowledge about 
isc_info_firebird_version
and returns error.

  
Hope it is clear now.
Vlad

PS usage of isc_info_firebird_version (instead of isc_info_version) was 
introduced at year 2003,
so, i guess, you have tooo old Firebird running or even Interbase.

  

[firebird-support] Re: losing connection with server on a local PC

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

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

  BARO (Client) Tue May 12 12:20:20 2015  C:\Program 
  Files\Firebird\Firebird_2_5\bin\fbserver.exe: terminated abnormally 
  (4294967295)
 

  It means that Firebird crashes, unfortunately. Could you provide me with full 
memory dump when (if) next crash happens ?

Regards,
Vlad

[firebird-support] Re: Memory usage excess / leak in FBServer 2.5.4

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

 ---In firebird-support@yahoogroups.com, joja.lists@... wrote :
 
  I'm facing a problem where the firebird server v2.5.4 on win 2012 server in 
 superserver mode is leaking  memory. Currently I'm at ~5GB usage.

1. Show us results of 
  select count(*) form mon$attachments
  select count(*) form mon$transactions
  select count(*) form mon$statements

2. Do you use any custom UDF's ?

Regards,
Vlad

[firebird-support] Re: Fwd: Nbackup restore failure

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

 ---In firebird-support@yahoogroups.com, vncastanheira@... wrote :
  
  It should be a nbackup file since it has .nbk extension 

  Strong proof :-)

 and created by a tool that uses nbackup.

  What tool ? How it is uses nbackup ? 

 Is it possible to recover it, if corrupted?
  
  I don't know.

  Show us output of  

gstat -h your_nbk_file

to make sure it is really zero-level nbakup file.

Regards,
Vlad

[firebird-support] Re: Fwd: Nbackup restore failure

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

 ---In firebird-support@yahoogroups.com, vncastanheira@... wrote :
 
  I'm trying to restore a backup lvl 0, but encountering the following error:
  
 Failure: Cannot get backup guid clumplet from I.O backup
 

 There's no place where this error gets reported and I'm unable to figure out 
 the reason by this simple message.

 Any thoughts?


  Your level 0 backup file is not produced by nbackup (or corrupted 
somewhat). It must contain GUID of the
backup at database header page but it can't be found there.

Regards,
Vlad

  

Re: Odp: [firebird-support] Database restore speed with IBExpert and Gbak

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

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

 From a throughput perspective, this would mean:
  
 IB XE3 = 0,918 MB/s
 IB XE7 = 1,896 MB/s
 FB 3 = 3,282 MB/s
  
 To be honest, astonishing low numbers in 2015, for all three.
 
  To be even more honest, measure time of restore without building indices and 
non-table metadata objects. Else your statement is about nothing, sorry

  
Regards,
Vlad

PS I don't say Firebird is able to restore at full speed of HDD sequential 
write, but your 
conclusion is very far from truth. IMHO, of course.

[firebird-support] Re: Firebird 2.52 gbak fails to do a restore - error trigger (3)

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

---In firebird-support@yahoogroups.com, jackmason@... wrote :
 
  The databases reside on a Linux Mint server, but we back them up to a 
  Windows 7 system.   Both are running the same release of Firebird.  

  Are you sure you run Firebird 2.5 on Win7 (not 2.1) ?

Please, run following in command prompt (at firebird\bin folder) and show us 
results:

isql -user sysdba -pass masterkey
create database 'localhost:c:\temp\a.fdb';
show version;
drop database;
exit;

Regards,
Vlad

[firebird-support] Re: Restoring from Firebird-2.5.4.26856_0_Win32 to Firebird-3.0.0.32136_0_Win32_RC1

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

Rajiv,

there is already a field named SEC$NAME_PART in ODS12 system tables.

You should rename your field\domain in source database in order to restore it 
in FB3.

Regards,
Vlad

Re: [firebird-support] Re: How to make Sphinx work with Firebird on Linux?

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > On 13/11/15 14:22, hvlad@... mailto:hvlad@... [firebird-support] wrote:
 > > Patch was updated but not announced:
 > > 
> > http://web.firebirdsql.org/download/sphinx/ 
> > http://web.firebirdsql.org/download/sphinx/
> > 
> > You should get Sphinx sources, apply patch and make by yourself on CentOS
 
> Little problem Vlad ... 2.2.9 is not actually available, not tried yet
> but will the patch work with 2.2.10?
 
  I'll check but i don't expect issues with sorce code patch. What could be a 
problem - not updated
patch for Linux build.

Regards,
Vlad

Re: [firebird-support] Re: How to make Sphinx work with Firebird on Linux?

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

 > ---In firebird-support@yahoogroups.com,  wrote :
> 
 > Seems that patch with a little tweaks in configure scripts can be applied to 
 > 2.2 branch  > 
 > https://github.com/sphinxsearch/sphinx/pull/10 
 > https://github.com/sphinxsearch/sphinx/pull/10 > 

 > configure part works i only have small bug with make 
 > 
 > make  all-am
 > make[2]: Entering directory '/home/mariuz/work/sphinx/src'
 > g++  -fno-rtti -Wall -g -D_FILE_OFFSET_BITS=64 -O3 -DNDEBUG   -o indexer 
 > indexer.o libsphinx.a  -L/usr
> /lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -ldl-lodbc -lexpat 
> -ldl -lm -lz  -L/usr/local/lib -lrt  -lpthread
 libsphinx.a(sphinx.o): In function `CSphSource_FBSQL::SqlError()':
 > 
 > /home/mariuz/work/sphinx/src/sphinx.cpp:28777: undefined reference to 
 > `isc_dsql_free_statement'
 > collect2: error: ld returned 1 exit status
 


   I see no fbclient in linker command line

Regards,
Vlad




  

Re: [firebird-support] Re: How to make Sphinx work with Firebird on Linux?

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

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

 On 13/11/15 14:22, hvlad@... mailto:hvlad@... [firebird-support] wrote:
 > Patch was updated but not announced:
 > 
 > http://web.firebirdsql.org/download/sphinx/ 
 > http://web.firebirdsql.org/download/sphinx/
 > 
 > You should get Sphinx sources, apply patch and make by yourself on CentOS
 
>  Little problem Vlad ... 2.2.9 is not actually available, not tried yet
>  but will the patch work with 2.2.10?
 
  I've updated the patch (no linux build, sorry), see 
http://web.firebirdsql.org/download/sphinx/2.2.10/

Hope it helps,
Vlad

[firebird-support] Re: Problem occuers while trying to generate trace file

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

---In firebird-support@yahoogroups.com, babak_bsn@... wrote :
  
  I would like to trace data base activity in fire bird using Audit/Trace 
  Services. I run this command in my command prompt:
  
  C:\Program Files\Firebird\Firebird_2_5\binfbtracemgr -se 
  localhost/3050:service_mgr -user SYSDBA -password masterkey 

  -start -name User Trace 1 -config fbtrace.conf  
  C:\Users\Babak\Desktop\trace.out
 
 
  This command generates trace.out file, but I get such an error:
  
  error during fopen operation for file ftrace.conf
  error while trying to open file
  System can not find the file
  
  What should I do to solve these errors?
 

   You specify 

 

 -config fbtrace.conf 

 

 but default fbtrace.conf file is at one folder upper. There is no fbtrace.conf 
in bin folder.
 

 Regards,
 Vlad

  

[firebird-support] Re: How to make Sphinx work with Firebird on Linux?

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

Patch was updated but not announced:

http://web.firebirdsql.org/download/sphinx/ 
http://web.firebirdsql.org/download/sphinx/

You should get Sphinx sources, apply patch and make by yourself on CentOS

Good luck,
Vlad

Re: [firebird-support] About firebird performance

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > I discovered something else quite interesting: > 
 > I realized both IB and FB dbs have pagebuffers set to 100.000. I changed FB 
 > to 0 and now it works like I 
> expected!!..Same query now completes on FB on 50 sec. It seems this is the 
> problem although I don't 
> understand why.
  

  Carefully read firebird.conf. Especially FileSystemCacheThreshold setting.

  You've set page cache for database above default value of 
FileSystemCacheThreshold (100K > 64K) thus disable file system caching for that 
database.

  If you really need both page cache of 100K pages and file system cache, raise 
FileSystemCacheThreshold value to be more than 100K

Regards,
Vlad

[firebird-support] Re: What is better: UPDATE or DELETE + INSERT?

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Hello,
> 
> I need to rebuild some records in my database. I have to choices:
> 
> 1. Firstly I delete set of records and then insert new ones.
> 2. I update the existing ones.
> 
> What solution is preferable? Will scenerio number 1. cause more garbage in 
> database and decrease its performance?

  Think about indices: 
- when you do UPDATE, Firebird will add new index entries only for those 
indices which keys was changed;
- when you do DELETE and INSERT, Firebird will add new index entries for every 
index in relation.

Regards,
Vlad

  

[firebird-support] Re: Error on Gfix

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > I'm using Firebird 2.5.x and the command gfix -v -full returned:
 > 
 > Summary of validation errors
 > 
 > Number of record level errors : 3
 > Number of database page errors : 1993
 > 
 > What I have to do in order to fix it, without a backup/restore? I read in 
 > "Firebird Documentation" that the 
> command gfix -v -full will fix the orphan pages and record level erros, 
> however, every time when i ran 
> "gfix -v -full" is returned the error above.

  gfix will correct orphan pages if and only if there were no other errors. 
Strongly speaking, "orphan pages"
is not error - in the sense that it not affect database operations. So, there 
is no strong need to fix it ASAP.


Regards,
Vlad

PS In fb3 we divided all inconsistencies detectable by validation (gfix) by 
"errors" and "warnings". 
All kind of "warnings" are not affect correctness of database operation

Re: [firebird-support] Re: Error on Gfix

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Hi Vlad, thank for your e-mail!
 > 
 > I understood what you said, however, in the link 
 > http://www.firebirdsql.org/manual/gfix-dbverify.html 
 > http://www.firebirdsql.org/manual/gfix-dbverify.html I got the following 
 > information:
 > 
 > 
 > Full Validation
 > 
 > By default, validation works at page level. If no need to go deeper and 
 > validate at the record level as well, the command to do this is:
 > 
 > gfix -v[alidate] -full database_name
 > 
 > using this option will validate, report and update at both page and record 
 > level. Any corrupted structures etc will be fixed.
 > 
 > 
 > With the text above, I understood that the command gfix -v -full would fix 
 > both orphan page and record level error.
 > 
 > Is the text above wrong? 

  Yes, it is poorly worded and thus not correct. Note, validation process was 
always poorly documented, unfortunately.

A bit more info :
a) orphan record versions not fixed in any case, they just stay in database
b) hard corrupted records could be fixed by marking as "damaged" but only if 
-mend was specified.
  Such records stay in database and skipped by the engine.
c) orphan pages are fixed if and only if there was no other errors detected and 
-full was specified

Regards,
Vlad

Re: [firebird-support] Firebird Embedded on web hosting

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > On 18-9-2015 19:50, amigoface@... mailto:amigoface@... [firebird-support] 
 > wrote:
 > > i have problems on using firebird embedded on my asp.net web host .
> >
 > > everything work fine locally but on the web i have an exception
 >
> > System.Data.Entity.Core.EntityException: The underlying provider failed
 > > on Open. ---> FirebirdSql.Data.FirebirdClient.FbException: Can't
 > > create directory "C:\ProgramData\firebird\". OS errno is 5
 > > ---> FirebirdSql.Data.Common.IscException: Can't create directory
 > > "C:\ProgramData\firebird\". OS errno is 5

...
 > For reference error number 5 is access denied. Firebird writes the lock 
> files for a database into C:\ProgramData\firebird but it isn't able to 
> create the folder (you don't have enough rights).

  Correct. 

  One addition - process which hosts Firebird (web server, afaiu) should have 
privileges 
to create folder in "C:\ProgramData". Or you can create it by yourself and 
grant 
privileges to create\delete and read\write files at it 
("C:\ProgramData\firebird") to the account used for web server.

> To redirect the lock files, you need to set the environment variable 
> FIREBIRD_LOCK to the location where you can write.

  Never, never, never do it !!! At least until you 200% sure you really need it 
!

Regards,
Vlad

[firebird-support] Re: Firebird Embedded on web hosting

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

> ---In firebird-support@yahoogroups.com,  wrote :
 
 > anyway, since i cannot control where fbembedded put it's lock and trace 
 > files, 

  Does you read what Mark wrote about FIREBIRD_LOCK environment variable ?

> my only option now is to build firebird from source and change the directory 
> by hand 

  It is absolutely not needed 

  
Regards,
Vlad

[firebird-support] Re: Event name size?

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

Quick look at sources show that

1. isc_event_block\gds__event_block and isc_event_counts\gds__event_counts make 
assumption that event name length is 1-byte wide,
i.e. it encode\decode event into\from event blocks using 1-byte length for 
names. 
This give us up to 255 bytes for name string.

2. gds__event_block_a\isc_event_block_a assume that event names is no more than 
31. 
I.e. if application uses these API, it will be limited by 31 bytes names. 
Comments shows that XXX_a API's is used by ADA preprocessor.

3. Event manager uses 2-byte for name length.

4. Engine make no guess about event name length, at least i see no such code.

So, i'd say you may use events with names up to 255 bytes (assume you not using 
XXX_a API's). 
But, again, it was a quick look and i could miss something.

Regards,
Vlad

[firebird-support] Re: How much percentage of a database space is a table contents taking.

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

> ---In firebird-support@yahoogroups.com,  wrote :
 
> > Monday, November 30, 2015, 7:40:50 PM, Thomas Steinmaurer wrote:
 > > That information is also available on the database header page,
 > > which can be extracted by gstat -h

> Unfortunately, that information is absent from gstat -h, 
  Because on the header page no such information as "number of allocated pages".

Why not calculate it as and file_size / page_size ?

Regards,
Vlad

  

[firebird-support] Re: Simply bad, new is not always better. FB3 and ODBC

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Using Database Workbench I added a trigger to a table, but it didn't 
> seen to do anything (in particular didn't throw an exception which it 
> should have done when I deliberately created an invalid record).
 > 
> After restarting DBWB the trigger behaved as expected.
 > 
> Do triggers not take effect on existing connections, or something?


  Make sure you committed transaction where trigger was created.
Also, what is exact Firebird version ?

Regards,
Vlad

[firebird-support] Re: Simply bad, new is not always better. FB3 and ODBC

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

---In firebird-support@yahoogroups.com,  wrote :
 
 http://tracker.firebirdsql.org/browse/CORE-5210 
http://tracker.firebirdsql.org/browse/CORE-5210 
  
 
 
 http://tracker.firebirdsql.org/browse/CORE-5210
 
 [#CORE-5210] Firebird 3.0 + fbclient 3.0 - POST_EVEN... 
http://tracker.firebirdsql.org/browse/CORE-5210 Database trigger POST_EVENT 
after field update. Test results: 1. Firebird 3 server + fbclient 3 - doesn't 
work 2. Firebird 3 server + fbclient 2 - works 3. Fire...


 
 View on tracker.fireb... http://tracker.firebirdsql.org/browse/CORE-5210
 Preview by Yahoo 
 

  

 > Is this problem solved? Just tried on FB 3.0.1.32525, still didn't work.

Problem described in tracker - yes, solved. Something unknown you speak about - 
i have no idea.

Regards,
Vlad

Re: [firebird-support] Re: Issue with Database Cache Size on FB 3.0

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Something funny is going on regarding the memory consumption, I have 
 > > attached screenshots showing the memory counters. 
> If you look at the process itself (file 
> TaskManager_Process_Firebird_Only_Consumed_2GB.jpg), it only consumed 2 GB. 
> If you look at Windows Task Manager (file 
> TaskManager_Performance_ALL_RAM_Consumed.jpg), where it says "Physical 
> Memory, 
> just under the memory graph where it shows 3.13 GB used, the cached memory is 
> 29963 Mb. I know Firebird is who consumed that 
> CACHED memory because I am forcing the DB to read 70GB on purpouse, but why 
> is the memory not showing under the process 
> Firebird ? (it only shows 2GB used there).

  File system cache maintained by OS, not by Firebird (or any other app). It is 
shared between all processes and it is impossible to
assign any part of it to the some process. 

> My aim is to set the config to only use RAM (no file system cache), and to 
> place the hole DB in RAM for caching when reading, 

  Use RamMap utility (by SysInternals) to see which files is cached by OS and 
how much.

> I am struggling to find a config mix that will do that. The config variables 
> are:
 > 
> FileSystemCacheThreshold = 0
> FileSystemCacheSize = 80
> TempCacheLimit = 24576M (I am not sure what this value is for, it seems to 
> mean the maximum amount of temporary things to have in memory, like DB read 
> cache)

  No, it is used mainly for sorting and not for the page cache.

> The above 3 variables are the ones I am playing with, plus the 
> DefaultDBCachePages = 9 in Databases.conf
 
  This is the only setting for cache size. If your db have page size of 8KB it 
will consume ~703MB of memory, if page size is 
16KB, it will take near 1.4 GB.

  

 
 Hope it helps,
Vlad

[firebird-support] Re: Max RAM for Database Cache in SS FB 3.0 64bit is to small?

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

 > ---In firebird-support@yahoogroups.com,  wrote :
 
 >> Setting DefaultDbCachePages more than allowed caused a crashed.
 
 > Try a v3.0.1 snapshot build, IIRC it was fixed recently.
 
  If you mean CORE-5240 - it was a bug in v 2.5 only, i.e. v3 is not affected

Regards,
Vlad

[firebird-support] Re: Max RAM for Database Cache in SS FB 3.0 64bit is to small?

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > 
 > Hi guys,
> 
> Am I correct that maximum allowed RAM for database cache on SS FB 3.0 in 
> Win64 is only 2 GB? 
> Calculated from max DefaultDbCachePages (131072) x max Page Size (16384) = 2 
> GB.

  No. This is limit for 32-bit builds (and it is the same as in v2.5, btw)
 
> Setting DefaultDbCachePages more than allowed caused a crashed.

  Reproducible example, please. I work with cache of 12GB with no issues for a 
few years since v2.5

> If this true, how to optimaze free RAM for performance?

  File system cache already used "free RAM", don't worry ;)

Regards,
Vlad

[firebird-support] Re: Searching for FB3 backup suggestions

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > "C:\Program Files\Firebird\Firebird_3_0\gbak.exe" -b -v -t -se service_mgr 
 > employee e:\xxx.bak -user SYSDBA > works if no one is connected else i 
 > always get error "file is used by another process".

  Specify remote (network) access explicitly (else it tries to open database 
using embedded engine and fails):

gbak.exe -b -v -t -se xnet://service_mgr employee e:\xxx.bak -user SYSDBA

or

gbak.exe -b -v -t -se localhost:service_mgr employee e:\xxx.bak -user SYSDBA

  
Regarsd,
Vlad

[firebird-support] Re: Issue with Database Cache Size on FB 3.0

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

---In firebird-support@yahoogroups.com,  wrote :
 
 ...
 
> The setting we are playing with are:
 > 
> On firebird.conf
 > 
> FileSystemCacheThreshold = 0
> FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB 
> ram.)

  Does you ever read comments describing this setting ? I can do it for you : 

"measured in % of total physical RAM". 

 
I.e. any value not in range [0..100] is wrong by definition. Read firebird.log, 
it should contain 
error message:

"Incorrect FileSystemCacheSize setting %d. Using default (30 percent)."

If you really want to limit file system cache by 50% of avalable RAM - set 
FileSystemCacheSize 
value to 50 (and restart Firebird), no need to calc in bytes...

Regards,
Vlad

  

RE: [firebird-support] performance issue with firebird 3.0 embedded on linux

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > yes, one connection per database, any thread needs to access this database 
 > would 
> create its own transaction. 

  Then you have serialization at connection level. Both engine and remote layer 
doesn't allow to run more than one thread per connection at the same time.

Regards,
Vlad
 

[firebird-support] Re: Borland InterBase Buffer Overflow Vulnerability and Firebird

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > Hi,
 > In which version of Firebird (if in any) was fixed Borland Interbase 2007
> Integer Overflow bug.
> Links:
> http://www.securityfocus.com/bid/29302/discuss 
> http://www.securityfocus.com/bid/29302/discuss

It is easy to find, just read carefully :

http://www.securityfocus.com/archive/1/492330 
http://www.securityfocus.com/archive/1/492330
http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2008-0467 
http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2008-0467 http:// 
http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2008-0467tracker.firebirdsql.org/browse/CORE-1603
 

Regards,
Vlad


  

[firebird-support] Re: [OffTopic] Sphinx for Firebird on Windows 2003

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

Hi Alex,

try http://web.firebirdsql.org/download/sphinx/2.2.10/ 
http://web.firebirdsql.org/download/sphinx/2.2.10/

Regards,
Vlad

PS I was sure official site is updated with links to this build...

[firebird-support] Re: [OffTopic] Sphinx for Firebird on Windows 2003

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Hi Vlad,
 > 
 > Thanks a lot. Can you by chance rebuild it with -id64 support or it will be 
 > too much of a hassle?

  -id64 is already ON by default, see

  
https://github.com/sphinxsearch/sphinx/commit/39f7f010e06b121a90f27b6325bf52e0d22e41f0
 
https://github.com/sphinxsearch/sphinx/commit/39f7f010e06b121a90f27b6325bf52e0d22e41f0
 

   Download page is updated, let me know if it is ok

 

 Regards,
 Vlad




 




 

Re: [firebird-support] Re: [OffTopic] Sphinx for Firebird on Windows 2003

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Hi Vald,
 > 
 > That was what I tried first, but unfortunately this build will not run on 
 > Windows Server 2003.

  Got it. It doesn't run on W2K3 because VS 2013 doesn't support XP\W2K3 by 
default.
I'll re-build it a bit later with support for XP.

Regards,
Vlad



 


 

[firebird-support] Re: UDF install in FB3.0 RC2

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > If we dismiss rfunc.dll because it has bugs then I mention ib_udf.dll (the 
 > > one with FB2.x) and freeUDFLib.dll (that works under FB2.x). 
> Both dlls install into fb3rc2. Both fail in the manner I described. So what 
> is the problem? Better yet what are solutions?

  Just tried ib_udf.dll from v2.5.6 and old FreeUDFLib.dll with FB3 - no 
problem found.

Regards,
Vlad

[firebird-support] Re: request synchronisation error

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

  If your application not see such error - don't worry. Sooner of all it is 
related with prefetch logic by 
network server and handled by it internally.

Regards,
Vlad

[firebird-support] Re: Firebird embedded on ISAPI

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Seems like I cannot use Firebird embedded on an IIS webserver via ISAPI. > 
 > > I am using Delphi REST server. 
 > 
 > Please help if it is possible.
 


   Seems you forgot to describe exact problem

Regards,
Vlad


 
  

Re: [firebird-support] Re: UDF install in FB3.0 RC2

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > I believe you mentioned earlier that you were testing FB3 RC2 on a fresh 
> install. Maybe the old UDFs require the presence of the Microsoft Visual 
> Studio 2005 C runtime (msvcr8), while Firebird only provides the 
> Microsoft Visual Studio 2010 C runtime (msvcr100).
 > 
> Try to install this runtime and see if it works then.

Yes, it could be that. Note, though FreeUDFLib not need in MSVC CRT it 
requires gds32.dll yet

Regards,
Vlad

  

[firebird-support] Re: Update big table and nbackup benefit or not?

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

Karol,

if you worry about database size after update, i could suggest to split huge 
update 
by few smaller, each in own transaction and garbage collect between passes.
I.e. something like

update t set ... where id between 0 and N;
commit;
select count(*) from t where id between 0 and N;
commit;

 update t set ... where id between N+1 and 2*N;
commit;
select count(*) from t where id between N+1 and 2*N;
commit;
...

Regards,
Vlad
  



  

[firebird-support] Re: Database Corrupt ?

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

---In firebird-support@yahoogroups.com,  wrote :
 
 Your glibc seems ok (a bit outdated, but not critical)

 > 4. MemTotal: 7911216 kB (8 GB)
 
  I mean - could you run memory test to ensure your RAM is ok ? 
Random corruptions of different databases could be sign of hardware problem 
(not necessary, but
possible)

 > 5. Here is a part of my less /proc/cpuinfo - Is it weird that I have 4 GHz 
 > but the cpu MHz just 800 Mhz ?

  It is OK, while CPU is not 100% buzy

Regards,
Vlad

[firebird-support] Re: Another Error When Backup

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

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

 > 
 > Did anyone ever face this error when gbak ? 
 > 
 > gbak: ERROR:internal Firebird consistency check (decompression overran 
 > buffer (179), file: sqz.cpp line: 239)
 > gbak: ERROR:gds_$receive failed
 > gbak:Exiting before completion due to errors
  


  Your database still get corrupted. gbak reads whole db therefore it detect 
corruption. But gbak is nor a reason of corruption, nor 
a correct tool for diagnosis (gfix is). I ask you again to check RAM on server 
machine. One more question - does you run DDL 
statements such as ALTER TABLE on live database ?

Regards,
Vlad

[firebird-support] Re: Error using a transaction parameter block with the OO API.

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > I'm using the Firebird 3.0 release version.  Delphi 10.1 Berlin with the 
 > firebird.pas release file and fbclient3.  I can connect to the database 
> and can run queries, but I can't seem to get a custom transaction parameter 
> block to work.   Here's my code:
> 
> // Transaction := attachment.startTransaction(status, 0, nil);
> tpb := Util.getXpbBuilder(status, IXpbBuilder.TPB, nil, 0);
> tpb.insertTag(status, isc_tpb_write);
> tpb.insertTag(status, isc_tpb_read_committed);
> tpb.insertTag(status, isc_tpb_nowait);
> tpb.insertTag(status, isc_tpb_rec_version);
> 
> // This always seems to error with "invalid format for transaction 
> parameter block"

  TPB must start with isc_tpb_version1 or isc_tpb_version3

> Transaction := attachment.startTransaction(status, 
> tpb.getBufferLength(status), tpb.getBuffer(status));
> 
> Has anyone gotten that to work?  Any suggestions are appreciated.  Also, does 
> anyone know what the default transaction parameters 
> are (e.g. if no custom tpb is used)?  

  IIRC, by default transaction started as {isc_tpb_concurrency, isc_tpb_write, 
isc_tpb_wait}

Regards,
Vlad

  

[firebird-support] Re: Database Corrupt ?

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

Christine,

few questions:

- am i correctly understand that you have few different DB's and all of them 
corrupted ?
- does you use 32-bit or 64 bit build of Firebird and CentOS (i686 or x64)?
- what is glibc version ?
- could you check memory on server machine ?

Regards,
Vlad

[firebird-support] Re: Firebird 3 performance

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

  Could you try latest snapshot build of 3.0.1 ?

Regards,
Vlad

Re: [firebird-support] Firebird - explanation of errors

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

---In firebird-support@yahoogroups.com,  wrote :
 
   Mark, your answer as usual very detailed and very good, i just want to
correct it a bit.

>> /1. //POS003 (Server) Fri Jul 15 11:56:32 2016/
 >>
 >> / Database: C:\GASTRO.FDB/
 >>
 >> / Index 6 is corrupt (missing entries) in table POZRACH
 >> (259)/
 
> Potentially disturbing. A backup and (if successful) a restore might be 
> in order. If the backup fails, you may first need to repair the database 
> first.
 
  Rebuild of problem index is enough, no need to backup\restore.

  If index can't be rebuild because of PK\FK violation, duplicated\orphaned
records should be found and fixed first.

...
 >> /6. //POS003 (Server) Mon Jul 18 12:51:48 2016/
 >>
 >> / Shutting down the Firebird service with 1 active
 >> connection(s) to 1 database(s)/
 
> Informational message. Something or someone stopped the Firebird service 
> while there were still connections. This may indicate potentially cause 
> data loss if that connection had active transactions.

  It means that Firebird will rollback all active transactions on shutdown. 
I.e. uncommitted
data could be lost.

Regards,
Vlad

[firebird-support] Re: gfix -shut ? server-klient

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

---In firebird-support@yahoogroups.com,  wrote :
 ...
 > Before making repair the database checked the database are logged 2 users 
 > SYSDBA (server and client). In this case, 
> the command gfix not block access to the database - not disconnect the user 
> SYSDBA client. ...

 > What command to use before taking any remedial action on the database 
 > Firebird to disconnect a user SYSDBA (client)?

  Read 
http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/gfix-dbstartstop.html#d0e30113
 
http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/gfix-dbstartstop.html#d0e30113
carefully and choose what you really need. For example, you may want to get of  
all users from db (including SYSDBA):
gfix -shut full -force 0

...then allow single attachment...
 gfix -online single
 

 ...to validate database...
 gfix -v -full
 

 ...and finally bring database online for all users


gfix -online 

  
Hope it helps,
Vlad

[firebird-support] Re: Triggers on table MON$ATTACHMENTS

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

> ---In firebird-support@yahoogroups.com,  wrote :
 >
 > I can create triggers in the MON$ATTACHMENTS table?

  No

> I use Firebird 2.5.6 with flamerobin
 > 
> I want to do something like "After Delete"
> delete from table1 where con = old.MON$ATTACHMENT_ID
 
  RTFM: Database triggers

http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-trgr.html#fblangref25-ddl-trgr-dbtrigger
 
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-trgr.html#fblangref25-ddl-trgr-dbtrigger
  


  

Re: AW: WG: [firebird-support] Firebird 3 encryption in the background

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

---In firebird-support@yahoogroups.com,  wrote :
 
> Need to come back to this. I can reproduce the behavior with flamerobin as 
> client.
> Connect via the embedded client to a database start encryption, disconnect. I 
> can see that a handle to the database 
> is still opened by flamerobin even if disconnected and I can monitor the 
> encryption progress via “gstat -e”.
> When the encryption is ready and no connection to the database is open then 
> the hosting process will crash and burn.
  
  If you are sure there is no database linger set and there is really no user 
conections - report a bug in tracker, please.

> Is there a way to stop/start the encryption progress?

  Of course - no. But you may use the fact that background encryption thread 
pause itself while database
physical state is not "normal". I.e. when database file is locked for physical 
backup. 

Regards,
Vlad

[firebird-support] Re: Server crashes sometimes

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > my Database isn’t reachable for short times. This happens once in a few 
 > days. The firebird.log shows following messages: > linux-j4zt  Wed Aug 
 > 17 10:59:58 2016
 > /usr/sbin/fbguard: /usr/sbin/firebird terminated abnormally (-1)
 >  

 > linux-j4zt  Wed Aug 17 10:59:58 2016
 > /usr/sbin/fbguard: guardian starting /usr/sbin/firebird
 >  

 > During this period users get database unreachable messages.
   > 
 > Can I figure out why this happen? Is there a way to do a trace and to send 
 > it to bug tracker?
 

   This (a bit old but still correct) article could help to produce stack 
backtrace
 http://www.ibphoenix.com/resources/documents/development/doc_36 
http://www.ibphoenix.com/resources/documents/development/doc_36


 

 > I’m using FB 3.0 x64 on OpenSuse Leap 14.1, Kernel 4.1.21-14-default.
 

   Could you try snapshot build of 3.0.1 ?
 

 Regards,
 Vlad

[firebird-support] Re: Is there an easy way for input rows from a script file?

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

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

 If I connect to a database "Database1" using ISQL, I can write something like:
 

 OUTPUT MyFile.DAT;
 SELECT * FROM MyTable;
 OUTPUT;
 

 And all the rows of "MyTable" will go to the text file "MyFile.DAT". That's ok 
and works fine.
 

 But now, I want to connect to "Database2", which also have a table called 
"MyTable" and with the same structure.
 

 After that, I want to insert into "MyTable" (of "Database2") the rows 
contained in "MyFile.DAT"
 

 How can I do such thing without a lot of effort writing an INSERT command in 
each line of "MyFile.DAT"?

If you need to export some data from one Firebird database to another Firebird 
database, use EXECUTE STATEMENT ... ON EXTERNAL and forget about files.

Regards,
Vlad

 

[firebird-support] Re: Starting auditing session

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > I've started looking into Firebird tracing capabilities and there seems to
> be very little documentation on the topic besides the official manual and
 > a few commercial applications that are well known.
 
  Does you read
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-trace.html
 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-trace.html
and 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-apiods-api.html#rnfb25-apiods-api-svctrace
 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-apiods-api.html#rnfb25-apiods-api-svctrace
?

 > I am aware of the "fbtracemgr" utility but I would like to handle things
> myself.
 
  Good. Note, you need not "auditing" session but usual "user trace" session.
Audit is a bit different, read the docs, please.

 > First of all: how do I start a tracing session? Is it a sql command that I
> call like a function or I select from like in a procedure?
 
  FIrst, you need to know how to work with Services API.
Second, read again 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-apiods-api.html#rnfb25-apiods-api-svctrace
 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-apiods-api.html#rnfb25-apiods-api-svctrace
 
  Also, you can read source code of fbtracemgr
https://github.com/FirebirdSQL/firebird/blob/B2_5_Release/src/utilities/fbtracemgr/traceMgrMain.cpp
 
https://github.com/FirebirdSQL/firebird/blob/B2_5_Release/src/utilities/fbtracemgr/traceMgrMain.cpp

 > Second: how do I feed Firebird the tracing session configuration file?
> Does it need to be a "physical" file on the same server as FB or can it be
> read from like STDIN?

  If you read docs, you know that trace configuration is sent as a part of SPB

Hope it helps,
Vlad

[firebird-support] Re: Fwd: Firebird database corrupt issue. Due to I/O error

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > I am facing a DB corruption issue on firebird database. Everytime my 
 > application tries to open connection or perform any DML activities, 
> randomly i get the following error.

  So, everytime or randomly ?
 > ERR::I/O error during "CreateFile (open)" operation for file "database"
 > Error while trying to open file; FirebirdSql.Data.FirebirdClient: 

   This is a part of error message, provide full text, everytime (not randomly)

 > I seeing this issue more frequently now a days. Tried this same database on 
 > another pc still getting this error.
 > Could someone help me.
  Sure. If\when you provide full description of what happens. Including 
Firebird version and OS name for start.

Regards,
Vlad

Re: [firebird-support] Re: Delegating SYSDBA and enumerating users

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

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

 > Now I have a couple of questions for you: > 

 > 1. Do you know if it is possible in gsec to log in under a custom ROLE? 

  Sure, it is possible

> Further to this, is there a way of GRANTing the custom ROLE the RDB$ADMIN 
> ROLE? 

  No

> I am guessing this isn't possible for 2 reasons; custom ROLE is in a 
> particular DB not the Security2.fdb, 
> and you can't GRANT a ROLE to a ROLE.
 
  Something in this direction will be available in FB4


 > 2. Do you know how things work via the .NET Provider (or rather why they 
 > don't :)? 
> When connecting using SYSDBA I see all users (ie via 
> FirebirdSql.Data.Services.FbSecurity.DisplayUsers()), 
> however logging in using another user (eg your ADM1) and the RDB$ADMIN ROLE, 
> I am still only seeing the 
> the single user ADM1.

  I don't use .NET by myself. But quick look at code show that 
FbSecurity.DisplayUsers() doesn't pass
role name into service manager (it should be done using isc_spb_sql_role_name 
tag). So, you could
add requiest to the tracker.


Regards,
Vlad

  

[firebird-support] Re: Delegating SYSDBA and enumerating users

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

A little sample with Firebird 2.5

I added ordinary users 'user1' and 'user2' and admin user 'adm1'.
Now try to display list of users

1. Use sysdba account:

firebird\bin>gsec -user sysdba -pass masterkey -display
 user nameuid   gid admin full name

SYSDBA  0 0   Sql Server Administrator
USER1   0 0
USER2   0 0
ADM10 0 admin

Of course, sysdba could see all users.
You see - adm1 is really admin user while user1 and user2 are not admins.


2. Ordinary users could see itself only:

firebird\bin>gsec -user user1 -pass u1 -display
 user nameuid   gid admin full name

USER1   0 0


3. What about non-sysdba admin ?

firebird\bin>gsec -user adm1 -pass adm1 -display
 user nameuid   gid admin full name

ADM10 0 admin

Something wrong ? Let see next sample


4. Specify admin role:

firebird\bin>gsec -user adm1 -pass adm1 -role rdb$admin -display
 user nameuid   gid admin full name

SYSDBA  0 0   Sql Server Administrator
USER1   0 0
USER2   0 0
ADM10 0 admin

  Is it what you need ? 

Regards,
Vlad

Re: [firebird-support] dropping the connection

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > Killing connections by deleting from MON$STATEMENTS requires Firebird 
> 2.5 and an ODS 11.2 database (so you will also need to backup and 
> restore the database when migrating to Firebird 2.5!).
 
  MON$ATTACHMENTS ;)

Regards,
Vlad

[firebird-support] Re: Delegating SYSDBA and enumerating users

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > In a production environment using Firebird v2.5, we need to delegate 
 > authority of USER CRUD operations to more than one person without these 
 > admins sharing the SYSDBA user and password.
 
  Does you read this chapter ?

https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-admin.html#rnfb25-prvlgs-super
 
https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-admin.html#rnfb25-prvlgs-super


 > These admins have been created as users with ADMIN ROLE, and are logged in 
 > under the RDB$ADMIN ROLE (eg in Flame Robin or via the .NET Provider, or 
 > '-admin' switch in gsec). With this ROLE, it is possible to perform 
 > Creation, Update, and Deletion operations of CRUD via Flame Robin as well as 
 > gsec.
 
  Ok


 > The roadblock, however, is not being able to list/enumerate the users (ie 
 > Read). In gsec when logged in as SYSDBA all users are displayed via the 
 > 'display' command, whereas using another RDB$ADMIN superuser only the logged 
 > in user is displayed. 

  Does you pass RDB$ADMIN role name to a gsec command line ?

> The latter is also the case when using the .NET Provider and making the call 
> to FirebirdSql.Data.Services.FbSecurity.DisplayUsers(). 

  Does you specify RDB$ADMIN role when using Services API ?

> Via Flame Robin menu Server | Manager Users, you are prompted with the 
> Database Credentials dialog with Username pre-populated with 'SYSDBA' and 
> read-only.
 
  I don't know if Flame Robin ask for role in this dialog.

  It is enough for start :)

Regards,
Vlad

Re: [firebird-support] Re: Starting auditing session

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

> In which DLL are the isc_action_svc_trace_* functions located? I am 
 > planning to call it from a Lazarus project

  As i already wrote, you should work with Services API. If you never works 
with it before i suggest to 
read IB6 "API Guide" 

https://www.firebirdsql.org/en/reference-manuals/ 
https://www.firebirdsql.org/en/reference-manuals/

and corresponding docs for Lazarus access components you used to work with 
Firebird.

isc_action_svc_trace_* is not a functions exported from DLL.
This is numeric constants (tags) used to work with Services API.

Hope it helps,
Vlad

PS avoid overquoting, please

Re: [firebird-support] Gbak performance issues on FB3

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

Fabian,

run gbak using services (with -se switch)

Vlad

PS overquoting is BAD 

 

[firebird-support] Re: "nbackup needs local access to database file"

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > I am getting this error while running nbackup:
 >  

 > "nbackup needs local access to database file"
  > 
 > The nbackup sentence is:
  > 
 > Nbackup.exe -user SYSDBA -pas HereMyPass   -B  0  localhost:MyDB_FB3.fdb 
 >C:\temp \MyDB_Level_0.nbk
 >  

 > If I remove localhost: then it works, but that would be running as embebed 
 > and it is a superserver with active network connections.
 
F:\b30\output_Win32>firebird -a

F:\fb30\output_Win32>isql localhost:test_a -user sysdba -pass masterkey
Database: localhost:test_a, User: SYSDBA
SQL> shell;
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

F:\fb30\output_Win32>nbackup.exe -b 0 localhost:test_a s:\test_a.nbk -user 
sysdba -pass masterkey
time elapsed0 sec
page reads  181
page writes 181

F:\fb30\output_Win32>exit;
SQL> exit;

Regards,
Vlad

  

[firebird-support] Re: Connecting to Firebird 3.01 with JDBC

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Hi guys, 
> 
> Anyone here able to connect to FB 3.01 with JDBC 2.x?
> 
> I have tried with jaybird-full-2.2.11 & have adding these lines to 
> firebird.conf 
> 1) UserManager = Srp, Legacy_UserManager
> 2) AuthClient = Srp, Win_Sspi, Legacy_Auth

 Have no impact on JayBird connection

> But no avail, always got error message 
> org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544421. connection 
> rejected by remote interface

  You missed AuthServer setting

Regards,
Vlad

[firebird-support] Re: Nbackup restored database size is 50 Mega shorter than original DB

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > 
 > We noticed the results of restoring with NBackup a 7 Gb database is 50 
> megabytes shorter in size than the original DB. Is this a problem? Or it 
> does not matter? Both Db are equal?

  Engine expands database file by relatively big chunks, in advance, therefore 
some pages at the 
tail of the database file could be never used at backup moment. nbackup doesn't 
include such 
pages into backup file.

  If you want to be sure, run gfix -v on restored database.

Regards,
Vlad

Re: [firebird-support] deploy Windows Application to access Firebird database

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Hi
 > What;s requred for the Windows app to access a remote Firebird database ?
 > The minimal installation
 
 ...
> 3. The icu* libraries, the same bitness as your application
 
 Client not used ICU. It is required for embedded, though

Regards,
Vlad

[firebird-support] Re: Which connection causes object in use - is there a way to find that out?

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > From time to time, when I try to alter a stored procedure in a 
> production database, I get "object in use" error. 

  Run DDL statement in wait transaction. Better to set some timeout to not wait 
too much, but not no-wait mode

> I was wandering, is 
 > there a way to find the connections (users) that prevent a particular 
> procedure from being altered?

  No, sorry (one can use lock manager but it is not trivial)

Regards,
Vlad

[firebird-support] Re: Best way to delete millions of rows

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > Pagesize is 16384 

  It is OK

> and pagebuffers is 256. 

  It is *terrible* ! Set it to 1024 and look what happens

> Servermode is superclassic. 

  Not matters in this test

 > If I drop the primary key/index everything works as expected. 

  Sure :)

Regards,
Vlad

[firebird-support] Re: Best way to delete millions of rows

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > I have finally had some more time to look into the problem I have been 
 > having, and have isolated and 
> reproduced the problem. The cause of the problem seams to be a Varchar(36) ID 
> column containing a 
> guid/uuid.

  What is database page size and page cache setting ?

Regards,
Vlad

  

Re: [firebird-support] New auto-increment column, existing data

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

---In firebird-support@yahoogroups.com,  wrote :
 
 As said I was a little unclear in my first post, my problem is not to create 
the autoincrementing field which most of your replies is about but to handle 
existing table entries. (Neither will I use the rdb$db_key for something 
persistent.)
 However the "character set octets" solved my problem and I also found out how 
to update the generator value regarding the existing items. Here is my working 
version:
  
 set term ~ ;
 execute block
 as
  declare dbkey char(8) character set octets;
 begin
  for
  select rdb$db_key
  from mytable
  into :dbkey
  do
  begin
   update mytable set id = next value for mygenerator
   where rdb$db_key = :dbkey;
  end
 end~
 set term ; ~


  This is full (and less efficient) equivalent of the

update mytable set id = next value for mygenerator

Regards,
Vlad


  

Re: [firebird-support] Re: Best way to delete millions of rows

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

---In firebird-support@yahoogroups.com,  wrote :
 
>> Here you delete some records, but then count whole table.
>>Add same "where" condition as in "count" query
> 
> I think the execution of select count is for garbage collection. 

  Sure

> If count of records that does not exists is executed, will be the garbage 
> collection executed for the deleted records?

  Exactly. No need to count not deleted records :)

Regards,
Vlad

[firebird-support] Re: Best way to delete millions of rows

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

> ---In firebird-support@yahoogroups.com,  wrote :
 > > Hey Vlad > If i only count the deleted rows, the problem is the same, and 
 > > the server hangs while performing garbage collection.
 
  Sad. Probably, table have a lot of indices ? IO subsystem, amount of RAM, 
page size and page cache size also
matters. Also interesting if filesystem is mounted with "barrier=on" option


 > On the other hand, if I count the rows not deleted, no garbage collection is 
 > performed - that makes sens as the index 
> on created makes sure that no delete records/rows are accessed. This is 
> useful to know as i can make sure that other 
> parts of the application never issues a query for rows older that 6 months.
 
  It still will clean up record versions left after UPDATE's


 > Now the garbage collection will be deferred and happen during backup. Is it 
 > possible that the same "hang" behavior will 
> happen during backup as a result of the deferred garbage collection? 

  gbak have no magic, it just reads the tables and put data into backup file.

Regards,
Vlad

[firebird-support] Re: Best way to delete millions of rows

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

---In firebird-support@yahoogroups.com,  wrote :
 
 Hey
 I have a database that needs some cleanup, in some tables i need to delete 
millions of rows. I performed the cleanup on a copy of the production database 
in a development environment, using:
 

 delete from table where created < dateadd(-6 month to current_date)
 followed by a count(*) on the table
 

Here you delete some records, but then count whole table.
Add same "where" condition as in "count" query

Regards,
Vlad

[firebird-support] Re: Data pages and data page slots

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > When I run GSTAT with the switch -data it shows me numbers after "data 
 > pages" and "data page slots". >

 > Usually these numbers are the same, but ... what happen when they are 
 > different?

  Physical numbers of data pages stored at kind of array and data of this array 
are split over
pointer pages. Every item of array also called "slot". Slot could be empty or 
contain data page 
number. When table grows, engine allocates new data pages and put its numbers 
into empty 
slots (allocating pointer pages when necessary). When data pages is freed, 
slots become empty 
but they are still present. Pointer pages freed only when table is dropped. 
Therefore number of 
"data page slots" could be much larger than number of "data pages". 

Hope it helps,
Vlad

[firebird-support] Re: Why this procedure crashes server

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

---In firebird-support@yahoogroups.com,  wrote :
 > 
 > I am trying to create or modify the following procedure:
> 
> create or alter procedure test_procedure(id integer, p_from_date date)
> as
> 
>   declare variable from_date date;
>   declare variable to_date date;
> 
> begin
> end   
> 
> But this SQL restarts server or the connection remains hanging (from IBExpert 
> or from Flamerobin). 
> This problem is in Firebird 3.0.0 and 3.0.1 (64 bit). There are no relevant 
> messages in firebird.log 
> (e.g. no gds exceptions).

  I can't reproduce it:

\Firebird-3.0.0.32483-2_x64_pdb>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> set sql dialect 1;
SQL> create database 'a.fdb';
SQL> show version;
ISQL Version: WI-V3.0.0.32483 Firebird 3.0
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V3.0.0.32483 
Firebird 3.0"
on disk structure version 12.0
SQL>
SQL> set term ^;
SQL>
SQL> create or alter procedure test_procedure(id integer, p_from_date date)
CON> as
CON>
CON>   declare variable from_date date;
CON>   declare variable to_date date;
CON>
CON> begin
CON> end
CON> ^
SQL warning code = 301
-DATE data type is now called TIMESTAMP
-SQL warning code = 301
-DATE data type is now called TIMESTAMP
-SQL warning code = 301
-DATE data type is now called TIMESTAMP
SQL>
SQL> set term ;^
SQL> show procedure;
Global procedures

Procedure NameInvalid Dependency, Type
= === =
TEST_PROCEDURE
SQL>
SQL> show procedure test_procedure;
Procedure text:
=
declare variable from_date date;
  declare variable to_date date;

begin
end
=
Parameters:
IDINPUT INTEGER
P_FROM_DATE   INPUT TIMESTAMP
SQL> commit;
SQL>
SQL> drop database;
SQL> exit;

Reagrds,
Vlad

[firebird-support] Re: firebird 3.0 ten times slower than 2.5 ?

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > In my box in isql mode ( win7_x64, dedicated ssd drive for databases)
 > default db configuration
 > 
> is still difference between 2.5 and 3.0:for select first 100 *
> (now over 100% of time fb 2.5)
 > 
 > 
> //2.5
> SQL> select count(*) from t1;
...
> Elapsed time= 4.44 sec
 Buffers = 2048
 Reads = 37865
...
> SQL> select first 100 * from t1 order by id desc;
...
> Elapsed time= 22.34 sec
> Buffers = 2048
> Reads = 37869
...
 
> // 3.0
...
> Elapsed time= 5.143 sec
> Buffers = 2048
> Reads = 80764
...
> SQL> select first 100 * from t1 order by id desc;
 ...
> Elapsed time= 55.905 sec
> Buffers = 2048
> Reads = 80701


  
  It is strange that FB3 reads 2 times more than FB2.5. You show that page size 
is the same in 
both databases (16KB). What about charset of varchar field ? gstat -r also 
could be useful to see.

Regards,
Vlad

Re: [firebird-support] Re: firebird 3.0 ten times slower than 2.5 ?

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > In my box in isql mode ( win7_x64, dedicated ssd drive for databases)
 > default db configuration
 > 
> is still difference between 2.5 and 3.0:for select first 100 *
> (now over 100% of time fb 2.5)
 

   Try Firebird 3.0.2 snapshot build, it contains some improvements

Regards,
Vlad

[firebird-support] Re: FB 3 embedded on Windows 7 64 bit

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

---In firebird-support@yahoogroups.com,  wrote :
 
> Are ther other possibilities to set the Firebird path than setting 
 > %FIREBIRD%? A parameter to attachDatabase() maybe?
 
  SetEnvironmentVariable. 

  
https://msdn.microsoft.com/en-us/library/windows/desktop/ms686206(v=vs.85).aspx 
https://msdn.microsoft.com/en-us/library/windows/desktop/ms686206(v=vs.85).aspx

  Use it before attempt to establish very first connection, or before 
attempt to load fbclient.dll (if it is possible in your app).


Regards,
Vlad

  

Re: [firebird-support] Re: FB 3 embedded on Windows 7 64 bit

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

 

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

 On 10.03.2017 15:35, hvlad@... mailto:hvlad@... [firebird-support] wrote:

> So setting FIREBIRD environment variable is the correct and only way to 
> define the Firebird/plugin path at runtime?
 
  Correct - yes, only - no (explained already), easy - yes. 
To be exact: it defines Firebird root directory, not plugin path.

Regards,
Vlad

[firebird-support] Re: firebird 3.0 ten times slower than 2.5 ?

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

---In firebird-support@yahoogroups.com,  wrote :
 
 Hi,
 
 > fb 2.5.7.27050_0_x64
 > fb 3.0.1.32609_0_x64
 >
> same table and data:
 > 
...
> 
> select count(*) from t1
 > 
> //FB 2.5 time 4 sec.
> //FB 3.0 time 6 sec
 > 
> //now select first 1 mln recs order by id desc
 > 
> select first 100 * from t1 order by id desc
 > 
> //FB 2.5 time 11 sec
> //FB 3.0 time 1 min 54 sec.

  The main question - where selected records was directed to ? I.e. you select 
1 mln records and
where do you store it ? Show on console ? It will be slw and doesn't depend 
on Firebird at all.
I run this in isql (note "output null" below):

a) 2.5.7.27038

SQL> set plan;
SQL> set stat;
SQL> output null;

SQL> select count(*) from t1;

PLAN (T1 NATURAL)
Current memory = 9395256
Delta memory = 124384
Max memory = 9465760
Elapsed time= 2.40 sec
Buffers = 2048
Reads = 151770
Writes 0
Fetches = 20303446

SQL> select first 100 * from t1 order by id desc;

PLAN SORT ((T1 NATURAL))
Current memory = 9396336
Delta memory = 1080
Max memory = 71268832
Elapsed time= 10.58 sec
Buffers = 2048
Reads = 151591
Writes 0
Fetches = 20303022


b) 3.0.1.32609

SQL> set plan;
SQL> set stat;
SQL> output null;

SQL> select count(*) from t1;

PLAN (T1 NATURAL)
Current memory = 10928296
Delta memory = 251424
Max memory = 10997848
Elapsed time= 3.311 sec
Buffers = 2048
Reads = 151821
Writes = 0
Fetches = 20303499

SQL> select first 100 * from t1 order by id desc;

PLAN SORT (T1 NATURAL)
Current memory = 10929000
Delta memory = 704
Max memory = 73851528
Elapsed time= 12.710 sec
Buffers = 2048
Reads = 151621
Writes = 0
Fetches = 20303049


c) 3.0.2.32691

SQL> set plan;
SQL> set stat;
SQL> output null;

SQL> select count(*) from t1;

PLAN (T1 NATURAL)
Current memory = 11024632
Delta memory = 251424
Max memory = 11094184
Elapsed time= 2.390 sec
Buffers = 2048
Reads = 151821
Writes = 1
Fetches = 10606295

SQL> select first 100 * from t1 order by id desc;

PLAN SORT (T1 NATURAL)
Current memory = 11025336
Delta memory = 704
Max memory = 73947864
Elapsed time= 11.653 sec
Buffers = 2048
Reads = 151621
Writes = 0
Fetches = 10605902

Regards,
Vlad