[firebird-support] Re: Block size exceeds implementation restriction
I observed something similar many years ago, though I don't remember the exact error message. I think I solved it by simply using shorter aliases and removing redundant spaces and words (e.g. I would change from from mv$pdv1 pedidovend0_ left outer join crt1 carteira1_ on pedidovend0_.numcrt1=carteira1_.numcrt1 to from mv$pdv1 p0 left join crt1 c1 on p0.numcrt1=c1.numcrt1 ) The reason for my problem in the first place was me trying to flatten a result set in Firebird rather than some statistical package (16 left joins or so since there could be up to 16 rows per person, if each of these 16 rows could have up to four records in another table, then you end up with 64 potential fields for each 'real' field - needless to say, the vast majority of the result fields would be ). I think the max length of the SQL statement might have been 16 or 32Kb and without removing your spaces, your query is 18Kb. Though I don't know whether this was a Firebird restriction or had to do with the tool that I used. One thing is the length of the query itself, another thing is the row size of your result set (here the max size at least was 64Kb, don't know whether it has increased or not). Normally, this can be avoided by defining your fields as BLOB rather than long (VAR)CHAR fields. I'd also like to point out that it is normally better to have all your (inner) JOINs before any LEFT JOINs, if possible. The reason is that Firebirds optimizer freely rearranges all tables until it encounters a LEFT JOIN, but after that, the order is fixed (at least on Firebird 1.5 and probably 2.1, don't know about Firebird 2.5). So, in your case, Firebird cannot choose the order of the tables in the PLAN at all, if you'd put 'inner join cad1 conta2_' above 'left outer join crt1 carteira1_', then the optimizer could have chosen whether it should have conta2_ or pedidovend0_ as the first table in the plan. However, in your case it is very unlikely that you get any better plan by following my advice, your [LEFT] JOIN and WHERE clauses indicate that there's only one sensible plan that could be created (provided of course, that pedidovend0_.nummv$pdv1 and the fields to the right in you JOIN fields are indexed). HTH, Set
[firebird-support] Advice needed : Using Firebird in a Large Client Server Env
Hi all, Usually, I am using firebird in a small client server environment ( 1 server and up to 5 clients). For Server, I use a desktop pc. It runs ok, though there was performance decrease when reports generated. Database size relatively small, under 300 MB after 3 years. Now, I have a need to build firebird for use by around 60-70 clients. May be someone here could share their experiences about firebird performance on this kind of environment, hardware to use, OS to choose (windows server or linux server), etc. Also, is there a database benchmark tools for firebird? Thanks in advance. Best regards, Incendio.
[firebird-support] Re: FB 2.5 takes too much RAM
Hi Sean, I have just installed FB 2.5.1, but the problem still exists. Haven't the bug been fixed in FB 2.5.1? Regards Guido --- In firebird-support@yahoogroups.com, "Leyne, Sean" wrote: > > Guido, > > > It's exactly the same database. With FB 2.1.4 all works fine and with FB > > 2.5 I > > get the error. > > > > I'm new to FB 2.5 and help is appreciated. > > This error has been confirmed with v2.5 and a fix will be available in the > v2.5.1 update which is currently undergoing pre-packaging release testing. > > If you need an immediate solution, you might consider downloading the latest > v2.5 daily snapshot which would contain the fix. > > > Sean >
Re: [firebird-support] Re: Transactions and cleaning up of rows after a massive deletion
On Wed, Oct 26, 2011 at 2:47 PM, Aage Johansen wrote: > Thomas wrote: > << > ... > 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. > ... > >> > > Will updating (mark with a flag) create record versions (and > stubs)? Marking the record with a flag will create a version. Deleting that version will create a stub. > These will need to be taken care of later, so maybe not much > time will be saved (relative to GC). Maybe just moved to another > point in time. > Yup. Or, in the worst case, creating the problem when it occurs and then the same sized problem later. > A "select count(*)" can be run with the same WHERE clause as in the > update (if at all practicable). Unless the "where" is too > complicated it might save some time compared to a full GC. > It could be a good thing for the updating program itself to do the > select (after committing). Only if it's sure that it's the oldest transaction in the system. > Get rid of the garbage - don't leave it > as a surprise for an unsuspecting user at a later time. YMMV, no > solution is perfect in every situation. > So sadly true. 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
Thomas wrote: << ... 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. ... >> Will updating (mark with a flag) create record versions (and stubs)? These will need to be taken care of later, so maybe not much time will be saved (relative to GC). Maybe just moved to another point in time. A "select count(*)" can be run with the same WHERE clause as in the update (if at all practicable). Unless the "where" is too complicated it might save some time compared to a full GC. It could be a good thing for the updating program itself to do the select (after committing). Get rid of the garbage - don't leave it as a surprise for an unsuspecting user at a later time. YMMV, no solution is perfect in every situation. -- Aage J.
Re: [firebird-support] Error CREATE VIEW (code = -607)
On 10/26/2011 9:34 AM, eric_developer wrote: > > Hi, > > how to solve? simple example: > > RECREATE VIEW TMP_REGC100 AS > SELECT CODFOR > FROM TMP_NFENT > GROUP BY 1 > UNION all > SELECT CODEMP > FROM TMP_NFSAI > > --- > Dynamic SQL Error > SQL error code = -607 > Invalid command > must specify column name for view select expression > --- > > i tried no success: > AS SELECT * FROM ( SELECT ... GROUP ... UNION ... SELECT ... ) > > > > What version of FB? And what type is CODFOR and CODEMP? I ran it in FB 2.5.1 on two tables with an integer ID field and it works. -- Thanks, Gordon [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Firebird 2.5 crashing after calling UDF
On 26.10.2011 02:49, sir_wally_lewis wrote: > 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. If it's not too much code, I could check them if you want. Frank -- "Fascinating creatures, phoenixes, they can carry immensely heavy loads, their tears have healing powers and they make highly faithful pets." - J.K. Rowling
Re: [firebird-support] Basic questions regarding gbak restore
Morning Jocob, > 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 This is good! Beware that at version 2 and previous (I've not yet tested this on 2.5) the -force option acts exactly as per the -attach option. However, unless you see an error message, the database will indeed be closed down. (See www.firebirdsql.org/pdfmanual/Firebird-gfix.pdf) Anyone attempting to connect to the database will be prevented from doing so and will see the following error: Statement failed, SQLCODE = -901 database shutdown > Here is the restore command: > > gbak -user sysdba -password masterkey -r o c:\backups\MYDB1.fbk MYDB No no no! ;-) You are asking for trouble here. The manual for gbak explains this in the section you are quoting (below): Restoring a database is the process by which you take the existing file and delete prior to replacing it on disc with a backup copy. Gbak does this when you specify the -r[ecreate_database] o[verwrite] switch or the -rep[lace_database] switch. > 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? Only if the gfix command worked without error. But surely you would test a connection after shutting it down, just to be doubly sure? > 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??? Not while it is shut down, the very paragraph you quote has the details "you might corrupt the database especially if the database is in use and has not been shut down using gfix." The (implied) advice is that you should always restore the dump file to a brand new database file. That way, if anyone does try and attach to the (original) database, and it hasn't been shut with gfix, they won't corrupt the recovering database. You should, as advised in the manual, never ever restore a database over the original in the manner you indicate above. The first thing a restore does is clear the contents of the file(s) being restored to, if they exist. This means that your database will be "empty" just after the start of the restore. In the event of a problem with the backup file, you now are in a situation where you have no original database and no workable backup. Not a good position to be in! So, always restore to a copy database and test that it is fine before allowing the users back in - either by editing the aliases.conf file to point at the new database, or, by moving the (closed) original database away somewhere, closing the copy, and moving it to the original location and filename(s) - no editing of aliases.conf is required. HTH Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
