[firebird-support] table insert into itself - can this be done?
Hi, I am seeking advice on whether this is possible:- insert into table_X (mydatetime, taskcode, sitecode) Select x.mydatetime, x.taskcode, ss.sitecode From table_X x inner join sites ss on (ss.siteparentcode=x.sitecode) Where (x.mydatetime between '30 nov 2010' and '1 nov 2011'); /* table_X has no keys */ In other words a table inserting into itself. Essentially for a particular sitecode the records are duplicated for each sitecode relationship. On the left side of my brain, I say firebird would collect all records from the select first and then perform inserts with no problems. On the right side, I say as records are inserted these very same records my be gathered into the select and also be duplicated with horrifying results. Any thoughts would be appreciated. with thanks in advance David
Re: [firebird-support] table insert into itself - can this be done?
Morning David, On 14/12/11 08:04, p51b.mustang wrote: Hi, I am seeking advice on whether this is possible:- ... In other words a table inserting into itself. Essentially for a particular sitecode the records are duplicated for each sitecode relationship. On the left side of my brain, I say firebird would collect all records from the select first and then perform inserts with no problems. That's what Oracle does. On the right side, I say as records are inserted these very same records my be gathered into the select and also be duplicated with horrifying results. That's what Firebird does! In Oracle I frequently use insert into table_a select * from table_a to build up some volume in a table. The first time I tried that in Firebird, it never finished - I had to kill it. Oracle's Read Consistency works differently from Firebird's. When you SELECT from a table in Oracle, it gives you the rows exactly as they were at the time you issued the SELECT. So, your table has 200 rows in it, then the insert inserts 200 rows. Run it again and yo get an extra 400 rows, then 800 and so on. Firebird's read consistency doesn't seem to do this, you get to see all the rows in the table even the ones being inserted by the command. Best avoided! By the way, I've not tried this in Firebird 2.5 as I don't want to have to kill off my session to stop it running away! 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
Re: [firebird-support] Multi-Version Consistent Read Question
Morning Ann, On 13/12/11 18:18, Ann Harrison wrote: ... The method used by Oracle (and MySQL's InnoDB) keeps old versions in log files which are purged periodically. A long running transaction can get inconsistent data if the log file that contained the record version appropriate for it has been purged. If I may be so bold Oracle uses log files for REDO and has ROLLBACK_SEGMENTS or UNDO Segments (depending on Oracle version) for UNDO. It never uses log files for UNDO - and UNDO is what provides Read Consistency/MVCC in an Oracle database. Changes are written to the LOG_BUFFER (n memory) and periodically - on commit, every 3 seconds max, or when the buffer is 33% full - flushed to the REDO logs. These REDO logs might be archived to disc when they fill up. That Depends on the database archive log mode though. These logs are used when a database is restored and rolled forward (using the RECOVER DATABASE command, for example). In order to roll back changes and to ensure read consistency, UNDO is used. These do live on disc - as tablespace files - but remain in memory in the buffer cache alongside data blocks etc. When a SELECT is started, the data returned are the data from the data blocks. Each row in a block has an indicator that tells when it was last updated. If a pending update is taking place (currently uncommitted) or if a commit has taken place since this SELECT started then the data read from that data block has changed - and is not consistent with the start time of this SELECT transaction. When this is detected, Oracle rolls back the changes to the start time of the SELECT taking place by looking for the UNDO block(s) associated with the transaction that made the changes. If that results in the correct (consistent) data, that's what you get. If it turns out that there were other transactions that also changed the data, they too will be detected and undone. In this way you only ever see data that was consistent at the start of your own transaction. As long as the DBA correctly sizes the UNDO tablespace and correctly sets the UNDO_RETENTION parameter to a decent enough value, data changes are able to be rolled back happily all the time. If the DBA failed miserably in his/her duties, the ORA-01555 Snapshot too old errors are the result. And are most irritating. Long running SELECTS - batch reports for example - tend to show up this error mostly. This mechanism is also used to carry out a CONSISTENT=YES export of the data - any changes made since the start of the export are rolled back to get the data as it were when the export started. UNDO segments are in three states: Active - currently in use by a transaction. Unexpired - A transaction has committed, but the UNDO_RETENTION period has not yet expired. This segment should not be used. Expired - this segment is free for use. When a transaction needs a new UNDO segment, it attempts to use the expired ones, if there are none, it will try to create a new one in the free space, if any, of the undo tablespace. If it cannot, it will then re-use an unexpired segment. This renders the data in that segment unavailable and is the cause of the above snapshot too old errors. Note to self: next time, be brief! 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
RE: [firebird-support] table insert into itself - can this be done?
In Oracle I frequently use insert into table_a select * from table_a to build up some volume in a table. The first time I tried that in Firebird, it never finished - I had to kill it. Doesn't sound quite logical that Firebird should behave this way, but I don't doubt your observations and think I've observed something similar (although last time I saw a never ending query was when I tried a simple select with GROUP BY on a constant value in Firebird 1.5.4. Though the very same query on a newer Firebird version yielded the correct result). Haven't tried it, but adding DISTINCT to Davids query: insert into table_X (mydatetime, taskcode, sitecode) Select DISTINCT x.mydatetime, x.taskcode, ss.sitecode From table_X x inner join sites ss on (ss.siteparentcode=x.sitecode) Where (x.mydatetime between '30 nov 2010' and '1 nov 2011'); ought to ascertain the query finishes. Although it might not insert as many records as David intended. HTH, Set
[firebird-support] Re: table insert into itself - can this be done?
Thanks guys. It looks like it would be safer to take a longer route via a temp table. You have probably save me from a disaster as the table to load properly would include 10,000s of records. However, out of curiosity I may try using DISTINCT on an standalone test computer if I get the chance. Thanks
Re: [firebird-support] Copy a database to a blank one
Hello! I'm using FB2.5. Is there any command line to copy a database to a blank one? I mean, I need to create an empty copy of a given database, but I'd like to do it using a command in a bath file... I hope I'm clear! I'v been googling but had no luck!. -sergio Create a metadata backup with gbak. -- With regards, Thomas Steinmaurer * Upscene Productions - Database Tools for Developers http://www.upscene.com/ * My Blog http://blog.upscene.com/thomas/index.php * Firebird Foundation Committee Member http://www.firebirdsql.org/en/firebird-foundation/
[firebird-support] Re: Firebird Database Replication Inquiry
Formalejo, Richard [2011-12-14 01:07] : Is there a third party program need to install on my server in order to do this? look at http://www.ibphoenix.com/products/software/ibreplicator
Re: [firebird-support] table insert into itself - can this be done?
Em 14/12/2011 06:25, Norman Dunbar escreveu: By the way, I've not tried this in Firebird 2.5 as I don't want to have to kill off my session to stop it running away! Hi Norman I've tried in Firebird 2.5.1 (just for curiosity) and the behavior is still the same. :) Regards, Fabio Schunig
[firebird-support] database unavailable
Hi. I hope you fine folks can help me with something. I've written a program that I allow the user to send it to the tray at logon. I install the embedded server in the program's, program folder (C:\Program files.). When ran in a virtual environment, everything is AOK, just like a friend of mine installed it on his computer, AOK. It starts up just fine. However, when the program is ran on a computer that has the full version of FB installed, as well as the embedded, when my program attempts to start, the error database unavailable is given. Once the windows is up and running 100%, the program can be started with no problem. I want to keep the embedded server in place, and insure that the program uses it, instead of the full server (the db versions are different). So here are my questions: 1. When running with the embedded and full server, which one does the program use by default? 2. If #1 is the full version, how can I prevent my program from attempting to access the database, until the full one is up and going? It is irrelevant whether I put the start-up at log-in in the registry or the Startup folder, the same thing happens. Like I said, I prefer to keep using the embedded version, because the version of FB I use is different (2.1.3.18185). I've had problems with the newer version of fb (2.5), that is why I'm still on (2.1.3.18185)
[firebird-support] Problems with full ICU on 64bit firebird
Hello, we have a database with UTF8 fields, but we need czech ordering. We started with 32bit FB2.1, we used full ICU 3.0 for win32 (msvcr7.1) and define our collations according to full ICU capabilities - no problem. Then we upgrade to FB2.5 win32 - use the same ICU - no problem. But when we try to use Win64bit firebird I cannot run the server with 64bit ICU - there is different compiler - msvcr6. What I have to do when I want to use full ICU with 64bit firebird? Should I use a newer ICU? Which version, and how to set server to use newer version of ICU? On ICU web page I can found 64bit versions 3.2, 3.4, but they use msvcr6 too. Version 4.0 use msvcr8, maybe this is the way, but how to set firebird to use them? I'm testing it on Win7-64bit and Win2008 R2 64bit server. On linux we have bigger problem - nor 32bit nor 64bit works. Can I ask You why the firebird doesnt use the full ICU in standard installation? Thank You in advance. Please help, Tom
[firebird-support] Can you use the embedded Firebird server on Windows from perl?
Not sure if this is the right forum to be posting this question or whether it should be directed to a perl forum somewhere. Perhaps someone can redirect me if this isn't the best place. I see from the release notes for DBD::Firebird 0.91 that it builds a version on the module which uses the embedded server if it is available. But looking at the build scripts it only seems to be looking for the Unix shared library, there doesn't seem to be any attempt to build this for Windows. Does anyone know if this is available, or can be done with a bit of tweaking of the build?
Re: [firebird-support] Server Log Entry - help!
At 04:23 AM 15/12/2011, Kevin Stanton wrote: I have a customer that is down - can't start my application and this entry jumped out at me from firebird.log: DATA01Wed Dec 14 07:05:25 2011 SERVER/process_packet: broken port, server exiting Environment: Win 2003 Server R2 6 GB Ram Plenty of disk space FB Classic 1.56 Any help deciphering the above message would be greatly appreciated. It's the server's response to a network error that broke the connection between a client and the server. You will almost certainly find some preceding network errors that might give a clue as to what failed. Usually, it's a faulty network connection; if you see lots of them, it's more likely the fault is on the server's card; if it's consistently associated with one workstation, look there first. Of course, you would first eliminate the possibility that the user simply crashed out of the app while an operation was under way. ./heLen
[firebird-support] question about copyright law
Hello everybody, one of our customer has no confidence in the Copyright Law of the firebird-database. Is there a certificate for confirmation? We need to confirm all copyright claims to take over! Thank your for your help! Best regards Olaf
Re: [firebird-support] question about copyright law
one of our customer has no confidence in the Copyright Law of the firebird-database. Is there a certificate for confirmation? We need to confirm all copyright claims to take over! What does copyright law mean here? Regards, Thomas Thank your for your help! Best regards Olaf ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links
Re: [firebird-support] question about copyright law
On Wed, 14 Dec 2011 19:32:39 +0100, olaf.kl...@satron.de wrote: Hello everybody, one of our customer has no confidence in the Copyright Law of the firebird-database. Is there a certificate for confirmation? We need to confirm all copyright claims to take over! Thank your for your help! What exactly does your customer question? The law (which has nothing to do with Firebird, but with your country), the license or copyright of Firebird, or the license or copyright of the database? You don't register for copyright, copyright is something that is linked with the creation of a work itself, so there is no 'certificate of confirmation' (whatever that is, and whatever legal value such a document would have). The open-source license of Firebird is available for inspection and review, as is the original open-source licnese for the release of Interbase. The history of source changes and their committers can be viewed on sourceforge (through CVS and Subversion). If you really need advise on copyright, I'd advise you took talk to a IP lawyer with experience with open source.. Also I don't understand what you mean with 'We need to confirm all copyright claims to take over!'. Mark Hello Mark and Thomas, our customer thinks, that he must pay for firebird in future, even retroactively. In German: Sie sind ja bestimmt auch über das deutsche Urheberrechtsgesetz in Kenntnis. Ein Internet Ausdruck hat rechtlich absolut keinen Wert, wenn es dem Eigentürmer einfällt Geld zu verdienen so kann er das jederzeit tun. Auch rückwirkend. Nur durch eine Form der Urkunde werden Rechte überlassen. I don't know how can I formulate this words in english. Thanks again Olaf
Re: [firebird-support] question about copyright law
Hello, olaf! Wednesday, December 14, 2011, 10:32:39 PM, you wrote: oksd Hello everybody, oksd one of our customer has no confidence in the Copyright Law of oksd the firebird-database. Is there a certificate for confirmation? oksd We need to confirm all copyright claims to take over! oksd Thank your for your help! read idplicense.txt which comes with any Firebird installation or zip file. It's in the root of Firebird install directory. -- Dmitry Kuzmenko, www.ib-aid.com
[firebird-support] Versions of FBClient.dll
Hello, We have a number of clients that have installations of our software using either version 2.0 or 2.1 of Firebird. We have plans of upgrading them to version 2.5. We have changed our connectivity to use FBClient.dll. All of our clients have GDS32.dll installed in their Windows System folder, but not FBClient.dll. We would like to be able to just include FBClient.dll along with our application. If we use the latest version of FBClient.dll, will it be compatible with all versions of Firebird (2.0, 2.1 and 2.5)? Thanks Todd
Re: [firebird-support] Multi-Version Consistent Read Question
And a different good morning to you Norman, If I may be so bold Of course, and thank you for the detailed and complete description of a mechanism I glossed over irresponsibly. As long as the DBA correctly sizes the UNDO tablespace and correctly sets the UNDO_RETENTION parameter to a decent enough value, data changes are able to be rolled back happily all the time. If the DBA failed miserably in his/her duties, the ORA-01555 Snapshot too old errors are the result. And are most irritating. Long running SELECTS - batch reports for example - tend to show up this error mostly. Ha ha ha! You don't get month end this month because your DBA screwed up! Try Firebird next time. Cheers, Ann
Re: [firebird-support] table insert into itself - can this be done?
On Wed, Dec 14, 2011 at 3:04 AM, p51b.mustang p51b.must...@gmail.com wrote: On the left side of my brain, I say firebird would collect all records from the select first and then perform inserts with no problems. On the right side, I say as records are inserted these very same records my be gathered into the select and also be duplicated with horrifying results. Trust the right side, Luke. Maybe someone has fixed this in a more recent version of Firebird, but it's a characteristic bug of databases that Jim Starkey writes. Unless there's a sort (e.g. order by, distinct, grouped) they get records one a time rather than finding all qualifying rows first as the standard anticipates. Rdb/Eln, InterBase, Firebird, Netfrastructure, Falcon, and, until recently NuoDB all go into an infinite loop if you have any rows in table A an do something like this: insert into A select * from A Retrieving all the rows and data first is really inefficient. Retrieving the rows and retaining only the db-keys (or equivalent) does the same amount of I/O as retrieving data first, but doesn't use as much memory or temporary disk space. The solution that NuoDB implemented is to keep a sparse bitmap of the db-keys of the inserted records and not reinsert them when they come back from the select. Note that db-keys are not necessarily monotonically increasing, so the query has to run until it exhausts the result set, not just stop when it finds the first new record. And, of course, you can't just ignore records created by your transaction, or running the query twice wouldn't get the right answer. It's possible to do the same thing using the information in the savepoint undo list, I think, and maybe Firebird implemented that after 2.0. Good luck, Ann
Re: [firebird-support] Multi-Version Consistent Read Question
Ann Harrison aharri...@ibphoenix.com wrote: And a different good morning to you Norman, And already it's evening. :-) Of course, and thank you for the detailed and complete description of a mechanism I glossed over irresponsibly. Welcome. You may be surprised at how many people think that Oracle uses log files for undo. ... Ha ha ha! You don't get month end this month because your DBA screwed up! Try Firebird next time. But I do! I looked after over 600 Oracle databases and 43 Firebird ones until recently. More problems with Oracle. Cheers, Norm. -- Sent from my Android phone with K-9 Mail. Please excuse my brevity.
[firebird-support] Re: table insert into itself - can this be done?
Thanks Ann, Being of two minds I am pleased that I decided to check with this group. As I am migrating from 1.5 to 2.5 I can not consider savepoint as two production DBs are still 1.5. The process I am trying to achieve is at least annual, and at most very infrequently, that efficiency will be secondary to readability and understandability for my successors. I shall also include a precise of these responses into a comment field; just in case. Thanks again David --- In firebird-support@yahoogroups.com, Ann Harrison aharrison@... wrote: On Wed, Dec 14, 2011 at 3:04 AM, p51b.mustang p51b.mustang@... wrote: On the left side of my brain, I say firebird would collect all records from the select first and then perform inserts with no problems. On the right side, I say as records are inserted these very same records my be gathered into the select and also be duplicated with horrifying results. Trust the right side, Luke. Maybe someone has fixed this in a more recent version of Firebird, but it's a characteristic bug of databases that Jim Starkey writes. Unless there's a sort (e.g. order by, distinct, grouped) they get records one a time rather than finding all qualifying rows first as the standard anticipates. Rdb/Eln, InterBase, Firebird, Netfrastructure, Falcon, and, until recently NuoDB all go into an infinite loop if you have any rows in table A an do something like this: insert into A select * from A Retrieving all the rows and data first is really inefficient. Retrieving the rows and retaining only the db-keys (or equivalent) does the same amount of I/O as retrieving data first, but doesn't use as much memory or temporary disk space. The solution that NuoDB implemented is to keep a sparse bitmap of the db-keys of the inserted records and not reinsert them when they come back from the select. Note that db-keys are not necessarily monotonically increasing, so the query has to run until it exhausts the result set, not just stop when it finds the first new record. And, of course, you can't just ignore records created by your transaction, or running the query twice wouldn't get the right answer. It's possible to do the same thing using the information in the savepoint undo list, I think, and maybe Firebird implemented that after 2.0. Good luck, Ann
