Re: [firebird-support] <>

[Alexey Kovyazin]

Hello Richard,

Follow these steps


How to repair a corrupt Firebird database?

http://ib-aid.com/articles/item135

Regards,
Alexey Kovyazin
IBSurgeon

>
> Hi to all,
>
> I have error to my database while connecting it to my IBOConsole.
>
> Detail Message:
>
> ISC ERROR CODE:335544335
>
> ISC ERROR MESSAGE:
>
> Database file appears corrupt (/data/MYDB.FDB)
>
> Wrong page type
>
> Page 64586 is of wrong type (expected 5, found 0)
>
> I'll already try this procedure but still my database is corrupted
>
> Steps to fix database:
> 
> 1) always work with a copy of the database in case further damage
> occurs. Working with a copy also gives you exclusive access
> which is required to perform the following operations:
>
> 2) gfix -v -f database.gdb
>
> 3) if previous step reports corruption: gfix -m -i database.gdb
>
> 4) repeat step 2 to see if the corruption was fixed
>
> Please help L
>
> Regards,
>
> Chad
>
> Disclaimer:
>
> This message contains confidential information and is intended only 
> for the individual named. If you are not the named addressee you 
> should not disseminate, distribute or copy this e-mail. Please notify 
> the sender immediately by e-mail if you have received this e-mail by 
> mistake and delete this e-mail from your system. E-mail transmission 
> cannot be guaranteed to be secured or error-free as information could 
> be intercepted, corrupted, lost, destroyed, received late or 
> incomplete, or could contain viruses. The sender therefore does not 
> accept liability for any error or omission in the contents of this 
> message, which arises as a result of e-mail transmission. If 
> verification is required, please request a hard-copy version from 
> Toshiba Information Equipment (Phils.) Inc., 103 East Main Avenue 
> Extension, Special Export Processing Zone, Laguna Technopark, Binan, 
> Laguna, Philippines 4024
>
> [Non-text portions of this message have been removed]
>
> 



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

Re: [firebird-support] division by zero in FB2.5

[Alan J Davies]

No, dialect was and still is 3
I backed up (after making another copy) installed 2.5.1.26351, then 
restored.
I can run on the remote customer server (2.5.1) side-by-side with my 
server (2.1) and see a fail - vs - success on the same data.

Its not a regional settings thing either because I also get a failure on 
my laptop (2.5.1). My server and laptop are UK settings, the customer 
server has US settings.

Looks like I'm going to have to isolate each calculation - not much fun 
in some 120 or so.
Thanks all, anyway.
Alan J Davies
Aldis

