Database Connections to MySQL get lost
Question:
JobScheduler
I encounter the error:
Error connecting to [host]:[port]: SOS-JAVA-105 Java-Exception java.sql.SQLException("No operations allowed after connection
closed."), methode=rollback []
Answer:
If the connection to your MySQL database was idle for some hours without any jobs running, then MySQL will close the connection without telling this to the client, i.e. JobScheduler.
Solution:
- To change this behavior you can change the value of the system variable
wait timeout
. This value assigns the maximum duration in seconds of non-interactive idle connections to the database. - Alternatively you could run a job like
scheduler_dequeue_mail
that is more frequently repeated; this job dequeues mails that have previously been stored in case of a failure of your mail server and creates a history record in the database even if no mails are to be sent.
JOC Cockpit :
I encounter the error:
ERROR qtp1973336893-20 c.s.j.c.JOCDefaultResponse - DBInvalidDataException: java.sql.SQLNonTransientConnectionException: Last stream not finished
Screenshot :
Answer
- To change this behavior you can change the value of the system variable
wait_timeout
. This value assigns the maximum duration in seconds of non-interactive idle connections to the database.
How to Change MySQL wait_timeout
- Steps to change wait_timeout server system variable
- Login into database which is used by JOC Cockpit via MySQL command line or any other MySQL GUI client such as MySQL Workbench. (IMP : following commands will only be effective for currently selected database)
- Set wait_timeout variable's value by running following command. To get the default and maximum value for idle
wait_timeout
for your platform refer following article Server System Variables : wait_timeoutset @wait_timeout = 31536000;
- To check updated value of wait_timeout variable by running following command
- select @wait_timeout;