[firebird-support] How to convert to one charset to another
Hello, I need your help and suggestions to solve a (maybe uncommon) task. I searched a lot on the web, I had some success using a tool called fbclone but this is definitely not a solution (due to extreme slowness) I have a large Firebird 2.5 DB (a couple of tables have few millions records) to convert from WIN1251 charset to UTF8 charset. Is there a way to do efficiently this kind of conversion? (preferable not using a intermediate text sql dump, that would not applicable because the size of the DB). I'm using Firebird on a linux machine, and I really like to do the job using a bash script, but I can fallback also using a remote Windows pc. Thanks for any suggestions. Best regards. -- Nota di riservatezza : Il presente messaggio, corredato dei relativi allegati, contiene informazioni da considerarsi strettamente riservate, ed è destinato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato ad usarlo, copiarlo e, sotto la propria responsabilità, diffonderlo. Chiunque ricevesse questo messaggio per errore o comunque lo leggesse senza esserne legittimato è avvertito che trattenerlo, copiarlo, divulgarlo, distribuirlo a persone diverse dal destinatario è severamente proibito, ed è pregato di rinviarlo immediatamente al mittente distruggendone l'originale. Grazie. Confidentiality Notice : This message, together with its annexes, contains information to be deemed strictly confidential and is destined only to the addressee(s) identified above who only may use, copy and, under his/their responsibility, further disseminate it. If anyone received this message by mistake or reads it without entitlement is forewarned that keeping, copying, disseminating or distributing this message to persons other than the addressee(s) is strictly forbidden and is asked to transmit it immediately to the sender and to erase the original message received. Thank you.
Re: [firebird-support] Firebird 1.56 server freezing / hanging all clients (maybe during event handling)
Exactly the same problem here. I'm on a different platform (Debian stable x64, Delphi app with BDE, etc), so it's most probably not a platform/toolkit issue. Did you solve the problem? EB On 15/05/14 03:12, Paul Read p...@ntasktic.com [firebird-support] wrote: Summary: All clients freeze until FBServer is restarted Background: For the past 6 months plus, a couple of our customers have been experiencing our client application locking up for all users. We have spent considerable resources trying to understand the issue but have made little progress and are now reaching out to the Firebird community for suggestions on how to overcome or at least minimize occurrence of this issue Typical database installation: * SuperServer 1.5.6.5026 SuperServer running as a service with FBGuardian * Borland C++ 2007 VCL with IB Objects (8/25/2007 Sub-Release 4.8 Build 7) based client applications * Client application is basically single threaded event driven * Single FB database on server only * Typically a dedicated MS-Windows based machine for the database server * Each client has two connections to DB that are open for the life time of the app (1 for data, 1 for events) * Typically sub 3GB fdb file * Typically 3-20 clients per database installation 70+ installations across the globe work fine but 2 are suffering: * all connected clients freezing concurrently (within a minute of each other) * sometimes 3 or more times a day * sometimes can go 5-10 days without an issue then multiple freezes * clients remain locked up until FBServer is restarted at which point they suddenly spring into life reporting the connection is lost * Worse case the freeze can happen 10 mins or so after restarting the FBServer * restarting the server machine does not help the issue i.e. just as likely to happen as just restarting FBServer via Control Panel * FBServer at freeze point does not show high CPU or memory consumption in task manager * Waiting 10 mins and still the clients do not recover * Nothing unusual in MS-Windows event table * Nothing unusual in Firebird logs * No Anti-virus running on servers * No firewall running on servers * Full firewall exceptions given for client applications * Bizarrely seems to be far worse on Thursday and Fridays for one of the installations (no crash pattern yet noted at the other installation) * other client applications connected to the same database and using same build technology but with different exe names do not appear to be impacted (though they are far less intensive and so this may be misinformation) * Client application running directly on the server also freezes * Both these installations are running MS-Windows 7 as their servers (but so are working installations) * Seems to be event related, as a typical madExcept call stack at the point of freeze shows 'isc_que_events': 778af8ca +00e ntdll.dll NtWaitForSingleObject 75472f7b +05b ws2_32.dll WahReferenceContextByHandle 75476a25 +09c ws2_32.dll select 064b6d59 +069 fbclient.dll isc_que_events 0064e92e +06e nCall.exe Ib_components TIB_Component.CheckSession 00773157 +06f nCall.exe Ib_events TIB_Events.API_QueueEvents 007729d1 +09d nCall.exe Ib_events TIB_Events.RegisterEvents 0040ebf4 +7ec nCall.exe TSubscriber.cpp 287 +68 TSubscriberIBO.SetEvents 0040d40b +2eb nCall.exe TSubscriber.cpp 67 +9 TSubscriber.AddEvent 004bf350 +fa8 nCall.exe TdmMain.cpp 400 +170 TdmMain.Connect We have tried: * Backup and restore has no benefit with freezes experienced within a few hours of a backup and restore * Daily restart of the database server - no effect * The freeze is not related to performing a backup * The freeze is not related to performing an intense job * Clients on static and dynamic IPs - no effect * Firewall exceptions have been verified at both installations with program exceptions for the clients * RemoteAuxPort values of 0, 3051, 4 tried with no noticeable benefit * We've tried registering for more events * We've tried ensuring an event is generated every minute (in an attempt to keep the event port open in case it is being closed through lack of use) * 3rd party network experts tell us there are no networking working issues being experienced by other applications * Users have suggested that the client application normally slows down before the freezing (not verified) * FBClient.dlls 1.52 and 1.56 (including ensuring all clients are using same version) Having read many postings regarding similar issues, only these two appear useful but don't indicate any real solutions or if the issue has been resolved in a later version of Firebird. * https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/75364 * https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/75366 Suggestions on what to try next sought! Paul
Re: [firebird-support] Performance of Stored Procedures versus execute blocks
On Sun, May 18, 2014 at 6:16 AM, laf...@xietel.com [firebird-support] firebird-support@yahoogroups.com wrote: 4) SQL statements run as byte code in a “virtual machine” and are just as as speed efficient(if not more so) at data and sting manipulation (Casting, concatenation, substing and replace) than other byte coded languages such as java, python etc. Regards, Lafras For #4 we had a stored procedure that did some string manipulation and calculation routines that was called many times by other stored procedures and triggers. We moved it out to a UDF written in C and saw a large performance increase. -steve -- Steve Wiser President Specialized Business Software 6325 Cochran Road, Unit 1 Solon, OH 44139 www.specializedbusinesssoftware.com www.docunym.com (440) 542-9145 - fax (440) 542-9143 Toll Free: (866) 328-4936
Re: [firebird-support] Firebird 1.56 server freezing / hanging all clients (maybe during event handling)
No. No further to resolving this issue. It maybe a network issue that is causing a serious problem for Firebird 1.56 but that is only a guess. Even more bizzare - introducing a VPN connection into the network with a client app connected over the VPN and the freezes don't happen (though only 3 days clear running so far). Paul On 20/05/2014 10:51, Ernesto Benestante ebenesta...@gmx.net [firebird-support] wrote: Exactly the same problem here. I'm on a different platform (Debian stable x64, Delphi app with BDE, etc), so it's most probably not a platform/toolkit issue. Did you solve the problem? EB On 15/05/14 03:12, Paul Read p...@ntasktic.com [firebird-support] wrote: Summary: All clients freeze until FBServer is restarted Background: For the past 6 months plus, a couple of our customers have been experiencing our client application locking up for all users. We have spent considerable resources trying to understand the issue but have made little progress and are now reaching out to the Firebird community for suggestions on how to overcome or at least minimize occurrence of this issue Typical database installation: * SuperServer 1.5.6.5026 SuperServer running as a service with FBGuardian * Borland C++ 2007 VCL with IB Objects (8/25/2007 Sub-Release 4.8 Build 7) based client applications * Client application is basically single threaded event driven * Single FB database on server only * Typically a dedicated MS-Windows based machine for the database server * Each client has two connections to DB that are open for the life time of the app (1 for data, 1 for events) * Typically sub 3GB fdb file * Typically 3-20 clients per database installation 70+ installations across the globe work fine but 2 are suffering: * all connected clients freezing concurrently (within a minute of each other) * sometimes 3 or more times a day * sometimes can go 5-10 days without an issue then multiple freezes * clients remain locked up until FBServer is restarted at which point they suddenly spring into life reporting the connection is lost * Worse case the freeze can happen 10 mins or so after restarting the FBServer * restarting the server machine does not help the issue i.e. just as likely to happen as just restarting FBServer via Control Panel * FBServer at freeze point does not show high CPU or memory consumption in task manager * Waiting 10 mins and still the clients do not recover * Nothing unusual in MS-Windows event table * Nothing unusual in Firebird logs * No Anti-virus running on servers * No firewall running on servers * Full firewall exceptions given for client applications * Bizarrely seems to be far worse on Thursday and Fridays for one of the installations (no crash pattern yet noted at the other installation) * other client applications connected to the same database and using same build technology but with different exe names do not appear to be impacted (though they are far less intensive and so this may be misinformation) * Client application running directly on the server also freezes * Both these installations are running MS-Windows 7 as their servers (but so are working installations) * Seems to be event related, as a typical madExcept call stack at the point of freeze shows 'isc_que_events': 778af8ca +00e ntdll.dll NtWaitForSingleObject 75472f7b +05b ws2_32.dll WahReferenceContextByHandle 75476a25 +09c ws2_32.dll select 064b6d59 +069 fbclient.dll isc_que_events 0064e92e +06e nCall.exe Ib_components TIB_Component.CheckSession 00773157 +06f nCall.exe Ib_events TIB_Events.API_QueueEvents 007729d1 +09d nCall.exe Ib_events TIB_Events.RegisterEvents 0040ebf4 +7ec nCall.exe TSubscriber.cpp 287 +68 TSubscriberIBO.SetEvents 0040d40b +2eb nCall.exe TSubscriber.cpp 67 +9 TSubscriber.AddEvent 004bf350 +fa8 nCall.exe TdmMain.cpp 400 +170 TdmMain.Connect We have tried: * Backup and restore has no benefit with freezes experienced within a few hours of a backup and restore * Daily restart of the database server - no effect * The freeze is not related to performing a backup * The freeze is not related to performing an intense job * Clients on static and dynamic IPs - no effect * Firewall exceptions have been verified at both installations with program exceptions for the clients * RemoteAuxPort values of 0, 3051, 4 tried with no noticeable benefit * We've tried registering for more events * We've tried ensuring an event is generated every minute (in an attempt to keep the event port open in case it is being closed through lack of use) * 3rd party network experts tell us there are no networking working issues being experienced by other applications * Users have suggested that the client application normally slows down before the freezing (not verified) * FBClient.dlls 1.52 and 1.56 (including ensuring all clients are using same version) Having read many postings regarding similar issues, only these two appear useful
Re: [firebird-support] Re: database became broken for any reasons
On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com wrote: For preventing orphan pages or backversions I turn off autosweep. So for the last month I don't see any orphan pages or orphan backversions. Probably you were shutting the database down hard while a sweep was active. I know that shutdown during sweep can broke database, so I turn off autosweep and do only manual sweep. I've got to take exception to your statement. Shutdown during sweep doesn't break the database. It leaves some space (record or page) inaccessible, but, assuming you're running with forced writes turned on, the database is not corrupt. All the data is accessible and correct. My service of database looks like this: 1) try normally close all connections(all my applications get commands for starting of autoclose procedure) OK 2) kill all terminal instance of my applications OK, though that will leave errors in the log - but you caused them, so not a big deal. 3) shutdown database(my software use non root access, so it's guarantee single user access) OK. 4) killing all processes fb_inet_server.exe Not OK. Once the connections are gone, the fb_inet_servers will eventually stop, after they've written all their changes to disk. If you kill them, there's a chance that, for example, an old record version will have been removed and the page it was on will have been written, but the older versions that are chained to that record will not have been removed and their pages flushed to disk. Firebird uses a technique called careful write to maintain on-disk consistency without a separate log. Essentially that means that when new things are created, the thing is created first, then pointer to it follow. Conversely, when a thing is removed, all the pointers are removed first, then the thing itself. When done correctly and consistently, careful writes never leave broken pointers. However, it can lead to lost space. For example, when a table needs a new data page, Firebird looks for a free page - they're indicated on pages called PageInformationPages or PIPs. When it finds a free page on a PIP, Firebird marks the page as in use, before it starts the process of allocating that page to the table, If the operation is interrupted, the PIP will say that the page is in use, but it's not part of a table or any other part of the database - hence, orphaned. Orphans are a normal and benign result of a hard database shutdown with write pending. Good luck, Ann
Re: [firebird-support] Re: database became broken for any reasons
I like very much the way Ann explains, almost impossible to be more clear. Greetings. Walter. On Tue, May 20, 2014 at 10:47 AM, Ann Harrison aharri...@ibphoenix.com[firebird-support] firebird-support@yahoogroups.com wrote: On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com[firebird-support] firebird-support@yahoogroups.com wrote: For preventing orphan pages or backversions I turn off autosweep. So for the last month I don't see any orphan pages or orphan backversions. Probably you were shutting the database down hard while a sweep was active. I know that shutdown during sweep can broke database, so I turn off autosweep and do only manual sweep. I've got to take exception to your statement. Shutdown during sweep doesn't break the database. It leaves some space (record or page) inaccessible, but, assuming you're running with forced writes turned on, the database is not corrupt. All the data is accessible and correct. My service of database looks like this: 1) try normally close all connections(all my applications get commands for starting of autoclose procedure) OK 2) kill all terminal instance of my applications OK, though that will leave errors in the log - but you caused them, so not a big deal. 3) shutdown database(my software use non root access, so it's guarantee single user access) OK. 4) killing all processes fb_inet_server.exe Not OK. Once the connections are gone, the fb_inet_servers will eventually stop, after they've written all their changes to disk. If you kill them, there's a chance that, for example, an old record version will have been removed and the page it was on will have been written, but the older versions that are chained to that record will not have been removed and their pages flushed to disk. Firebird uses a technique called careful write to maintain on-disk consistency without a separate log. Essentially that means that when new things are created, the thing is created first, then pointer to it follow. Conversely, when a thing is removed, all the pointers are removed first, then the thing itself. When done correctly and consistently, careful writes never leave broken pointers. However, it can lead to lost space. For example, when a table needs a new data page, Firebird looks for a free page - they're indicated on pages called PageInformationPages or PIPs. When it finds a free page on a PIP, Firebird marks the page as in use, before it starts the process of allocating that page to the table, If the operation is interrupted, the PIP will say that the page is in use, but it's not part of a table or any other part of the database - hence, orphaned. Orphans are a normal and benign result of a hard database shutdown with write pending. Good luck, Ann
Re: [firebird-support] Re: database became broken for any reasons
I turn off automatic sweep - only manual sweep. For today I try to change keepalive parameters - it's look like all ok. All processes named fb_inet_server.exe closed, but it's work if connection was lost. It's not work when the connection established and not closed for any reasons(for example the application waiting for some system process on client side and so don't close connection and don't react on my commands from server side). So how I can from server side close connection normally, so after that I may shutdown database and it's not create orphan records or pages? I use shutdown for absolute prohibition of connection to database using user accounts for database update process(changing metadata or changing only data). 20.05.2014 18:47, Ann Harrison aharri...@ibphoenix.com [firebird-support] пишет: On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com mailto:markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com wrote: For preventing orphan pages or backversions I turn off autosweep. So for the last month I don't see any orphan pages or orphan backversions. Probably you were shutting the database down hard while a sweep was active. I know that shutdown during sweep can broke database, so I turn off autosweep and do only manual sweep. I've got to take exception to your statement. Shutdown during sweep doesn't break the database. It leaves some space (record or page) inaccessible, but, assuming you're running with forced writes turned on, the database is not corrupt. All the data is accessible and correct. My service of database looks like this: 1) try normally close all connections(all my applications get commands for starting of autoclose procedure) OK 2) kill all terminal instance of my applications OK, though that will leave errors in the log - but you caused them, so not a big deal. 3) shutdown database(my software use non root access, so it's guarantee single user access) OK. 4) killing all processes fb_inet_server.exe Not OK. Once the connections are gone, the fb_inet_servers will eventually stop, after they've written all their changes to disk. If you kill them, there's a chance that, for example, an old record version will have been removed and the page it was on will have been written, but the older versions that are chained to that record will not have been removed and their pages flushed to disk. Firebird uses a technique called careful write to maintain on-disk consistency without a separate log. Essentially that means that when new things are created, the thing is created first, then pointer to it follow. Conversely, when a thing is removed, all the pointers are removed first, then the thing itself. When done correctly and consistently, careful writes never leave broken pointers. However, it can lead to lost space. For example, when a table needs a new data page, Firebird looks for a free page - they're indicated on pages called PageInformationPages or PIPs. When it finds a free page on a PIP, Firebird marks the page as in use, before it starts the process of allocating that page to the table, If the operation is interrupted, the PIP will say that the page is in use, but it's not part of a table or any other part of the database - hence, orphaned. Orphans are a normal and benign result of a hard database shutdown with write pending. Good luck, Ann
[firebird-support] Updating Firebird
Hello. I want to update Firebird from 2.5.2.26539 to 2.5.2.26540. Should I close all databases and stop de Firebird server? -- Atenciosamente, Hugo Eyng
Re: [firebird-support] Updating Firebird
Hi 1 backup your database 2 uninstall old version 3 install last version 4 restor your database Sent from Yahoo! Mail on Android
Odp: [firebird-support] Re: database became broken for any reasons
Hi, Delete from mon$attachments excluding current connection After that you are only logged user Regards, Karol Bieniaszewski - Reply message - Od: Markov Dmitri markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Re: database became broken for any reasons Data: wt., maj 20, 2014 20:22 I turn off automatic sweep - only manual sweep. For today I try to change keepalive parameters - it's look like all ok. All processes named fb_inet_server.exe closed, but it's work if connection was lost. It's not work when the connection established and not closed for any reasons(for example the application waiting for some system process on client side and so don't close connection and don't react on my commands from server side). So how I can from server side close connection normally, so after that I may shutdown database and it's not create orphan records or pages? I use shutdown for absolute prohibition of connection to database using user accounts for database update process(changing metadata or changing only data). 20.05.2014 18:47, Ann Harrison aharri...@ibphoenix.com [firebird-support] пишет: On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com wrote: For preventing orphan pages or backversions I turn off autosweep. So for the last month I don't see any orphan pages or orphan backversions. Probably you were shutting the database down hard while a sweep was active. I know that shutdown during sweep can broke database, so I turn off autosweep and do only manual sweep. I've got to take exception to your statement. Shutdown during sweep doesn't break the database. It leaves some space (record or page) inaccessible, but, assuming you're running with forced writes turned on, the database is not corrupt. All the data is accessible and correct. My service of database looks like this: 1) try normally close all connections(all my applications get commands for starting of autoclose procedure) OK 2) kill all terminal instance of my applications OK, though that will leave errors in the log - but you caused them, so not a big deal. 3) shutdown database(my software use non root access, so it's guarantee single user access) OK. 4) killing all processes fb_inet_server.exe Not OK. Once the connections are gone, the fb_inet_servers will eventually stop, after they've written all their changes to disk. If you kill them, there's a chance that, for example, an old record version will have been removed and the page it was on will have been written, but the older versions that are chained to that record will not have been removed and their pages flushed to disk. Firebird uses a technique called careful write to maintain on-disk consistency without a separate log. Essentially that means that when new things are created, the thing is created first, then pointer to it follow. Conversely, when a thing is removed, all the pointers are removed first, then the thing itself. When done correctly and consistently, careful writes never leave broken pointers. However, it can lead to lost space. For example, when a table needs a new data page, Firebird looks for a free page - they're indicated on pages called PageInformationPages or PIPs. When it finds a free page on a PIP, Firebird marks the page as in use, before it starts the process of allocating that page to the table, If the operation is interrupted, the PIP will say that the page is in use, but it's not part of a table or any other part of the database - hence, orphaned. Orphans are a normal and benign result of a hard database shutdown with write pending. Good luck, Ann
Re: Odp: [firebird-support] Re: database became broken for any reasons
It's not resolve problem, because any my application can reconnect to database. 20.05.2014 23:45, 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] пишет: Hi, Delete from mon$attachments excluding current connection After that you are only logged user Regards, Karol Bieniaszewski - Reply message - Od: Markov Dmitri markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Re: database became broken for any reasons Data: wt., maj 20, 2014 20:22 I turn off automatic sweep - only manual sweep. For today I try to change keepalive parameters - it's look like all ok. All processes named fb_inet_server.exe closed, but it's work if connection was lost. It's not work when the connection established and not closed for any reasons(for example the application waiting for some system process on client side and so don't close connection and don't react on my commands from server side). So how I can from server side close connection normally, so after that I may shutdown database and it's not create orphan records or pages? I use shutdown for absolute prohibition of connection to database using user accounts for database update process(changing metadata or changing only data). 20.05.2014 18:47, Ann Harrison aharri...@ibphoenix.com [firebird-support] пишет: On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com mailto:markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com wrote: For preventing orphan pages or backversions I turn off autosweep. So for the last month I don't see any orphan pages or orphan backversions. Probably you were shutting the database down hard while a sweep was active. I know that shutdown during sweep can broke database, so I turn off autosweep and do only manual sweep. I've got to take exception to your statement. Shutdown during sweep doesn't break the database. It leaves some space (record or page) inaccessible, but, assuming you're running with forced writes turned on, the database is not corrupt. All the data is accessible and correct. My service of database looks like this: 1) try normally close all connections(all my applications get commands for starting of autoclose procedure) OK 2) kill all terminal instance of my applications OK, though that will leave errors in the log - but you caused them, so not a big deal. 3) shutdown database(my software use non root access, so it's guarantee single user access) OK. 4) killing all processes fb_inet_server.exe Not OK. Once the connections are gone, the fb_inet_servers will eventually stop, after they've written all their changes to disk. If you kill them, there's a chance that, for example, an old record version will have been removed and the page it was on will have been written, but the older versions that are chained to that record will not have been removed and their pages flushed to disk. Firebird uses a technique called careful write to maintain on-disk consistency without a separate log. Essentially that means that when new things are created, the thing is created first, then pointer to it follow. Conversely, when a thing is removed, all the pointers are removed first, then the thing itself. When done correctly and consistently, careful writes never leave broken pointers. However, it can lead to lost space. For example, when a table needs a new data page, Firebird looks for a free page - they're indicated on pages called PageInformationPages or PIPs. When it finds a free page on a PIP, Firebird marks the page as in use, before it starts the process of allocating that page to the table, If the operation is interrupted, the PIP will say that the page is in use, but it's not part of a table or any other part of the database - hence, orphaned. Orphans are a normal and benign result of a hard database shutdown with write pending. Good luck, Ann
[firebird-support] ibase_blob_echo problem
Hello I have a weird problem. I have just started using ibase_blob_echo. When I retrieve the information from the table I get the correct data from the file name field and the file size field but I get 0x0001 from the blob field (using ibase_blob_echo). In a test database I got the blob without any problems but in the application I encounter the problem. I copied everything as is. I have been to various search engines and I am not finding the solution. I only attach PDFs and Word documents. Please help. Mags
Re: Odp: [firebird-support] Re: database became broken for any reasons
You might create an on connect trigger that trows an exception on certain condition, for example the value of a generator (bcause this gets visible to every transaction immediately) and for other users than yourself. That will prevent applcication from reconnecting successfully. Am 20.05.2014 22:18, schrieb Markov Dmitri markovdmi...@yahoo.com [firebird-support]: It's not resolve problem, because any my application can reconnect to database. 20.05.2014 23:45, 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] пишет: Hi, Delete from mon$attachments excluding current connection After that you are only logged user Regards, Karol Bieniaszewski - Reply message - Od: Markov Dmitri markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Re: database became broken for any reasons Data: wt., maj 20, 2014 20:22 I turn off automatic sweep - only manual sweep. For today I try to change keepalive parameters - it's look like all ok. All processes named fb_inet_server.exe closed, but it's work if connection was lost. It's not work when the connection established and not closed for any reasons(for example the application waiting for some system process on client side and so don't close connection and don't react on my commands from server side). So how I can from server side close connection normally, so after that I may shutdown database and it's not create orphan records or pages? I use shutdown for absolute prohibition of connection to database using user accounts for database update process(changing metadata or changing only data). 20.05.2014 18:47, Ann Harrison aharri...@ibphoenix.com [firebird-support] пишет: On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com mailto:markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com wrote: For preventing orphan pages or backversions I turn off autosweep. So for the last month I don't see any orphan pages or orphan backversions. Probably you were shutting the database down hard while a sweep was active. I know that shutdown during sweep can broke database, so I turn off autosweep and do only manual sweep. I've got to take exception to your statement. Shutdown during sweep doesn't break the database. It leaves some space (record or page) inaccessible, but, assuming you're running with forced writes turned on, the database is not corrupt. All the data is accessible and correct. My service of database looks like this: 1) try normally close all connections(all my applications get commands for starting of autoclose procedure) OK 2) kill all terminal instance of my applications OK, though that will leave errors in the log - but you caused them, so not a big deal. 3) shutdown database(my software use non root access, so it's guarantee single user access) OK. 4) killing all processes fb_inet_server.exe Not OK. Once the connections are gone, the fb_inet_servers will eventually stop, after they've written all their changes to disk. If you kill them, there's a chance that, for example, an old record version will have been removed and the page it was on will have been written, but the older versions that are chained to that record will not have been removed and their pages flushed to disk. Firebird uses a technique called careful write to maintain on-disk consistency without a separate log. Essentially that means that when new things are created, the thing is created first, then pointer to it follow. Conversely, when a thing is removed, all the pointers are removed first, then the thing itself. When done correctly and consistently, careful writes never leave broken pointers. However, it can lead to lost space. For example, when a table needs a new data page, Firebird looks for a free page - they're indicated on pages called PageInformationPages or PIPs. When it finds a free page on a PIP, Firebird marks the page as in use, before it starts the process of allocating that page to the table, If the operation is interrupted, the PIP will say that the page is in use, but it's not part of a table or any other part of the database - hence, orphaned. Orphans are a normal and benign result of a hard database shutdown with write pending. Good luck, Ann -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Re: Odp: [firebird-support] Re: database became broken for any reasons
You might create an on connect trigger that trows an exception on certain condition, for example the value of a generator (bcause this gets visible to every transaction immediately) and for other users than yourself. That will prevent applcication from reconnecting successfully. Am 20.05.2014 22:18, schrieb Markov Dmitri markovdmi...@yahoo.com [firebird-support]: It's not resolve problem, because any my application can reconnect to database. 20.05.2014 23:45, 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] пишет: Hi, Delete from mon$attachments excluding current connection After that you are only logged user Regards, Karol Bieniaszewski - Reply message - Od: Markov Dmitri markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Re: database became broken for any reasons Data: wt., maj 20, 2014 20:22 I turn off automatic sweep - only manual sweep. For today I try to change keepalive parameters - it's look like all ok. All processes named fb_inet_server.exe closed, but it's work if connection was lost. It's not work when the connection established and not closed for any reasons(for example the application waiting for some system process on client side and so don't close connection and don't react on my commands from server side). So how I can from server side close connection normally, so after that I may shutdown database and it's not create orphan records or pages? I use shutdown for absolute prohibition of connection to database using user accounts for database update process(changing metadata or changing only data). 20.05.2014 18:47, Ann Harrison aharri...@ibphoenix.com [firebird-support] пишет: On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com mailto:markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com wrote: For preventing orphan pages or backversions I turn off autosweep. So for the last month I don't see any orphan pages or orphan backversions. Probably you were shutting the database down hard while a sweep was active. I know that shutdown during sweep can broke database, so I turn off autosweep and do only manual sweep. I've got to take exception to your statement. Shutdown during sweep doesn't break the database. It leaves some space (record or page) inaccessible, but, assuming you're running with forced writes turned on, the database is not corrupt. All the data is accessible and correct. My service of database looks like this: 1) try normally close all connections(all my applications get commands for starting of autoclose procedure) OK 2) kill all terminal instance of my applications OK, though that will leave errors in the log - but you caused them, so not a big deal. 3) shutdown database(my software use non root access, so it's guarantee single user access) OK. 4) killing all processes fb_inet_server.exe Not OK. Once the connections are gone, the fb_inet_servers will eventually stop, after they've written all their changes to disk. If you kill them, there's a chance that, for example, an old record version will have been removed and the page it was on will have been written, but the older versions that are chained to that record will not have been removed and their pages flushed to disk. Firebird uses a technique called careful write to maintain on-disk consistency without a separate log. Essentially that means that when new things are created, the thing is created first, then pointer to it follow. Conversely, when a thing is removed, all the pointers are removed first, then the thing itself. When done correctly and consistently, careful writes never leave broken pointers. However, it can lead to lost space. For example, when a table needs a new data page, Firebird looks for a free page - they're indicated on pages called PageInformationPages or PIPs. When it finds a free page on a PIP, Firebird marks the page as in use, before it starts the process of allocating that page to the table, If the operation is interrupted, the PIP will say that the page is in use, but it's not part of a table or any other part of the database - hence, orphaned. Orphans are a normal and benign result of a hard database shutdown with write pending. Good luck, Ann -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann