Mirth Connect
  1. Mirth Connect
  2. MIRTH-3484

Validate MyBatis connections to recover from lost or timed out connections

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 3.2.0
    • Component/s: Server
    • Labels:
      None

      Description

      If connections are idle past MySQL's wait_timeout setting, then exceptions will occur the next time the user tries to load a page on the Administrator that uses MyBatis.

      http://www.mirth.com/community/forums/showthread.php?t=11807
      http://www.mirth.com/community/forums/showthread.php?t=10119

      Usually the error is harmless and a new connection will be created the next time a request is made. If for some reason someone REALLY needs a workaround right now, the answer is to run the following code from a deploy script or something each time the server is started. Ideally the "setPoolPingConnectionsNotUsedFor" value should be slightly less than the "wait_timeout" value that's set in MySQL. Note that the "wait_timeout" value is in seconds while the "setPoolPingConnectionsNotUsedFor" value is in milliseconds.

      [CODE]var manager = Packages.com.mirth.connect.server.util.SqlConfig.getSqlSessionManager();
      var dataSource = manager.getConfiguration().getEnvironment().getDataSource();
      dataSource.setPoolPingEnabled(true);
      dataSource.setPoolPingQuery("SELECT 1");
      dataSource.setPoolPingConnectionsNotUsedFor(1000);[/CODE]
      

        Issue Links

          Activity

          Hide
          Wayne Huang (Inactive) added a comment -

          It looks like this issue has more implications than just the wait_timeout setting for MySQL. If a connection to the database is lost, as long as the connection has not been used for longer than poolPingConnectionsNotUsedFor, there will not be an exception when the connection is used again. Often times a user may see an exception the first time they try to use their Administrator after a night or extended period of time. This is usually due to the connection to the backend database being briefly lost. For some databases MyBatis can recover this after one error, but for some (Oracle) this can mean that MyBatis connections fail until the Server is restarted. If validation for MyBatis is enabled, users will probably no longer even notice the blip when they return.

          Show
          Wayne Huang (Inactive) added a comment - It looks like this issue has more implications than just the wait_timeout setting for MySQL. If a connection to the database is lost, as long as the connection has not been used for longer than poolPingConnectionsNotUsedFor, there will not be an exception when the connection is used again. Often times a user may see an exception the first time they try to use their Administrator after a night or extended period of time. This is usually due to the connection to the backend database being briefly lost. For some databases MyBatis can recover this after one error, but for some (Oracle) this can mean that MyBatis connections fail until the Server is restarted. If validation for MyBatis is enabled, users will probably no longer even notice the blip when they return.
          Hide
          Wayne Huang (Inactive) added a comment -

          Now validating MyBatis connections that are idle for longer than 10 seconds. This value can be configured (in milliseconds) by setting the database.test-idle-time property in mirth.properties.

          When upgrading to 3.2 or higher, anyone using the above workaround should stop.

          Show
          Wayne Huang (Inactive) added a comment - Now validating MyBatis connections that are idle for longer than 10 seconds. This value can be configured (in milliseconds) by setting the database.test-idle-time property in mirth.properties. When upgrading to 3.2 or higher, anyone using the above workaround should stop.
          Hide
          Nick Rupley added a comment -

          Verified that MyBatis connections are being pinged now after the idle time has been reached. For MySQL, exceptions no longer occur when leaving the Administrator open for more than the configured wait_timeout.

          Show
          Nick Rupley added a comment - Verified that MyBatis connections are being pinged now after the idle time has been reached. For MySQL, exceptions no longer occur when leaving the Administrator open for more than the configured wait_timeout.

            People

            • Assignee:
              Wayne Huang (Inactive)
              Reporter:
              Wayne Huang (Inactive)
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development