How to deal with database crashes (Glassfish / MySQL)?

Question

I have a three-tier application with Glassfish 3.1.2.2, a MySQL database and a Swing client application. How can I deal with a database server failure?

I tried to stop the MySQL service while my application was running. Then I get javax.ejb.EJBAccessException exceptions every time I try to access the database through one of my beans facade sessions.

I want to notify the user that the database is currently unavailable. In addition, I want to install my application as a "sleep mode" until the database is started and restarted.

What would be a good (and possibly simple) approach for handling such a scenario?

Thanks for your help in advance!

Answer 1

A) Make sure you configure the Glassfish connection pool to automatically recover / reconnect

In the JDBC Glassfish 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 jdbc-connection-pool configuration Properties
Glassfish Admin Control Panel - create-jdbc-connection-pool

Steps:

• Assuming Glassfish is working (for example, start the server in the Netbeans Services tab, open the servers and right-click Glassfish), then you should have a domain administration server
• Open the admin console in a web browser: http://localhost:4848 (or use any port that you specified during installation.
• On the left side of the General Tasks menu, open Resources → JDBC and click JDBC Connection Pools
• Click on the POOL NAME connection pool (or create a new one by clicking the Create button).
• Select the "Advanced" tab and enter:
• Check no more often: (e.g. 60) in seconds
• retry attempts to create: (e.g. 3)
• Repeat Interval: (e.g. 10) in seconds
• Connection Check: (Tick) Required
• Verification Method: (e.g. auto-commit)
• Other additional properties as desired
• Select the General tab and enter:
• Ping (Tick)
• Other common properties as desired

B) Implement application error handling / database monitoring and alerts

• Mandatory: false user errors at the infrastructure level and alerts support staff.

  Create a simple JMX class to send a notification and call its method when a fatal JMX MBean Class error occurs that sends notifications . You can use the JMX monitor console to monitor server status - 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 for accessing JMX notifications).

  Use the Google Calendar API to send an error message to Google Calendar Support (1 or 2 minutes in the future). Then configure your Google calendar to send notifications via email / sms - you will receive an error warning right to support staff in almost real time. It depends on Google’s usage limits (politeness limit of 10,000 requests per day, so make sure your applications aren’t too glitches and use logic to limit the number of messages sent per hour / day / week)

• Desirable: to control the database (and, possibly, the application server) and notify support staff about disconnection

  • Zabbix open source has built-in mySQL monitoring and alerting - it's easy, but requires setup and configuration
  • Hyperic open source has extension plugins for monitoring mySQL and built-in alerts - this is heavy weight, it can be difficult to configure and configure
  • The default Nagois open source for * nix OSes is heavy, can be difficult to configure and configure

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

  If it is “out of scope”, create your own simple monitor:

  • An EJB timer to start a scheduled simple test request to your database - if it fails to send a warning (via JMX / Google Calendar / Java Mail / SMS gateway API). Or use the Quartz open source scheduler to do the same job.

Answer 2

In it, your own connection pool will monitor the status of the database. You can create an interceptor and pick up when the db goes down. The container will try to reconnect, but you will not necessarily know when it will succeed. Once you find the error, you need to install the semaphore somewhere. Then you will need to start the timer to periodically check the status of db and reset the semaphore as soon as it returns. Finally, you will need to encode your application to respect the semaphore.

This is a high level offer. good luck.

Also, you can hook all events using JMX ... I'm not sure though.