Lazarus

Programming => Databases => Topic started by: RedOctober on October 24, 2020, 01:06:45 am

Title: IBX 2.3.3 and INET errors
Post by: RedOctober on October 24, 2020, 01:06:45 am
I'm investigating "Unable to write to connection" errors.  In the firebird.log I see many lines like the following:

INET/inet_error: read errno = 10054, aux server host = JLS1, address = fe80::34c3:aee1:b467:95fc%6/55461

One app, using Delphi and FireDAC components, these errors are tolerated (maybe retried?) and my app keeps running.

In the other app, using Lazarus 1.8.4, FPC 3.0.4 and IBX 2.3.3  these errors are not tolerated, and my app throws a "Unable to write to connection" error, and the app is unusable after that.

Is there a setting in the IBX 2.3.3 components (or somewhere else) that says "Try again 10 times if an INET error occurs" ?

Maybe @tonyw can comment?


Title: Re: IBX 2.3.3 and INET errors
Post by: tonyw on October 24, 2020, 10:01:32 am
The TCP settings are in the Firebird client library and are not readily settable from IBX. To make any changes, you need to edit the firebird.conf file on the client system. "ConnectionTimeout" and "DummyPacketInterval" are the only ones that are likely to be relevant.

At the IBX level, if you lose a connection to the server then the correct recovery strategy depends on the application. For example, you may want to roll forward or roll back any limbo transactions. I don't believe that it is possible to do a "one size fits all" approach to recovery from connection failure as a new connection to the server cannot carry forward any open transactions.
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 25, 2020, 12:37:50 am
RUBI runs reliably on server JLD1, with all default Firebird .conf settings, so, what I'm about to tell you has take that into account.

RUBI needs to display about 20 grids at the same time, all populated with different data from different tables, each with an attached "form" (DBEdits) that allow data to be edited and saved.  The only way I found to do this in IBX is to have 20 different TIBQuery components "wired" to their own TIBTransaction component.  So that means there are at least 20 open transactions at any given time.  The user bounces between the grids, which are always refreshed by (committing changes, then) closing the related open transaction, starting it again, then run the associated query.  Just due to the nature of the work, the transactions are all open, but they are updated (by user manually clicking) every 10 seconds to 10 minutes.

I have read that leaving "long running" transactions open, is considered "bad form".  But I found that if I close the transaction, the grid is depopulated.  I need the grids to have data at all times.

I have read that an entire app is supposed to have only one TIBTransaction.  How?  I need the user to be able to edit many different datasets (grid) at random, and each has to be able to save to it's own table.


Is there a way to:

1)  Have data displayed to the user without leaving a transaction open?  If I have to populate TBufferDataSets for every grid then close the transaction... I'll be programming for another 2 years (added on to the 2 years already spent on this)

2) Save data to different tables using the same transaction?

Does having 20 open transactions use "database locks"?  Do I need to increase my lock count in database.conf for the RUBI database?

Please keep in mind, sentence one, of this post.


Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 25, 2020, 01:10:25 am
In an attempt to get away from TCP and port 3050, I have changed my TIBDatabase connection params to use "Local" instead of "Remote", which I guess changes the protocol to XNET, according to this document:

http://www.firebirdfaq.org/faq260/

Last paragraph:

Quote
On Windows, you can also connect to server on local machine using local transport protocol. In Firebird 2.0, implementation has switched from IPServer to XNET. In this case, the connection string is supplied without the hostname prefix, in the same way as Firebird embedded is used.

C:/db/mydb.fdb

I have not tested this setting to see if the INET error goes away on JLS1, because, before I test on JLS1, I noticed that this setting allows any user to bypass Firebird security.  A user doesn't have to enter a password, or even their own login ID.  All they have to do is enter a valid Login ID, and they are allowed to log in.    Do I have to manually write my own security module if the database is in "Local" connection setting?  Is there a way to stop Windows from allowing Firebird users to "tag along" on the Windows security system to allow entry?





Title: Re: IBX 2.3.3 and INET errors
Post by: tonyw on October 26, 2020, 06:18:34 pm
Local is not the same as xnet. Local implies an embedded server when you are using Firebird 3 or later.

If you want to use xnet, then you have to format the connection string (databasename property) using the xnet:// syntax. You can explicitly change the port number using the xnet or inet syntax. However, on the server side, the port number can only be changed by editing firebird.conf.