On 24/10/2011 22:51, Helen Borrie wrote:
> At 10:18 AM 25/10/2011, Alan J Davies wrote:
>  >mmbtu=tons*:mmbtu_factor, /* factor is 8.54 supplied from another table
>  >
>  >near the top of SP
>  > select mmbtu*0.002
>  > from paramfil
>  > into mmbtu_factor,
>  >tons are always <>0 by selection, so mmbtu cannot be 0
>  >
>  >I come back to the same question - it works with the same data on 2.1
>  >but not 2.5
>
> Did you change the dialect of the database when you upgraded it?
>
> ./hb
>
> 
>
> No virus found in this message.
> Checked by AVG - www.avg.com 
> Version: 2012.0.1834 / Virus Database: 2092/4572 - Release Date: 10/24/11
>




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [firebird-support] division by zero in FB2.5

[Mark Rotteveel]

On Tue, 25 Oct 2011 12:27:21 +0100, Alan J Davies
 wrote:
> No, dialect was and still is 3
> I backed up (after making another copy) installed 2.5.1.26351, then 
> restored.
> I can run on the remote customer server (2.5.1) side-by-side with my 
> server (2.1) and see a fail - vs - success on the same data.

What is the exact 2.1 version?

Mark

Re: [firebird-support] division by zero in FB2.5

[Alan J Davies]

2.1 version is 2.1.3.18185


Alan J Davies
Aldis

On 25/10/2011 12:37, Mark Rotteveel wrote:
> On Tue, 25 Oct 2011 12:27:21 +0100, Alan J Davies
>  > wrote:
>  > No, dialect was and still is 3
>  > I backed up (after making another copy) installed 2.5.1.26351, then
>  > restored.
>  > I can run on the remote customer server (2.5.1) side-by-side with my
>  > server (2.1) and see a fail - vs - success on the same data.
>
> What is the exact 2.1 version?
>
> Mark
>
> 
>
> No virus found in this message.
> Checked by AVG - www.avg.com 
> Version: 2012.0.1834 / Virus Database: 2092/4572 - Release Date: 10/24/11
>

Re: [firebird-support] Re: How to insert only if a matching row does not exist?

[Milan Babuskov]

gastrocus wrote:
> INSERT INTO T1 (type, name, sysid, flag) values (1, 'Z', 1, 0)
> WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z')

You can apply select to rdb$database table which always returns one row:

INSERT INTO T1 (type, name, sysid, flag)
SELECT 1, 'Z', 1, 0 FROM RDB$DATABASE
WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z');

HTH

-- 
Milan Babuskov

==
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==

[firebird-support] Transactions and cleaning up of rows after a massive deletion

[fernnaj]

Hello,

I have the following situation. A table is used to store values that are 
generated quite frequently. A second process will read the values and do 
something with them. After that processing the read values are no longer needed 
and they can (should!) be deleted from the database. However, the second 
process might fail (if the network is not available for example) and in this 
case the values pile up in the table (real situation: 2 GB of data in that 
single table after a network outage of 2 days). Eventually the process will 
work again and those values will be processed - and deleted.

And that is where my problem is. Firebird has a thing with bulk deletions - the 
next time you do a select from the table the deleted records seem to be 
"cleaned up" (don't know the technical expression), and if you have deleted a 
lot of rows, this can take forever.

The question is: if I have a select in one transaction that is suffering from 
the cleaning up after a deletion of say 1 million rows (and it's taking forever 
to return results), will another transaction that just writes a new row in the 
same table be also delayed? Writing speed in that table is critical, reading is 
important but not critical.

Of course I am going to do tests inhouse, but I would really appreciate an 
answer from the point of view of the inside work of Firebird.

Thanks a lot in advance,

Fernando Nájera

Re: [firebird-support] Transactions and cleaning up of rows after a massive deletion

[Ann Harrison]

On Tue, Oct 25, 2011 at 11:24 AM, fernnaj  wrote:

>
>
> The question is: if I have a select in one transaction that is suffering
> from the cleaning up after a deletion of say 1 million rows (and it's taking
> forever to return results), will another transaction that just writes a new
> row in the same table be also delayed? Writing speed in that table is
> critical, reading is important but not critical.
>

We call that process "garbage collection".   The answer depends slightly on
the architecture of Firebird you're using.  With Classic, the single client
doing the select will be very slow, and others will be slower than normal
because of the overall system load, but will be scheduled by the operating
system according to its priorities.  In SuperServer,  all queries are
threads in the server process which schedules them on a round-robin basis,
giving each a quantum of time.  So again, the inserts will be slower than
normal, but will not run while the garbage is being collected.   More or
less the same answer for both, but in Classic you can blame the OS for not
scheduling things when you want, and in SuperServer, blame the server.

Please don't try to speed up the inserts in Classic  by increasing their
priority.  They will be contending with the garbage collecting query for
pages and if it can't run to release them, the whole system will slow down.

Good luck,

Ann


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

[firebird-support] Basic questions regarding gbak restore

[jacobhavkrog]

Hi - 

Using FB2.5, I'm doing a simple restore of my database alias MYDB

Before doing so, I shut it down:

gfix -user sysdba -password masterkey -shut full -force 0 MYDB

Here is the restore command:

gbak -user sysdba -password masterkey -r o c:\backups\MYDB1.fbk MYDB

Is the database file MYDB.gdb completely shielded from being accessed by the 
clients while it is shut down? I.e. during the restore process?

I ask because in http://www.firebirdsql.org/pdfmanual/Firebird-gbak.pdf it says:

"In addition, if you have only partially completed the restore of a database, 
and some users decide to see if they can login, the database may well be 
corrupted."

Is this possible with FB 2.5 and while the database is shut down???



By the way:
After the restore, I notice that the database is online. I find that a bit 
surprising. Does gbak restore put the database online for me?


Thanks!
Jacob

[firebird-support] Re: Transactions and cleaning up of rows after a massive deletion

[fernnaj]

Hello Ann,

Thank you so much for your prompt answer! A few questions follow...

> In SuperServer,  all queries are
> threads in the server process which schedules them on a round-robin basis,
> giving each a quantum of time.  So again, the inserts will be slower than
> normal, but will not run while the garbage is being collected.

We're using Firebird 2.1.3 for Windows. and the process running is 
fbserver.exe, so I understand we are using SuperServer.

The last bit "will not run while the garbage is being collected" can have two 
meanings for me. One is that the insert will be blocked completely until the 
garbage collector finishes the cleaning of 1 million rows for the other 
transaction; the other is that the garbage collector will clean a bit now, a 
bit later (blocking the first query), and in between the other transactions (= 
my insert) are scheduled and performed and finished.

I hope the second option is the correct one... right?

A side question: if you run two selects concurrently that happen to "go 
through" the same garbage, how will the garbage collection work? I assume that, 
if the answer to the other question is that the garbage collector cleans a bit 
now, a bit later, then both selects will take long and the garbage collector 
will be invoked sometimes by the first query, sometimes by the second, until 
both have a clean set to work with... is it so?

...which leads me to the third question: would my plain "insert into () values 
()" query "provoke" the garbage collection?

Thanks a lot for your help!

Regards, Fernando

[firebird-support] Controlling sweep in Firebird Embedded

[canacourse]

Using Firebird embedded via Ado provider and noticed that occasionally our c# 
application takes several minutes to start up -  twenty minutes in fact.  After 
shutting down the application and connecting to the same database with IBexpert 
the same delay is observed.  

I'm pretty sure this is down to automatic garbage collection but we appear not 
to be able to control it and this issue has happened several times.   These is 
what we've tried or thought might work so far.

1.   Set sweep interval to 0 and run gfix sweep in the early hours.  
Gfix does does not run against an embedded database
Fbvalidator does not appear to work on an firebird embedded  database. (Maybe 
for the same reasons as gfix) see http://tracker.firebirdsql.org/browse/DNET-399

2.   Set sweep interval to 2 and set "GCPolicy = background" in 
firebird.conf
Sweep does not appear to be running. The application ran overnight and many 
100' of 1000's of records are added and deleted.  Yet when the app is restarted 
the next day the delay occurs. SELECT is called at least once a minute in the 
app.

Not sure if it will help but here is some stats for the database just before 
the app was restarted and the 20 minute delay occurred. Sweep interval was 
2.

Oldest transaction   1463306 
Oldest active1702095 
Oldest snapshot  1702095 
Next transaction 1702097

And from a few minues after the app was fully loaded (After 20 minute delay)

Oldest transaction  1702097 
Oldest active   1712652 
Oldest snapshot 1712652 
Next transaction1712653 

Anything else we can try? Or should we move to another version of Firebird so 
we can run manual sweep.

Thanks.

Re: [firebird-support] Basic questions regarding gbak restore

[Dmitry Kuzmenko]

Hello, jacobhavkrog!

Tuesday, October 25, 2011, 8:38:14 PM, you wrote:

j> Using FB2.5, I'm doing a simple restore of my database alias MYDB
j> Before doing so, I shut it down:
j> gfix -user sysdba -password masterkey -shut full -force 0 MYDB
j> Here is the restore command:
j> gbak -user sysdba -password masterkey -r o c:\backups\MYDB1.fbk MYDB

please, please, please - don't overwrite existing DB when you do
restore. If backup is broken (for example, due bad RAM or HDD errors),
you will jump around broken db and broken backup.

Restore DB to the temporary name, then rename existing db, and
rename temporary db to original db name. If you will not be satisfied with
restore, you will still have old original db intact.

j> Is the database file MYDB.gdb completely shielded from being
j> accessed by the clients while it is shut down? I.e. during the restore 
process?

j> I ask because in
j> http://www.firebirdsql.org/pdfmanual/Firebird-gbak.pdf it says:

j> "In addition, if you have only partially completed the restore of
j> a database, and some users decide to see if they can login, the database may 
well be
j> corrupted."

it will not be corrupted, and in FB 2.x db is restored in
"single-user mode", but there are OS specific things - for example, on
Linux, if file is still open by some process, deleting it will not
delete it really.
Before 2.0 - yes, users could connect to "partially restored DB",
and could have unpredictable results executing queries.

j> Is this possible with FB 2.5 and while the database is shut down???

j> After the restore, I notice that the database is online. I find
j> that a bit surprising. Does gbak restore put the database online for me?

If restore went ok, database will be online. If something went wrong
during restore (errors), it will be in shutdown state.

-- 
Dmitry Kuzmenko, www.ib-aid.com

Re: [firebird-support] Re: Transactions and cleaning up of rows after a massive deletion

[Ann Harrison]

On Tue, Oct 25, 2011 at 1:57 PM, fernnaj  wrote:

>
> The last bit "will not run while the garbage is being collected" can have
> two meanings for me. One is that the insert will be blocked completely until
> the garbage collector finishes the cleaning of 1 million rows for the other
> transaction; the other is that the garbage collector will clean a bit now, a
> bit later (blocking the first query), and in between the other transactions
> (= my insert) are scheduled and performed and finished.
>
> I hope the second option is the correct one... right?
>

Yes, each connection gets a time-slice to run, then stops and waits for
other threads to run.

>
> A side question: if you run two selects concurrently that happen to "go
> through" the same garbage, how will the garbage collection work? I assume
> that, if the answer to the other question is that the garbage collector
> cleans a bit now, a bit later, then both selects will take long and the
> garbage collector will be invoked sometimes by the first query, sometimes by
> the second, until both have a clean set to work with... is it so?
>

There are two garbage collection methods in SuperServer, and three modes of
usage, so again, the answer is, it depends.  There is a garbage collect
thread which just removes old row versions, and there is co-operative
garbage collection in which each running query removes the old record
versions it finds.  There's also a mode in which running queries remove the
record versions that are "convenient" and puts others on the list for the
garbage collector thread.  I think "convenient" means that all the old
versions being removed are on the same page, but, hey, I'm old and easily
confused so it may mean something different.

Why, you may ask, do we have both a garbage collect thread and cooperative
garbage collection?  Several reasons.  One is that a garbage collect thread
doesn't work well with Classic.  Another is that when Firebird SuperServer
is heavily loaded, the garbage collect thread doesn't get enough time to run
and garbage builds up, gradually to the point that it affects performance.
 Fortunately, it doesn't attract rats.

>
> ...which leads me to the third question: would my plain "insert into ()
> values ()" query "provoke" the garbage collection?
>

I don't know. There may be some interaction with indexes that causes it to
look at other records.


Good luck,

Ann


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

[firebird-support] Re: Transactions and cleaning up of rows after a massive deletion

[fernnaj]

> Yes, each connection gets a time-slice to run, then stops and waits for
> other threads to run.

That makes a lot of sense.

> [...] when Firebird SuperServer
> is heavily loaded, the garbage collect thread doesn't get enough time to run
> and garbage builds up, gradually to the point that it affects performance.
>  Fortunately, it doesn't attract rats.

Oh! If it's not rats, I will then need to check what is making that strange 
noise inside my computer! :-)

> > would my plain "insert into ()
> > values ()" query "provoke" the garbage collection?
> >
> 
> I don't know. There may be some interaction with indexes that causes it to
> look at other records.

I see... a lot of testing time ahead of me!

Thank you very very much for your time and answers, Ann. You've been very 
helpful!

Best regards, Fernando

Re: [firebird-support] Transactions and cleaning up of rows after a massive deletion

[Thomas Steinmaurer]

> I have the following situation. A table is used to store values that are 
> generated quite frequently. A second process will read the values and do 
> something with them. After that processing the read values are no longer 
> needed and they can (should!) be deleted from the database. However, the 
> second process might fail (if the network is not available for example) and 
> in this case the values pile up in the table (real situation: 2 GB of data in 
> that single table after a network outage of 2 days). Eventually the process 
> will work again and those values will be processed - and deleted.
>
> And that is where my problem is. Firebird has a thing with bulk deletions - 
> the next time you do a select from the table the deleted records seem to be 
> "cleaned up" (don't know the technical expression), and if you have deleted a 
> lot of rows, this can take forever.
>
> The question is: if I have a select in one transaction that is suffering from 
> the cleaning up after a deletion of say 1 million rows (and it's taking 
> forever to return results), will another transaction that just writes a new 
> row in the same table be also delayed? Writing speed in that table is 
> critical, reading is important but not critical.
>
> Of course I am going to do tests inhouse, but I would really appreciate an 
> answer from the point of view of the inside work of Firebird.

Perhaps your read process couldn't delete the record but mark it with a 
flag and a cleanup process scheduled e.g. in the night deletes the 
marked records followed by initiating a manual SELECT COUNT(*) on that 
table in case of co-operative garbage collection.

Btw, in respect to client transaction management. Are you using hard 
commits or soft commits aka commit retaining?


-- 
With regards,
Thomas Steinmaurer

* Firebird Foundation Committee Member
http://www.firebirdsql.org/en/firebird-foundation/

* Upscene Productions - Database Tools for Developers
http://www.upscene.com/

* My Blog
http://blog.upscene.com/thomas/index.php

[firebird-support] Re: Transactions and cleaning up of rows after a massive deletion

[fernnaj]

Hi Thomas,

> Perhaps your read process couldn't delete the record but mark it with a 
> flag and a cleanup process scheduled e.g. in the night deletes the 
> marked records followed by initiating a manual SELECT COUNT(*) on that 
> table in case of co-operative garbage collection.

Instead of marking rows we use timestamps to find the old records that we can 
delete, but the idea was exactly the same: to delete records and then a manual 
SELECT COUNT(1) to force the garbage collection (actually followed by a 
re-computation of indices to ensure that the queries we need are tuned). All of 
this with two problems:

* There is no "night" for us: the customer works 24/7/365, and the work load is 
equally distributed around the clock. What we thought is that, instead of 
deleting 1 million rows at once, we can delete a few hundreds/thousands, do the 
select count(), wait a bit, and continue deleting. As long as the rate of 
deletion is bigger than the inserts, we would be safe.

* We are concerned that, during the "select count(1)" phase (or during the 
recomputing of the indices), the database would be irresponsible and the new 
inserts would be blocked or partially blocked because of that.

> Btw, in respect to client transaction management. Are you using hard 
> commits or soft commits aka commit retaining?

Hard (aka "normal" :-)) commits. Would it make any improvement if we had used 
retaining commits?

Regards, Fernando

[firebird-support] Re: Firebird 2.5 crashing after calling UDF

[sir_wally_lewis]

Thanks for the fixes to my C library Tomasz and for your help.

All my C Library is originally extracted from FreeUDFLibC and I added extra 
functions.

There were errors in the C Code that I was unaware of.

Amazing that the same unaltered code works under CenTos 64 bit in 2.1 though.

Still do not understand why my freepascal UDF is crashing.

I think we will stick to using "C" now.

Even though it is a TERRIBLE language!

Kind Regards,

Robert.