How to handle database crashes (Glassfish/MySQL)?

Question

I have a 3-tiered application with Glassfish 3.1.2.2, MySQL database and a Swing client application. Is it somehow possible to handle a database server crash?

I tried to stop the MySQL service during my application is running. Then I get javax.ejb.EJBAccessException exceptions everytime I try to access the database through one of my facade session beans.

I want to notify the user that the database is currently down. furthermore i want to set my application in kind of a "sleep-mode" until the database is up and running again.

What would be a good (and possibly easy) approach to handle such a scenario?

Thanks for your help in advance!

Answer 1

A) Ensure you configure Glassfish Connection Pool for automatic recovery/reconnection

In the Glassfish JDBC connection pool configuration, set the values for:

• Attributes: is-connection-validation-required, validate-atmost-once-period-in-seconds, connection-creation-retry-attempts, connection-validation-method, connection-creation-retry-interval-in-seconds, ping

Glassfish Configuration jdbc-connection-pool Properties
Glassfish Admin - create-jdbc-connection-pool subcommand

Steps:

1. Assuming Glassfish is running (e.g. start server in Netbeans Services Tab, by opening Servers and right-clicking Glassfish), then you should have the Domain Admin Server running
2. Open Admin Console in web browser: http://localhost:4848 (or use whatever port you specified during install.
3. On left, under Common Tasks menu, open Resources -> JDBC and click on JDBC Connection Pools
4. Click on your connection pool POOL NAME (or create a new one by clicking New button)
5. Select Advanced Tab & Enter:
6. Validate At Most Once: (e.g. 60) in seconds
7. Creation Retry Attempts: (e.g. 3)
8. Retry Interval: (e.g. 10) in seconds
9. Connection Validation: (Tick) Required
10. Validation Method: (e.g. auto-commit)
11. Other Advanced Properties as Desired
12. Select General Tab & Enter:
13. Ping (Tick)
14. Other General Properties as Desired

B) Implement Application Error Handling / DB Monitoring & Alerts

1. Mandatory: trap "infrastructure-level" fatal user errors and alert support staff.

   Create a simple JMX class to send a notification & call its method when a fatal error occurs JMX MBean Class which sends notifications. You can use a JMX monitor console to observe state of servers - some of these consoles send email alerts (e.g. JManage and RHQ) and there are bridges for access from HTTP/AJax or other languages (e.g. Jolokia can use javascript/perl/java API to access JMX notifications).

   Use Google Calendar API to send the error msg to support staff's google calendar (1 or 2 mins into future). Then configure google calendar to send email/sms notifications - will give error alert straight to support staff in near-realtime. This is subject to Google usage restrictions (a courtesy limit of 10,000 queries per day, so make sure your apps not super buggy and use logic to limit the number of messages sent in an hour/day/week)

2. Desirable: monitor DB (& probably app server) and alert support staff of outages

   • Zabbix open source has inbuilt mySQL monitoring and alerting - is lightweight but requires setup & configuration
   • Hyperic open source has extension plug-ins for mySQL monitoring and inbuilt alerting - is heavy-weight, can be complex to setup & configure
   • Nagois open source is the default for *nix OSes - is heavy weight, can be complex to setup and configure

   In all cases, setup will not happen instantly - best to implement as a separate mini project & do it right - best to have support staff involved in this.

   If these are "out of scope", then create your own simple monitor:

   • An EJB timer to run scheduled simple test query against your DB - if it fails send an alert (via JMX/Google Calendar/Java Mail/ SMS gateway API). Or use Quartz open source scheduler to do the same job

Answer 2

On it's own Connection Pool will monitor the state of the database. You could create an interceptor and pick up when db goes down. The container will try to reconnect, but you won't necessarily know when it's successful. Once you detect the error you'd need to set a semaphore somewhere. Then you'd have to fire up a timer to periodically check the state of the db and to reset the semaphore once it comes back. Last, you'd have to code your app to respect the semaphore.

This is a high level suggestion. good luck.

Alternatively, you might be able to hook all the events using JMX... I'm not sure though.