The faq looks out-of-date. See the Firebird 3 release notes "Connection string and protocols"

In an attempt to get away from TCP and port 3050, I have changed my TIBDatabase connection params to use "Local" instead of "Remote", which I guess changes the protocol to XNET, according to this document:

http://www.firebirdfaq.org/faq260/


Last paragraph:

Quote
On Windows, you can also connect to server on local machine using local transport protocol. In Firebird 2.0, implementation has switched from IPServer to XNET. In this case, the connection string is supplied without the hostname prefix, in the same way as Firebird embedded is used.

C:/db/mydb.fdb

I have not tested this setting to see if the INET error goes away on JLS1, because, before I test on JLS1, I noticed that this setting allows any user to bypass Firebird security.  A user doesn't have to enter a password, or even their own login ID.  All they have to do is enter a valid Login ID, and they are allowed to log in.    Do I have to manually write my own security module if the database is in "Local" connection setting?  Is there a way to stop Windows from allowing Firebird users to "tag along" on the Windows security system to allow entry?
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 26, 2020, 06:50:38 pm
Thanks for the excellent info Tony.   Hey, I was going through the document, and it is still a little unclear as to what the default protocol will be if I connect locally without specifying a "net" of some kind (ie: XNET, WNET, INET).  Here is what I have:

RUBI reads it's inifile, which contains the line:

DatabaseLCL=RUBI

The string "RUBI" is supplied to the TIBDatabase component as the "DatabaseName" property.

In databases.conf, there is the following line:

RUBI = D:\app_vlt\db\fdb\RUBI_TXT.FDB


Using this method, I have done some preliminary testing and I have not been able to get an INET error message (and crash) at the RUBI application, and there are no INET messages in firebird.log.

So, is this method using INET or not?


~~~~~~~~~~~~~~~~~~~~~~~~

Just a side note... to force Firebird to use authentication, I at first connect to the database using an INET string specifically.  This allows me to keep out unauthorized users and count bad logins.  Once the user has successfully logged in using INET, I then disconnect, and use the "Local" method described above, to avoid crashes while using RUBI.  Is there any "bad thing" that will happen using this method?  I'll be doing some testing in the next few days to ensure that there is not some "show stopper" condition (like, only 1 user allowed at a time) or some craziness like that, that will prevent me from using this more reliable method.



Title: Re: IBX 2.3.3 and INET errors
Post by: tonyw on October 27, 2020, 10:52:44 am
As a general rule, if you specify just the full pathname of a local database file with Firebird 3 or later,  then the Firebird Client library will attempt to open it using the embedded server.

If you prefix the pathname with "localhost:" then it will use the "loopback" provider, which should at least try xnet on Windows before trying TCP.

If you want to be explicit about the protocol then you should use the (e.g.)inet:// or xnet:// syntax - although legacy alternatives are available.

When you are using the embedded server then Firebird username/passwords are not used to control access - you rely on the local file system access controls. For that reason, you would normally only use the embedded server with a personal database. It is not suitable for databases shared between multiple users, even if they are on the same system. You can use group permissions to allow shared access - but why not use the remote server - it is designed for this case.

Thanks for the excellent info Tony.   Hey, I was going through the document, and it is still a little unclear as to what the default protocol will be if I connect locally without specifying a "net" of some kind (ie: XNET, WNET, INET).  Here is what I have:

RUBI reads it's inifile, which contains the line:

DatabaseLCL=RUBI

The string "RUBI" is supplied to the TIBDatabase component as the "DatabaseName" property.

In databases.conf, there is the following line:

RUBI = D:\app_vlt\db\fdb\RUBI_TXT.FDB


Using this method, I have done some preliminary testing and I have not been able to get an INET error message (and crash) at the RUBI application, and there are no INET messages in firebird.log.

So, is this method using INET or not?


~~~~~~~~~~~~~~~~~~~~~~~~

Just a side note... to force Firebird to use authentication, I at first connect to the database using an INET string specifically.  This allows me to keep out unauthorized users and count bad logins.  Once the user has successfully logged in using INET, I then disconnect, and use the "Local" method described above, to avoid crashes while using RUBI.  Is there any "bad thing" that will happen using this method?  I'll be doing some testing in the next few days to ensure that there is not some "show stopper" condition (like, only 1 user allowed at a time) or some craziness like that, that will prevent me from using this more reliable method.
Title: Re: IBX 2.3.3 and INET errors
Post by: tonyw on October 27, 2020, 11:05:54 am
You seem to have two threads in one here!

