RE: [firebird-support] Re: [Mac][mavericks] build firebird server through macports?
Oh i see it's an issue with thread-local support that was only added in clang 3.3 http://llvm.org/releases/3.3/tools/clang/docs/ReleaseNotes.html#id1 ps: here is the port file and the ticket https://trac.macports.org/ticket/42128
[firebird-support] Computed Index for Integer-Fields and STARTING WITH clause
Hi, I have a question about computed indices related to integer fields. In my program there are several selects on integer-fields with a STARTING WITH clause. Firebird internally converts the integer value to a string and then filters the value. It would be great if I could create a computed index on that (internal) string so FB does an index-lookup for the data. I tried to create an computed index on a table with the expression CAST(MY_INT_FIELD AS VARCHAR(11)) cause I think firebird internally uses a VARCHAR(11) or CHAR(11) value but that doesn't solve the problem. Any idea on how to get this working without changing every select in the program? Thanks and best regards! Christian
[firebird-support] nbackup questions
I am a admin of the Fishbowl Inventory (FBI) system for our company, which is a Java app on top of Firebird. I need to move the system off an unstable machine. FBI does not allow me to simply replace the .FDB files, for reasons that have not been explained (error codes that make no sense). I experimented with using the system's automated nbackups to replace the information in an existing .FDB, and that works. *phew* The problem is that the system's Scheduler function, without any warning or errors, stopped making these nbackups last May. So I need to make an nbackup manually. So I tried... nbackup -U gone -P fishing -B 0 ../data/EXAMPLE.FDB ../data/ex.bak However, this results in the error, You must be either SYSDBA or owner of the database. The thing is, the user gone is the only one that appears in SHOW USER in isql, so how could it be the case that they are not the owner? Can one assign ownership to a non-existant user? Might it be assigned to SYSDBA? Moving on I tried the same command, but this time with SYSDBA and masterkey. This results in another error, Your user name and password are not defined. I assume this is because masterkey is no longer used, but the installer process does not ask for a password. The dox suggest that the password might be randomly assigned in this case, and then stored in a file, but I cannot find the file it refers to. Can anyone offer suggestions on how to solve this?
[firebird-support] index ignored with parameterised 'Like' search?
I know there are some nuances to ensuring an index is used with 'Like' searches. I would expect this search to use an appropriate index if one is available (hvalue_search has an ascending_index with selectivity recalculated). SELECT id, hname, hvalue, hvalue_search from entity_header where hvalue_search like 'GLX%' If I do a search in straight sql, the plan returns this: 'PLAN (ENTITY_HEADER INDEX (I_HEADER_HVALUESEARCH))' This is the plan used either using a tool like DBWorkbench, or using the python DB-API with the FDB driver. However, if I try to use a parameterised version of this query (using either the FDB driver or DB Workbench), then the index is ignored and the entire table is scanned. 'PLAN (ENTITY_HEADER NATURAL)' I assumed at first that this was a problem with the python driver or with the python db-api. So when I tested with DB Workbench I was surprised to see that it seems that firebird will do a full table scan if there is a parameterised query with like. Is this the normal behaviour? This is what I am using in DB Workbench to show that the introduction of a parameter causes the full table scan: SELECT id, hname, hvalue, hvalue_search from entity_header where hvalue_search like :1 I can't seem to run a paramterised query within the command line ISQL, so I can't determine if this behaviour is really a limitation of the tools I've been trying or of firebird itself.
[firebird-support] Confused about delta files
Sorry if this is a dup, Yahoo did something weird… Looking at my production system I see that I have an FDB file and an FDB.delta. The date of the FDB is May 2nd, 2013. The date of the delta is today. I believe that this is the reason I cannot directly connect to the FDB file on my new server? In any event, is there some way to fold these two files back together into a single FDB?
Re: [firebird-support] index ignored with parameterised 'Like' search?
Em 15/1/2014 10:13, jamesjlovel...@gmail.com escreveu: I know there are some nuances to ensuring an index is used with 'Like' searches. I would expect this search to use an appropriate index if one is available (hvalue_search has an ascending_index with selectivity recalculated). SELECT id, hname, hvalue, hvalue_search from entity_header where hvalue_search like 'GLX%' If I do a search in straight sql, the plan returns this: 'PLAN (ENTITY_HEADER INDEX (I_HEADER_HVALUESEARCH))' This is the plan used either using a tool like DBWorkbench, or using the python DB-API with the FDB driver. However, if I try to use a parameterised version of this query (using either the FDB driver or DB Workbench), then the index is ignored and the entire table is scanned. 'PLAN (ENTITY_HEADER NATURAL)' I assumed at first that this was a problem with the python driver or with the python db-api. So when I tested with DB Workbench I was surprised to see that it seems that firebird will do a full table scan if there is a parameterised query with like. Is this the normal behaviour? This is what I am using in DB Workbench to show that the introduction of a parameter causes the full table scan: SELECT id, hname, hvalue, hvalue_search from entity_header where hvalue_search like :1 I can't seem to run a paramterised query within the command line ISQL, so I can't determine if this behaviour is really a limitation of the tools I've been trying or of firebird itself. Yes, it's the normal behaviour... A parameterised query will be re-used with any value for the parameter, so the plan is choosen before the value of a given parameter will be set, since there is no way to predict if the parameter would be 'ABC%' or '%ABC%' or '_ABC' the optimizer chooses a plan that will solve all the possibilities, thereafter it can't use a plan that uses an index. You could use STARTING WITH, this way you will always use an index. see you !
Re: [firebird-support] Computed Index for Integer-Fields and STARTING WITH clause
Em 15/1/2014 10:37, Christian Kusenbach escreveu: Hi, I have a question about computed indices related to integer fields. In my program there are several selects on integer-fields with a STARTING WITH clause. Firebird internally converts the integer value to a string and then filters the value. It would be great if I could create a computed index on that (internal) string so FB does an index-lookup for the data. I tried to create an computed index on a table with the expression CAST(MY_INT_FIELD AS VARCHAR(11)) cause I think firebird internally uses a VARCHAR(11) or CHAR(11) value but that doesn't solve the problem. Any idea on how to get this working without changing every select in the program? Thanks and best regards! Christian I think you meant expression index instead of compound index The expression index will be used if the search criteria is the same as the expression index: your example: select * from MyTable where MyIntCollumn starting with '1' you created an expression index as Cast(MyIntColumn as varchar(11)) so, your query should be: select * from MyTable where Cast(MyIntCollumn as varchar(11)) starting with '1' So there is no way you could use the expression index without rewriting your query. I have no idea why you are doing this kind of select but it lookslike weird to me... And since you will need to rewrite the query, perhaps could be a good idea to write it in a better way (in the last case, use a mirror collumn updated by a after insert/update trigger with the int value formated as you wish to search for). see you !
[firebird-support] Table update performance dropped significantly within few days.
Hi guys. I have one table that currently has 100 000 000 records. PageSize is 16384. Index statistics for primary key are 0.00 (I've recomputed them). Over a few days inserting and updating this table slowed down significantly. When I try to update one row: UPDATE TABLE SET VALUE=X WHERE PK_COLUMN_ID =12335343 update executes in 0.250 miliseconds. What is surprising for me that just few days ago everything was fine.. how could I speed thing up? What could happen over these few days? Thanks in advance.
Re: [firebird-support] Table update performance dropped significantly within few days.
Increase database page buffers and try again. It happens when you combine a large table with multiple indexes. Dropping one or two indices can also increase speed a lot in this case. Em 15/01/2014 16:30, brucedickin...@wp.pl escreveu: Hi guys. I have one table that currently has 100 000 000 records. PageSize is 16384. Index statistics for primary key are 0.00 (I've recomputed them). Over a few days inserting and updating this table slowed down significantly. When I try to update one row: UPDATE TABLE SET VALUE=X WHERE PK_COLUMN_ID =12335343 update executes in 0.250 miliseconds. What is surprising for me that just few days ago everything was fine.. how could I speed thing up? What could happen over these few days? Thanks in advance.
Re: [firebird-support] Table update performance dropped significantly within few days.
Em 15/1/2014 16:30, brucedickin...@wp.pl escreveu: Hi guys. I have one table that currently has 100 000 000 records. PageSize is 16384. Index statistics for primary key are 0.00 (I've recomputed them). Over a few days inserting and updating this table slowed down significantly. When I try to update one row: UPDATE TABLE SET VALUE=X WHERE PK_COLUMN_ID =12335343 update executes in 0.250 miliseconds. What is surprising for me that just few days ago everything was fine.. how could I speed thing up? What could happen over these few days? Thanks in advance. It's hard to tell without more information. But the first thing I would look out is the transaction counters. Perhaps you have some open transaction that is preventing the garbage collection process and acumulating record versions. use GSTAT -h to get the header information of your database and post it back. see you !
Re: [firebird-support] Table update performance dropped significantly within few days.
2014/1/15 fabianoas...@gmail.com Increase database page buffers and try again. It happens when you combine a large table with multiple indexes. Dropping one or two indices can also increase speed a lot in this case. I think, 100 million records are not so much records, and If you use classic or superclassic, pagebuffers cannot be high. What firebird version? How many indices has that table? Do you update indexed fields? May be the problem is Firebird performance with large indices.
Re: [firebird-support] nbackup questions
Hi. I am not sure if i understood your question. You want to move the fdb file to another computer? Hugo Eyng Em 15/01/2014 12:52, Maury Markowitz escreveu: I am a admin of the Fishbowl Inventory (FBI) system for our company, which is a Java app on top of Firebird. I need to move the system off an unstable machine. FBI does not allow me to simply replace the .FDB files, for reasons that have not been explained (error codes that make no sense). I experimented with using the system's automated nbackups to replace the information in an existing .FDB, and that works. *phew* The problem is that the system's Scheduler function, without any warning or errors, stopped making these nbackups last May. So I need to make an nbackup manually. So I tried... nbackup -U gone -P fishing -B 0 ../data/EXAMPLE.FDB ../data/ex.bak However, this results in the error, You must be either SYSDBA or owner of the database. The thing is, the user gone is the only one that appears in SHOW USER in isql, so how could it be the case that they are not the owner? Can one assign ownership to a non-existant user? Might it be assigned to SYSDBA? Moving on I tried the same command, but this time with SYSDBA and masterkey. This results in another error, Your user name and password are not defined. I assume this is because masterkey is no longer used, but the installer process does not ask for a password. The dox suggest that the password might be randomly assigned in this case, and then stored in a file, but I cannot find the file it refers to. Can anyone offer suggestions on how to solve this? -- Atenciosamente, Hugo Eyng
[firebird-support] RE: Table update performance dropped significantly within few days.
Thanks a lot, I will post that info tommorow. Regards.
[firebird-support] Is there a way to create Stored Procedure which uses UDF when there is no DLL with implementation?
Hello,
take a look at this example UDF declaration:
DECLARE EXTERNAL FUNCTION F_BLOBLOAD
CSTRING(8191),
BLOB
RETURNS PARAMETER 2
ENTRY_POINT 'blob_load' MODULE_NAME 'FAUfile';
I can compile and save this function even when there is NO FAUfile.dll
physically on the disk. I will get error only when I try to use this function,
and that is fine.
Now, take a look at this code:
SET TERM ^ ;
CREATE PROCEDURE TEST (BINARY_VALUE BLOB SUB_TYPE 0)
AS
BEGIN
SELECT
F_BLOBLOAD('c:\blabla')
FROM
RDB$DATABASE
INTO
:BINARY_VALUE;
END^
SET TERM ; ^
My stored procedure is using the UDF function. I can compile this SP without an
error, but I can not save it to database. I am getting error:
Engine Code: 335544343
Engine Message :
invalid request BLR at offset 50
function F_BLOBLOAD is not defined
module name or entrypoint could not be found
My question is, can I somehow store this SP in the database even if there is no
DLL present at the time of saving SP? Can I somehow cheat the FB to think that
the DLL is present, really any solution would be fine for me...
I find current behaviour inconsistent, either Firebird should prevent me from
creating UDF when there is no dll present or it should allow me to create SP
which is using UDF.
Thanks for any advices, I really need to be able to create SP with UDF in
conditions where there is not yet dll created for that UDF.
Re: [firebird-support] Table update performance dropped significantly within few days.
I will take a lok at this option, thank you.
Re: [firebird-support] Table update performance dropped significantly within few days.
What firebird version? Firebird 2.5.1 Classic, on Linux. How many indices has that table? 22 :( Do you update indexed fields? Partially May be the problem is Firebird performance with large indices. But you know, few days ago performance was good. I had not changed anything since 2 months in that database.
Re: [firebird-support] nbackup questions
On 2014-01-15, at 2:02 PM, Hugo Eyng wrote: I am not sure if i understood your question. You want to move the fdb file to another computer? That is correct. For reasons that I do not understand, the simple method of simply copying the FDB from one machine to another does not work. When I try to attach to that database, it reports file not found, which I assume is a spurious error code. But there already is a small (example) database file on that machine that I *can* attach to. I can also restore into it. So if I can get nbackup to work, that provides a way to move the data over.
Re: [firebird-support] Table update performance dropped significantly within few days.
We had this same issue. We have a large table with much indices and some day all goes slow. Reducing index quantity helped a lot. We was using 90 pages of cache in Classic. We increased this number to 150, shutdown all conections and thats it! Problem solved. Latter we increased this number to 2048 as Superserver default. Our big client has 140 active simultaneous connections and no problem. Of course we calculate the necessarie ram. Em 15/01/2014 17:15, brucedickin...@wp.pl escreveu: I will take a lok at this option, thank you.
Re: [firebird-support] Table update performance dropped significantly within few days.
Finally this isn't a Firebird bug is only a configuration issue. Let me know if this solve your problem because you will need one more parameter ;) Em 15/01/2014 17:36, fabianoas...@gmail.com escreveu: We had this same issue. We have a large table with much indices and some day all goes slow. Reducing index quantity helped a lot. We was using 90 pages of cache in Classic. We increased this number to 150, shutdown all conections and thats it! Problem solved. Latter we increased this number to 2048 as Superserver default. Our big client has 140 active simultaneous connections and no problem. Of course we calculate the necessarie ram. Em 15/01/2014 17:15, brucedickin...@wp.pl escreveu: I will take a lok at this option, thank you.
[firebird-support] Is there a way to create Stored Procedure which uses UDF function from a dll that is not yet physically on the disk?
Hi guys.
I've already posted this question but it has not shown up on the group, so I am
sorry if it appears duplicated later.
My case is probably hopeless but I thought I'd ask anyway.
As you know, it is possbile to declare UDF without dll present in the required
path. For example:
DECLARE EXTERNAL FUNCTION F_BLOBLOAD
CSTRING(8191),
BLOB
RETURNS PARAMETER 2
ENTRY_POINT 'blob_load' MODULE_NAME 'FAUfile';
This statement compiles, and everything is ok when I commit transaction even
when there is no FAUfile.dll present on the hard drive. This is a good
behaviour, it is like a late binding in programming, you got error only if you
try to use function which is not in a dll or dll is not present.
However, the same rule does not work when I try to declare stored procedure
which is using UDF:
SET TERM ^ ;
CREATE PROCEDURE TEST (BINARY_VALUE BLOB SUB_TYPE 0)
AS
BEGIN
SELECT
F_BLOBLOAD('c:\blabla')
FROM
RDB$DATABASE
INTO
:BINARY_VALUE;
END^
SET TERM ; ^
This statement compiles, but when I try to commit transaction I am getting
error:
Engine Code: 335544343
Engine Message :
invalid request BLR at offset 50
function F_BLOBLOAD is not defined
module name or entrypoint could not be found
IMHO it is an inconsistent behaviour. Either I should get error when I am
commiting declaration of UDF at the first place, or I should not get error when
I am creating a SP which is using UDF.
My question is: can I some how commit my stored procedure in case like I've
presented? I would like to get the error only when I execute my SP and
FAUfile.dll for UDF is not present.
Maybe I could somehow insert manually the SP to the system tables? I know it is
unsafe but I really need to be able to create SP under the conditions that dll
with udf is not yet present.
Thanks for any advices,
Regards.
Re: [firebird-support] nbackup questions
Em 15/1/2014 17:30, Maury Markowitz escreveu: On 2014-01-15, at 2:02 PM, Hugo Eyng wrote: I am not sure if i understood your question. You want to move the fdb file to another computer? That is correct. For reasons that I do not understand, the simple method of simply copying the FDB from one machine to another does not work. When I try to attach to that database, it reports file not found, which I assume is a spurious error code. But there already is a small (example) database file on that machine that I *can* attach to. I can also restore into it. So if I can get nbackup to work, that provides a way to move the data over. I think it's related to not finding the delta file you mentioned on another post. For some reason the delta file is not merging back to the main file. Take a look on nbackup (http://www.firebirdsql.org/manual/nbackup.html) manual so you could try to merge it back manually. see you !
Re: [firebird-support] Table update performance dropped significantly within few days.
Em 15/1/2014 17:36, fabianoas...@gmail.com escreveu: We had this same issue. We have a large table with much indices and some day all goes slow. Reducing index quantity helped a lot. We was using 90 pages of cache in Classic. We increased this number to 150, shutdown all conections and thats it! Problem solved. Latter we increased this number to 2048 as Superserver default. Our big client has 140 active simultaneous connections and no problem. Of course we calculate the necessarie ram. Maybe the problem was solved when you detached all connections because it closes the an old open transaction :) see you !
[firebird-support] RE: Table update performance dropped significantly within few days.
Regarding the number of pages. Correct me if I am wrong (http://www.firebirdsql.org/manual/gfix-buffers.html) but if Superserver is using 2048 pages and page size is 16384, than computer must have 32 GB RAM installed. What is happening when there is less RAM installed?
Re: [firebird-support] nbackup questions
On 2014-01-15, at 2:43 PM, Alexandre Benson Smith wrote: Take a look on nbackup (http://www.firebirdsql.org/manual/nbackup.html) manual so you could try to merge it back manually. Indeed, but that brings me back to the actual problem... I do not know the password for SYSDBA, and the only registered user does not have enough privs to run nbackup. So, how do I retrieve the SYSDBA pass?
Re: [firebird-support] RE: Table update performance dropped significantly within few days.
Em 15/1/2014 17:52, brucedickin...@wp.pl escreveu: Regarding the number of pages. Correct me if I am wrong (http://www.firebirdsql.org/manual/gfix-buffers.html) but if Superserver is using 2048 pages and page size is 16384, than computer must have 32 GB RAM installed. What is happening when there is less RAM installed? 32 MB ! :) Page size is defined in bytes
Re: [firebird-support] nbackup questions
On 2014-01-15, at 2:43 PM, Alexandre Benson Smith wrote: Take a look on nbackup (http://www.firebirdsql.org/manual/nbackup.html) manual so you could try to merge it back manually. But wait... what about... ALTER DATABASE END BACKUP If I am reading it correctly, this appears to do the same thing as nbackup -N. Is that correct?
Re: [firebird-support] Table update performance dropped significantly within few days.
No. Only when increased cache. Shutdown all conections because when they reconnect will use the new cache limit. Em 15/01/2014 17:45, Alexandre Benson Smith ibl...@thorsoftware.com.br escreveu: Em 15/1/2014 17:36, fabianoas...@gmail.com escreveu: We had this same issue. We have a large table with much indices and some day all goes slow. Reducing index quantity helped a lot. We was using 90 pages of cache in Classic. We increased this number to 150, shutdown all conections and thats it! Problem solved. Latter we increased this number to 2048 as Superserver default. Our big client has 140 active simultaneous connections and no problem. Of course we calculate the necessarie ram. Maybe the problem was solved when you detached all connections because it closes the an old open transaction :) see you !
RE: [firebird-support] Table update performance dropped significantly within few days.
No. Only when increased cache. Shutdown all conections because when they reconnect will use the new cache limit. SL But this shutdown had the effect of closing any long running transactions, which is the most common cause of performance degradation because it inhibits garbage collection -- which was Alexandre's point.
Re: [firebird-support] Table update performance dropped significantly within few days.
Em 15/1/2014 18:01, fabianoas...@gmail.com escreveu: No. Only when increased cache. Shutdown all conections because when they reconnect will use the new cache limit. I have my doubts... I still think it's more related to a long running transaction then to the increase on the page buffers... see you !
RE: [firebird-support] RE: Table update performance dropped significantly within few days.
32 MB. The page size is in bytes. If you did not have enough memory, the server will refuse to open the connection (needing the extra memory) if I remember well. De : brucedickin...@wp.pl [mailto:brucedickin...@wp.pl] Envoyé : 15 janvier 2014 14:53 À : firebird-support@yahoogroups.com Objet : [firebird-support] RE: Table update performance dropped significantly within few days. Regarding the number of pages. Correct me if I am wrong (http://www.firebirdsql.org/manual/gfix-buffers.html) but if Superserver is using 2048 pages and page size is 16384, than computer must have 32 GB RAM installed. What is happening when there is less RAM installed?
RE: [firebird-support] RE: Table update performance dropped significantly within few days.
We have even restarted the computer and even a backup and restore. So no way we can have old record versions and the database sill slow. The slow down is due to hard disk usage. I tought when are much records and much indices when a record is updated there is no room for recalculate the indexes. When there is no room more pages must be fetched from disk to ram to recompute the indexes. It increases hdd usage and dramatically slow down. This happens from one time to other with no changes in system or database. When you increase database caches Firebird haves enought room to recompute all indexes with a minimum hdd usage and so very fast, very! As I sayd other solutions are reducing record numbers or deleting indexes. Em 15/01/2014 18:23, Evelyne Girard egir...@softinfo.ca escreveu: 32 MB. The page size is in bytes. If you did not have enough memory, the server will refuse to open the connection (needing the extra memory) if I remember well. *De :* brucedickin...@wp.pl [mailto:brucedickin...@wp.pl] *Envoyé :* 15 janvier 2014 14:53 *À :* firebird-support@yahoogroups.com *Objet :* [firebird-support] RE: Table update performance dropped significantly within few days. Regarding the number of pages. Correct me if I am wrong ( http://www.firebirdsql.org/manual/gfix-buffers.html) but if Superserver is using 2048 pages and page size is 16384, than computer must have 32 GB RAM installed. What is happening when there is less RAM installed?
Re: [firebird-support] nbackup questions
Maury Markowitz wrote: But wait... what about... ALTER DATABASE END BACKUP If I am reading it correctly, this appears to do the same thing as nbackup -N. Is that correct? That is correct, but in order to issue that command you must connect to the database as SYSDBA or owner. From your earlier postings, I gather that in May 2013 nbackup failed to complete, so the main database file has been frozen for 8 months, and all the changes since May 2013 have accumulated in the delta. User GONE doesn't seem to be the owner, and you don't know the SYSDBA password. The fact that it isn't masterkey makes sense: either the installer generated a random password, or the password was left at masterkey. In the latter case, any database administrator in his right mind would immediately change it to something else. Why don't you contact the administrator of the system in question? After all, *someone* should know the SYSDBA password, shouldn't they? And how about the scripts that executed nbackup until they went broken last May? Do you have read access to them? What authentication method did they use? And if you can't access those scripts, who can? Kind regards, Paul Vinkenoog
[firebird-support] RE: Garbage collection / sweep not happening on super classic
Ok, running the gfix -sweep with everyone logged out did work and only took about 5 minutes. I believe the issue came from me only trying to run the sweep one time and a transaction being stuck. I never tried to run sweep AFTER restarting the services. I also think there was confusion because I was under the incorrect assumption that gbak was doing a sweep while backing up. I really appreciate everyone's responses that help me get to the root of the problem. Thanks! Ryan
[firebird-support] RE: nbackup questions
From your earlier postings, I gather that in May 2013 nbackup failed to complete, so the main database file has been frozen for 8 months, and all the changes since May 2013 have accumulated in the delta. This is too strong statement. To verify it we need to know physical backup state of the database. gstat -h will show it (and it not requires admin privileges). Regards, Vlad
RE: [firebird-support] RE: nbackup questions
From your earlier postings, I gather that in May 2013 nbackup failed to complete, so the main database file has been frozen for 8 months, and all the changes since May 2013 have accumulated in the delta. This is too strong statement. SL Agreed. In fact, AFAIR, while the main database file would be frozen, the delta file should only contain the latest/un-garbagecollected row versions. SL Garbage collections should still be running/following the standard rules (i.e. long running transactions). The only effect should be that the new rows/pages would be written to the delta file.
Re: [firebird-support] RE: nbackup questions
Vlad wrote: From your earlier postings, I gather that in May 2013 nbackup failed to complete, so the main database file has been frozen for 8 months, and all the changes since May 2013 have accumulated in the delta. This is too strong statement. To verify it we need to know physical backup state of the database. gstat -h will show it (and it not requires admin privileges). I based my assumption on what Maury wrote in the earlier thread Confused about delta files: The date of the FDB is May 2nd, 2013. The date of the delta is today. ...combined with his later statement that the system's Scheduler function, without any warning or errors, stopped making these nbackups last May. Cheers, Paul Vinkenoog
Re: [firebird-support] nbackup questions
On 2014-01-15, at 4:29 PM, Paul Vinkenoog wrote: Why don't you contact the administrator of the system in question? After all, *someone* should know the SYSDBA password, shouldn't they? I don't believe so. The installer process that created the DB, which comes from Fishbowl, not Firebird, never asked for a SYSDBA password, and did not report one. Some of the dox suggest the Firebird installer will leave behind a file saying what the password was randomly set to. No file of this type appears to be on my system.
Re: [firebird-support] RE: nbackup questions
On 2014-01-15, at 6:31 PM, Paul Vinkenoog wrote: The date of the FDB is May 2nd, 2013. The date of the delta is today. ...combined with his later statement that the system's Scheduler function, without any warning or errors, stopped making these nbackups last May. Indeed. Perhaps file sizes may be of interest. The original FDB is ~68MB, and holds data for about three years. The FDB.delta is 70MB and holds data for 8 months. I suspect the implication here is that we've touched so many rows of the original db that practically everything had to be duplicated, and that we've added about 2MB of new material since then.
Re: [firebird-support] nbackup questions
On 2014-01-15, at 5:40 PM, hv...@users.sourceforge.net
hv...@users.sourceforge.net wrote:
This is too strong statement. To verify it we need to know physical backup
state of the database. gstat -h will show it (and it not requires admin
privileges).
I am able to run this on the original server, are there some additional
switches you would like? Here is what I got with -h, and I see that force
write, backup lock at the bottom, which I suspect is interesting...
Database ../data/company.fdb
Database header page information:
Flags 0
Checksum12345
Generation 753393
Page size 4096
ODS version 11.2
Oldest transaction 734639
Oldest active 734640
Oldest snapshot 734640
Next transaction734721
Bumped transaction 1
Sequence number 0
Next attachment ID 18032
Implementation ID 26
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date Sep 1, 2012 10:22:08
Attributes force write, backup lock
Variable header data:
Database backup GUID: {407742FD-11DE-43CD-60B7-CBCCC1AA866E}
Sweep interval: 2
*END*
Re: [firebird-support] nbackup questions
Hello Maury, Why don't you contact the administrator of the system in question? After all, *someone* should know the SYSDBA password, shouldn't they? I don't believe so. The installer process that created the DB, which comes from Fishbowl, not Firebird, never asked for a SYSDBA password, and did not report one. So Fishbowl installed the Firebird server and kept the SYSDBA password to itself. That makes (some) sense. The only user you know the password of doesn't seem to have access to the database. This suggests that either SYSDBA or another Firebird user account (created transparently by Fishbowl, with name and password kept internal) is the owner. I think you should contact Fishbowl Inventory support to clear this up. Some of the dox suggest the Firebird installer will leave behind a file saying what the password was randomly set to. No file of this type appears to be on my system. Yes, some installers on Posix systems do this. If this is the case, the generated password is in the file SYSDBA.password in the Firebird installation directory. Most keep it at masterkey though. In your case, I assume that the Fishbowl software has generated a secure SYSDBA password. Kind regards, Paul Vinkenoog
Re: [firebird-support] nbackup questions
On 2014-01-15, at 8:23 PM, Paul Vinkenoog wrote: I think you should contact Fishbowl Inventory support to clear this up. Indeed; they currently have a multi-hour phone wait time. Most keep it at masterkey though. In your case, I assume that the Fishbowl software has generated a secure SYSDBA password. Nice of them to record it...
Re: [firebird-support] nbackup questions
There are some things you should observe: 1 - Use the same version of FB server in the computer you move the 'fdb' to that you use in the 'source' computer; 2 - Before copying the 'fdb' stop the FB server service; Another thing I would tell you is that you should use 'gbak' to backup your source 'fdb' and then, in the destination computer, use 'gbak' to restore and recreate the 'fdb'. Em 15/01/2014 17:30, Maury Markowitz escreveu: On 2014-01-15, at 2:02 PM, Hugo Eyng wrote: I am not sure if i understood your question. You want to move the fdb file to another computer? That is correct. For reasons that I do not understand, the simple method of simply copying the FDB from one machine to another does not work. When I try to attach to that database, it reports file not found, which I assume is a spurious error code. But there already is a small (example) database file on that machine that I *can* attach to. I can also restore into it. So if I can get nbackup to work, that provides a way to move the data over. -- Atenciosamente, Hugo Eyng
Re: [firebird-support] nbackup questions
On 2014-01-15, at 8:23 PM, Paul Vinkenoog wrote: I think you should contact Fishbowl Inventory support to clear this up. So a little Google-fu was all that took. Now armed with the credentials, what would be the next steps at this point? I suspect the next step is to END BACKUP. However, I wish to tread carefully... I don't have a backup newer than last May, the existing FDB is not a usable file as far as the new machine is concerned, etc. So, what exactly happens when you do the END BACKUP? Does the server have to fold the delta into the original FDB? If so, I suspect this is a long-running task given the size of the file? Or is this a much simpler task, simply renaming the files or such? If I first copy the original FDB and delta, if something goes wrong during END BACKUP will those files be useful to me? It does not appear the original FDB is a working database (at least I cannot connect to it), and I suspect the name delta is accurate so it is not a usable file by itself either. This seems to suggest (I know I know, ASSuME) that simply copying these files to offline storage gets me basically nothing?
Re: [firebird-support] nbackup questions
Hello Maury, So a little Google-fu was all that took. Now armed with the credentials, what would be the next steps at this point? I suspect the next step is to END BACKUP. However, I wish to tread carefully... I don't have a backup newer than last May, the existing FDB is not a usable file as far as the new machine is concerned, etc. So, what exactly happens when you do the END BACKUP? Does the server have to fold the delta into the original FDB? If so, I suspect this is a long-running task given the size of the file? Or is this a much simpler task, simply renaming the files or such? If I first copy the original FDB and delta, if something goes wrong during END BACKUP will those files be useful to me? It does not appear the original FDB is a working database (at least I cannot connect to it), and I suspect the name delta is accurate so it is not a usable file by itself either. This seems to suggest (I know I know, ASSuME) that simply copying these files to offline storage gets me basically nothing? If you connect to the database and issue ALTER DATABASE END BACKUP: - The delta (containing the changes since May last year) will be re-integrated with the main database file (.fdb). - Upon success, the delta will be deleted and the state flag of the database file set back to normal. Then, you should make a regular backup with gbak and restore that on your target machine (using gbak on your target machine). I have no idea how much time re-integrating a 70 MB delta will take. Is it a problem if it takes several hours? (Not that I expect this.) As for copying the current .fdb and .delta to your target machine: - Copying the .fdb is safe - IN THIS CASE! - because the file is frozen and closed. (Under normal circumstances, copying an .fdb file is unsafe, unless you take special precautions.) - Copying the .fdb.delta may be unsafe because its a live database file. Even if no users are connected to it, the 'hidden' user (or SYSDBA) may be connected from within Fishbowl. Can you stop Fishbowl completely? And then check if no process has the .delta file open. Then it may be safe (and even wise) to copy the two files to your target machine, just in case. (Note: if the source and target machine have a different platform architecture, straight file copies may not work on the target machine. If they have different endianness, straight file copies are *guaranteed* not to work. Cheers, Paul Vinkenoog
[firebird-support] Unknown ISC Error 0
Hi Using Firebird 2.5.2 32 bit SuperServer. Happens on Win XP, WIN7 64 BIT oS'S Occasionally on some sites I am getting a dialog Box with ISCErrorCode 335544648 Unknown ISC Error 0 According to the error code list the 335544648 code means 335544648 conn_lost Connection lost to pipe server. Not sure what this means. I have terminated the application using Firebird. Stopped and Started the Firebird Service. Backed up and restored the database with no errors. Restart the application and the error occurs immediately. The only way I have been able to clear the error is to reboot the PC. Is there another way to clear the conditions causing this error? Cheers Paul
[firebird-support] RE: Table update performance dropped significantly within few days.
Hi guys.
Yesterday I was trying to figure out what is the cause of my problem. And I've
noticed that two months ago my colleague added one trigger on this table. The
trigger is:
SET TERM ^ ;
ALTER TRIGGER CLIENT_LM INACTIVE
AFTER INSERT OR UPDATE POSITION 1
AS
BEGIN
UPDATE OR INSERT INTO LAST_MODIFICATION (TABLE_NAME, RECORD_TIME)
VALUES ('CLIENT', NEW.RECORD_TIME)
MATCHING (TABLE_NAME);
END^
SET TERM ; ^
So, after each insert or update this trigger was saving last modification time
to a different table. On each table in the system such trigger exists. After
dissabling the trigger, performance get back to normal.
Results for gstat are strange:
Database header page information:
Flags 0
Checksum12345
Generation 228629442
Page size 16384
ODS version 11.2
Oldest transaction 219409373
Oldest active 219409374
Oldest snapshot 219409374
Next transaction227560937
Bumped transaction 1
Sequence number 0
Next attachment ID 1068492
Implementation ID 24
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date Aug 9, 2013 23:34:51
Attributes force write
Variable header data:
Sweep interval: 2
*END*
I see the big difference between oldest transaction and the current
transaction. Is it possible that this trigger is caused this? BTW my colleague
has reported to me that he had a number of deadlocks becuase of this mentioned
trigger.
---In firebird-support@yahoogroups.com, iblist@... wrote:
Em 15/1/2014 16:30, brucedickinson@... mailto:brucedickinson@... escreveu:
Hi guys.
I have one table that currently has 100 000 000 records. PageSize is 16384.
Index statistics for primary key are 0.00 (I've recomputed them).
Over a few days inserting and updating this table slowed down significantly.
When I try to update one row:
UPDATE TABLE SET VALUE=X WHERE PK_COLUMN_ID =12335343
update executes in 0.250 miliseconds.
What is surprising for me that just few days ago everything was fine.. how
could I speed thing up? What could happen over these few days?
Thanks in advance.
It's hard to tell without more information.
But the first thing I would look out is the transaction counters.
Perhaps you have some open transaction that is preventing the garbage
collection process and acumulating record versions.
use GSTAT -h to get the header information of your database and post it back.
see you !
[firebird-support] RE: Table update performance dropped significantly within few days.
One thing I do not understand. If the trigger has been installend over two months ago, why performance degraded just few days ago and not immediatelly after the trigger has been activated?
Re: [firebird-support] Unknown ISC Error 0
At 06:28 p.m. 16/01/2014, Cam wrote: Hi Using Firebird 2.5.2 32 bit SuperServer. Happens on Win XP, WIN7 64 BIT oS'S Occasionally on some sites I am getting a dialog Box with ISCErrorCode 335544648 Unknown ISC Error 0 According to the error code list the 335544648 code means 335544648 conn_lost Connection lost to pipe server. Not sure what this means. I have terminated the application using Firebird. Stopped and Started the Firebird Service. Backed up and restored the database with no errors. Restart the application and the error occurs immediately. The only way I have been able to clear the error is to reboot the PC. It appears your clients are using Named Pipes protocol (WNET) to connect to the server. The number of simultaneous WNET connections will be limited by the Windows licence. It's not just database connections that are counted: so are network and shared printers, file shares, NAS directories, etc. If the network is being stretched to the limit, the NMS will disconnect any quiet connections to let the next connection request through. Watch the Firebird log when the app gets a 335544648: there might be a corresponding WNET error there. But WNET is a noisy protocol, prone to Connection Lost and Connection Reset errors with no cause other than poor error recovery. Is there another way to clear the conditions causing this error? Better to *avoid* the error. Use TCP/IP as the connection protocol for your database clients. It isn't affected by the Windows licence limitations. It's less noisy, as well. All that said, it would make sense to check the network hardware - cards, connectors, cables, routers, including wifi if it's in use. Helen Borrie, Support Consultant, IBPhoenix (Pacific) Author of The Firebird Book and The Firebird Book Second Edition http://www.firebird-books.net __
Re: [firebird-support] RE: Table update performance dropped significantly within few days.
At 07:15 p.m. 16/01/2014, brucedickin...@wp.pl wrote: One thing I do not understand. If the trigger has been installend over two months ago, why performance degraded just few days ago and not immediatelly after the trigger has been activated? You might be shooting at rainbows. Your colleague altered the trigger and made it INACTIVE: ALTER TRIGGER CLIENT_LM INACTIVE AFTER INSERT OR UPDATE POSITION 1 Do you know if or when the trigger was ACTIVATED? It's very hard to see how deactivating a hungry trigger like that one could *degrade* performance. Helen Borrie, Support Consultant, IBPhoenix (Pacific) Author of The Firebird Book and The Firebird Book Second Edition http://www.firebird-books.net __
