gbak:restoring privilege for user SYSDBA
gbak:restoring SQL role: RDB$ADMIN
gbak: ERROR: attempt to store duplicate value (visible to active
transactions) in unique index RDB$INDEX_39
RDB$INDEX_39 relation name is RDB$ROLES.
RDB$ROLES has one row, RDB$ADMIN.
Where do I go from here?
Executing
INSERT INTO REASON_CODE(REASON,DESCRIPTION) VALUES (99,'TEST';
Yields the error
Dynamic SQL Error SQL error code = -104 corrupt pool
To set the scene, imagine a large script, one typo, and an error message
that makes you think you just thoroughly corrupted a production
database.
Have you timed your query with the plans hardcoded to ascertain that it
really is quicker using your preferred plan, Rick?
No, but I'm familiar with the data set and identified this query as running
slow (on FB 2.5.2) compared to its speed on a (now decommissioned) FB 1.5.6 box.
query
The problem appeared when on a small test box the cache was reduced from 8192
8KB pages to 4096 pages in order to reduce memory usage. The query is
extremely fast when run a second time, so I assume a large number of pages have
to be moved from disk to cache for each query.
-Original
I have a query that needs to use an index that the optimizer isn't
using. How can I get it to add the index to the query plan?
The current plan is
PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX
(I_PBM_CLAIM_NDC), AC RGM SA INDEX (PK_ACT_CHC_PHARM_SUBACCT), AC RGM C
INDEX (PK_CHC),
Thanks to both.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Leyne, Sean
Sent: Thursday, April 18, 2013 4:56 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] RE: Default column value not applied
Rick,
I get the error 'validation error for column DISCOUNTED, value
***null***At procedure ...'
The column definition is
DISCOUNTED D_BOOLEAN DEFAULT 'N' NOT NULL
So why wouldn't its value be set to N when I insert in to the table? Is
it because I'm explicitly trying to insert NULL for the column?
During restore DB is itself in single-user mode, so, noone can connect until
restore finishes.
Is this new for 2.x? I know we could connect during a restore under 1.5.
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Dmitry Kuzmenko
Sent:
Why do you use '-shut single' and not '-shut full'
Under 1.5 you can connect to a database while it is restoring, which breaks the
restore. We were throwing everything we could think of at the problem so it
wouldn't happen. If 2.x doesn't allow connections during restore we shouldn't
have
Would a backup/restore change the physical layout to match the logical?
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison
Sent: Wednesday, April 03, 2013 4:54 PM
To: firebird-support@yahoogroups.com
Subject: Re:
We have a database that we want to wipe and replace every week,
regardless of whether it is in use.
Are these the correct steps? I notice that 'gfix -online' throws an
error, probably because the database is online after gbak completes. Is
there a way for the restore to not become online during
Does anyone know what this error from gbak is?
Done with volume #1, /mnt/usb/firebird/clone.fbk
Press return to reopen that file, or type a new
name followed by return to open a different file.
] Re: Catching exception fails under isql
--- In firebird-support@yahoogroups.com, Rick Debay wrote:
I think it's a 1.5 bug, as executing the procedure from DBW also fails
no current record for fetch is well-known optimizer bug and in some
form exists even in latest Firebird versions. Usually
The isql script drops the procedure (which should fail as it doesn't
exist), creates it, uses it, drops it, then exits.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas
Steinmaurer
Sent: Friday, March 08, 2013 11:08 AM
: Sunday, March 10, 2013 4:23 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Catching exception fails under isql
--- In firebird-support@yahoogroups.com, Rick Debay wrote:
Does anyone have any insight in to this?
Running this statement in isql:
EXECUTE PROCEDURE
, 2013 4:23 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Catching exception fails under isql
--- In firebird-support@yahoogroups.com, Rick Debay wrote:
Does anyone have any insight in to this?
Running this statement in isql:
EXECUTE PROCEDURE P_IMPORT
Does anyone have any insight in to this?
Running this statement in isql:
EXECUTE PROCEDURE P_IMPORT;
Yields this error:
Statement failed, SQLCODE = -508
no current record for fetch operation
-violation of PRIMARY or UNIQUE KEY constraint
Yet running the same statement in Database
After backup we do a test restore:
gbak -v -se server_name:service_mgr -use_all_space -r backup.fbk
test.fdb
Normally we just delete the file after gbak completes, but I'd like to
be nicer to the database server. Some coworkers have also been saying
they think that Firebird is still using the
I wonder that you
- use -use_all_space for test database. You see real speedup?
It requires less space for the test.
- delete restored database. Why you keep backup, that needed to be
restored again at some reasons? Instead, better keep database, delete
backup.
The backup is written to tape.
We are currently running Firebird 1.5 on a physical server running
Linux. Automated server processes have the database password hardcoded.
We are migrating to FB 2.5 on Linux VMs. Since FB only integrates with
Windows Active Directory authentication, does anyone have suggestions on
how to manage
If I insert duplicate rows, I assume the order of operations is:
Before triggers
Insert
Exception
After triggers -- never executed
Since a block wasn't exited before the exception was handled, wouldn't
any database statements in the block stand?
Assume for a moment that it wasn't a constraint violation, but a table
trigger throwing a custom exception, and the catch block handles it
instead of handling constraint violations.
Thanks, I'm trying to follow Arthur Conan Doyle's advice Once you
eliminate the impossible, whatever remains, no matter how improbable,
must be the truth and turn improbabilities to impossibilities.
Rick DeBay
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird
https://bugzilla.redhat.com/show_bug.cgi?id=886578
The file /etc/init.d/firebird-superclassic was incorrect. We (James
Calfee) reported a bug against the EPEL firebird-superclassic.x86_64
package and Redhat patched it.
This is a FB 1.5.6 bug. It does not appear in our testing for the move
to FB 2.5.2.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Friday, November 09, 2012 4:22 PM
To: firebird-support@yahoogroups.com
CREATE EXCEPTION E '';
CREATE TRIGGER T_TEST_BEFORE FOR T ACTIVE BEFORE INSERT POSITION 0 AS
begin
EXCEPTION E 'TEST BEFORE';
End
CREATE TRIGGER T_TEST_AFTER FOR T ACTIVE AFTER INSERT POSITION 0 AS
begin
EXCEPTION E 'TEST AFTER';
End
Inserting into the table T does not throw an exception.
I have a table T (PBM_CLAIM BIGINT, CLAIM_SRC SMALLINT, ...) with the
unique constraint:
ALTER TABLE RELAY_CLAIM ADD CONSTRAINT U_RELAY_CLAIM_SRC UNIQUE
(PBM_CLAIM,CLAIM_SRC);
If I try to insert a row with (NULL, NULL, ...) when this constraint
exists, I get the error
ISC ERROR
I'd have to say that for foreign key relationships a null in the referencing
field is considered to match
How about in the referenced field? BTW, thanks for the quick reply.
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison
Sent:
Anyone available for a quick reply? Schools are closing early for
hurricane Sandy and I'd like to resolve this before an involuntary long
weekend babysitting :-(
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
I'm modifying a foreign key from one column to two.
I dropped the original foreign key from the child table, populated the
new columns, and then created the new key.
I forgot to populate the new column in the child table, so all the
relationships looked like this:
ChildParent
-
-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison
Sent: Tuesday, October 16, 2012 6:27 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] RE: Order of key elements
On Tue, Oct 16, 2012 at 9:47 AM, Rick Debay rde...@accessrxs.com
wrote
neither of these constraints make any sense (the PK itself assures uniqueness)
See the posts with the title Unique foreign key for child tables. It's
needed to be the target for a foreign key.
UPDATEs used to be slow for non-selective indexes
This is what I'm trying to avoid. Does anyone
What is the effective difference between these two key elements
orderings?
ALTER TABLE T ADD CONSTRAINT U UNIQUE (PK, HIGH_MAX_DUP)
ALTER TABLE T ADD CONSTRAINT U UNIQUE (HIGH_MAX_DUP, PK)
The first version is how I normally order key elements, from most unique
to least. Since a primary key
is
to close the program and start it again.
Greetings.
Walter.
On Thu, Oct 4, 2012 at 10:13 AM, Rick Debay rde...@accessrxs.com
wrote:
**
Anyone?
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent
, 2012 11:25 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Unique foreign key for child tables
-- Original Message ---
From: Rick Debay rde...@accessrxs.com
I have a parent table with multiple child tables.
Each child row has a foreign key that points
, October 04, 2012 3:58 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Order of operations - AND boolean
operator
On 03-10-12 22:30, Rick Debay wrote:
The field f.ID is alphanumeric, it can contain many types of data and
the data type is described by f.ID_QUALIFIER
Anyone?
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Wednesday, September 12, 2012 11:30 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] violation of FOREIGN KEY constraint
ISC
The field f.ID is alphanumeric, it can contain many types of data and
the data type is described by f.ID_QUALIFIER .
The problem is that Firebird is always evaluating f.ID = which throws
an exception when f.ID contains non-numeric characters and b.ID_TYPE_*
is numeric.
FROM FOO f
JOIN BAR b ON
Does anyone know what these errors mean?
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Friday, September 14, 2012 5:09 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Can't
(290))
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Dmitry Yemanov
Sent: Wednesday, September 12, 2012 11:54 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Can't continue after bugcheck
12.09.2012 19:42, Rick Debay wrote
ISC ERROR CODE:335544345
ISC ERROR MESSAGE:
lock conflict on no wait transaction
violation of FOREIGN KEY constraint FK_RPLPO_REPLGRP on table
RPL_PO
The relationship between parent and child was always valid, so I don't
know why this error was raised. Can this occur if a column in the
Rarely I'll get this error, and it goes away after recreating the
connection.
Neither gbak (backup restore) nor gfix (validate) report errors.
What should be my next steps?
I exported the original database as DDL and recreated it. It now works.
Scary, as the database validation tools didn't detect any errors.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Monday, August 27
I have a stored procedure that reads from an external file table.
One of the fields is varchar(10) in the stored procedure and char(10) in
the file.
I am selecting from the stored procedure and inserting in to a table
which has a matching varchar(10) COLLATE ISO8859_1 field.
If a field is empty
-support@yahoogroups.com
Subject: RE: [firebird-support] Transactions exception trapping
At 05:00 AM 11/08/2012, Rick Debay wrote:
FOR ... BEGIN
INSERT_PROCEDURE RETURNING VALUE
UPDATE TABLE WITH VALUE
WHEN ... BEGIN
LOG
END
END
If the insert procedure directly or indirectly causes
I have a table with before and after insert triggers. I also have a
stored procedure that loops, inserting rows in to the table. The last
statement in the loop is WHEN ANY to catch and log errors to an external
table and continue looping.
If an insert trigger throws an exception, will the
:39 PM, Rick Debay wrote:
Correct. There is no row, but we are getting data in the non-existing
row.
So how do I file a bug report on this?
Go to the Firebird main web site
http://www.firebirdsql.org
Select Development.
Look for the Tracker in the upper right corner of the Development page
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Left join and computed columns
Rick Debay schrieb am 19.06.2012 um 15:58 (-0400):
Since TEST_TABLE is empty, the results should be NULL.
Changing the query to 'SELECT *' return the one row in RDB$DATABASE,
and the column TEST_TABLE.ID
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Thursday, May 31, 2012 11:15 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Left join and computed columns
I'll test in 2.5.1 and file a bug
Since TEST_TABLE is empty, the results should be NULL.
Changing the query to 'SELECT *' return the one row in RDB$DATABASE, and
the column TEST_TABLE.ID is NULL and the column TEST_TABLE.COMPUTED_COL
is FAILED.
If TEST_TABLE is empty, how can anything result from a join?
-Original
I'll test in 2.5.1 and file a bug report.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Leyne, Sean
Sent: Friday, May 25, 2012 1:04 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Left join and
A query with a left join to a table with a computed column returned
values in the computed column even though the row didn't exist in the
table. All other non-computed values came back as null, which was to be
expected.
Is this a bug, or correct by SQL specifications?
Disclaimer: This message
Wouldn't gbak complain about unknown parameters?
I'm not able to connect to the database server right now so I can't run
gbak -help, but the manual at firebirdsql.org states:
-G[ARBAGE_COLLECT)inhibit garbage collection
Disclaimer: This message (including attachments) is confidential and
So each statement in an isql script is a new transaction? Is there any
way to force the whole script to be one transaction?
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas
Steinmaurer
Sent: Friday, May 04, 2012 2:40
I should have paid more attention to your reply, and also googled before
asking: http://www.firebirdsql.org/manual/isql-transactions.html
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Wednesday, May 09
Thanks for the quick reply. We'll know the results in ninety minutes.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison
Sent: Wednesday, May 09, 2012 4:49 PM
To: firebird-support@yahoogroups.com
Subject: Re:
Clever. I'll take a look at it.
What isn't prevented is addition of parentless children, but you didn't
mention that.
Sad, but it's allowed.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent:
Is a new transaction started after a commit command in an isql script?
Disclaimer: This message (including attachments) is confidential and may be
privileged. If you have received it by mistake please notify the sender by
return e-mail and delete this message from your system. Any unauthorized
I have a parent table with multiple child tables.
Each child row has a foreign key that points to one row in the parent.
Each parent row must have a child row pointing to it.
Each parent row can have only child from any of the child tables
pointing to it.
The child is created before the parent.
A
I think it is likely that your query could be improved. Am I right in
assuming that you want
there in ALL cases where there is an RPL_PO_ITM to also exist at least
one RPL_POBILL_ITM
record and that all matching RPL_POBILL_ITM must have COST defined?
No. In all cases for each RPL_PO_ITM.NDC
This query returns 'no current record for fetch operation'
SELECT po.ID, pb.ID
FROM
RPL_PO po
JOIN RPL_POBILL pb
ON pb.PO = po.ID
WHERE
po.ID = ? AND
NOT EXISTS (
SELECT 1
FROM RPL_PO_ITM poi
LEFT JOIN RPL_POBILL_ITM pbi
ON poi.NDC = pbi.NDC AND pbi.INVOICE = pb.ID
In case anyone is confused as to what I'm trying to accomplish, it's an
outer join on line items where both sides match with no NULL items.
If my approach is poor, then I won't have to worry about question about
the exception.
SELECT po.ID, pb.ID
FROM
RPL_PO po
JOIN RPL_POBILL pb
ON pb.PO
Damn. Anyone know why the heck FB won't use an index in a join with 'starting
with'?
FWIW, I ran the natural part of the query to generate a bunch of starting with
'xyx' or and appended them to query the other table. Luckily I didn't hit any
query limits.
-Original Message-
From:
Anyone? Without any indexes, I won't live to see this complete.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Friday, April 06, 2012 5:18 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support
This query uses natural for both tables, when I expected it to use an
index for the join.
select *
from table1 t1
join table2 t2 on t2.indexed_char14 starting with t.indexed_char10
where t.unindex_varchar containing 'foo'
This uses an index, so the optimizer knows about it.
select *
from table2
PLAN (B_TABLE NATURAL)
PLAN (AT INDEX (IDX_A_TABLE))
Does this mean that for every single row selected from table A, it
touches every single row from table B?
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Leyne, Sean
Whether Firebird takes advantage of this fact
That was my hope, but I don't think that's happening.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Tomasz Tyrakowski
Sent: Tuesday, April 03, 2012 3:31 AM
To:
If B_TABLE.VALUE2 never contains 'X' and half of A_TABLE.VALUE1 are 'Y'
what would be the cost of executing this statement? A_TABLE.VALUE1 is
indexed and B_TABLE.VALUE2 does not have an index.
Would it be half of A_TABLE rows times all of B_TABLE rows? Or would
the Not Exists statement be
We test restoring all our backups. Last night, this happened. We
compared the DDL of our production system with a week of backups, and
didn't find any changes in column definitions. We are starting another
restore with verbose logging. Any ideas as to what could have happened
or what we should
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Tuesday, March 06, 2012 12:08 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Precision in stored procedures
If a stored procedure has three variables
DECLARE VARIABLE A NUMERIC(18,3);
DECLARE VARIABLE B
Of Rick Debay
Sent: Tuesday, March 06, 2012 12:08 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Precision in stored procedures
If a stored procedure has three variables
DECLARE VARIABLE A NUMERIC(18,3);
DECLARE VARIABLE B NUMERIC(18,3);
DECLARE VARIABLE C DOUBLE PRECISION
If a stored procedure has three variables
DECLARE VARIABLE A NUMERIC(18,3);
DECLARE VARIABLE B NUMERIC(18,3);
DECLARE VARIABLE C DOUBLE PRECISION;
And a function is evaluated with these values
A = B / C;
A = 7.3 / 0.839080459770115
Then the value stored in A will be 8.7 not 8.700119 (a
Twice the size is an old and possibly still a good guess, I think. It
all depends on the number and size of the indices.
So to restore an 11GB database we should have 22GB available where
TempDirectories points?
Disclaimer: This message (including attachments) is confidential and may be
gbak: ERROR: I/O error for file /tmp/fb_sort__M9plMy
gbak: ERROR: Error while trying to write to file
gbak: ERROR: No space left on device
We've started getting this error when restoring an 11GB database. How
much space should we estimate for temp files?
Disclaimer: This message
@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Monday, January 23, 2012 11:53 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Sizing conversion project
You mean beside licensing cost?
I was hoping to use a SWAG such as cost per LOC to estimate
-support@yahoogroups.com
Subject: Re: [firebird-support] Sizing conversion project
Rick Debay wrote:
We are using Firebird and I've been tasked with determining the costs
for migrating to MS SQL Server. The last time I used the latter was
ten years ago, and in the role of a Java programmer.
Does
I found a restore script that has been in use for a while that is
calling gbak incorrectly. It contains gbak -se service_mgr instead of
gbak -se localhost:service_mgr.
The backup is being restored correctly. Is gbak silently ignoring the
-se flag or is it assuming localhost?
Thanks, Rick DeBay
Subject: Re: [firebird-support] Debian install
I have always used the packet taken down from the official place of
firebird without a hitch.
Best Regards
=
|| ISMAEL ||
=
- Original Message -
From: Rick Debay
To: firebird-support@yahoogroups.com
Sent: Wednesday
Thanks.
-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Dmitry Kuzmenko
Sent: Friday, October 21, 2011 10:06 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Gbak and service manager
Hello, Rick!
@yahoogroups.com] On Behalf Of Philippe Makowski
Sent: Friday, October 21, 2011 10:43 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Debian install
2011/10/19 Rick Debay rde...@accessrxs.com
I'm running Debian 6.0 (squeeze) which has a package for FB 2.5.0. FB
2.5.1, is only
80 matches
Mail list logo