I don't see why you need 20 separate transactions, especially given that each is potentially a separate view on the database. You may end up with odd inconsistencies between your grids if the transaction isolation is not as you expect.

An IBX dataset is a buffered dataset. It buffers the result of a query and allows you to edit rows, normally writing any edits back to the database before moving on to another row (the exception is when you use cached updates).

When you commit a transaction, IBX clears the buffers given that they can be out-of-step with the underlying database. When you open a new transaction and run a query you see not just your edits, but the changes made under other transactions. If you do not want to clear the buffers, then using "CommitRetaining" is usually the best answer. This commits any changes you have made to the database, but maintains the existing transaction context and allows the buffer contents to remain valid - but again think carefully about how your transaction isolation works in a multi-user database.

I have never really got to the bottom of guidance against long lived transactions. Part of the argument may be that the longer a transaction goes on, the more resources get locked up. Datasets and rows become locked for updates by other users, etc. CommitRetaining should overcome some of those objections. E.g. locks should be released.

However, the downside of CommitRetaining is that due to your transaction isolation settings, you may not see edits made by other users and this state continues until you properly end the transaction and start a new one.

RUBI runs reliably on server JLD1, with all default Firebird .conf settings, so, what I'm about to tell you has take that into account.

RUBI needs to display about 20 grids at the same time, all populated with different data from different tables, each with an attached "form" (DBEdits) that allow data to be edited and saved.  The only way I found to do this in IBX is to have 20 different TIBQuery components "wired" to their own TIBTransaction component.  So that means there are at least 20 open transactions at any given time.  The user bounces between the grids, which are always refreshed by (committing changes, then) closing the related open transaction, starting it again, then run the associated query.  Just due to the nature of the work, the transactions are all open, but they are updated (by user manually clicking) every 10 seconds to 10 minutes.

I have read that leaving "long running" transactions open, is considered "bad form".  But I found that if I close the transaction, the grid is depopulated.  I need the grids to have data at all times.

I have read that an entire app is supposed to have only one TIBTransaction.  How?  I need the user to be able to edit many different datasets (grid) at random, and each has to be able to save to it's own table.


Is there a way to:

1)  Have data displayed to the user without leaving a transaction open?  If I have to populate TBufferDataSets for every grid then close the transaction... I'll be programming for another 2 years (added on to the 2 years already spent on this)

2) Save data to different tables using the same transaction?

Does having 20 open transactions use "database locks"?  Do I need to increase my lock count in database.conf for the RUBI database?

Please keep in mind, sentence one, of this post.
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 27, 2020, 02:47:51 pm
To reduce locks, I have set all the grids that don't need to be edited to "read"  (read only).  The forms use a TIBDataSet which is editable.
Title: Re: IBX 2.3.3 and INET errors
Post by: tonyw on October 27, 2020, 02:58:56 pm
Are the transactions Read Only - because that is what counts at the Firebird Level? All that the read only flag at the TIBDataset level means that it won't let you edit a row and shouldn't complain if there are no insert/update/delete queries defined.

To reduce locks, I have set all the grids that don't need to be edited to "read"  (read only).  The forms use a TIBDataSet which is editable.
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 27, 2020, 03:35:57 pm
Yes, the transactions associated with grids fed by TIBQuery components that don't need editing have the "read" parameter added.
Title: Re: IBX 2.3.3 and INET errors
Post by: tonyw on October 28, 2020, 10:17:23 am
If this is the case then why do not all the grids that are read only share the same long lived read only transaction, with a ReadCommitted isolation level. I don't believe that read only transactions take up many resources and if the are "ReadCommitted" then they should always see all committed edits whenever the queries are refreshed.

If you want to limit a refresh to as and when necessary, you can use a Firebird event. The event should be invoked from an insert/update/delete trigger on each table that you are monitoring. TIBEvent component can then be used to monitor the event and refresh a grid whenever an event is raised. You can have different events associated with different updates in order to refine the process further.

Yes, the transactions associated with grids fed by TIBQuery components that don't need editing have the "read" parameter added.
Title: Re: IBX 2.3.3 and INET errors
Post by: rvk on October 28, 2020, 04:26:23 pm
20 transaction (or even 40) isn't really a problem for Firebird.
The number of transactions in FB3 can be 2^48 - 1 (about 281.474.976.710.655 transactions)
So... 20 transactions... no problem.

