Re: [firebird-support] Re: The worst day i can have with firebird
following through this thread and I agree it is a programming error causing a waste of resources. But I am confused as to why this is a problem for Firebird. I thought that lots of short transactions was actually something to be encouraged over long running transactions. Is there some limit to # of transactions / time period? The installation was using Firebird 2.5.2 SC. There was near 80 transactions/day, more than a half between 8:00 AM and 15:00PM. The system runs fine and the performance was good except the problems of page conversion, some minutes the System was frozen (10 minutes) at 9:30 AM, and the crash i had deleting one attachment. 1. The frozen may be because a sweep was executed. I had sweep interval set to 2. Now is disabled and executed at night. Ok. Just a note. In a recent 2.5.2 snapshot build, King Vlad added support for tracing the sweep process via the Trace API. Very useful! Let me know if you want a recent FB TraceManager build, which has support for that. 2. The page conversion, i think is solved, when we corrected the overhead generated by the transactions. During load, I would run fb_lock_print to check the lock table size and hash slots. Check out Dmitry's conference session, if you are curious: http://www.youtube.com/watch?v=iYNjwbYcjuc 3. The crash deleting the attachment, is complicated to reproduce now, because I think the source is the amount of transactions. I'm worried with this one, because I don't like engine crash. May be this amount of transactions is not good for Firebird. 60 transactions / day, uniformly distributed means ~ 7 transactions per second, which is nothing. I'm sure you don't have that pattern in production, thus e.g. the most busy hour in respect to transaction throughput might be interesting. I would also check out the header page regularly and its transaction counters. Combined with the monitoring table and Trace API, you have a toolset to identify the offending operations. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Regards, jesus [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
Re: [firebird-support] Re: The worst day i can have with firebird
60 transactions / day, uniformly distributed means ~ 7 transactions per second, which is nothing. I'm sure you don't have that pattern in production, thus e.g. the most busy hour in respect to transaction throughput might be interesting. I know 7 transactions seconds and peaks of 20/30 transactions seconds firebird can handle. The problem was trying to delete one attachment, while (may be) one of these peaks. I tried first to delete the transaction and i couldn't, raises an error. Then I tried to delete the attachment. When the engine crash, there was no input in Firebird.log, i used windows event viewer to verify it. May be there is a bug trying to delete one attachment, and the transactions linked to it, while there is a lot of transactions created and destroyed, but is complicated for me to reproduce it. The system was running in production one day with this workload with good performance and the same amount of transactions, with the only problem of page conversion. Jesus [Non-text portions of this message have been removed]
Re: [firebird-support] Newbie: Viewing system users
Hi, thank you. I plan on using my own system administrative account - hence not the default SYSDBA account. I will connect to security2.fdb to locate the users table and will post If I have trouble. Regards Danie van Eeden On 7/10/2012 7:54 AM, Thomas Steinmaurer wrote: I'm new to Firebird and still constructing backend units for application development. I;m writing code in Delphi using Zeoslib. I am struggling to find the system relation / table containing existing users (including the system administrative user (SYSDBA)). The idea is to provide any System Admin (in this case my application together with SYSDBA login details) access via a user interface to maintain database existing users of firebirddb. That is, view existing users and add new users via 'Create User'. Where would I find such users table (RDB$Users) if such exists? Or am I going about this the wrong way? Users are stored instance-wide in a security database called security2.fdb located in the Firebird root directory. To manage users in Firebird 2.5, there are the following ways: * CREATE/ALTER/DROP USER ... DDL statement (new in 2.5) * Use the gsec command-line tool, located in the Firebird\bin directory * Use the Services API Btw, move away from using the SYSDBA as your database and database objects owner. This will save you from various problems when you are deploying a Firebird-based application to an existing Firebird server installation, where you don't have access to the SYSDBA password permanently. Hope this helps. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Kind Regards dve83 ++ 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 [Non-text portions of this message have been removed]
Re: [firebird-support] Permission denied with qli and isql - Email found in subject
Now that I have a list of different questions all going in different directions I need to start over. I took several databases that were backed up from IB v6 and 2007. Before backing them up I removed ownership of the tables RDB$OWNER_NAME = NULL. This I learned to do when transferring databases to a possible different owner. For the FB test it seemed prudent since I suspected FB, like its predecessor IB, would assign ownership when the database was restored to whoever restored the database. I tried getting super server v2.1 running under Ubuntu but I could never get flamerobin to connect nor get the isql CREATE DATABASE command to work. So I switched to classic server. That I could get flamerobin and gbak (running under root) to restore databases. The client programs and server run on the same computer. There can be no tcp/ip connection for performance reasons. (I wish the shared memory connection was still available.) Our processing is a single connection reading and writing billions of records. NOT millions of connections processing a few records. Our programs, which were transfered from OpenSuSE, compiled without any problems under Ubuntu. Now we just need a few different users able to access their databases on the Ubuntu computer. Not on the Ubuntu server from a client computer. We need to use a pipe connection from our programs, qli, isql, and gbak to the databases owned by a user logged into that same computer. Right now I can only access any of the different user databases (that were restored either with flamerobin or gbak) when the programs are executed as root.
Re: [firebird-support] Newbie: Viewing system users
2012/7/10 Danie van Eeden vaneeden.da...@gmail.com ** I will connect to security2.fdb to locate the users table and will post If I have trouble I think you can't connect no more to security2.fdb with firebird 2.5. You will have to get users trough API. Regards, Jesus [Non-text portions of this message have been removed]
[firebird-support] service_mgr is not defined - calling from PHP
Hi
I am trying to write a PHP script to create a new Firebird user but when
I run the code I get the following:
Service DB - Could not connect: Cannot attach to services manager
service var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
I am running Firebird 2.5 on Ubuntu.
This is the PHP code:
// connect to the service database if (($service =
ibase_service_attach($host, $username, $password)) == FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg()); $smarty-display('feedback.tpl'); break;}
Can I do this on Firebird and if so how?
Thanks
Neil
[Non-text portions of this message have been removed]
[firebird-support] Re: service_mgr is not defined - calling from PHP
Seems that the rich text editor doesn't work and has stripped out all the
formatting so here is the error message and code again:
*
Service DB - Could not connect: Cannot attach to services manager service
var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
*
// connect to the service database
if (($service = ibase_service_attach($host, $username, $password)) == FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' . ibase_errmsg());
$smarty-display('feedback.tpl');
break;
}
Thanks
Neil
--- In firebird-support@yahoogroups.com, grip_2ls lists@... wrote:
Hi
I am trying to write a PHP script to create a new Firebird user but when
I run the code I get the following:
Service DB - Could not connect: Cannot attach to services manager
service var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
I am running Firebird 2.5 on Ubuntu.
This is the PHP code:
// connect to the service database if (($service =
ibase_service_attach($host, $username, $password)) == FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg()); $smarty-display('feedback.tpl'); break;}
Can I do this on Firebird and if so how?
Thanks
Neil
[Non-text portions of this message have been removed]
[firebird-support] Re: update statement Excel Sheet
the SQL statement that containing the error is
-
select L_ID, AC_ID, AC_ENAME
,AC_ANAME , DOCNO, REF,
ENTRY_ID,JV ,
A_DESC , E_DESC ,
DT , CR ,
BLNC_REC ,BAL_AC_ID , MOV_DATE
, POST_DATE , CHK ,
US_EX ,AC_USER ,
NOTES from ldgr(:AC_ID)
union select 0 ,cast(null as varchar(30)) , cast(null as varchar(50)),
cast(null as varchar(50)), cast(null as varchar(20)), cast(null as
varchar(20)) ,cast(null as integer), cast(null as varchar(10)),cast('ÑÕíÏ' as
varchar(50)) , cast('Balance' as varchar(50)) ,SUM(E_TO) , SUM(E_FROM) ,
SUM(E_TO)-SUM(E_FROM),cast(null as varchar(30)), MAX(MOV_DATE), MAX(POST_DATE),
cast(null as smallint), cast(null as float), cast(null as varchar(15)),
cast(null as varchar(50))
from entry where AC_CR starting with :AC_ID
having min(post_date)post_date
order by 14, 1
--
how can I replace the HAVING?
I tried to put 'WHERE'
like below:
from entry where AC_CR starting with :AC_ID
and min(post_date)post_date
but compiler gave me:
cannot use an aggregate functio in a where clause, use HAVING instead.
so is there anyway to get off this ?
--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
svein.erling.tysvaer@... wrote:
after the update i got a problem with the application the error msg is
General SQL erroe.
Invalid expression in the HAVING clause (nither an aggregate function nor a
part of the GROUP BY clause).
I think I need to update the the UDF or LIB maybe
the application was working great with the FB ver. 1.0 but when I update to
2.5 I got this message.
any advice?
I have to applications (delphi) only one of them is giving the error and the
other is great.
Hi Ahmad!
I don't think this error has anything to do with UDFs or similar, it is more
likely poorly written SQL within the application. Firebird 2.5 is stricter
regarding the SQL than Firebird 1.0 was. The particular error you observed
can be experienced when you do refer to a variable neither in the output set
nor an aggregate function in your HAVING clause. E.g. (using RDB$DATABASE in
this example, it exists in all Firebird databases, so you can test the SQL on
your database)
select RDB$DESCRIPTION
from RDB$DATABASE
group by RDB$DESCRIPTION
having RDB$RELATION_ID 0
This is incorrect SQL (RDB$RELATION_ID is not part of the grouped result,
since I don't have Fb 1.0 here, I don't know whether that version would have
allowed this or not) and should be replaced by
select RDB$DESCRIPTION
from rdb$database
where RDB$RELATION_ID 0
group by RDB$DESCRIPTION
You may discover other differences as well and thorough testing of your
applications are recommended when upgrading from 1.0 to 2.5 (of course, how
thorough aren't the same for applications used in nuclear power plants and
for applications to keep track of birthdays within a family).
HTH,
Set
Re: [firebird-support] Re: service_mgr is not defined - calling from PHP
Seems that the rich text editor doesn't work and has stripped out all the
formatting so here is the error message and code again:
*
Service DB - Could not connect: Cannot attach to services manager service
var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
*
I've no idea about the PHP usage, but the string above looks a bit
weird. The first part shouldn't be the path to database, but the
server/port information. E.g. the following is valid:
localhost:service_mgr
or
localhost/3051:service_mgr
Perhaps you are mixing something related to a database connection path
vs. server name when using the ibase_service_attach call?
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/
// connect to the service database
if (($service = ibase_service_attach($host, $username, $password)) == FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg());
$smarty-display('feedback.tpl');
break;
}
Thanks
Neil
--- In firebird-support@yahoogroups.com, grip_2ls lists@... wrote:
Hi
I am trying to write a PHP script to create a new Firebird user but when
I run the code I get the following:
Service DB - Could not connect: Cannot attach to services manager
service var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
I am running Firebird 2.5 on Ubuntu.
This is the PHP code:
// connect to the service database if (($service =
ibase_service_attach($host, $username, $password)) == FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg()); $smarty-display('feedback.tpl'); break;}
Can I do this on Firebird and if so how?
Thanks
Neil
[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
[firebird-support] Re: service_mgr is not defined - calling from PHP
Thomas
That is the error message you are referring to, the call is:
---
if (($service = ibase_service_attach($host, $username, $password)) == FALSE) {
---
the host is defined as:
---
$host = '192.168.10.109:/var/lib/firebird/2.5/data/grip.fdb';
---
So I think that it is correct.
Also if I query the database this works fine - only attaching to the service db
is a problem.
Neil
--- In firebird-support@yahoogroups.com, Thomas Steinmaurer ts@... wrote:
Seems that the rich text editor doesn't work and has stripped out all the
formatting so here is the error message and code again:
*
Service DB - Could not connect: Cannot attach to services manager service
var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
*
I've no idea about the PHP usage, but the string above looks a bit
weird. The first part shouldn't be the path to database, but the
server/port information. E.g. the following is valid:
localhost:service_mgr
or
localhost/3051:service_mgr
Perhaps you are mixing something related to a database connection path
vs. server name when using the ibase_service_attach call?
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/
// connect to the service database
if (($service = ibase_service_attach($host, $username, $password)) ==
FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg());
$smarty-display('feedback.tpl');
break;
}
Thanks
Neil
--- In firebird-support@yahoogroups.com, grip_2ls lists@ wrote:
Hi
I am trying to write a PHP script to create a new Firebird user but when
I run the code I get the following:
Service DB - Could not connect: Cannot attach to services manager
service var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
I am running Firebird 2.5 on Ubuntu.
This is the PHP code:
// connect to the service database if (($service =
ibase_service_attach($host, $username, $password)) == FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg()); $smarty-display('feedback.tpl'); break;}
Can I do this on Firebird and if so how?
Thanks
Neil
[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
Re: [firebird-support] Re: service_mgr is not defined - calling from PHP
grip_2ls wrote:
*
Service DB - Could not connect: Cannot attach to services manager service
var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
*
// connect to the service database
if (($service = ibase_service_attach($host, $username, $password)) == FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg());
$smarty-display('feedback.tpl');
break;
}
What are you putting in $host? It should just be the machine name ...
Services run on the server so do not need any reference to a particular
database.
--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php
[firebird-support] Why not sweeping?
I have 20+ databases in production, most of them between 3 and 8GB with no BLOBs. My problem seems to be that sweeping is not executing when I either perform a full backup or doing full table scans. Below is the gstat header info and as you can clearly see that it should have kicked in long ago. We are running classic mode V2.5. Can I see and output/error messages for sweeping? What are we overlooking? Our only current solution is to do a full backup and restore at silly morning hours, otherwise the system grows terribly slow due to garbage not being collected. Database header page information: Flags 0 Checksum 12345 Generation 50327462 Page size 8192 ODS version 11.2 Oldest transaction 460 Oldest active 461 Oldest snapshot 461 Next transaction 50325450 Bumped transaction 1 Sequence number 0 Next attachment ID2006 Implementation ID 26 Shadow count 0 Page buffers 0 Next header page 0 Database dialect 3 Creation dateJul 3, 2012 23:07:46 Attributes force write Variable header data: Sweep interval:2 *END* Analyzing database pages ... RPLRESULTS (259) Primary pointer page: 475, Index root page: 476 Average record length: 32.29, total records: 15044 Average version length: 9.10, total versions: 20554673, max versions: 6768 Data pages: 70750, data page slots: 70750, average fill: 93% Fill distribution: 0 - 19% = 0 20 - 39% = 1 40 - 59% = 0 60 - 79% = 0 80 - 99% = 70749
Re: [firebird-support] Permission denied with qli and isql - Email found in subject
What Firebird release/version # and version of the engine (Classic, SuperServer, SuperClassic or Embedded) are you trying to access? I think your question might be part of the problem. I am not trying to access an engine. The word access to me talks about network connection. I am using the classic server v2.1, mainly because the other option was super server, which I could not get to operate at all. It would not start. In the log file I found nothing. I am trying to access database files on a server from programs, including qli, isql, and gbak from that same server. Possibly I should be trying the other option of classic server 2.0 because the newer the version the more security that seems to be added. ;)
Re: [firebird-support] Re: service_mgr is not defined - calling from PHP
Neil,
That is the error message you are referring to, the call is:
---
if (($service = ibase_service_attach($host, $username, $password)) ==
FALSE) {
---
the host is defined as:
---
$host = '192.168.10.109:/var/lib/firebird/2.5/data/grip.fdb';
---
So I think that it is correct.
No, sorry. When attaching to the services manager, you don't attach to a
particular database, but just to the server, thus you just have to
provide 192.168.10.109 for the host.
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/
Also if I query the database this works fine - only attaching to the service
db is a problem.
Neil
--- In firebird-support@yahoogroups.com, Thomas Steinmaurer ts@... wrote:
Seems that the rich text editor doesn't work and has stripped out all the
formatting so here is the error message and code again:
*
Service DB - Could not connect: Cannot attach to services manager service
var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
*
I've no idea about the PHP usage, but the string above looks a bit
weird. The first part shouldn't be the path to database, but the
server/port information. E.g. the following is valid:
localhost:service_mgr
or
localhost/3051:service_mgr
Perhaps you are mixing something related to a database connection path
vs. server name when using the ibase_service_attach call?
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/
// connect to the service database
if (($service = ibase_service_attach($host, $username, $password)) ==
FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg());
$smarty-display('feedback.tpl');
break;
}
Thanks
Neil
--- In firebird-support@yahoogroups.com, grip_2ls lists@ wrote:
Hi
I am trying to write a PHP script to create a new Firebird user but when
I run the code I get the following:
Service DB - Could not connect: Cannot attach to services manager
service var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
I am running Firebird 2.5 on Ubuntu.
This is the PHP code:
// connect to the service database if (($service =
ibase_service_attach($host, $username, $password)) == FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg()); $smarty-display('feedback.tpl'); break;}
Can I do this on Firebird and if so how?
Thanks
Neil
[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
++
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] Why not sweeping?
Hello, fdt4y! Tuesday, July 10, 2012, 1:31:23 PM, you wrote: f What are we overlooking? Our only current solution is to do a f full backup and restore at silly morning hours, otherwise the system grows terribly slow f due to garbage not being collected. f Oldest transaction 460 f Oldest active461 f Oldest snapshot 461 f Next transaction 50325450 you overlooking that some of your applications started transaction long time ago (50 million transactions, 7 days ago), and this transaction is still ACTIVE. That's why versions still not garbage, and that's why sweeping does not help. -- Dmitry Kuzmenko, www.ib-aid.com
RE: [firebird-support] Why not sweeping?
I have 20+ databases in production, most of them between 3 and 8GB with no BLOBs. My problem seems to be that sweeping is not executing when I either perform a full backup or doing full table scans. Below is the gstat header info and as you can clearly see that it should have kicked in long ago. We are running classic mode V2.5. Can I see and output/error messages for sweeping? What are we overlooking? Our only current solution is to do a full backup and restore at silly morning hours, otherwise the system grows terribly slow due to garbage not being collected. Oldest transaction 460 Oldest active 461 Oldest snapshot 461 Next transaction 50325450 You are overlooking that oldest transaction and oldest active transaction aren't moving. That means that Firebird must keep all changes done since transaction 460 or 461 started since old versions could still contain data visible to those old transactions. Another way to say this, is that you have one or more programs that do not take proper care of one or more transactions and this is the reason for your slowness. Sweeping could only sweep the first 460 records of your database, the rest cannot be swept until one or many of your transactions commit or rollback. Set
[firebird-support] Re: service_mgr is not defined - calling from PHP
Lester/Thomas
Thanks for your help - you are right as soon as I took out the db name it
worked!
Thanks again
Neil
--- In firebird-support@yahoogroups.com, Lester Caine lester@... wrote:
grip_2ls wrote:
*
Service DB - Could not connect: Cannot attach to services manager service
var/lib/firebird/2.5/data/grip.fdb:service_mgr is not defined
*
// connect to the service database
if (($service = ibase_service_attach($host, $username, $password)) ==
FALSE) {
$smarty-assign('msg', 'Service DB - Could not connect: ' .
ibase_errmsg());
$smarty-display('feedback.tpl');
break;
}
What are you putting in $host? It should just be the machine name ...
Services run on the server so do not need any reference to a particular
database.
--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php
Re: [firebird-support] Re: service_mgr is not defined - calling from PHP
grip_2ls wrote: Thanks for your help - you are right as soon as I took out the db name it worked! There is also a list for firebird/php support ... http://groups.yahoo.com/group/firebird-php/ -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk// Firebird - http://www.firebirdsql.org/index.php
Re: [firebird-support] Permission denied with qli and isql
Add those users to the firebird group, or make sure all those programs use the service manager or tcp/ip connection to attach to the database. The problem is now that they try to access the database as a local database (which AFAIK means it behaves as a privately owned classic server process), but can't get it to work as they don't have access to shared resources like the lockfiles. Not having access to the lockfiles might be a major part of the problem. I have played with the RDB$OWNER_NAME field on the RDB$RELATIONS table. By changing that to the user created in the gsec program I was able to get access to one of the databases from flamerobin. But I suspect flamerobin is forcing the connection through tcp/ip.
[firebird-support] Re: Why not sweeping?
Thank you very much for your responses - this makes sense. We have a couple of server side applications that run constantly. Although, they should all be closing their connections. We will investigate further. Could this somehow be related to the .NET driver with connection pooling? The one application that I suspect starts and commits small incremental transactions, but uses connection pooling? Regards, --- In firebird-support@yahoogroups.com, Svein Erling Tysvær svein.erling.tysvaer@... wrote: I have 20+ databases in production, most of them between 3 and 8GB with no BLOBs. My problem seems to be that sweeping is not executing when I either perform a full backup or doing full table scans. Below is the gstat header info and as you can clearly see that it should have kicked in long ago. We are running classic mode V2.5. Can I see and output/error messages for sweeping? What are we overlooking? Our only current solution is to do a full backup and restore at silly morning hours, otherwise the system grows terribly slow due to garbage not being collected. Oldest transaction 460 Oldest active 461 Oldest snapshot 461 Next transaction 50325450 You are overlooking that oldest transaction and oldest active transaction aren't moving. That means that Firebird must keep all changes done since transaction 460 or 461 started since old versions could still contain data visible to those old transactions. Another way to say this, is that you have one or more programs that do not take proper care of one or more transactions and this is the reason for your slowness. Sweeping could only sweep the first 460 records of your database, the rest cannot be swept until one or many of your transactions commit or rollback. Set
Re: [firebird-support] Re: Why not sweeping?
Hello, Thank you very much for your responses - this makes sense. We have a couple of server side applications that run constantly. Although, they should all be closing their connections. We will investigate further. Could this somehow be related to the .NET driver with connection pooling? The one application that I suspect starts and commits small incremental transactions, but uses connection pooling? Don't know if Jiri is watching this group, but you might get better response to .NET provider related questions in the separate Firebird-net-provider support list. Beside that, as you are using Firebird 2.5, use the monitoring tables to detect the problematic client attachment. If you are interested in a continous stream of forthcoming operations, use the Trace API. With the Trace API you can have a look on what applications are doing and how. (Sorry for being a Trace API evangelist *g*) -- With regards, Thomas Steinmaurer http://www.upscene.com/ Regards, --- In firebird-support@yahoogroups.com, Svein Erling Tysvær svein.erling.tysvaer@... wrote: I have 20+ databases in production, most of them between 3 and 8GB with no BLOBs. My problem seems to be that sweeping is not executing when I either perform a full backup or doing full table scans. Below is the gstat header info and as you can clearly see that it should have kicked in long ago. We are running classic mode V2.5. Can I see and output/error messages for sweeping? What are we overlooking? Our only current solution is to do a full backup and restore at silly morning hours, otherwise the system grows terribly slow due to garbage not being collected. Oldest transaction 460 Oldest active 461 Oldest snapshot 461 Next transaction 50325450 You are overlooking that oldest transaction and oldest active transaction aren't moving. That means that Firebird must keep all changes done since transaction 460 or 461 started since old versions could still contain data visible to those old transactions. Another way to say this, is that you have one or more programs that do not take proper care of one or more transactions and this is the reason for your slowness. Sweeping could only sweep the first 460 records of your database, the rest cannot be swept until one or many of your transactions commit or rollback. Set ++ 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
[firebird-support] Re: Why not sweeping?
Thank you very much for your responses - this makes sense. We have a couple of server side applications that run constantly. Although, they should all be closing their connections. We will investigate further. Could this somehow be related to the .NET driver with connection pooling? The one application that I suspect starts and commits small incremental transactions, but uses connection pooling? A common reason would be using CommitRetaining rather than Commit. Although CommitRetaining can be convenient, it stops the OAT from moving on so you must ascertain that a hard Commit is used occasionally. Connection pooling by itself shouldn't cause your problems, unless there's also some kind of transaction pooling (but a transaction can span several connections). Note that the transaction does not have to do any modification to the database, even SELECTs can make the OAT stuck (the one exception is that transactions that are read only AND read committed do not stop the OAT from moving). Actually, we had a similar (though not related) problem recently, which turned out to be due to having a TIB_Transaction (IB Objects) with AutoCommit, and then in code do 'if TIB_Transaction1.TransactionIsActive then TIB_Transaction1.Commit'. Due to the AutoCommit, TransactionIsActive was false, so no hard Commit was done. AutoCommit did CommitRetaining, so the OAT got stuck until the program terminated. Our users typically start that program in the morning and closes it at the end of the day, so we only had one day of delay. Your gap indicates that your problematic transaction (well, there could be several problematic transactions, you'll find out) was started shortly after the restore, so programs started later than about July 4th are not suspected. Set
[firebird-support] Re: Why not sweeping?
Again, thank you guys - I've found the issue in a application that started a read-only transaction to update some labels on a form and keeping the OAT stuck. With my luck, I closed the app and froze the poor server, now trying to sweep 7 days worth of transactions from all other tables... ouch - I was not popular. All in all, I made some program changes, the database is back up and running and the OAT seems to be moving forward slowly as it should. What a great day this is - this has been troubling us for a while now and we could never pin point the problem. Regards, Wim --- In firebird-support@yahoogroups.com, Svein Erling Tysvær svein.erling.tysvaer@... wrote: Thank you very much for your responses - this makes sense. We have a couple of server side applications that run constantly. Although, they should all be closing their connections. We will investigate further. Could this somehow be related to the .NET driver with connection pooling? The one application that I suspect starts and commits small incremental transactions, but uses connection pooling? A common reason would be using CommitRetaining rather than Commit. Although CommitRetaining can be convenient, it stops the OAT from moving on so you must ascertain that a hard Commit is used occasionally. Connection pooling by itself shouldn't cause your problems, unless there's also some kind of transaction pooling (but a transaction can span several connections). Note that the transaction does not have to do any modification to the database, even SELECTs can make the OAT stuck (the one exception is that transactions that are read only AND read committed do not stop the OAT from moving). Actually, we had a similar (though not related) problem recently, which turned out to be due to having a TIB_Transaction (IB Objects) with AutoCommit, and then in code do 'if TIB_Transaction1.TransactionIsActive then TIB_Transaction1.Commit'. Due to the AutoCommit, TransactionIsActive was false, so no hard Commit was done. AutoCommit did CommitRetaining, so the OAT got stuck until the program terminated. Our users typically start that program in the morning and closes it at the end of the day, so we only had one day of delay. Your gap indicates that your problematic transaction (well, there could be several problematic transactions, you'll find out) was started shortly after the restore, so programs started later than about July 4th are not suspected. Set
Re: [firebird-support] Re: Why not sweeping?
Again, thank you guys - I've found the issue in a application that started a read-only transaction to update some labels on a form and keeping the OAT stuck. With my luck, I closed the app and froze the poor server, now trying to sweep 7 days worth of transactions from all other tables... ouch - I was not popular. All in all, I made some program changes, the database is back up and running and the OAT seems to be moving forward slowly as it should. What a great day this is - this has been troubling us for a while now and we could never pin point the problem. Additionally, I would take a step back and re-think transaction management in your client applications in general. According to your header page statistics, you had 50 mio transactions in 7 days, which means in your example approx. 83 transactions / second (if my math doesn't fail *g*) in case of an 24/7 environment. The transaction ID is a 32-bit integer, thus you can have ~ 2 bio. transactions until a backup/restore of your database is needed, which is ~ 280 days after your last restore. Keep an eye on that. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Regards, Wim --- In firebird-support@yahoogroups.com, Svein Erling Tysvær svein.erling.tysvaer@... wrote: Thank you very much for your responses - this makes sense. We have a couple of server side applications that run constantly. Although, they should all be closing their connections. We will investigate further. Could this somehow be related to the .NET driver with connection pooling? The one application that I suspect starts and commits small incremental transactions, but uses connection pooling? A common reason would be using CommitRetaining rather than Commit. Although CommitRetaining can be convenient, it stops the OAT from moving on so you must ascertain that a hard Commit is used occasionally. Connection pooling by itself shouldn't cause your problems, unless there's also some kind of transaction pooling (but a transaction can span several connections). Note that the transaction does not have to do any modification to the database, even SELECTs can make the OAT stuck (the one exception is that transactions that are read only AND read committed do not stop the OAT from moving). Actually, we had a similar (though not related) problem recently, which turned out to be due to having a TIB_Transaction (IB Objects) with AutoCommit, and then in code do 'if TIB_Transaction1.TransactionIsActive then TIB_Transaction1.Commit'. Due to the AutoCommit, TransactionIsActive was false, so no hard Commit was done. AutoCommit did CommitRetaining, so the OAT got stuck until the program terminated. Our users typically start that program in the morning and closes it at the end of the day, so we only had one day of delay. Your gap indicates that your problematic transaction (well, there could be several problematic transactions, you'll find out) was started shortly after the restore, so programs started later than about July 4th are not suspected. Set ++ 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] Permission denied with qli and isql
I tried firebird classic server 2.0. All the programs work just fine; qli, isql and gbak. Unfortunately, this means we will not be able to advance to any newer version of firebird. Unless somebody can explain what needs to be done to get normal access to database files.
[firebird-support] Re: Newbie: Viewing system users
Hello, I thank you for the reply. Please forgive my limited understanding of Firebird DB thus far. I understand that security2 is the replacement for the previous security.fbd and that now I cannot connect to it (even with DBAdmin). You mentioned using the API. COuld you perhaps direct me to documentation (I am currently working via ZeosLib libraries that interface with the dll's). Kind Regards --- In firebird-support@yahoogroups.com, Jesus Garcia jegaza@... wrote: 2012/7/10 Danie van Eeden vaneeden.danie@... ** I will connect to security2.fdb to locate the users table and will post If I have trouble I think you can't connect no more to security2.fdb with firebird 2.5. You will have to get users trough API. Regards, Jesus [Non-text portions of this message have been removed]
RE: [firebird-support] Re: Why not sweeping?
Isn't it possible to write one SQL procedure for all the 'Rules'. Should be much faster than shipping data from the database, apply some 'Rule' and shipping it back to the database. If anything fails in the SQL procedure or a data error was detected by some 'Rule' and exception rolls everything back. -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of fdt4y Sent: Tuesday, July 10, 2012 7:49 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] Re: Why not sweeping? Now you've made me think (and you math is correct BTW). This might be unrelated and if so, just ignore, but let me explain a simplified version of our architecture and ask for some advice regarding transaction handling. We have an application that almost continuously runs through a set of rules and processes them. Lets say there are 6000 rules stored in a table. In order to process each rule the application has to perform a set of selects and updates from many other tables (mostly storing warehouse inventory). Depending on the outcome of the process, the rule's status, lastexecutetime, etc is updated. These rules are then continuously displayed in a summarized view in a dashboard application. We currently process each rule in its own transaction, so to commit or roll-back depending on retuned values from the other statements. We cannot process all the rules in one transaction (unless if we use savepoints?). Also the inventory changes constantly so getting a snapshot at starttime will not make sense when getting to rule 5000+. Given the above makes sense (to anyone except me:-), I do not see another way to handle this without increasing the TransactionIDs continuously? ++ 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] Re: Why not sweeping?
Hello, fdt4y! Tuesday, July 10, 2012, 7:17:56 PM, you wrote: f After some more investigation I found that our main user interface f client app starts a single long running read transaction used to pull data for f display in its grids. All updates are executed in small/short f 'write' transactions. It is common for our client app to run 23 hours in a day (in a f warehouse running shifts). We use FIBPlus components which works f great, but now I'm faced with a catch-22. How can I commit/rollback the 'long read' f transaction without disconnecting the FIBDatasets which feed the f grids. If I do commit the read transaction, all the grids lose their content. read-only transaction must be real read-only, not logically read-only. So, you need to set this transaction parameters to read read_committed rec_version nowait -- Dmitry Kuzmenko, www.ib-aid.com
SV: [firebird-support] Re: update statement Excel Sheet
the SQL statement that containing the error is
Reformatted by me (it was almost unreadable) and slightly modified (no need to
cast nulls)
-
select L_ID, AC_ID, AC_ENAME, AC_ANAME, DOCNO, REF, ENTRY_ID, JV, A_DESC,
E_DESC,
DT, CR, BLNC_REC, BAL_AC_ID, MOV_DATE, POST_DATE, CHK, US_EX, AC_USER,
NOTES
from ldgr(:AC_ID)
union
select 0, null, null, null, null, null, null, null, cast('ÑÕíÏ' as
varchar(50)),
cast('Balance' as varchar(50)), SUM(E_TO), SUM(E_FROM),
SUM(E_TO)-SUM(E_FROM),
null, MAX(MOV_DATE), MAX(POST_DATE), null, null, null, null
from entry
where AC_CR starting with :AC_ID
having min(post_date)post_date
order by 14, 1
--
how can I replace the HAVING?
I tried to put 'WHERE'
like below:
from entry where AC_CR starting with :AC_ID
and min(post_date)post_date
Aggregate functions cannot be in the WHERE clause, they must be in HAVING.
Moreover, all non-aggregate fields must be in a GROUP BY clause and your query
doesn't even have GROUP BY. There's no way this statement could even parse in
any Firebird or InterBase version.
I'm even a bit uncertain what min(post_date) means in this setting, and to me
it seems like 'min(post_date) post_date' would always be false.
'min(post_date) another_date' could normally be replaced by 'and not
exists(select * from table where ... and post_date another_date')', but I'm
confused both by your use of post_date and that this is a union that involves a
stored procedure.
So please fix your sql and clarify your question.
Set
Re: [firebird-support] Re: Newbie: Viewing system users
At 12:29 AM 11/07/2012, dve83 wrote: Hello, I thank you for the reply. Please forgive my limited understanding of Firebird DB thus far. I understand that security2 is the replacement for the previous security.fbd and that now I cannot connect to it (even with DBAdmin). You mentioned using the API. COuld you perhaps direct me to documentation (I am currently working via ZeosLib libraries that interface with the dll's). If you are using ZeosLib then you are using the API. If you are trying to use old components with newer releases of Firebird then some functions that were previously available to ordinary users through a regular database connection (such as adding, deleting and modifying users) are NOT available to ordinary users. The old API documentation won't help much as it was written for InterBase before the days of security awareness, when services were openly available to ordinary uses via database parameters. I haven't looked at ZeosLib for years but it in the past it didn't support the Services API, which exposes the code in the engine that is used by gsec, gbak, etc. to the programmer, putting it under the control of a subsystem known as the Services Manager. It is the recommended route for all server-level activities. The VERY old functions were (and still are) in the database parameter block but have been deprecated in Firebird for most of its life. From v.2.0.6 and (I think) 2.1.1, only SYSDBA could connect using these parameters. Check whether the current version of ZeosLib provides support for the Services API: you would be looking for components whose names include clues to the specific Services API functions (such as backup, restore, user, statistics, and so on). From v.2.0 onward, connecting directly to security2.fdb by any means, by any user, is not allowed. All access is through the Services Manager, which makes an updatable view from the users table available to the client (gsec and other applications using the Services API). As others have told you, v.2.5 made an interface to security2.fdb available for clients through SQL requests when connected to any user database. Ordinary users can access only their own account and only to change their passwords; SYSDBA or equivalent can access multiple accounts and add, modify or delete users.
[firebird-support] Re: Why not sweeping?
Thank you for the advice, currently our read only transactions are not true read only, they contain the write parameter - I will try to change them tonight (should be possible) More importantly then are you saying that truly read-only transactions are not counted as interesting active transactions and garbage collection will proceed even if one exists that has been running for e.g. 30 days? Once again, I cannot thank all of you for the quick and helpful responses read-only transaction must be real read-only, not logically read-only. So, you need to set this transaction parameters to read read_committed rec_version nowait -- Dmitry Kuzmenko, www.ib-aid.com
[firebird-support] Database current time zone
Hello All, How do I get my database current time zone? I need it in order to calculate database current UTC time. Thank you, -Halim
RE: [firebird-support] Database current time zone
Halim, Hello All, How do I get my database current time zone? I need it in order to calculate database current UTC time. The database engine doesn't have native functions for this purpose but you could build* a UDF which would provide these details. Sean * There are UDF libraries which may have such a function, I am not aware of them of hand. A good place to find a list of UDF libraries is the IBPhoenix (www.ibphoenix.com) web site (though it has a server problem at the moment).
