Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Validate MyBatis connections to recover from lost or timed out connections #3381

Closed
rbeckman-nextgen opened this issue May 11, 2020 · 3 comments
Closed
Milestone

Comments

@rbeckman-nextgen
Copy link
Collaborator

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

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]

Imported Issue. Original Details:
Jira Issue Key: MIRTH-3484
Reporter: wayneh
Created: 2014-10-09T12:56:15.000-0700

@rbeckman-nextgen rbeckman-nextgen added this to the 3.2.0 milestone May 11, 2020
@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

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.

Imported Comment. Original Details:
Author: wayneh
Created: 2015-01-07T21:25:32.000-0800

@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

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.

Imported Comment. Original Details:
Author: wayneh
Created: 2015-01-08T11:29:15.000-0800

@rbeckman-nextgen
Copy link
Collaborator Author

@rbeckman-nextgen rbeckman-nextgen commented May 11, 2020

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.

Imported Comment. Original Details:
Author: narupley
Created: 2015-02-17T11:50:17.000-0800

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant
You can’t perform that action at this time.