You can check the statistics if you have very long running transactions (hanging which you didn't close).
But I don't think that's the problem (although transaction management shouldn't be underestimated in your design)

Is JLD1 (the correct working server) also using IPv6 ?
And is it also using INET?
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 28, 2020, 09:14:09 pm
Hi RVK.  Thanks for the reassurance about transactions.  In answer to your final two questions:

Yes, and Yes.  Both JLD1 (dev machine) and JLS1 (production server) are using the same Firebird configuration (IPv6 and INET).

[but with the change I spoke about in a previous post, using INET for testing log in creds, then closing and switching to "Local" connection string for subsequent in-app connections]

  Same OS, same hardware.  The differences will be in the install software.  Example:  JLS1 has Citrix on it, whereas JLD1 does not.  JLS1 is always accessed via RemoteDesktop whereas I'm always logged into JLD1 as the Administrator locally.
Title: Re: IBX 2.3.3 and INET errors
Post by: rvk on October 28, 2020, 09:36:41 pm
I think you can rule out remote desktop and citrix too.
You are essentially working locally then with the .exe.

Like I mentioned in the other topic, I think the read errno = 10054 are connection problems.

You say JLD1 has them too.
Does the Lazarus app also crash on JLD1?

Could it be a Windows connection limit?
Did you disable the virusscanner on both machines?
Firebird versions are the same?
Number of connections?

Another thought. Maybe it has nothing to do with that error.
It could be that Firebird recovers from this automatically (because you have them om both servers while one server exhibits the problem).

I can't find much on "Unable to write to connection" so what exactly is the exception?
And on what point in the program (with what action)?
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 28, 2020, 10:36:30 pm
Hi RVK,

JLD1 never has the problem described, only JLS1 has these errors.  I have been developing on JLD1 for more than 2 years, and never once have any of my Lazarus apps shown this error.   

Yes, I am working locally with the .exe, but my connection string was specifically for an INET port 3050 connection.

The error displayed in Lazarus is "Unable to write to the connection".  In the firebird.log, it lists these as the following:

Quote
JLS1   Sat Oct 24 14:29:33 2020
   INET/inet_error: read errno = 10053, aux server host = JLS1, address = fe80::3862:88ff:3231:1c98%6/51463


JLS1   Sat Oct 24 14:29:33 2020
   INET/inet_error: read errno = 10054, client host = jls1, address = fe80::3862:88ff:3231:1c98%6/51461, user = JOESMITH


JLS1   Sat Oct 24 14:29:33 2020
   INET/inet_error: send errno = 10054, server host = JLS1, address = fe80::3862:88ff:3231:1c98%6/3050

Always in groups of 3 as shown.  Always the same errors shown.

RUBI (Lazarus .exe) is unusable after one of these happens and must be shut down and restarted.  (It sometimes is stuck and will not even respond to a click on the "Close X" top right corner and must be End Task'd) These errors appear on JLS1 using RUBI after anywhere from 1 second, to about 40 seconds of use.  On JLD1,(server without the problem) Windows Defender has been shut off for Firebird's .exe, and .fbk and .fdb, but not for RUBI .exe.  On JLS1, (server with the problem) Windows Defender virus checker has been shut off for Firebird's .exe, Firebirds' folder, RUBI's .exe.

Difference:   Only on JLS1 there are legacy .exe's running that access a different Firebird database, using Delphi and TIBCConnection and other TIB Query components.  On JLD1, those same components are present and in Delphi, but the Delphi apps are not running.

Let me know if you need any more info.  I am happy to "help you help me".

Title: Re: IBX 2.3.3 and INET errors
Post by: rvk on October 28, 2020, 10:53:09 pm
Is it always preceded by the 10053 error? (Was that mentioned before?)
Does JLD1 also have these in the logs?

WSAECONNABORTED
(10053)
Software caused connection abort.
An established connection was aborted by the software in your host machine, possibly due to a data transmission time-out or protocol error.

WSAECONNRESET
(10054)
Connection reset by peer.
An existing connection was forcibly closed by the remote host. This normally results if the peer application on the remote host is suddenly stopped, the host is rebooted, or the remote host uses a hard close (see setsockopt for more information on the SO_LINGER option on the remote socket.) This error may also result if a connection was broken due to keep-alive activity detecting a failure while one or more operations are in progress. Operations that were in progress fail with WSAENETRESET. Subsequent operations fail with WSAECONNRESET.
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 28, 2020, 11:03:54 pm
Quote
Is it always preceded by the 10053 error?

Yes, always.

Quote
(Was that mentioned before?)

Just looking at the thread.. no.

Quote
Does JLD1 also have these in the logs?

No.  This error has never occured on JLD1.  There is no listing of either 10053 or 10054 errors in the firebird.log




Title: Re: IBX 2.3.3 and INET errors
Post by: rvk on October 28, 2020, 11:26:48 pm
Can you switch easily to using IPv4 just to check if that helps?
Using 192.x.x.x:3050/path_to_db as connection string.
Or inet4://C:\path\db.fdb

Are you working with threads or not?
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 29, 2020, 03:01:48 am
I am not working with threads.  I have enough trouble getting one thread to work reliably.  I will try the IPv4.
Title: Re: IBX 2.3.3 and INET errors
Post by: zoltanleo on October 29, 2020, 09:20:09 am
Hi RedOctober.

Try connecting via isql using your connection string. What do you get?
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 29, 2020, 03:55:15 pm
Hi ZoltanLeo, ISQL connects, using the connection string I used for RUBI (the "local" one that allows me to connect without a password, which is just the alias by itself... RUBI).  It doesn't indicate what protocol it (ISQL) used to connect, and I cannot find an ISQL command that would display this to me.
Title: Re: IBX 2.3.3 and INET errors
Post by: rvk on October 29, 2020, 05:25:47 pm
It doesn't indicate what protocol it (ISQL) used to connect, and I cannot find an ISQL command that would display this to me.
Code: SQL  [Select][+][-]
  1. SELECT * FROM MON$ATTACHMENTS
This will give you the connections.
In MON$REMOTE_PROTOCOL you'll see TCPv4 with IP MON$REMOTE_ADDRESS or something else.
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 29, 2020, 09:55:23 pm
Regarding ISQL:  SELECT MON$REMOTE_PROTOCOL, MON$REMOTE_ADDRESS FROM MON$ATTACHEMENTS;

Result:   Both fields show a value of <null>  so I still don't know what protocol I'm attached with (I have connected to the database using the alias RUBI, and that alias is designated as the Drive:/Path/RUBI.fdb in databases.conf file. (so, a "Local" connection)

Regarding the "Unable to write to connection" error:  Using a test utility, I was able to recreate the INET error using a remote connection string.  When I switched to a Local connection string, (which I assume is XNET) the error went away.  So in the test utility, I was able to rapidly do rollback/start transaction operations from three TIBXTransactions 10,000 times without error.  (so, a total of 30,000 ops) Using the remote INET connection, it wd fail after anywhere from 1 to 500 "rollback/start transaction" operations of a transaction.


The error in my RUBI app only and always happened during rapid rollback/start transaction operations, and only on JLS1 (the production server) and never on JLD1 (the development server).

Title: Re: IBX 2.3.3 and INET errors
Post by: rvk on October 29, 2020, 10:21:09 pm
Did you try IPv4 yet?
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 29, 2020, 11:35:55 pm
Hi RVK.

Using the "Remote" connection string with IPv4 protocol specified:  inet4://JLS1:3050/RUBI

Result: 30,000 cycles, zero errors.

We can now conclude that the problem is localized to a Remote connection string that "allows" the use of IPv6, and only on JLS1.

Thank you all for your kind attention to this matter.  I now have a way forward.
Title: Re: IBX 2.3.3 and INET errors
Post by: rvk on October 29, 2020, 11:42:59 pm
Great.

You can check on JLD1 with SELECT * FROM MON$ATTACHMENTS if the connection is defaulted to IPv4 or IPv6. Maybe it's IPv4 there and then either Firebird or IBX has problems with IPv6.

If JLD1 also is on IPv6 then it's still a mystery but you can go foward with IPv4.
Title: Re: IBX 2.3.3 and INET errors
Post by: RedOctober on October 29, 2020, 11:48:06 pm
I have to go with the "mystery" option RVK.  JLD1 has always been in IPv6 (for sure) for the full 2 years of development, and never once has thrown a "Unable to write to the connection" error.
TinyPortal © 2005-2018