1

After a Firebird database crash the connections of the JDBC pool become invalid. (We have to flush the pool manually.) A good concept might be to use jdbc connection pool validation. Connection validation properties I am not sure what is the validation classname of the payara implementation, or in general what configuration works here. Is there a better way to handle the problem?

Csaba Ocskó-Sós
  • 21
  • 7
  • It is not really clear what you're asking. If you want a Payara custom validation, you need to create one yourself. Alternatively, use a recent version of Jaybird and use normal JDBC validation, or use a simple `select 1 from rdb$database` statement. – Mark Rotteveel Jan 05 '23 at 15:35
  • You can probably use `org.glassfish.api.jdbc.validation.JDBC40ConnectionValidation` or `org.glassfish.api.jdbc.validation.DefaultConnectionValidation` – Mark Rotteveel Jan 06 '23 at 11:39
  • You'd better contact developers to investigate Firebird crashes than workarounding them... – user13964273 Jan 06 '23 at 11:58
  • I have tested both the custom validation classes (connection-validation-method=custom-validation), thank you @MarkRotteveel. The DefaultConnectionValidation class was not working properly. After it detected an invalid connection, somehow payara was unable to destroy the physical connection. The JDBC40ConnectionValidation was working fine. The logs was the following : Custom validation detected invalid connection. Executing the isValid() of org.glassfish.api.jdbc.validation.JDBC40ConnectionValidation class failed. Set resource-adapter log-level to FINE for exception stack trace. – Csaba Ocskó-Sós Jan 16 '23 at 12:15
  • i have also tested the table validator (connection-validation-method=table) using the rdb$database. This one also seems to be working fine. Furter information about my environment: I use jaybird 3.0.12 driver. I aslo use JDBC and JPA (different services). I have org.firebirdsql.ds.FBXADataSource and also org.firebirdsql.ds.FBConnectionPoolDataSource Datasources involved. So basically i have two working options(table vs custom). Which would you recommend @MarkRotteveel? – Csaba Ocskó-Sós Jan 16 '23 at 12:16
  • I suggest upgrading to Jaybird 5.0.0 (or otherwise at least Jaybird 4.0.8). In any case, if it works, I suggest using `JDBC40ConnectionValidation`, it is likely "cheaper" than using table validation. – Mark Rotteveel Jan 16 '23 at 17:09
  • Thank you @MarkRotteveel. I have consulted with the person in charge about the jaybird upgrade. They are not willing to agree so easily. Most importantly they want to know the benefits and the risks of the upgrade. As I read the release notes, 3 -> 4 upgrade offers a jdbc improvements aside from the FB4 compatibility. We are using FireBird 3.0.9 with UDFs... and we have a banking application. Could you enlighten me about the main benefits and the incidental risks of the upgrade? – Csaba Ocskó-Sós Jan 19 '23 at 12:25
  • For one bug fixes (see changes for Jaybird 4.0.6 and higher), Jaybird 3.0 is no longer supported, more secure defaults for authentication, wire compression (disabled by default), also upgrading to 4.0.8 gets rid of the ANTLR dependency (which can be especially relevant if you use other libraries depending on a newer ANTLR version than Jaybird), upgrading to 5.0.0 gets rid of the connector-api dependency. In addition, there generally are other minor optimizations and improvements. – Mark Rotteveel Jan 19 '23 at 12:39

1 Answers1

1

Thanks to Mark Rotteveel I have solved the problem. Also thanks for pointing out the importance of the jaybird driver upgrade. I used the following asadmin commands:

  • set resources.jdbc-connection-pool.jdbc_pool_name.connection-validation-method=custom-validation
  • set resources.jdbc-connection-pool.jdbc_pool_name.validation-classname=org.glassfish.api.jdbc.validation.JDBC40ConnectionValidation
  • set resources.jdbc-connection-pool.jdbc_pool_name.is-connection-validation-required=true
  • set resources.jdbc-connection-pool.jdbc_pool_name.fail-all-connections=true

In my case the main cause of the error was a Firebird segfault. The configurations above have passed the tests.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Csaba Ocskó-Sós
  • 21
  • 7