[firebird-support] fb_sort error
Strange stuff from firebird I am hoping to get some insight on...please bear with me while I describe the problem! I've run into a problem with a firebird installation that is using a 3rd party report viewer to display crystal reports from a firebird db on client PCs...this is a windows environment. the text of the error quoted here is from the sql trace log: DIAG [HY000] [ODBC Firebird Driver][Firebird]I/O error for file C:\WINDOWS\TEMP\fb_sort_## Error while trying to open file The data is invalid. (-902) And it seems to relate to this error message in the FB docs: -902 335544344 io_error I/O error for file @2 The best explanation I have found in the bing/google-verse is from another post on this group: Firebird creates sort files in the assigned temp directory if you don't have enough space in memory for them. Code 335544344 is an i/o error, either you are out of space on your C drive or Firebird can't access your temp directory for some reason. In due diligence the drive that c:\windows\temp is on has ~50GB free; EVERYONE has been given all privileges to the folder; And I can't fathom why Firebird wouldn't be able to access the folder for any reason. The server is operating on another machine under default conf...meaning everything in the file was commented. As a test I enabled 4x settings on each in-memory or related parameter in the conf, bounced the server and tested the report again with the same results. The odbc driver is 2.0.0.144 and the server is running firebird 1.5. The kicker: there is only one report (the most important one, desired by the users, of course) that is affected by this problem. Any advice, insights, or helpful comments of any kind would be appreciated. Thanks RT
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hello Bob, 1): The most obvious thing according to the header page is a very large gap between the oldest active transaction and the next transaction. This means, you have a long-running/stuck transaction. If you are lucky, you can go into the MON$TRANSACTIONS table and check out if you find the MON$TRANSACTION_ID for 41467442. Lucky, because I saw occasions where the OAT according to the header page isn't available in the monitoring tables. Perhaps some client (ETL tool?) doesn't behave well from a client transaction management POV. No such luck - 42450558 is the earliest of the 29 records listed. Haven't got an explanation why OAT isn't visible in the mon tables, but it could be due to a crashed server or possibly a transaction in limbo, in case you are using distributed transactions. You can use gfix to check if there are transactions in limbo. Other than that, a backup/restore will cure this situation. 2): Although you say you aren't in an OLTP pattern here, I guess due to ETL, it isn't a read-only database, right? If so, running the database in no reserve mode isn't a good idea, because, basically you are telling Firebird to not reserve space for back record version on the same data page as the primary record version. This results in more reads from disk, especially in a reporting scenario where you have long-running read-write transactions/queries, where concurrent read/write requests generate a longer back record chain until it can be removed via co-operative GC (the only GC mode in CS). I have definitely never used the no reserve option. I wonder if it was a default on an earlier version of the server that just carried over. I'll use gfix to use reserve to at least deal with those tables that are emptied and overwritten regularly. No reserve, isn't and never wasn't the default. It can be changed via gfix, during a gbak restore or even through client applications and the services API. Are you using IBObjects by any chance? While gfix can be used to remove the no reserve thing, this doesn't change the layout of already allocated data pages. If you have a maintainence window, I would go with a backup/restore cycle to re-build the database with reserve (the default, btw, thus you don't have to provide anything special for that) from scratch. Might be a challenge for a 90GB database and a small maintenance window. That has been a problem for a very long time. Right now, a full backup/restore cycle is taking more than 24 hours, and at best we only have a 12 hour window at best on a Sunday. Hence the May 2009 creation date of the current DB. Ok. Another scenario/option could be: * Disconnect all users from the database * Install a trigger-based logging mechanism (I won't do a product plug here *g*) * Start the gbak backup * Allow users to connect again This way, all DML after the backup has been started will be captured in the source database and can be re-applied on the restored database at a later point. This could cut downtime a lot. Although, never did that in production, but it should work. A few tricks to shorten the offline window: * Run both, backup and restore through the services API. When using gbak, this can be done via the -service switch. This results in not going through the TCP stack, which can improve performance a lot. That's a good trick, but since we are backing up to a seperate server the gbak -b can't use the service switch. Since we are restoring locally on the second server I could use that switch, but instead we are using the embedded gbak. Using embedded is definitely faster than regular gbak -c, but I'm curious as to whether -service is faster. I would assume that they are probably about the same. Don't have any concrete numbers, but to improve the backup process, you should backup to the local server, if this is possible, and then move the backup file to the remote server. * Backup the database with the -g option, because this suppress garbage collection in the source database This is standard practice when planning on replacing the database. * If enough RAM is available, restore the database with a MUCH higher page buffers value as 2048, because this can speed up index creation during a restore a lot. E.g. 10, with a page size of 8K, this means ~800MB RAM for the page cache for this single restore connection only. Use it with caution and don't forget to set it to the original value after the restore!!! Good suggestion, I'm going to try that tonight. * If you have a spare SSD, even if it is only a cheap consumer SSD, make use of it for both, backup and restore. Unfortunately it's a corporate datacenter with fixed configurations, so no goodies like SSD's. 3:) As you are talking about reporting, make use of read-only transactions. Even better would be a combination of read-only transaction in read committed isolation mode, but read committed
Re: [firebird-support] fb_sort error
Hello, Strange stuff from firebird I am hoping to get some insight on...please bear with me while I describe the problem! I've run into a problem with a firebird installation that is using a 3rd party report viewer to display crystal reports from a firebird db on client PCs...this is a windows environment. the text of the error quoted here is from the sql trace log: DIAG [HY000] [ODBC Firebird Driver][Firebird]I/O error for file C:\WINDOWS\TEMP\fb_sort_## Error while trying to open file The data is invalid. (-902) And it seems to relate to this error message in the FB docs: -902 335544344 io_error I/O error for file @2 The best explanation I have found in the bing/google-verse is from another post on this group: Firebird creates sort files in the assigned temp directory if you don't have enough space in memory for them. Code 335544344 is an i/o error, either you are out of space on your C drive or Firebird can't access your temp directory for some reason. In due diligence the drive that c:\windows\temp is on has ~50GB free; EVERYONE has been given all privileges to the folder; And I can't fathom why Firebird wouldn't be able to access the folder for any reason. The server is operating on another machine under default conf...meaning everything in the file was commented. What does: The sever is operating on another machine ...? The above mentioned directory must be local to the Firebird server and not to the client. Just in case ... -- With regards, Thomas Steinmaurer http://www.upscene.com/
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-17 16:23, Thomas Steinmaurer wrote: Look here: http://www.firebirdnews.org/?p=5027 Yep, that looks exactly like my case. Unfortunately, I'm not able to re-create users by hand because of the scale of the problem (and because I don't know their passwords, and resetting them means me looking for another job ;) ). But, I can't find the mentioned upgrade sql script whether in 2.5.1 nor in 2.5.2. Neither do I. There's only the standard pre-2.0-to-2.0 security upgrade script. If there is such script available and someone could post a link to it, please please do. There's one more thing I've tried. I copied security2.fdb to a new location and connected to it. Then I dumped the contents of RDB$USERS as inserts (via FlameRobin) and tried to insert the users into a fresh security2.fdb taken from a clean 2.5.1 installation. To my surprise, the very first insert failed with string truncation... error. It turned out the encrypted passwords in my old security database were 80 characters long, while the field RDB$PASSWD in the new security2.fdb is varchar(64). What's really surprising is that in my _OLD_ security DB this field is also varchar(64) (according to FlameRobin) and it stores 80-char strings without complaints. So, finding a way to transfer users (with passwords) from an existing security2.fdb to a new one would also be helpful in solving my problem. If anyone has tried that and succeeded, please post some hints. And thank you Thomas for finding this news post - your googling seems better than mine ;) regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-17 16:23, Thomas Steinmaurer wrote: Look here: http://www.firebirdnews.org/?p=5027 Yep, that looks exactly like my case. Unfortunately, I'm not able to re-create users by hand because of the scale of the problem (and because I don't know their passwords, and resetting them means me looking for another job ;) ). But, I can't find the mentioned upgrade sql script whether in 2.5.1 nor in 2.5.2. Neither do I. There's only the standard pre-2.0-to-2.0 security upgrade script. If there is such script available and someone could post a link to it, please please do. If you are brave, you could go into firebird-devel with that. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com/
RE: [firebird-support] Can non-sysdba really alter users?
Yep, that looks exactly like my case. Unfortunately, I'm not able to re-create users by hand because of the scale of the problem (and because I don't know their passwords, and resetting them means me looking for another job ;) ). You can pump the data to another database shell without touching or knowing the passwords Alan
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hello Bob, Is there any way to tell if the sweep was successful other than all of the markers matching? Is there any way to tell why a sweep would have failed? No. You should manually check transactions' markers difference, or use tool like FBDataGuard which can check it in desired time and send warning. I'm running the sweep from a batch file, but never thought to check the errorlevel after completion... errorlevel is not related with sweep results. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) [Non-text portions of this message have been removed]
Re: [firebird-support] Can non-sysdba really alter users?
Alan, I've already tried that. I described it in my previous post. The problem is the password ciphers seem too long compared to the RDB$PASS data type. I could try to change the type of RDB$PASS, but I don't know if it's safe to mess with the domains in security DB. Tomasz On 2012-09-18 08:50, Alan McDonald wrote: Yep, that looks exactly like my case. Unfortunately, I'm not able to re-create users by hand because of the scale of the problem (and because I don't know their passwords, and resetting them means me looking for another job ;) ). You can pump the data to another database shell without touching or knowing the passwords Alan -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
[firebird-support] firebird 2.5 superclassic hanging on ubuntu
I am running firebird 2.5.1 super-classic on ubuntu server 12.04 Firebird/linux AMD64 (access method), version LI-V2.5.1.26351 Firebird 2.5 on disk structure version 11.1 The problem I am experiencing is that Firebird randomly hangs every couple of days, I need to do a service restart to get it going again, all I see in the logs is this: ubuntufirebird Tue Sep 18 09:18:49 2012 Shutting down the server with 3 active connection(s) to 1 database(s), $ ubuntufirebird (Client) Tue Sep 18 09:18:52 2012 /usr/sbin/fbguard: /usr/sbin/fb_smp_server killed (did not terminate) ubuntufirebird (Client) Tue Sep 18 09:18:52 2012 /usr/sbin/fbguard: guardian starting /usr/sbin/fb_smp_server Any suggestions please? Thanks Gary
Re: [firebird-support] Can non-sysdba really alter users?
On Tue, 18 Sep 2012 09:16:30 +0100, Tomasz Tyrakowski t.tyrakow...@sol-system.pl wrote: Thomas, On 2012-09-18 08:47, Thomas Steinmaurer wrote: Neither do I. There's only the standard pre-2.0-to-2.0 security upgrade script. If there is such script available and someone could post a link to it, please please do. If you are brave, you could go into firebird-devel with that. ;-) I'll give it a shot (just requested joining firebird-devel). By the way, could you spend half a minute and look into your security2.fdb to check if your varchar(64) RDB$USERS.RDB$PASS field also contains 80-character long ciphers? That's kind of intriguing, isn't it? What is your connection characterset? What happens if you connect using UTF8 or UNICODE_FSS? Mark
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-18 08:47, Thomas Steinmaurer wrote: Neither do I. There's only the standard pre-2.0-to-2.0 security upgrade script. If there is such script available and someone could post a link to it, please please do. If you are brave, you could go into firebird-devel with that. ;-) I'll give it a shot (just requested joining firebird-devel). By the way, could you spend half a minute and look into your security2.fdb to check if your varchar(64) RDB$USERS.RDB$PASS field also contains 80-character long ciphers? That's kind of intriguing, isn't it? 40 bytes in my case. -- With regards, Thomas Steinmaurer http://www.upscene.com/
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-18 09:39, Mark Rotteveel wrote: could you spend half a minute and look into your security2.fdb to check if your varchar(64) RDB$USERS.RDB$PASS field also contains 80-character long ciphers? That's kind of intriguing, isn't it? What is your connection characterset? What happens if you connect using UTF8 or UNICODE_FSS? In fact it's NONE. Usually I use WIN1250, but when I tried to connect to a copy of security2.fdb with WIN1250, FlameRobin warned me that the database encoding is NONE, so I changed the connection encoding to match it. I've just tried UTF8 and UNICODE_FSS and I can still see 80-characters long ciphers. An example (I split the lines for the readability's sake - in the DB that's the content of the RDB$PASS in a single record): 45645867684b6f324465 3156324a354c4f514f59 71653550417572706147 78704a7569637650383d regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-18 08:50, Alan McDonald wrote: You can pump the data to another database shell without touching or knowing the passwords Alan I'm not quite sure the data itself is valid (and whether pumping it to another database will fix the problem). I suspect there might be something wrong with the entries of RDB$USER_PRIVILEGES in my security2.fdb. There are some combinations of RDB$USER, RDB$PRIVILEGE and RDB$GRANT_OPTION for RDB$RELATION_NAME='RDB$ADMIN' which may be missing. I'll try to compare the contents of this system table with a valid one (i.e. one which allows privileged users to alter other users) and maybe I'll come up with an idea how to write the upgrade script myself. regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
On 2012-09-18 08:47, Thomas Steinmaurer wrote: If you are brave, you could go into firebird-devel with that. ;-) Just got an answer on firebird-devel. I'll try the solution proposed by Dmitry Yemanov and post a summary in the afternoon. regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
Problem solved, thanks to Thomas Steinmaurer and Dmitry Yemanov. Here's what to do when you experience problems regarding users with elevated privileges not being able to alter other users via SQL. 1. Disconnect all clients from Firebird. 2. Copy security2.fdb to another location. 3. Connect to the copy of security2.fdb. 4. Issue ALTER VIEW USERS (USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PASSWD, PRIVILEGE, COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME, FULL_NAME) AS SELECT RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD, RDB$PRIVILEGE, RDB$COMMENT, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME, COALESCE (RDB$first_name || _UNICODE_FSS ' ', '') || COALESCE (RDB$middle_name || _UNICODE_FSS ' ', '') || COALESCE (RDB$last_name, '') FROM RDB$USERS WHERE CURRENT_USER = 'SYSDBA' OR CURRENT_ROLE = 'RDB$ADMIN' OR CURRENT_USER = RDB$USERS.RDB$USER_NAME; 5. Disconnect from the copied security2.fdb and copy it back to the Firebird folder. Thanks again guys for your help. The cause of all trouble was the lack of the OR CURRENT_ROLE = 'RDB$ADMIN' condition in the original view definition. regards Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
Re: [firebird-support] Can non-sysdba really alter users?
Thanks for posting the summary, Tomasz. Excellent job of sleuthing! I'm glad you figured it out in the end. Doug C.
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
Alexey From: Alexey Kovyazin [mailto:a...@ib-aid.com] Is there any way to tell if the sweep was successful other than all of the markers matching? Is there any way to tell why a sweep would have failed? No. You should manually check transactions' markers difference, or use tool like FBDataGuard which can check it in desired time and send warning. And what happens if the transaction markers don't align after a sweep? Bob M.. [Non-text portions of this message have been removed] ++ 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hello Bob, Is there any way to tell if the sweep was successful other than all of the markers matching? Is there any way to tell why a sweep would have failed? No. You should manually check transactions' markers difference, or use tool like FBDataGuard which can check it in desired time and send warning. And what happens if the transaction markers don't align after a sweep? It's an indication that you have tons of garbage in your database, and you'll have slowness soon. To have more details about garbage, you can gather gstat -r statistics after failed sweep and analyse it - in text form or graphically with IBAnalyst. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) [Non-text portions of this message have been removed]
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
Here's a related question for you - as I looked at our script for doing nightly backups, I see a note that says: do not use garbage collection (gbak -g) since we run a manual sweep every night Do you know if that's true - we don't need to do garbage collection via gbak if we are running gfix -sweep? Thanks, Bob M.. ++ 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Here's a related question for you - as I looked at our script for doing nightly backups, I see a note that says: do not use garbage collection (gbak -g) since we run a manual sweep every night Do you know if that's true - we don't need to do garbage collection via gbak if we are running gfix -sweep? Correct. If the backup acts as an immediate replacement for the source database after a restore, you also don't need to run a sweep on the source database. -- With regards, Thomas Steinmaurer http://www.upscene.com/ ++ 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re-8: [firebird-support] Restore fails due to duplicate Value in unique Index
Hello there, the problem persists. WTF is this? - doing a GBAK backup shows NO ERRORS! - GFIX shows NO ERRORS! - FBFirstAid shows NO ERRORS! - validating the Database via IBExpert shows NO ERRORS! - the Database is working fine! - there are NO duplicated Values in that RDB$COLLATIONS Table (i took an old Backup, restored it, and there were 148 entrys in that table, just like now) - Backup/Restore via nbackup is working fine! This problem is really getting very anoying! btw: Doing a restore in IBExpert shows a bit more information: IBE: Creating log file R:\RESTORE_VERBOSE.txt IBE: Starting restore. Current time: 12:59:34 IBE: Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values. attempt to store duplicate value (visible to active transactions) in unique index RDB$INDEX_20. IBE: Restore completed. Current time: 12:59:36. Elapsed time: 00:00:02 Is there any helpful information? btw2: dropping that RDB$INDEX_20 didn`t help anything. Mit freundlichen Grüßen aus der Lutherstadt Maik Sommer IT-Systemadministrator processed by David.fx Subject: Re: Re-6: [firebird-support] Restore fails due to duplicate Value in unique Index (13-Sep-2012 17:46) From:Alexey Kovyazin a...@ib-aid.com To: firebird-support@yahoogroups.com Hello Maik, database, stopped the default instance and made a copy to a different volume, so i have a database i can Analyse. Currently, i made another copy, that i use to Analyse it with IBFirstAid 2.6. This is a database corruption, but our IBFirstAID will not help in this case, since it's a system index problem. You can contact support at ib-aid.com to get professional recovery support through remote desktop. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) database, stopped the default instance and made a copy to a different volume, so i have a database i can Analyse. Currently, i made another copy, that i use to Analyse it with IBFirstAid 2.6. I think this is a very weird issue Mit freundlichen Grüßen aus der Lutherstadt Maik Sommer IT-Systemadministrator processed by David.fx Subject: Re: Re-4: [firebird-support] Restore fails due to duplicate Value in unique Index (13-Sep-2012 14:29) From: Thomas Steinmaurer t...@iblogmanager.com mailto:ts%40iblogmanager.com To: firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com The Result is: RDB$RELATION_NAMERDB$FIELD_NAME RDB$COLLATIONSRDB$COLLATION_NAME Thought so and that's weird. And you have executed both queries in the database the faulty backup is based on? Regards, Thomas Regards Maik Sommer IT-Systemadministrator processed by David.fx Subject: Re: Re-2: [firebird-support] Restore fails due to duplicate Value in unique Index (13-Sep-2012 14:13) From: Thomas Steinmaurer t...@iblogmanager.com mailto:ts%40iblogmanager.com To: firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com Thanks for your quick response. The query select rdb$collation_name from rdb$collations group by rdb$collation_name having count(*) 1 results in: RDB$COLLATION_NAME NULL And what's the result of: select i.rdb$relation_name , s.rdb$field_name from rdb$indices i join rdb$index_segments s on (i.rdb$index_name = s.rdb$index_name) where i.rdb$index_name = 'RDB$INDEX_20' Regards, Thomas Greetings from Lutherstadt Wittenberg :-) Maik Sommer IT-Systemadministrator processed by David.fx Subject: Re: [firebird-support] Restore fails due to duplicate Value in unique Index (13-Sep-2012 14:03) From: Thomas Steinmaurer t...@iblogmanager.com mailto:ts%40iblogmanager.com To: firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com i have a problem with my 27Gb FB2.5 Database. Doing a backup is workíng fine. Doing a restore is failing due to the following error: gbak: ERROR:attempt to store duplicate value (visible to active transactions) in unique index RDB$INDEX_20 gbak:Exiting before completion due to errors Here`s the clue: The database is working fine. There are no duplicated roles, no duplicated datasets, index-reorg is working fine, gfix could`t find ANY errors. I tried to find out, which relation RDB$INDEX_20 has. It refers to a table named Abrechnung, Column named RUECKBUCHUNG. But there are no duplicated values. In fact, Column RUECKBUCHUNG is always empty (IS NULL). Any Ideas? IMHO, RDB$INDEX_20 is an index on the RDB$COLLATIONS system table. This can be proved by: select i.rdb$relation_name , s.rdb$field_name from rdb$indices i join rdb$index_segments s on (i.rdb$index_name = s.rdb$index_name) where i.rdb$index_name = 'RDB$INDEX_20' So, basically this means, there are duplicate values in RDB$COLLATIONS.RDB$COLLATION_NAME. What do you get by executing the following
Re: [firebird-support] Number of instances of firebird
Thanks for the reply and I wouldn't consider my self an expert with databases. I have worked on teams responsible for database design and have done some small to somewhat complex queries, triggers and such. My recomendation to the client is that they store all of there data in a centralized database using a client server database. But all of there data, some of which wont be moved to the database is stored on shared network drives where the path to the files contains information pertinant to the data. That and there back up process was developed in such a manner to work with the network directories. They know that there system doesn't work but want to make the most simplisest of changes to the process as possible. Right now they have some data stored in large XML files in the directories which have become a bear to handle so they want me to move the data into database files, I'm still not sure they even know what they mean by database files. I could think of a lot of reason why it shouldn't be down but none as to why it couldn't be done. My skills are more system's development C, C++ etc my work with database has been enough just to get the job done. John From: Helen Borrie hele...@iinet.net.au To: firebird-support@yahoogroups.com Sent: Monday, September 17, 2012 4:08 PM Subject: Re: [firebird-support] Number of instances of firebird At 05:34 AM 18/09/2012, John Wilk wrote: Hi all I have a client who has software wich runs a test in which the software collects data. What my client would like to do is be able to store each test's data in an independent database located on a network share. Not on a network share...that is not supported by Firebird for read/write databases. Firebird server is a database management system that lives on a host server and manages one or more databases on the same physical host. Clients, such as your data-collecting application, attach to a database across a network, using a network protocol. In theory, there's no reason why each test couldn't have its own database. In practice, why should that be necessary? What is special about the requirements, that sets of results need to be isolated from one another? Does your customer understand the differences between a table and a database? Does [s]he really want to be administering multiple databases unnecessarily? This is a little beyound me and something I had never really thought about if anyone has any info or suggestions where I could look for info. It's not clear whether you are already a database developer yourself. If you haven't worked with client/server systems before, a good place to start would be the Quick Start Guide for the version of Firebird that you are using. You can find it in the \doc\ directory of a standard Firebird server installation; otherwise you can pick it up from the documentation pages at the Firebird website. My books (latest version The Firebird Book Second Edition) devote quite a lot of space to explaining how the various client/server models work. If you feel you need that, visit http://www.ibphoenix.com/products/books/firebird_book and consider buying the Developer DVD, on which it is distributed. ./heLen [Non-text portions of this message have been removed]
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas Steinmaurer Here's a related question for you - as I looked at our script for v doing nightly backups, I see a note that says: do not use garbage collection (gbak -g) since we run a manual sweep every night Do you know if that's true - we don't need to do garbage collection via gbak if we are running gfix -sweep? Correct. If the backup acts as an immediate replacement for the source database after a restore, you also don't need to run a sweep on the source database. But what if you are *not* replacing the database? ++ 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
[firebird-support] Firebird 2.5.1 to 2.5.2
We are having an issue with caching when using Firebird 2.5.1 on a Windows Server 2008 box. The system flies when turning forced writes off, but we don't want to operate that way. The strange thing is that we have a number of clients using 2.5.1 on Windows Server 2008 and nobody has brought up performance issues. Anyhow, we were reading about 2.5.2 and how there was a problem with caching that was fixed. We are going to try the beta version that is out. Do we need to backup on 2.5.1 and restore to 2.5.2? Any help with this issue would be appreciated. Todd Brasseur Compass Municipal Services Inc. HOME OF CAMAlot
RES: [firebird-support] Firebird 2.5.1 to 2.5.2
The database is bigger than the RAM memory? If yes this is an issue of 2.5.1 You do not need a backup/restore cycle for migrating to 2.5.2 Fabiano De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Em nome de todderamaa Enviada em: terça-feira, 18 de setembro de 2012 12:22 Para: firebird-support@yahoogroups.com Assunto: [firebird-support] Firebird 2.5.1 to 2.5.2 We are having an issue with caching when using Firebird 2.5.1 on a Windows Server 2008 box. The system flies when turning forced writes off, but we don't want to operate that way. The strange thing is that we have a number of clients using 2.5.1 on Windows Server 2008 and nobody has brought up performance issues. Anyhow, we were reading about 2.5.2 and how there was a problem with caching that was fixed. We are going to try the beta version that is out. Do we need to backup on 2.5.1 and restore to 2.5.2? Any help with this issue would be appreciated. Todd Brasseur Compass Municipal Services Inc. HOME OF CAMAlot _ Nenhum vírus encontrado nessa mensagem. Verificado por AVG - www.avgbrasil.com.br Versão: 2012.0.2221 / Banco de dados de vírus: 2437/5275 - Data de Lançamento: 09/18/12 [Non-text portions of this message have been removed]
[firebird-support] Re: Firebird 2.5.1 to 2.5.2
18.09.2012 19:21, todderamaa wrote: Do we need to backup on 2.5.1 and restore to 2.5.2? In this particular case, you do need backup/restore. Dmitry
RES: [firebird-support] Re: Firebird 2.5.1 to 2.5.2
Do we need to backup on 2.5.1 and restore to 2.5.2? In this particular case, you do need backup/restore. Dmitry __._,_._ I´m curious Dmitry, why? __ [Non-text portions of this message have been removed]
Re: RES: [firebird-support] Firebird 2.5.1 to 2.5.2
Finished backing up and restoring. Same issue with 2.5.2. You can hear the server writing to disk constantly when running the software. As I mentioned, we have a number of installations (approx 60) of the same database and software and no complaints from other clients about performance. We have never changed any of the configuration options in Firebird. Really confused why this one server is having this issue. Todd --- In firebird-support@yahoogroups.com, todderamaa todderamaa@... wrote: We connect to two databases. One that stores images and one that stores data. The images database is 4.5 gigs. The data database is 600 mb. There is 16 gigs of memory. So I am guessing 2.5.2 won't be solving this problem. Still doing the test though, so I will let you know how it goes. Any other ideas what to look for? Todd --- In firebird-support@yahoogroups.com, Fabiano fabianoaspro@ wrote: The database is bigger than the RAM memory? If yes this is an issue of 2.5.1 You do not need a backup/restore cycle for migrating to 2.5.2 Fabiano De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Em nome de todderamaa Enviada em: terça-feira, 18 de setembro de 2012 12:22 Para: firebird-support@yahoogroups.com Assunto: [firebird-support] Firebird 2.5.1 to 2.5.2 We are having an issue with caching when using Firebird 2.5.1 on a Windows Server 2008 box. The system flies when turning forced writes off, but we don't want to operate that way. The strange thing is that we have a number of clients using 2.5.1 on Windows Server 2008 and nobody has brought up performance issues. Anyhow, we were reading about 2.5.2 and how there was a problem with caching that was fixed. We are going to try the beta version that is out. Do we need to backup on 2.5.1 and restore to 2.5.2? Any help with this issue would be appreciated. Todd Brasseur Compass Municipal Services Inc. HOME OF CAMAlot _ Nenhum vírus encontrado nessa mensagem. Verificado por AVG - www.avgbrasil.com.br Versão: 2012.0.2221 / Banco de dados de vírus: 2437/5275 - Data de Lançamento: 09/18/12 [Non-text portions of this message have been removed]
RES: RES: [firebird-support] Firebird 2.5.1 to 2.5.2
I have a guess! What is the database Server? Is a truly *Dell* server? (Like Dell PowerEdge T410) If yes, make sure the hardware that control the HD HAS cache! If it doesn´t have the problem is it! To solve that replace the disk controller or use another server [Non-text portions of this message have been removed]
Re: RES: RES: [firebird-support] Firebird 2.5.1 to 2.5.2
It is a Dell Server (Power Edge T110). How can I tell if the hardware that controls the HD has cache? Todd --- In firebird-support@yahoogroups.com, Fabiano fabianoaspro@... wrote: I have a guess! What is the database Server? Is a truly *Dell* server? (Like Dell PowerEdge T410) If yes, make sure the hardware that control the HD HAS cache! If it doesn´t have the problem is it! To solve that replace the disk controller or use another server [Non-text portions of this message have been removed]
RES: RES: RES: [firebird-support] Firebird 2.5.1 to 2.5.2
Maybe you can go to the Dell website and check this. In Portuguese is controladora SAS6/IR (suporta apenas RAID 0 e 1), sem cachê). Or SAS6/IR controller with no cache. Maybe you can call them and check this (sorry I have no internet connection) De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Em nome de todderamaa Enviada em: terça-feira, 18 de setembro de 2012 14:14 Para: firebird-support@yahoogroups.com Assunto: Re: RES: RES: [firebird-support] Firebird 2.5.1 to 2.5.2 It is a Dell Server (Power Edge T110). How can I tell if the hardware that controls the HD has cache? Todd --- In firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com , Fabiano fabianoaspro@... wrote: I have a guess! What is the database Server? Is a truly *Dell* server? (Like Dell PowerEdge T410) If yes, make sure the hardware that control the HD HAS cache! If it doesn´t have the problem is it! To solve that replace the disk controller or use another server [Non-text portions of this message have been removed] _ Nenhum vírus encontrado nessa mensagem. Verificado por AVG - www.avgbrasil.com.br Versão: 2012.0.2221 / Banco de dados de vírus: 2437/5275 - Data de Lançamento: 09/18/12 [Non-text portions of this message have been removed]
Re: RES: RES: [firebird-support] Firebird 2.5.1 to 2.5.2
I believe this is the problem. We found the setting when looking at the Device Manager. The 'enable write caching' is turned off. It won't allow us to turn it on. I am guess the hardware doesn't allow it. In the Windows Help it mentions that turning it on can cause issues with corruption. Are these the same issues as we have if we turn 'forced writes off' in Firebird? I am thinking the risk of 'forced writes off' in Firebird is greater than turning write caching on in Windows. Todd --- In firebird-support@yahoogroups.com, todderamaa todderamaa@... wrote: It is a Dell Server (Power Edge T110). How can I tell if the hardware that controls the HD has cache? Todd --- In firebird-support@yahoogroups.com, Fabiano fabianoaspro@ wrote: I have a guess! What is the database Server? Is a truly *Dell* server? (Like Dell PowerEdge T410) If yes, make sure the hardware that control the HD HAS cache! If it doesn´t have the problem is it! To solve that replace the disk controller or use another server [Non-text portions of this message have been removed]
RES: RES: RES: [firebird-support] Firebird 2.5.1 to 2.5.2
In our test we can turn on but there are no result in terms of performance. Is a hardware issue. I think changing the disk controller and turning write caching on does not have any problem is the default installation on Windows! De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Em nome de todderamaa Enviada em: terça-feira, 18 de setembro de 2012 14:31 Para: firebird-support@yahoogroups.com Assunto: Re: RES: RES: [firebird-support] Firebird 2.5.1 to 2.5.2 I believe this is the problem. We found the setting when looking at the Device Manager. The 'enable write caching' is turned off. It won't allow us to turn it on. I am guess the hardware doesn't allow it. In the Windows Help it mentions that turning it on can cause issues with corruption. Are these the same issues as we have if we turn 'forced writes off' in Firebird? I am thinking the risk of 'forced writes off' in Firebird is greater than turning write caching on in Windows. Todd --- In firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com , todderamaa todderamaa@... wrote: It is a Dell Server (Power Edge T110). How can I tell if the hardware that controls the HD has cache? Todd --- In firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com , Fabiano fabianoaspro@ wrote: I have a guess! What is the database Server? Is a truly *Dell* server? (Like Dell PowerEdge T410) If yes, make sure the hardware that control the HD HAS cache! If it doesn´t have the problem is it! To solve that replace the disk controller or use another server [Non-text portions of this message have been removed] _ Nenhum vírus encontrado nessa mensagem. Verificado por AVG - www.avgbrasil.com.br Versão: 2012.0.2221 / Banco de dados de vírus: 2437/5275 - Data de Lançamento: 09/18/12 [Non-text portions of this message have been removed]
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas Steinmaurer Here's a related question for you - as I looked at our script for v doing nightly backups, I see a note that says: do not use garbage collection (gbak -g) since we run a manual sweep every night Do you know if that's true - we don't need to do garbage collection via gbak if we are running gfix -sweep? Correct. If the backup acts as an immediate replacement for the source database after a restore, you also don't need to run a sweep on the source database. But what if you are *not* replacing the database? If you intend to run a sweep on the source database after a backup, you don't need to garbage collect in the source database during a backup, thus using gbak -g is fine. -- With regards, Thomas Steinmaurer http://www.upscene.com/ ++ 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
Re: RES: [firebird-support] Re: Firebird 2.5.1 to 2.5.2
Do we need to backup on 2.5.1 and restore to 2.5.2? In this particular case, you do need backup/restore. Dmitry __._,_._ I´m curious Dmitry, why? From the 2.5.2 RC1 release notes. Warning re Databases Created or Restored under Firebird 2.5.1 All users upgrading from Firebird 2.5.1 to a higher sub-release are strongly advised to migrate databases using gbak backup/restore. If this is impracticable, at least rebuild all compound indices in the databases being migrated. Databases being upgraded from older Firebird versions (ODS 11.1 and lower) or v.2.5.0 are not affected by this regression. -- With regards, Thomas Steinmaurer http://www.upscene.com/
Re: [firebird-support] Re: Desktop Application For Centralized Firebird Database...
I use zedebee with internet conection, bandwitdh drop only take 20% from direct connection, but it take processor time to compress and encrypt Terima Kasih Yanto Asnawi 087808244204 Pin BB : 2687C905 +whatsapp -Original Message- From: Kjell Rilbe kjell.ri...@datadia.se Sender: firebird-support@yahoogroups.com Date: Tue, 11 Sep 2012 21:56:24 To: firebird-support@yahoogroups.comfirebird-support@yahoogroups.com Reply-To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Re: Desktop Application For Centralized Firebird Database... Den 2012-09-09 06:20 skrev Vishal Tiwari såhär: But if I have internet not an Intranet facility, then also would it work ? Intranet or internet doesn't really matter re works or doesn't work. Internet has higher latency, which is nog very good for Firebird's chatty protocol, but it does work. You need to make sure all firewalls open port 3050 (or whatever port you choose for your FB server). You also need to make sure the clients know where the server is, i.e. its hostname or its IP address. This would normally be done either by using a regular domain name like yourcompany.com or if you don't have a domain name, simply make sure the server has a fixed IP address (most ISP:s provide this service at a low extra cost). Now, the data will travel over the internet unencrypted, so you either need to decide that this is no problem (not sensitive data) or establish a VPN or secure tunnel, e.g. using Zebedee as mentioned earlier. This might slow down the connection a bit further, depending on your situation and setup. If the data traffic is too slow, due to the combo of high latency internet and FB's chatty protocol, you can write an extra tier/layer/server thingy that executes on the same machine as the FB server and handles all communication with the actual database server. Your clients would talk to your server thingy instead, hopefully using a more efficient protocol. Kjell Vishal wrote: ... Here, my doubt is Using Delphi 5 and Firebird database, is it possible for all the instances of this application which are installed at different cities could access one centralized database ? If yes then HOW ? ... You need a fixed IP address for the machine which is hosting the database (the db server). Your program - wherever it runs - just need to use a connection string with IP address or machine name:database path and filename, or alias, e.g. 123.234.123.234:D:\DB\ourdb.fdb (or possibly VISHAL_SERVER:LIBRARY_DB, with necessary definitions in place). You may have to persuade administrators to open port 3050 in firewalls (in- and outbound). -- Aage J. [Non-text portions of this message have been removed] -- -- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
[firebird-support] Should i use UTF8 for all character fields in my database?
Hi all! Today it seems easier and simpler for me to create all character fields encoded in UTF8. Is there something against doing this? I asked some days ago about index sizes in this post (http://tech.groups.yahoo.com/group/firebird-support/message/119234), and it seems UTF8 can generate indices keys up to 4X larger than a usual key. As the majority of characters stored in these fields are below code 127 in ASCII table, with some rare accents and special symbols, i would like to know if using UTF8 should increase too much my database or affect its performance. Regards, Fabiano
Re: [firebird-support] Firebird Foreign Key + Unique Constraint ( One/Zero-To-One)
Carlos, Is it possible to combine a Foreign Key + a Unique Constraint to avoid duplicate index ? ALTER TABLE SVCs_BatchADD CONSTRAINT RF_SVCs_Batch2XDRs_Batch FOREIGN KEY (Id_XDRs_Batch) REFERENCES XDRs_Batch(Id_XDRs_Batch) using *UNIQUE* index FK_SVCs_Batch_XDRs_BATCH; this is not (yet) accepted ! Vote for it: http://tracker.firebirdsql.org/browse/CORE-651 -- With regards, Thomas Steinmaurer http://www.upscene.com/
