Re: [firebird-support] Re: Handling large imports while system is in production
rudibrazil78 schrieb am 06.02.2013 um 14:31 (-): calls table have 130 fields, one PK, 60 FK's, about 20 extra indexes. clientes table have 186 fields, one PK, 45 FK's, about 20 extra indexes. More than 60 indexes on one table … okay! :) For testing sake, I droped all indexes and fks on both big tables and things got much faster. I might consider this as a last resort solution tho. One Java consultant here in Germany said in a training I attended that in big financial systems FKs are usually turned off because otherwise they wouldn't be able to handle their large import batches. I could hardly believe it. I don't have any experience myself to corroborate that statement. Michael PS and not specific to this topic: I don't know what »User-Agent: eGroups-EW/0.82« is but it doesn't appear to honor Internet Mail Message IDs, omitting the »References« header, thus breaking threads, which negatively affects list readability. Threading: Message-ID, References, In-Reply-To http://cr.yp.to/immhf/thread.html
Re: [firebird-support] Re: Handling large imports while system is in production
Michael Ludwig schrieb am 06.02.2013 um 21:24 (+0100): PS and not specific to this topic: I don't know what »User-Agent: eGroups-EW/0.82« is but it doesn't appear to honor Internet Mail Message IDs, omitting the »References« header, thus breaking threads, which negatively affects list readability. Looks like that's the Yahoo web interface. Just checked my mail folder, the web interface used to work okay, even with the version string quoted above. So probably just a bug that'll hopefully get fixed soon.
Re: [firebird-support] Is it possible to do this with FK Constraints?
Chuck Belanger schrieb am 08.12.2012 um 13:09 (-0800): During a DB update that I apply to my users' desktop application, I need to Drop all FK constraints then add them back when all the updates of the various tables are complete. I have been issuing explicit DSQL statements to do this, but would like to go through the RDB$ tables and create the DROP statements semi-automatically. So, when I DROP a constraint does its presence in the system tables cease to exist? If so, I was thinking of storing the FK constraints into a table before DROPping them then use that table to ADD the constraints back. Sounds like three hacks in a row to me, but maybe there's no way around what you're proposing to do here. I don't have a good answer, but see here (if still interested): How can I temporarily disable all constraints in a Table in Firebird 2.1? http://stackoverflow.com/a/2757433/269126 Michael
Re: [firebird-support] Re: Changing Charset only Pumping ?
patrick_marten schrieb am 16.11.2012 um 14:59 (-): Thus, you may need to increase your character fields size. I mean z is a character and #1081; is a character. They just require different size when being stored. You weren't talking about literal #1081;, were you? Because that is a numeric character reference in XML (Unicode char 0x0439: й), but it is, of course, a CHAR(7) in SQL. Michael
Re: [firebird-support] Deadlock when 2 session update the same 01 row
Ann Harrison schrieb am 28.11.2012 um 16:07 (-0500): On Wed, Nov 28, 2012 at 9:42 AM, André Knappstein knappst...@beta-eigenheim.de wrote: … the default is NoWait. This makes my misunderstanding of the term deadlock complete, because in NoWait I usually get an exception right off, which is all but a deadlock in the way I used to understand it. There's a some history and a bit of theory here. I really like those history lessons, thanks, Ann! :) Fortunately, while I agree the term »deadlock« is confusing, the full error message is rather clear and descriptive: Statement failed, SQLSTATE = 40001 deadlock -update conflicts with concurrent update -concurrent transaction number is 488 Michael
Re: [firebird-support] How Resolve 902 Error
Mourad Hedfi schrieb am 07.09.2012 um 08:58 (+0100): When I'm executing the following query insert into IPOJ_INFOPOSO_JOURNALIERE values [snipped] Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements. I/O error for file D:\VALIDATION\DBFBFB\Database.FDB. Error while trying to read from file. Fin de fichier atteinte. = End of file reached. Nothing to do with Firebird. Some sort of I/O error, like disk full. Michael
Re: [firebird-support] Establishing a database with Firebird
jasonvv schrieb am 03.09.2012 um 04:11 (-): My aims are (i) establishing a new database on the new computer and (ii) ensuring that the old records are safely transferred. … would anyone please advise on the new database establishment as well as the record transferral as I am really a newbie to firebird. Install Firebird on the new computer. I guess I'd pick either 2.0.7 or 2.1.5, latest releases for 2.0 and 2.1, just a couple months old. http://www.firebirdsql.org/ There's some choices to be made when installing, like classic vs SS. Can't say what's best for you. You might just want to go with the same settings you already have for your old database. Then, data migration. Make sure the old database is offline, i.e. doesn't have attachments (connections), so your data doesn't get changed while being migrated, potentially resulting in record loss. Take a backup of the database using the gbak utility program coming with the old database installation. Copy the backup file to the new computer (using Windows Explorer). Restore the backup on the new computer using the gbak utility program coming with the new Firebird installation. This page will come in handy when using gbak: http://www.firebirdsql.org/manual/gbak-recipies.html -- Michael Ludwig
Re: [firebird-support] What is wrong with this delete SQL?
Bart Smissaert schrieb am 19.08.2012 um 14:13 (+0100): Firebird 1.5, classic on Windows. delete from keyword k where not k.rdb$db_key in (select max(k2.rdb$db_key) from keyword k2 group by k2.term_key, k2.term_id) Why does the delete SQL not work? Works fine for me on: WI-V2.5.1.26351 Firebird 2.5 Michael
Re: [firebird-support] Keep alive connection (Delpih, IBX)
ma_golyo schrieb am 29.08.2012 um 09:54 (-): It's TCP timeout. To avoid I have to run dummy select to keep alive. Do I need to run dummy select per active connection or per active transaction? Per connection, because one connection is tied to one socket, whereas each connection *may in theory* host multiple transactions. Re: [firebird-support] Single trans per connection? - Ann Harrison http://tech.groups.yahoo.com/group/firebird-support/message/118391 Michael
Re: [firebird-support] Howto compile Firebird C-API examples
Tomasz Tyrakowski schrieb am 16.08.2012 um 21:56 (+0200): On 2012-08-16 13:24, christian.moelders wrote: I am using Ubuntu 11.10 and try to compile the firebird C-API examples. I does not know what is wrong in my command: gcc -L/usr/lib -lfbclient -I../include api1.c Just put -lfbclient at the end and you'll be fine. See http://stackoverflow.com/q/45135/269126 (Linker order - GCC) for an explanataion; skip the (wrongly) accepted answer by stimms, see the answers by Johannes Schaub, casualcoder and Lumi instead. -- Michael Ludwig
Re: [firebird-support] Howto compile Firebird C-API examples
Tomasz Tyrakowski schrieb am 16.08.2012 um 21:56 (+0200): On 2012-08-16 13:24, christian.moelders wrote: I am using Ubuntu 11.10 and try to compile the firebird C-API examples. I does not know what is wrong in my command: gcc -L/usr/lib -lfbclient -I../include api1.c Just put -lfbclient at the end and you'll be fine. Same story on Windows/MinGW: cd D:\Opt\Firebird25\examples\api gcc -LD:\Opt\Firebird25\lib api1.o -lfbclient_ms : fine gcc -LD:\Opt\Firebird25\lib -lfbclient_ms api1.o api1.o:api1.c:(.text+0xd0): undefined reference to `isc_dsql_execute_immediate@28' api1.o:api1.c:(.text+0xe2): undefined reference to `isc_sqlcode@4' … -- Michael Ludwig
Re: [firebird-support] Saving / Retreving Chinese chrs from database
Andy Gable schrieb am 26.07.2012 um 03:21 (+0100): KITCHENDESCRIPTION_ENGVARCHAR(40), KITCHENDESCRIPTION_OTHER VARCHAR(100) CHARACTER SET UTF8, (programming language VB6 and interface is ODBC) I can display when adding a new product the Chinese chars but when I recall it from the database I get My guess: Some buffer or whatnot on the way from the character data column to is not ready to accept wide characters, so the replacement character (frequently just the question mark) is substituted for the real ones. Check your VB6 code. -- Michael Ludwig
Re: [firebird-support] Reference manual in on place
K Z schrieb am 12.07.2012 um 09:17 (-0700): Does anyone knows where i can find a full reference manual of the FB2.5? Full FB manual for version X.Y = IB 6.0 manual + FB update for X.Y http://www.firebirdsql.org/en/reference-manuals/ (Reference Manuals and the InterBase 6.0 Manuals Language Reference) Looks like you found what's available. Plus, a second edition of Helen's book is in the making: http://www.ibphoenix.com/products/books/firebird_book Michael
Re: [firebird-support] Does qli connection over tcp/ip require a user and password
Ted Miglautsch schrieb am 13.07.2012 um 09:53 (+0400): I am trying to connect between two Linux computers. The hosts.equiv file is properly setup as I previously did with IB but the connection is rejected. I can rsh to the remote computer without entering a password. But qli gets that user and password not defined message. When I made my fist steps onto Linux terrain eleven years ago I learnt that hosts.equiv and rlogin should not be used for security reasons. I've never ever set up hosts.equiv on any machine and never ever used rlogin anywhere. Are you sure these facilities are properly enabled on the machine you're using? As for QLI, it's a very old tool dating back to InterBase's pre-SQL days, and, as you might have guessed, I've never used it. It doesn't get mentioned a lot neither on this list nor on the devel list: http://firebird.markmail.org/search/?q=qli#query:qli list%3Anet.sourceforge.lists.firebird-devel The IB 6.0 PDF manuals don't mention QLI at all. Helen's book from 2004 mentions QLI on pages 454, 936, 1030 and 1037. Found a syntax reference and an article: www.ibphoenix.com/downloads/qli_syntax.pdf Using qli to Extract Data into a New Database http://www.ibphoenix.com/resources/documents/search/doc_42 For SQL, use the isql utility. Sorry in case you knew all that. Michael
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 is NULL and the column
TEST_TABLE.COMPUTED_COL is FAILED.
If TEST_TABLE is empty, how can anything result from a join?
Ah. You're expecting one row (because of the LEFT JOIN), but in that
row, which happens to have only one column, you're expecting all fields
from the right table to be NULL because there is no matching row for the
JOIN condition. And the COMPUTED column is sort of overriding the NULL.
Did I paraphrase your thoughts correctly?
I have no idea what's the correct behaviour here. -- Michael
CREATE TABLE TEST_TABLE
(
IDINTEGER,
COMPUTED_COL VARCHAR(6) COMPUTED BY ('FAILED') );
SELECT t.COMPUTED_COL
FROM RDB$DATABASE r
LEFT JOIN TEST_TABLE t
ON r.RDB$RELATION_ID = t.ID
COMPUTED_COL
FAILED
Re: [firebird-support] Re: Moving from Firebird 2.1 (32 bit) to 2.5 (64 bit)
michael jones schrieb am 18.06.2012 um 18:27 (+0100): [Moving from Firebird 2.1 (32 bit) to 2.5 (64 bit)] I have found no information in any FAQs or knowledge base The release notes have migration info: https://www.google.com/search?q=firebird+release+notes Michael
Re: [firebird-support] FB-OSX example DB won't open under isql?
Maury Markowitz schrieb am 17.06.2012 um 08:22 (-0400): The error remains the same no matter what you try to open: Statement failed, SQLSTATE = HY000 Can't access lock files' directory /tmp/firebird http://tracker.firebirdsql.org/browse/CORE-3185 Possibly permissions. Check the message by Popa Adrian Marius (mapopa) here: https://bugs.launchpad.net/ubuntu/+source/firebird2.5/+bug/678318 Michael
Re: [firebird-support] How to limit the number of concurrent users to a database
Fulvio Senore schrieb am 13.06.2012 um 22:17 (+0200): My purpose is very simple. The users can install the program on any number of computers connected to a Firebird server, and I want to limit the number of simultaneous connections. Do you control the server? If you don't it'll be difficult to enforce anything anyway. But if you do you might investigate whether it's possible to configure this at the network level, like with iptables on Linux, or whatnot on Windows. Take a look here, I didn't do further checks so I don't know whether it's possible or not: https://www.google.com/search?q=iptables+limit+number+of+connections Michael
Re: [firebird-support] Re: How to shorten =================================== between header and data rows?
ehaerim schrieb am 09.06.2012 um 01:16 (-): ii INTEGER Not Null; si SMALLINT Not Null; set width ii 4; set width si 2; select ii, si from test; ii si == === 1001 1 ii and si displays 10 chars and 7 chars. I want them to be 4 chars and 2 chars like ii si == 1001 1 SET WIDTH appears to work by truncating data for display. That may be okay for character data; up to the user to decide. But for numerical data? Would you rather have your numerical ID or your amount of money truncated on the left-hand or right-hand side? In other words, it doesn't do what you want. One might think that SET WIDTH should behave like printf implementations that try to fit data, including numerical data, into a given number of characters but do not under any circumstances truncate data. Or maybe - and I think this is better - operate like Oracle's sqlplus, which displays a garden fence if a number doesn't fit in a display column, to indicate display buffer overflow (see below). SET WIDTH bla in Firebird/ISQL versus COLUMN bla FORMAT spec in Oracle/SQLPLUS, that's obviously not the same level of sophistication. But then, they don't operate on the same budget either. -- SQL col a format 9 SQL select * from zwei; A B C -- -- -- 9 7 juhu 34567 8765 em2012 SQL col a format SQL select * from zwei; A B C - -- -- 9 7 juhu # 8765 em2012 SQL col a clear SQL select * from zwei where a 8; A B C -- -- -- 9 7 juhu 34567 8765 em2012 SQL help col ... SQL -- Michael
Re: [firebird-support] How to shorten =================================== between header and data rows?
Magnus Titho schrieb am 08.06.2012 um 08:24 (+0200): Am 08.06.2012 02:51, schrieb ehaerim: SQL select substring(data_feed_name from 1 for 10) dfn from test; [...] I want the = to be shortened to 15 so that it looks like DFN === InvestWare Anyway, you could use cast to shorten the field. Note however, that the seperating line is always at least 6 characters long. I think ehaerim is referring to the row separators in ISQL, not to any data items. There's a special SET instruction for that: SQL help set; … SET WIDTH col [n] -- set/unset print width to n for column col SQL set width abc 5; SQL select * from T; -- now column ABC displays with a width of five This won't cast the data, but rather truncate it for display, including the == row separator. One thing to note is that the display width depends on the character set chosen for the ISQL session. See below. Michael -- D:\temp :: isql eins Database: eins SQL show table kader; NAMEVARCHAR(12) CHARACTER SET UTF8 Not Null SQL select * from kader; NAME Müller Özil Höwedes Götze Schürrle Gündogan SQL set width name 10; SQL select * from kader; NAME == Müller Özil Höwedes Götze Schürrle Gündogan SQL quit; D:\temp :: isql -ch win1252 eins Database: eins SQL select * from kader; NAME Müller Özil Höwedes Götze Schürrle Gündogan SQL
Re: [firebird-support] Re: gbak to a non existing directory - Email found in subject
ma_golyo schrieb am 07.06.2012 um 06:07 (-): Why do you not want something like that? What is te purpose of this restriction? Or it's not a restriction, just not implemeted? Guess most people back up their databases to known locations, be it locally or on the server. Willy-nilly creation of folders simply isn't necessary. I'd say it is even undesirable because it could create a folder by accident because you mistyped the path and then your important backup might end up in a folder that you don't have on your radar and so it would be effectively lost when you need it. Why would you want to create a new folder from another machine instead of just backing up to C:\Backups ? Explain your use case if you have a good one. Michael
Re: [firebird-support] Server
Michael Vilhelmsen schrieb am 07.06.2012 um 09:38 (-): WEBESCONCommit Trans 6/6/2012 24995151735.774 WEBESCONLogin 6/6/2012 8590.59 WEBESCONLogout 6/6/2012 8580.595 WEBESCONStart Trans6/6/2012 24995161735.775 WEBESCONCommit Trans 6/7/2012 657036 456.275 WEBESCONLogin 6/7/2012 2320.161 WEBESCONLogout 6/7/2012 2310.160 WEBESCONStart Trans6/7/2012 657035 456.274 These are the number of connect(Login), disconnect(Logout) and transactions. As you can see they are connecting/disconnection some 1700+ times each minute duing the entire day. No - they're just using a lot of transactions. Login and logout match your comparative data below. Autocommit mode would easily account for the higher number of transactions. DWIUSER Commit Trans 6/6/2012 32384 22.48 DWIUSER Start Trans6/6/2012 18054 12.53 DWIUSER Commit Trans 6/7/2012 8297 5.761 DWIUSER Start Trans6/7/2012 4347 3.018 ESCONSERVICECommit Trans 6/6/2012 6070.421 ESCONSERVICELogin 6/6/2012 2860.198 ESCONSERVICELogout 6/6/2012 2860.198 ESCONSERVICEStart Trans6/6/2012 6070.421 ESCONSERVICECommit Trans 6/7/2012 1710.118 ESCONSERVICELogin 6/7/2012 79 0.054 ESCONSERVICELogout 6/7/2012 79 0.054 ESCONSERVICEStart Trans6/7/2012 1710.118 This morning (and some 10-12 times more the last 3-4 days) the server died with this: DBSRV03 (Server) Thu Jun 07 06:06:02 2012 Database: ThreadData::start() failed: operating system directive _beginthreadex failed Not enough storage is available to process this command. Which I believe is out of memory problem. It rather sounds like disk full, but I believe you're correct: http://technet.microsoft.com/en-us/library/cc978735.aspx User Action: Do one of the following, then retry the command: (1) reduce the number of running programs; (2) remove unwanted files from the disk the paging file is on and restart the system; (3) check the paging file disk for an I/O error; or (4) install additional memory in your system. As for the causes, I don't have a clue. Michael
Re: [firebird-support] Re: Server
Michael Vilhelmsen schrieb am 07.06.2012 um 10:45 (-): No - they're just using a lot of transactions. Login and logout match your comparative data below. Autocommit mode would easily account for the higher number of transactions. This might be true, but along this we have had some meetings where I had told them that I would have them to START and COMMIT every transaction manually. Maybe have some more meetings :) Whatever kind of connection they have and whatever they use it for they do connect/disconnect more than 1700 times per minute. They do COMMIT a lot, thereby exercising your triggers, but not CONNECT. This is obvious from the data you posted. Is there an index on the table the COMMIT TRIGGER is INSERTing data into? If so, at 1700 INSERTs per second, that would create a pretty nice write load. And even without an index it is not negligible. They have had a connection fore some month. The first 2½ month there where no problems. And they had a 20-80 connect/disconnect per minute. I still find this to be a lot given what they do, but it worked. Then suddenly this rose to 1700+. And the server started to die. I contacted them, and for a week they disabled one thing, and the connect/disconnect went dowen to 20-80. And the problem disappeared. NOw the day before yesterday they enabled again, and I started to die again. My logical conclussion is that this is causing the problem. It runs smoothly without this. It dies with this. Yes. Just the details of what this is are not clear. Disc is not full. OS in is drive C. 67Gb. Free 54 Gb DB is on driver D. 135 Gb. Free 80 Gb There is already nothing running on the server. A Firebird under heavy load is running, possibly eating lots of memory and stressing the disk. No errors in any logs. Except Firebird. Not a sysadmin, but I don't find that surprising if it is a dedicated Firebird server where Firebird is the only program causing load on the server … Michael
Re: [firebird-support] Dealing with inserts from multiple transactions
bobm_dashlogistics schrieb am 07.06.2012 um 16:00 (-): During the ETL process for Sales, we need to verify that the Employee record already exists, and if not then we insert it before writing the Sales record so that we can satisfy the FK. The same process happens during the import of the TimeClock data. The problem occurs when a new employee appears in both the Sales and TimeClock data and are processed at the same time in different transactions. The process looks like this: T1: Start transaction T2: Start transaction T1: Process Sale record T1: Verify Employee - does not exist T1: Insert Employee record T1: Insert Sale record T1: Process next Sale record T2: Process Timeclock record T2: Verify Employee - does not exist (can't see other transaction) T2: Insert Employee record - violation of unique constraint Both transactions are using READ_COMMITTED at this point, which is of course the problem. I can't commit either transaction because the entire Sale or Timeclock dataset must succeed or fail. Any ideas on how to do this type of process successfully? One solution would be to brutally serialize the problem out of the way: First do Sales, then do Timeclocks. Get rid of the concurrency, get rid of the race. Another would be to re-schedule Sale records without matching Employee record for later processing. Timeclock records may create Employees, but Sale records may not. Back them up to a separate file or separate table and process them again in due time. Employee records will have been created and the problem will have been avoided. Michael
Re: [firebird-support] Dealing with inserts from multiple transactions
Doug Chamberlin schrieb am 07.06.2012 um 14:07 (-0400): I think I would challenge whether adding a necessary entity (to satisfy the FK requirement) needs to be in the same transaction as the overall dataset processing. Another possibility might be to screen the dataset (which apparently has to be processed in one fell swoop) to detect any missing entities and then to quickly create them, or rather, assert their existence, creating them as necessary. Or maybe make all such screening go through one supervisor that each ETL process is required to get clearance from. Voilà, another way to serialize. I think it boils down to create some serialization / synchronization point to avoid races and hence duplication. Michael
Re: [firebird-support] firebird.conf
Tom Munro Glass schrieb am 05.06.2012 um 16:42 (+1200): The CentOS package firebird-2.1.4.18393.0-1.el5 installs firebird.conf in /etc/firebird/firebird.conf and /var/lib/firebird/firebird.conf. Shouldn't one of these be a symlink to the other I'd say the one in /etc is meant to be used for the admin to make edits to and for programs to read, while the other one might just be a pristine backup copy reflecting the default state of the file. The FHS doesn't exactly support this interpretation, though. http://www.pathname.com/fhs/2.2/fhs-5.8.html and which location is used by firebird classic and also nbackup? For a definitive answer, do an strace to run the program in question, redirect the output and grep for firebird.conf. Of course, that's only the status quo, not the intent; but let's assume status quo and intent are congruent. Michael
Re: [firebird-support] FireBird versions, 32-bit, 64-bit, and choice of client
magnusanger schrieb am 05.06.2012 um 06:22 (-): I have run a FireBird server for many years. I am currently using the version installed via the package Firebird-2.1.1.17910-0_Win32.exe. I now plan to swap to a Windows 2008 64-bit server, and of course need to install another version of FireBird. I suppose there are no problems whatsoever with the actual FireBird installation or conversion of the .FDB databases as such (or restored from .fbk backup files). It would be best to review the compatibility section to make sure none of the issues mentioned affect your setup, or else take appropriate action: http://www.firebirdsql.org/rlsnotesh/rlsnotes25.html#rnfb25-compat But what about the client ? I have used the minimum client installation from the Firebird-2.1.1.17910-0_Win32.exe package. Would I need to replace the client if the clients still run on 32-bit-machines ? Probably not, but make sure you fully understand this issue (I don't): http://www.firebirdsql.org/rlsnotesh/rlsnotes25.html#rnfb25-engine-stats64 If I use a newer FireBird version than 2.1.1 (such as for instance the 2.1.4 version, or the 2.5.1 version), would I need to replace the client ? Looks like that's the same question as above. If I choose not to update the client (many users, lots of administration), is there a 64-bit version available that matches the client that is currently distributed, and if so, where can I get hold of it ? Why not get the latest 2.1 version (2.1.4) for Win64 from here? Then you wouldn't have to worry about 2.1 2.5 issues. http://firebirdsql.org/en/firebird-2-1/#Win64 Michael
Re: [firebird-support] FireBird versions, 32-bit, 64-bit, and choice of client
Michael Ludwig schrieb am 05.06.2012 um 09:52 (+0200): http://www.firebirdsql.org/rlsnotesh/rlsnotes25.html#rnfb25-compat http://www.firebirdsql.org/rlsnotesh/rlsnotes25.html#rnfb25-engine-stats64 That applies only if you intend to upgrade to 2.5, of course. Which is what I had read into your questions, but you actually didn't say so. Why not get the latest 2.1 version (2.1.4) for Win64 from here? Then you wouldn't have to worry about 2.1 2.5 issues. http://firebirdsql.org/en/firebird-2-1/#Win64
[firebird-support] FB2 + Vulcan - FB3 en route?
I stumbled upon a Vulcan on the attic … http://www.ibphoenix.com/resources/documents/attic - Vulcan Which made for some interesting reading, but then there's lots of documents and I started wondering about the relevance of it all … What is it? Turns out it's an FAQ I've never seen before: http://www.firebirdfaq.org/faq245/ - What is Vulcan? The intention is to merge Firebird 2 and Vulcan code back and create Firebird 3. Is this what's currently happening? Michael
Re: [firebird-support] FB2 + Vulcan - FB3 en route?
Michael Ludwig schrieb am 04.06.2012 um 15:33 (+0200): I stumbled upon a Vulcan on the attic … http://www.ibphoenix.com/resources/documents/attic - Vulcan http://www.firebirdfaq.org/faq245/ - What is Vulcan? The intention is to merge Firebird 2 and Vulcan code back and create Firebird 3. Is this what's currently happening? No, apparently it's not; it either has happened or won't happen. I found a thread on fb-dev that reveals at least part of the history, especially the message by Bill Oliver of SAS: All of the key features from Vulcan have been front-ported to Firebird 2.5 beta. [Firebird-devel] status of vulcan project - James Gregurich - 03.08.09 http://markmail.org/thread/fzjh2xy2fzbvluih The Vulcan is on the attic for a reason. And hence FAQ 245 should reflect that fact, if only by pointing to that thread on Markmail. Michael
Re: [firebird-support] Why PLAN token is not allowed after ORDER BY?
Alec Swan schrieb am 03.06.2012 um 09:01 (-0600): Is there online docs for SELECT syntax? There's an online HTML version of the SQL reference linked to from the overview page: http://www.ibphoenix.com/files/60sqlref.html On Sun, Jun 3, 2012 at 8:59 AM, Alec Swan alecs...@gmail.com wrote: I found FB 2.5 SQL Language reference here http://www.firebirdsql.org/refdocs/langrefupd25-select.html but it does not contain the grammar for the entire SELECT statement, just individual fragments, such as JOIN, ORDER BY, etc. Where can I find a comprehensive syntax definition for the entire SELECT statement? There's no complete one-stop doc for the whole thing yet, as far as I know. Guess most people are happy enough using the old InterBase 6.0 docs (links at the bottom of the doc overview page) supplemented by the Firebird additions you've found. All here: http://www.firebirdsql.org/en/reference-manuals/ From the InterBase 6.0 Manuals section, download either the Full Set or just the Language Reference. Producing a comprehensive doc would require a lot of work, and there might also be copyright issues for the old InterBase 6.0 docs. Michael
Re: [firebird-support] Slow query because an index is not picked up during sort
Alec Swan schrieb am 01.06.2012 um 16:31 (-0600): We tracked down the query that generated a 10GB temp file running against a 1.5GB database. Can anybody explain why the query is not using an index on PHYSICAL_COPY.COMMIT_NUMBER? Prepare time: 3.969s PLAN SORT (JOIN (COPY INDEX (IDX_Lhi+/ZlWWZ/FDOab6YV2Vg==), PHYSICAL_COPY INDEX (IDX_AlJS5EmMT9tODQnFqmid0w==), COPY_CLASSIFICATION INDEX (IDX_soqMJd+Yux0RNvCbmE9rrg==))) Executing... Done. 6273556 fetches, 12 marks, 808464 reads, 10 writes. 0 inserts, 0 updates, 0 deletes, 1563789 index, 4789 seq. Delta memory: 607388 bytes. Total execution time: 0:02:04 (hh:mm:ss) Script execution finished. Your query is essentially: SELECT FIRST (1000) PHYSICAL_COPY.ID, […], COPY_CLASSIFICATION.IS_DIRTY FROM PHYSICAL_COPY INNER JOIN COPY ON PHYSICAL_COPY.COPY_ID = COPY.ID INNER JOIN COPY_CLASSIFICATION ON COPY.ID = COPY_CLASSIFICATION.COPY_ID WHERE (PHYSICAL_COPY.IS_DIRTY = 1) AND (COPY.SOURCE_ID = '123431234') AND (PHYSICAL_COPY.COMMIT_NUMBER = 1000) ORDER BY PHYSICAL_COPY.COMMIT_NUMBER ASC The problem appears to be with the ORDER BY clause. INDEX directionality, why and how? What about KEYs? - milu 11.05.10 http://tech.groups.yahoo.com/group/firebird-support/message/108428 What does SHOW INDEX say for the colum in question? Consider if the following may be the reason your index isn't used: SQL show index; TABLE2_ID_IDX DESCENDING INDEX ON TABLE2(ID) SQL set planonly on; SQL select * from table2 order by id desc; PLAN (TABLE2 ORDER TABLE2_ID_IDX) SQL select * from table2 order by id asc; PLAN SORT ((TABLE2 NATURAL)) SQL -- Index not used for sorting ASC :( SQL -- Now create a second, ASCENDING INDEX on the ID column: SQL create ascending index table2_id_asc on table2(id); SQL show index; TABLE2_ID_ASC INDEX ON TABLE2(ID) TABLE2_ID_IDX DESCENDING INDEX ON TABLE2(ID) SQL select * from table2 order by id desc; PLAN (TABLE2 ORDER TABLE2_ID_IDX) SQL select * from table2 order by id asc; PLAN (TABLE2 ORDER TABLE2_ID_ASC) -- Michael Ludwig
[firebird-support] GBAK documentation: -REPLACE, -RECREATE (OVERWRITE)
Follow-up on a recent thread and some pointers to GBAK docs: Re: Copy of the current db not updating generators http://tech.groups.yahoo.com/group/firebird-support/message/118211 Norman Dunbar schrieb am 31.05.2012 um 17:04 (+0100): Plus a whole chapter on making backups under various conditions here: http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/gbak-recipies.html That guide has gbak -replace overwrite throughout the page, which is not what the options in gbak -? show, and it doesn't appear to work either, at least not with 2.5.1 on Windows/32. The correct syntax for overwriting a database in 2.5.1 (apparently 2.0 and above) is either one of: gbak -replace_database … gbak -replace … gbak -rep … gbak -recreate_database overwrite … gbak -recreate over … gbak -r o … As switch, anything from -R to -RECREATE_DATABASE, and as keyword, anything from O to OVERWRITE. Of course, when overwriting, be sure to know what you're doing. Which is what FAQ 181 points out, going further to caution people against using the -replace switch, which prior to 2.0 was abbreviated as just -r instead of -rep: Why did 'gbak -r' overwrite my database? […] You should always use -c to create a new database from backup and then simply rename the database file. This will save you a lot of trouble […] Also, if you use -r and restore fails for some reason you won't have any functional database as the old one is deleted when restore starts. This has been changed in Firebird 2, now -R means RECREATE database, and will not overwrite existing database file. -- http://www.firebirdfaq.org/faq181/ So all in all, using -replace (-recreate overwrite) throughout in a guide on making backups might not be the best choice under each and every circumstances. In addition, FAQ 181 is slightly wrong or out of date: If you desire the old behaviour of -R switch, you can use -REP or -R -O, where -O means OVERWRITE. The last part is wrong and doesn't work. There is no -R -O; the correct option is -R OVERWRITE or -R O, where OVERWRITE is somewhat of a syntactical oddity, an optional keyword argument to a switch. So that should read: If you desire the old behaviour of -R switch, you can use -REP or -R OVERWRITE (-R OVER or -R O), where OVERWRITE/OVER/O is an optional keyword argument to -R. One remark about the rewiring of the -R switch to -RECREATE in 2.0 and up versus -REPLACE before. I think it is a bit unfortunate and increases confusion. Old docs and old knowledge keep sticking around and never entirely disappear. And imagine being accustomed to the safe 2.0 and up behaviour of -R and then unsuspectingly coming to an older installation and doing a -REPLACE you didn't intend. Anyway, the case is hopefully rare enough. But this shift in meaning is just one more thing to keep in mind. It might have been better to just disallow the shorthand -R notation and require the user to specify -REPLACE. I hope this last remark didn't make my comments too much of a rant … -- Michael Ludwig
[firebird-support] Docs for gfix -shut : decide on -attach, -tran, -force
Database Startup and Shutdown Database Shutdown http://www.firebirdsql.org/manual/gfix-dbstartstop.html I've just been reading parts of the GFIX manual. It took me some time to understand when you would want to specify -attach or -tran when shutting down a database. I think I got it now: -shut -attach n You want the shutdown to succeed only if after n seconds there are no remaining attachments (connections) to the database. In other words, you want everybody to log off within n seconds; else fail. (On the other hand, existing attachments may still start new transactions.) -shut -tran n You want the shutdown to succeed only if after n seconds there are no active transactions on the database. In other words, you want everybody to commit or abort their work; else fail. This is less strict than -attach n as you don't require people to log off the database. (On the other hand, it could be seen as stricter as you prevent people from starting new transactions.) -shut -force n You want the shutdown to succeed unconditionally after n seconds. Users may have attachments or active transactions, but you really don't care: they've got n seconds to get stuff done, and then it'll be too late and they'll be disconnected and their work discarded. Fair enough, there's important admin work to be done. Is this about correct? If it is and you find it useful, maybe you'd like to include it into the section of the guide I referred to above. One thing I'm still wondering about is why you would prefer -attach to -tran when shutting down? If the objective is to prevent work from being carried out on the database then, while both -tran and -attach are effective, -tran seems to be more efficient as it preempts new transactions during the grace period of n seconds but doesn't require users to log off. If this question has a good answer I think it might also be a worthwhile addition to the manual. Michael
Re: [firebird-support] Docs for gfix -shut : decide on -attach, -tran, -force
Michael Ludwig schrieb am 01.06.2012 um 19:16 (+0200): Database Startup and Shutdown Database Shutdown http://www.firebirdsql.org/manual/gfix-dbstartstop.html I've just been reading parts of the GFIX manual. It took me some time to understand when you would want to specify -attach or -tran when shutting down a database. GFIX - Firebird Administration Database Shutdown http://www.destructor.de/firebird/gfix.htm -- Stefan Heymann, last change 2010-01-11 This has been easier to understand for me: -at[tach] seconds Used with the -shut option. Waits seconds seconds for all current connections to end. If after seconds seconds there are still connections open, the shutdown will be cancelled and return an error. -tr[an] seconds Used with the -shut option. Waits seconds seconds for all running transactions to end. If after seconds seconds there are still running transactions, the shutdown will be cancelled. One thing I'm still wondering about is why you would prefer -attach to -tran when shutting down? No answer to this question either on Stefan's page. Michael
Re: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
Alec Swan schrieb am 01.06.2012 um 11:55 (-0600): Our customers complain that Firebird creates 6GB temporary sort file and we need to address this asap. Can anyone suggest a solution? That does sound unhealthy. Have you figured out what query or queries are causing these exorbitant temporary sort files? There might be ways of tailoring them such that the sort needed to produce the result is reduced to a more manageable size. Michael
Re: [firebird-support] Problems with dynamic events
lortherin schrieb am 28.05.2012 um 20:07 (-):
-- ISQL scriptlet:
-- Two triggers to go with it:
set term ;!!
At this point, it should be:
set term !! ;
And a perl script which will show the error. I know this is probably
horrible perl-fu.
Looks good to me. But I couldn't get it to work on Windows and using
DBD::Firebird. (DBD::InterBase didn't even compile, and it seems to
have been superseded by DBD::Firebird.) I got all sorts of strange
error, not reliably reproducible. Looked like concurrency issues to
me, but then I don't know much about them.
The synchronous method (ib_wait_event instead of ib_register_callback
and ib_cancel_callback) worked fine, though:
use strict;
use warnings;
use DBI;
use POSIX qw(strftime mktime);
use Time::HiRes 'time';
my $db = shift or die 'database!';
my $user = shift or die 'username!';
my $pass = shift or die 'password!';
my $dbi = DBI-connect('dbi:Firebird:' . $db, $user, $pass,
{ PrintError = 0, RaiseError = 1, AutoCommit = 1 });
my $station = 1;
my $curr_date = mktime(0, 0, 0, 1, 0, 110); # 01 Jan 2010 @00:00
my $end_date = mktime(0, 0, 0, 1, 0, 111); # 01 Jan 2011 @00:00
my $insert_count = 0;
my $t0 = time;
my $sql = execute procedure insert_log_entry (?, ?, ?, ?, ?, ?);
my $sth = $dbi-prepare($sql);
while ($curr_date $end_date) {
my @lt_curr_date = localtime $curr_date;
my $dd = strftime '%Y-%m-%d', @lt_curr_date;
my $hh = sprintf '%.2d', $lt_curr_date[2]; # hour
my $tt = ${hh}:000:00.00;
my $ev1 = PLAYLIST_INSERTED_${station}_${dd}_${hh};
my $ev2 = PLAYLIST_DELETED_${station}_${dd}_${hh};
my $evh = $dbi-func($ev1, $ev2, 'ib_init_event');
my $res = $sth-execute($station, $dd, $tt, 'Event Test Entry', 0, 'N');
$sth-finish; # call needed to trigger event!
$insert_count++;
printf %5u - Inserted $dd $hh\n, $insert_count;
my $events = $dbi-func($evh, 'ib_wait_event');
if ( $events ) {
while ( my($evnam, $evcount) = each %$events ) {
printf %50s %2u\n, $evnam, $evcount;
}
}
$curr_date += 3600; # Bump to next hour and repeat
}
$dbi-disconnect;
printf inserted $insert_count records in %.2f seconds\n, time - $t0;
__END__
Note that I didn't get any Firebird server errors as I tested against
the latest 2.5.1 (Win32 build on Win7/64).
--
Michael Ludwig
Re: [firebird-support] Role permissions
Newbie schrieb am 24.05.2012 um 19:36 (+0300): I tried to grant execute permission for stored procedures for a role. but when this procedure is executed by a user in this role I get message, that rights doesn't exist on table. I see in IBExpert DDL that grant select on tables used by that procedure are already given to the stored procedure. Could you provide the relevant part of SHOW GRANTS in isql? Question: is it sufficient that I grant a role to a proceudure? or should I grant that role also rights for each individual table as well? In order for a procedure to execute successfully, it needs to have the rights to do its jobs; either per grants to the procedure proper, or per grants to the invoker of the procedure, which are then conferred upon the procedure for when it is being executed. application does all access to data though stored procedures. so in reality users should not need to be granted rights on tables. how would be best practice to solve it? Create a role APPL and GRANT it all required EXECUTE privileges. Then grant APPL to PETER, PAUL and MARY. As for the procedures, you could look at each one and grant it just the privileges it needs. Very strict, very exact. You could also make a list of all required privileges across all procedures, package all those privileges in a role APPLSP, and then grant that role to every single procedure. Less exact, but possibly more convenient. -- Michael Ludwig
Re: [firebird-support] unable to allocate memory from operating system
Alexey Kovyazin schrieb am 21.05.2012 um 15:04 (+0400): You have hit transaction limit of Firebird. To prevent it, make backup/restore more often. What makes you think so? And why would that make the memory usage rise to nearly 100% ? Also, the limit is 2**64, that's pretty high. Re: [ib-support] Transaction number limit? -- Ann Harrison 12.12.02 http://tech.groups.yahoo.com/group/firebird-support/message/19581 Michael Exception class = TDBXError; message = invalid request handle Exception class = TDBXError; message = unable to allocate memory from operating system Any attempt to access from other programs (inc IBExpert) also gave the memory error as sis GFIX and GBAK. System memory did spike to near 100% before they failed. Copying the database to a WinXP machine and trying to open/test using a local IBExpert the memory again gradually increases to the XP address limit. Have successfully restored service from a backup but would like to understand the errors and what might have caused them Firebird 2.1 WI-V2.1.1.17910
Re: [firebird-support] There is a way to format datetime directly in SQL ?
Matthias Hanft schrieb am 17.05.2012 um 12:14 (+0200): Huan Ruan wrote: select extract(year from current_date) || '-' || lpad(extract(month from current_date), 2, '0') || '-' || extract(day from current_date) from rdb$database Is this an atomic operation? It is: It should be noted that CURRENT_TIMESTAMP returns the timestamp of statement start, which means it is constant during the statement execution. […] The reason behind all this is that statements should be atomic, so CURRENT_DATE and CURRENT_TIMESTAMP provide consistency. If statement execution takes a long time it can easily happen that the date changes between statement start and end. http://www.firebirdfaq.org/faq114/ Michael
Re: [firebird-support] Re: LEFT OUTER JOIN is extremely slow.
Ray Holme schrieb am 30.04.2012 um 19:15 (-0400): If you want performance too, make as many of your join criteria numerical and of the same type. I try to search on strings but join tables on integers (or bigints or shorts, doubles and floats are OK but not as good) Yes. I once was given the advice to always use the native integer type of the machine for joins. Because that should be fastest. Guess that 32 bit integers are fine on a 64 bit hardware because that's such a common requirement. Michael
Re: [firebird-support] Calling Firebird from Excel?
Maury Markowitz schrieb am 01.05.2012 um 09:33 (-0400): Does anyone have some sample VBA of calling Firebird via ADODB from Excel, or Office in general? I'm sure I can get up to speed quickly, but that first step... I've never done VBA, only VBS - but I think ADO via VBA would require a COM component for the Firebird client. As far as I know, there is no such beast. Wait, looks like there is an OLE DB provider based on COM, and maybe it has what you want; not free, though; take a look: http://www.ibprovider.com/eng/documentation/firebird_interbase_odbc_drivers.html Michael
Re: [firebird-support] FB SS 2.5 SMP - clarification
unordained schrieb am 20.04.2012 um 15:49 (-0500): Background: I'm currently contemplating switching from 2.1 CS to 2.1 SS on an SMP server, because of the non-shared-cache cost. The DB is metadata-heavy (but usually quite CPU-friendly, that's not an issue), and a lot of queries currently seem to block just at prepare time, as it loads 200 megs of metadata (it always seems like a lot to me -- does it prepare BLR inline'd, multiplying the size of the code in RAM?) into RAM for that instance. So, BLR stands for Binary Language Representation, I didn't know this. http://www.firebirdfaq.org/faq187/ Is metadata the same as BLR? All definitions of tables, permissions, procedures, etc? How do you measure metadata volume? I mean, when looking at taskmgr or ps/top/vmstat, how do you know that the memory consumption is due to metadata and not buffers allocated for this or that? They're connection-pooled (tomcat) but DBCP often rotates out the already cached db instances in favor of the small but dumb ones, and then another user soon pays the cost again, which pains me greatly. It can take 3-4 seconds to load that much metadata; also, that accumulates quickly in a multi-user setting, and I start having swap-space issues, which aren't helping my i/o costs. (I have a lot of tables, triggers, and dependencies between them: a single insert can, in theory, have a ripple effect across dozens of tables, and I understand why FB has to prepare for it.) I'm hoping SS can help me pay that cost nearer to once per boot and, overall, perform better. Interesting report. Thanks. Michael
Re: [firebird-support] Re: Restore problem
Dmitry Kuzmenko schrieb am 16.04.2012 um 10:59 (+0400): Friday, April 13, 2012, 11:55:38 PM, you wrote: AJ gbak -se service_mgr AJ -fix_fss_metadata ISO8859_1 AJ -fix_fss_data ISO8859_1 AJ -rep -v -z AJ -user SYSDBA -password masterkey AJ E:\...\...\sl1b3_20120412.fbk15 SL1 AJ == AJ Previous restores have never failed. DK mmm, you did this command SEVERAL TIMES??? AJ Yes, I've do this quite often. if you run same command line for the same DB, i.e. backup/restore, backup/restore, etc, than you killed metadata in it. Options -fix must be specified only ONE TIME, when you restore your database at 2.5 from previous versions backups, and moreover, only if you have ERROR during restore without -fix options. This note might be important enough to make it into the GBAK manual. http://www.firebirdsql.org/manual/gbak-cmdline.html Maybe repeated here: http://www.firebirdsql.org/manual/gbak-caveats.html If I understand correctly, corruption occurs as a result of an unwarranted application of the -fix_* switches. I can only guess how exactly the corruption occurs. Does it by any chance try to apply an encoding scheme to some data suspected not to have received the proper encoding due to a bug in the FB or GBAK version it originated in? Michael AJ Why wouldn't I use the same restore command each time I fetch a new backup? because, as I said, -fix options are special options to fix incorrect encoding of data and metadata, that could happen in previous FB versions.
Re: [firebird-support] Re: No index used for join on 'starting with'
unordained schrieb am 11.04.2012 um 15:18 (-0500): I once worked on medical software (with Firebird!) and we had to deal with ICD-9 and CPT-4 codes. The coding mechanism is somewhat hierarchical: code 201 might mean something, and 201.1 might be more specific, and 201.12 might be even *more* specific. Sounds like it might be a cool application for nested sets to represent tree as popularized by Joe Celko. https://www.google.com/search?q=celko+tree Funny, first hit for me looks like an InterBase site. :) http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html But maybe your scenario wouldn't have been a precise fit for that model. Michael (Sadly, they weren't entirely consistent.) I could see joins from a list of basic conditions (diabetes, etc.) to actual conditions (per-patient), where you want to find any patients who have certain conditions; you don't want the person setting up the report to have to list each individual sub-condition, when you know the key is structured such that a starts-with query would find the rest. So you throw the list of user-selected base conditions (201.%) into a temp table, and then do a simple join, but not an equi-join. The values can be considered natural PK's (natural in that they're provided by some outside system, and building your own would just mean headaches later on), and trying to normalize that into a set of fields would be foolhardy (arbitrary number of parts in the tree; it could be a key like 1.2.3 that also sometimes goes to 1.2.3.4.5.6 -- MIBs come to mind, in the realm of SNMP.)
Re: [firebird-support] how to use the x notation (ex ID=x'049094') inside parameter ?
nathanelrick schrieb am 30.03.2012 um 07:49 (-): how to use the x notation (ex ID=x'049094') inside parameter ? The hexadecimal notation for “binary” strings is for literals. http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-hexbinstrings.html insert into ... (Field1) Values (?); This looks like you're using some general purpose language (like Perl or Java). with the parameter x'049094' i receive an error Does it come with a message? probably i can write any char i want (from #0 to #255) inside parameter and i don't need the x notation ? Guess it depends on the type of Field1. Michael
Re: [firebird-support] an existing connection was forcibly closed by the remote host
homerjones1941 schrieb am 30.03.2012 um 08:12 (-): Server: Windows XP with all updates current.Database: Firebird 2.1 Client 1: Windows 7 - 64 bit Intel I7 - Wireless connection Client 2: Windows XP 32 bit AMD Dual Core - Wired connection (this is not the server). Language: Delphi 2007 - Data access via IBDAC components (devart). Application: Customer lookup and support data.The program normally has only one table open using an IBDAC Query component. The data is displayed one record at a time (not a grid). There are a good number of other tables, but they are not open when this problem occurs. Condition/Symptom: Within 15 or 20 seconds of opening the database, the program freezes for a short period before returning the error: ... an existing connection was forcibly closed by the remote host. Sounds like a Microsoft Windows TCP level problem; you probably know that much already. Troubleshooting: Connection Forcibly Closed http://msdn.microsoft.com/en-us/library/ms187005.aspx This happens while navigating from one record to the next. It also happens in the same amount of time when sitting idle.Examination of Firebird.log shows multiple coinsurances of : MYCOMPUTERNAME (Server) Thu Mar 29 18:11:06 2012 INET/inet_error: send errno = 10054 MYCOMPUTERNAME (Server) Thu Mar 29 18:19:03 2012 INET/inet_error: read errno = 10054 Assumptions:Since 10054 = Connection reset by peer, I assume the origin of both messages is occurring on the server, and they are both reporting same condition. Question:If my assumption is correct, is there something I need to do with Firebird that will prevent/minimize this? Can't think of anything, but I'm not an expert. Rather, try unsetting the SynAttackProtect registry name as suggested by the MSDN article. I plan to look at Firewall and FileZilla settings, Should I check anything else? Firewall, maybe; not sure how FileZilla would be related. More background:I have a server application (also written in Delphi using Indy) running on the same server in the same domain. It has been running flawlessly for many months. This same machine, router and switch has been my web server for over a year. The clients for that other server might just not happen to run into or trigger or detect this error condition. -- Michael Ludwig
Re: [firebird-support] SP problem execute statement
Jack Mills schrieb am 30.03.2012 um 12:30 (-): I can post the full sp if it would make thing clearer. Why not try and bisecting the problem down to the smallest query that exhibits the error? That's a proven debugging technique that always works. -- Michael Ludwig
Re: [firebird-support] OIT / NT
Tiberiu, consider the comments on COMMIT RETAINING in the quotes from Helen's and Ann's messages below - maybe there's one place where you're running with the default and it's only triggered on the installation you're seeing the problem on. Michael Tiberiu Horvath schrieb am 31.03.2012 um 10:03 (+0300): My BIG concern is that my program (Delphi XE with good old IBX components) run with the exactly same release, on 4 different clients generating diferent results. FB performance - Helen Borrie 06.01.06 http://groups.yahoo.com/group/firebird-support/message/70793 Then look closely at how transactions are being committed. The IBX default for AutoCommit is to use CommitRetaining. This is handy for Delphi client apps and disastrous for the server. It causes garbage to build up steadily. You are seeing better performance on a clean database and degrading performance as garbage builds up and doesn't get cleared. 3 of these are in normal parameters (while working, the gap between OIT/NT and OAT/NT and OST/NT increases by a few transactions / 5 sec) but when that particular client (same program, same FB client, same OS, same ISP) starts my program, the mentioned values increase in an alarming rate of 100...200 / 5 sec, so that at the end of a normal working day, the gap is arround 1.5 mil transactions. People using the program at this company complain about the slowdown in the afternoon compared with the morning. From: Ann Harrison Sent: Friday, March 30, 2012 7:07 PM OK, time for my lecture on OAT and OIT. The OAT is the oldest transaction that the system considers to be active. It blocks garbage collection and induces database bloat. Transactions that commit using commit retaining do not advance the OAT. Transactions that are left open for hours - even transactions that have not changed the database - leave the OAT stuck. Once the OAT is stuck, Firebird must keep old versions of records that transaction might read if it ever wakes up and starts working again.
Re: [firebird-support] How to do a full text search ?
[[firebird-support] How to do a full text search ?] nathanelrick schrieb am 23.03.2012 um 14:12 (-): now i want to permit user to also filter by keyword something like (FieldVarcharDescr like '%Keyword1%' or FieldVarcharDescr like '%Keyword2%') Not sure what you're asking. This is a filter on text data, and it mighte be okay if you don't filter a lot of data. If, on the other hand, you want full text search as in Full Text Indexing and Searching as indicated by your subject line, then this is not, in my opinion, the job of the database proper. I took a note of this back when they announced it, but have never used it - not sure what has become of it: Sphinx Full Text Search http://www.firebirdsql.org/en/sphinx-full-text-search/ If that turns out to be not ready for center stage, you could still use an external indexer and search engine combo such as Sphinx or Lucene or whatnot and go with that. Michael
Re: [firebird-support] Garbage collection performance issues ????
Robert martin schrieb am 21.03.2012 um 15:18 (+1300): We have noticed performance issues on a machine running a web service connected to a FB 2.5 database. On a brand new machine (to rule out computer problems) everything worked fine for the first few hours, however after deleting a large number of records our performance issues surfaced. Instead of a process starting for each connection, running for a few seconds, then completing (closing) the transactions open and sit idle for a long period of time before processing. Alter doing a backup and restore the database changed from 1.9GB to 560MB. Restarting the web server and everything is working great again. Just a guess, but you could read these replies from the archives and see if that might be the problem you're facing, long-running transactions preventing garbage from being collected, slowing down operations in the process. FB performance - Helen Borrie 06.01.06 http://groups.yahoo.com/group/firebird-support/message/70793 Then look closely at how transactions are being committed. The IBX default for AutoCommit is to use CommitRetaining. This is handy for Delphi client apps and disastrous for the server. It causes garbage to build up steadily. You are seeing better performance on a clean database and degrading performance as garbage builds up and doesn't get cleared. Check the database header statistics over this period of degradation (gstat -h). If you see a widening gap between the Oldest Transaction and the Oldest Active then you'll know that poor database hygiene is a significant part of your problem. Gbak never end... - Svein Erling Tysvær 12.02.09 http://tech.groups.yahoo.com/group/firebird-support/message/100241 One very important part of Firebird is transactions. They should never be running for a long time (well, 'never' is a strong word - read-only, read-committed transactions may be kept running, as can all transactions on read-only databases, however, your transactions probably do not fall in these two categories when you have the kind of symptoms you describe). There may of course be other reasons for your problem, but it is typical for us Delphi developers (particularly if we have desktop database background) to not take proper care of our transactions and observe that our programs become slower and slower until we finally decide to stop the database. Then everything works fine for a while again until a new slow-down cycle starts. If this fits your observations, then you need to take a look at the transactions. If your problem is not gradually worsening, but that the program seems to work perfectly until suddenly coming to a halt and that there is no large gap in the statistics, I'd say there is probably some other reason for your problem. fb 2.1 on Windows: how to use more RAM? - Svein Erling Tysvær 28.08.10 http://tech.groups.yahoo.com/group/firebird-support/message/109741 Databases that respond quick when they're started, and then gradually slow down until almost coming to a halt, are a typical sign of poor transaction handling in one or more applications. The gap between oldest transaction and next transaction is often the 'proof'. Multi-Version Consistent Read Question - Ann Harrison 13.12.11 http://tech.groups.yahoo.com/group/firebird-support/message/116063 Firebird keeps information about each running transaction, including the oldest transaction that was running when the transaction started. When choosing record versions to remove, Firebird compares the transaction identifier in the record version header with the oldest of the oldest - i.e. the oldest transaction that was running when the oldest transaction now running started - and keeps one version older than that. If the oldest transaction running is 200 and the oldest transaction running when it started was 175 and the chain of record versions goes 199, 176, 175, 174, 173, 140, 123, Firebird can remove the versions created by 173, 140, and 123. The newer versions will all stay until transaction 200 exits and the next oldest of the oldest is higher than 175. If that's not your problem, then I'd say it's a nice collection of useful quotes nonetheless. :) Michael My suspicion is that this is related to garbage collection. Does this sound likely? We are using the FB 2.5, superclasic with default garbage collection settings. What changes would people recommend? I am considering turning off garbage collection and scheduling it to be manually run late at night. This would work fine for this system but may not suit other users. Machine is Win7-64 Quad core I7 with 8GB RAM running Apache.
Re: [firebird-support] when to use introducer syntax for charachter sets?
Marsupilami79 schrieb am 06.03.2012 um 09:40 (+0100):
The statement about the usage of introducers is on Page 172 in the
middle of the page in a section with the heading String Literal:
An introducer will be required if the database column being searched
has a character set which is different from that of the client
connection.
Okay, and then Helen gives the following example:
... WHERE name = _ISO8859_1 'joe';
Which is somewhat pointless as joe is the same in all ASCII supersets,
and hence doesn't require conversion. (And Firebird doesn't do EBCDIC.)
Guess Helen doesn't have the ö on her keyboard. I'd still suggest that
for the second edition, it should be Jörg instead of Joe.
Here's an example, because I was curious:
D:\ :: isql eins -u milu -p moin -ch utf8
Database: eins, User: milu
SQL create table z(a varchar(20) character set utf8);
SQL commit;
SQL insert into z (a) values ('Jörg');
Statement failed, SQLSTATE = 22000
Dynamic SQL Error
-SQL error code = -104
-Malformed string
SQL -- I've been lying to FB about the connection charset.
SQL -- My connection is really in win1252, not in utf8.
SQL -- Still, I can save the party by using an introducer.
SQL insert into z (a) values (_win1252 'Jörg');
SQL commit;
SQL select * from z;
A
===
Jörg
SQL quit;
Note two things in the output above:
(1) FB thinks my connection is utf8 (because I've been lying), so it
cranks out data in UTF-8, which displays like above on a win1252
terminal.
(2) The bar is 79 characters long when the column has only 20 chars.
This is because FB reserves 4 bytes per Unicode character. (Don't ask
me why it's 79 instead of 80.)
D:\ :: isql eins -u milu -p moin -ch win1252
Database: eins, User: milu
SQL select * from z;
A
Jörg
Everything's fine, text automatically converted for display in win1252.
--
Michael
Am 02.03.2012 19:26, schrieb Michael Ludwig:
Marsupilami79 schrieb am 23.02.2012 um 15:17 (+0100):
I have a problem in understanding when to use the introducer syntax
for character sets.
For literals. See here:
http://dev.mysql.com/doc/refman/5.0/en/charset-literal.html
Helen Borrie states in the firebird books
What page number?
that the introducer syntax should be used whenever a column is
searched and the connection character set is different from the
character set of that column:
So in case I have a WIN1252 client connection and the database uses
UTF8, should I really always use something like
where name = _WIN1252 'Jörg'
Not needed when you type in the characters in the proper connection
encoding.
But suppose in your connection you're going to execute a SQL script
which happens to be encoded in UTF-8. In order not to depend on the
connection encoding du jour, you'd play safe and use introducers in
your script.
Why should I do that? Normally I would expect firebird to know that
everything is WIN1252, as this is the connection character set, and
do the necessary conversions without any further hints?
Or should I use the introducer syntax only when I want to send some
bytes that are explicitly to be treated in a different way than the
connection character set?
for example something like
where name = _UTF8 'Jörg'
when I have the 'Jörg' in an UTF8 encoded way?
Exactly.
Re: [firebird-support] Re: Effect of GUID in primary key on v 1.5 vs 2.5
Benno schrieb am 06.03.2012 um 20:24 (+0100): I can't find the article, but I would NOT use GUID. GUID is a string, and afaik strings are not very efficient in combination with FB. So I would use a numeric PK. Well, it's a 128 bit (or 16 byte) integer, usually formatted as a string, like [Guid(3D27DE89-A43C-480C-866A-958E33705B19)]. http://www.firebirdfaq.org/faq98/ Visual Studio has a handy tool called guidgen.exe to create them (for COM, or possibly other stuff). That said, I second Benno's advice not to use them. Sixteen bytes, that's like 4 billion times 4 billion times 4 billion times 4 billion. A former employer used GUIDs to denote male/female. Michael
Re: [firebird-support] when to use introducer syntax for charachter sets?
Marsupilami79 schrieb am 23.02.2012 um 15:17 (+0100): I have a problem in understanding when to use the introducer syntax for character sets. For literals. See here: http://dev.mysql.com/doc/refman/5.0/en/charset-literal.html Helen Borrie states in the firebird books What page number? that the introducer syntax should be used whenever a column is searched and the connection character set is different from the character set of that column: So in case I have a WIN1252 client connection and the database uses UTF8, should I really always use something like where name = _WIN1252 'Jörg' Not needed when you type in the characters in the proper connection encoding. But suppose in your connection you're going to execute a SQL script which happens to be encoded in UTF-8. In order not to depend on the connection encoding du jour, you'd play safe and use introducers in your script. Why should I do that? Normally I would expect firebird to know that everything is WIN1252, as this is the connection character set, and do the necessary conversions without any further hints? Or should I use the introducer syntax only when I want to send some bytes that are explicitly to be treated in a different way than the connection character set? for example something like where name = _UTF8 'Jörg' when I have the 'Jörg' in an UTF8 encoded way? Exactly. -- Michael Ludwig
Re: [firebird-support] Roles and connection pooling
Nols Smit schrieb am 18.01.2012 um 13:43 (+0200): The question is: If a user log in by using some (restricted) role, will this pooled connection still be able to serve users in other (less restricted) roles? I think connection pools are typically implemented by keying connections on their parameters (credentials, role, etc). So when requesting a connection from the pool you'll be passed one matching your connection parameters. -- Michael Ludwig
Re: [firebird-support] Re: fb server crash
Nick Upson schrieb am 15.01.2012 um 11:34 (+): On 14 January 2012 18:27, Dmitry Yemanov wrote: 14.01.2012 18:39, Nick Upson wrote: using 2.1.4 (epel) on centos 5.5, We had a firebird crash with this message: Fatal lock manager error: invalid lock id (0), errno: 4 The system was under heavy load and it happened just after nbackup level 2 started (which may be related) We need to work out how to avoid it happening again but currently don't know why it happened. I'd suggest to avoid using nbackup until you upgrade to FB 2.5. Dmitry That is several months away at least. Is this a known problem with nbackup? This page suggests it isn't: http://www.firebirdsql.org/manual/nbackup.html If it's advisable not to use nbackup before 2.5, a big warning up-front would seem to be in order: NBackup should not be used with Firebird server versions prior to 2.5; [risks and reasons here]. And maybe repeat the warning in the Limitations section here: http://www.firebirdsql.org/manual/nbackup-overview.html Michael
Re: [firebird-support] Protecting data from end users
richwiz11 schrieb am 15.01.2012 um 20:37 (-): The problem I am left with is how to protect a row from being deleted. The two method I have thought of is a) Using roles and remove the delete privilege (we currently just using SYSDBA, which is probably a bit bad anyway) or b) Add some kind of trigger logging to keep this deleted data and copy to another db. Go with method (a). Why reinvent the wheel? And do get rid of SYSDBA as your application user. -- Michael Ludwig
Re: [firebird-support] UTF8 in firebird ?
Mark Rotteveel schrieb am 05.01.2012 um 21:21 (+0100): On Thu, 05 Jan 2012 21:10:15 +0400, Vander Clock Stephane svandercl...@yahoo.fr wrote: now let thing i target such country (portugal, spain, france, italian, etc..) what kind of charset will best fit my database ? of course UTF8 ! but is it neccessary that when i declare a varchar(255) for my varchar column firebird handle in background a varchar(1250) ?? absolutely not as in these language most of the char need only one byte to be encoded in UTF8 If you target Portugal, Spain, France and Italy, then ISO-8859-1 should be enough for your needs. No, it's not; Œ and œ are missing from ISO-8859-1 (Latin1). Guess the French delegation to the ISO botched up badly on that one … :) They were more alert later when ISO-8859-15 (Latin9) was devised, which includes not only the sigil of an ill-conceived currency (€) but also the Œ and œ. So that's what you should be using, I think, for your selection of Western European countries. http://www.cs.tut.fi/~jkorpela/latin9.html -- Michael Ludwig
Re: [firebird-support] UTF8 in firebird ?
Vander Clock Stephane schrieb am 06.01.2012 um 14:50 (+0400): No it isn't possible. You could attempt to store unicode codepoints in ISO-8859-1 by inventing your own encoding, not inventing my own encoding ! simply store in iso8859_1 the code point (1 UTF8 code point = 1 bytes) There's no such thing as UTF-8 codepoints. They're *Unicode* codepoints. The difference is between character répertoire and encoding algorithm. Character répertoires are about characters: encoding schemes are about bytes (octets). I think you're talking about raw UTF-8 bytes; as other have suggested, you should be using CHARACTER SET OCTETS. Which means no characters, just bytes (octets). where you see that some bytes are forbidden in ISO8859_1 ? firebird never complain about it ! Then it could be said this is a bug, like here: http://tech.groups.yahoo.com/group/firebird-support/message/112680 http://tracker.firebirdsql.org/browse/CORE-3416 -- Michael Ludwig
Re: [firebird-support] Record Recycling
Daniel L. Miller schrieb am 29.12.2011 um 10:07 (-0800): Way back in my dark ages of programming (dBase III+) there was a concept of record recycling. This involved one or more methods of tracking deleted records - and then re-using those records. This was to avoid what dBase called the pack operation - which would be a subset of a Firebird sweep. Is there any reason for an application developer to implement such a strategy in their data access framework - or just let Firebird handle it? From app developer to app developer, I'd say no: There is no reason and, more importantly, no foothold for an app developer to get at the physical layer of data storage when dealing with a system the prime virtue of which is to isolate you from that very layer to allow you to work at the logical layer instead. (I trust the senior sys dev ladies and gentlemen on this list are going to rein in any inaccuracies in this perception of mine.) I've never worked with dBase but from what I've read [1] it also had a query language that kept you at the logical level. How could you get a hold on the physical level in dBase? [1] http://en.wikipedia.org/wiki/DBase -- Michael Ludwig
Re: [firebird-support] 1000 databases per server ?
Vander Clock Stephane schrieb am 25.12.2011 um 17:54 (+0400): yes, but what i want to know if for the same amout of data, is their any disadvantage (or advantage) to use several database instead of one ? More maintenance? Imagine altering your schema for 100 databases. Then again, there's reduced downtime for each shop because the modification can be done faster for a small database. -- Michael Ludwig
