"ORA-01000: maximum open cursors exceeded" on com.mirth.connect.server.migration.ServerMigrator

[rbeckman-nextgen]

I report the following blocking error to the startup of MirthConnect 3.5.1, which occurs when a large number of codetemplates is installed (in my case 280 codetemplates), With this condition, the startup of the MirtConnect service crashes with the following oracle error: "ORA-01000: maximum open cursors exceeded"

The problem is caused by the failure to close the statement, inside a loop, on the migrateSerializedData method of the com.mirth.connect.server.migration.ServerMigrator class. We have made a fix, closing the statement that performs an update query inside the loop. After performing a test with the same critical conditions, the error has been solved I report the fixed method:

/**

• It is assumed that for each migratable class that uses this an "id" column exists in the
• database, which is used as the primary key when updating the row. It's also assumed that for
• the time being, any additional columns besides the ID and serialized XML (e.g. name,
• revision) will not change during migration. */ private void migrateSerializedData(String selectSql, String updateSql, Class<?> expectedClass) { ObjectXMLSerializer serializer = ObjectXMLSerializer.getInstance(); Connection connection = null; Statement selectStatement = null; PreparedStatement updateStatement = null; ResultSet resultSet = null; ​ try { connection = getConnection(); selectStatement = connection.createStatement(); resultSet = selectStatement.executeQuery(selectSql); ​ while (resultSet.next()) { try { String id = resultSet.getString(1); String serializedData = resultSet.getString(2); Object obj = serializer.deserialize(serializedData, expectedClass); if (obj instanceof ExportClearable) { ((ExportClearable) obj).clearExportData(); } String migratedData = serializer.serialize(obj); ​ if (!migratedData.equals(serializedData)) { updateStatement = connection.prepareStatement(updateSql); updateStatement.setString(1, migratedData); updateStatement.setString(2, id); updateStatement.executeUpdate(); } } catch (Exception e) { logger.error("Failed to migrate serialized data", e); }finally { DbUtils.closeQuietly(updateStatement); //<-------------------- FIX }
  } } catch (SQLException e) { logger.error("Failed to migrate serialized data", e); } finally { DbUtils.closeQuietly(resultSet); DbUtils.closeQuietly(selectStatement); DbUtils.closeQuietly(updateStatement); } }

Imported Issue. Original Details: Jira Issue Key: MIRTH-4329 Reporter: fabryforce Created: 2018-09-20T09:09:05.000-0700

[rbeckman-nextgen]

OS(s) and JRE version: virtual Window 7 with JRE version 1.8.0_173 Version(s)/Build(s) to reproduce failure: mirthconnect-3.6.1.b220-windows-x64.zip Version(s)/Build(s) to verify fixes: mirthconnect-3.6.1.b226-windows-x64 Version(s)/Build(s) to verify fixes: mirthconnect-3.7.0.b2363-windows-x64 How Tested:

• Update mirth.properties to use Oracle for mirthdb (using Oracle 11gR2 - mirthdb3)
• Start up mcserver
• Create 300 code templates, Save Changes (see attachment)
• Restart mcserver and verify server startup properly

Observed Failure:

• After re-start mcserver, getting error below

• Failed to connect to Administrator "error connecting to server at specified address. Please verify server is up and running" `ERROR 2018-11-08 13:05:55,285 [Main Server Thread] com.mirth.connect.server.migration.ServerMigrator: Failed to migrate serialized data java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded ORA-01000: maximum open cursors exceeded

  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4875) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361) at com.mirth.connect.server.migration.ServerMigrator.migrateSerializedData(ServerMigrator.java:330) at com.mirth.connect.server.migration.ServerMigrator.migrateSerializedData(ServerMigrator.java:91) at com.mirth.connect.server.controllers.DefaultMigrationController.migrateSerializedData(DefaultMigrationController.java:136) at com.mirth.connect.server.Mirth.startup(Mirth.java:242) at com.mirth.connect.server.Mirth.run(Mirth.java:153) ERROR 2018-11-08 13:06:02,123 [Main Server Thread] com.mirth.connect.server.controllers.Cache: Code Template Library cache: Failed to load item b6ee540d-a610-431c-822e-d10cbb7701dc from the database org.apache.ibatis.exceptions.PersistenceException: ## Error querying database. Cause: com.mirth.connect.donkey.util.xstream.SerializerException: com.mirth.connect.donkey.util.DonkeyElement$DonkeyElementException: java.io.EOFException: input contained no data ## The error may exist in oracle/oracle-codeTemplate.xml ## The error may involve CodeTemplate.getLibrary-Inline ## The error occurred while setting parameters ## SQL: SELECT LIBRARY FROM CODE_TEMPLATE_LIBRARY WHERE ID = ? ## Cause: com.mirth.connect.donkey.util.xstream.SerializerException: com.mirth.connect.donkey.util.DonkeyElement$Donkey ElementException: java.io.EOFException: input contained no data at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:59) at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.apache.ibatis.session.SqlSessionManager$SqlSessionInterceptor.invoke(SqlSessionManager.java:282) at com.sun.proxy.$Proxy6.selectOne(Unknown Source) at org.apache.ibatis.session.SqlSessionManager.selectOne(SqlSessionManager.java:151) at com.mirth.connect.server.controllers.Cache.getItem(Cache.java:183) at com.mirth.connect.server.controllers.Cache.refreshCache(Cache.java:138) at com.mirth.connect.server.controllers.Cache.getAllItems(Cache.java:62) at com.mirth.connect.server.controllers.DefaultCodeTemplateController.getLibraries(DefaultCodeTemplateController.java:83) at com.mirth.connect.server.builders.JavaScriptBuilder.appendCodeTemplates(JavaScriptBuilder.java:528) at com.mirth.connect.server.builders.JavaScriptBuilder.generateScript(JavaScriptBuilder.java:99) at com.mirth.connect.server.util.javascript.JavaScriptUtil.compileAndAddScript(JavaScriptUtil.java:654) at com.mirth.connect.server.util.javascript.JavaScriptUtil.compileAndAddScript(JavaScriptUtil.java:633) at com.mirth.connect.server.util.javascript.JavaScriptUtil.compileAndAddScript(JavaScriptUtil.java:629) at com.mirth.connect.server.util.javascript.JavaScriptUtil.compileGlobalScripts(JavaScriptUtil.java:613) at com.mirth.connect.server.controllers.DefaultScriptController.compileGlobalScripts(DefaultScriptController.java:165) at com.mirth.connect.server.Mirth.startup(Mirth.java:292) at com.mirth.connect.server.Mirth.run(Mirth.java:153) Caused by: com.mirth.connect.donkey.util.xstream.SerializerException: com.mirth.connect.donkey.util.DonkeyElement$Donkey ElementException: java.io.EOFException: input contained no data at com.mirth.connect.model.converters.ObjectXMLSerializer.handleDeserializationException(ObjectXMLSerializer.java:406) at com.mirth.connect.model.converters.ObjectXMLSerializer.deserialize(ObjectXMLSerializer.java:286) at com.mirth.connect.server.sqlmap.extensions.SerializedObjectTypeHandler.getResult(SerializedObjectTypeHandler.java:40) at org.apache.ibatis.executor.resultset.FastResultSetHandler.createPrimitiveResultObject(FastResultSetHandler.java:425) at org.apache.ibatis.executor.resultset.FastResultSetHandler.createResultObject(FastResultSetHandler.java:372) at org.apache.ibatis.executor.resultset.FastResultSetHandler.createResultObject(FastResultSetHandler.java:355) at org.apache.ibatis.executor.resultset.FastResultSetHandler.getRowValue(FastResultSetHandler.java:255) at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleRowValues(FastResultSetHandler.java:214) at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSet(FastResultSetHandler.java:186) at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSets(FastResultSetHandler.java:152) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:57) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101) ... 21 more Caused by: com.mirth.connect.donkey.util.DonkeyElement$DonkeyElementException: java.io.EOFException: input contained no data at com.mirth.connect.donkey.util.DonkeyElement.fromXml(DonkeyElement.java:490) at com.mirth.connect.donkey.util.DonkeyElement.(DonkeyElement.java:53) at com.mirth.connect.model.converters.ObjectXMLSerializer.getDonkeyElement(ObjectXMLSerializer.java:428) at com.mirth.connect.model.converters.ObjectXMLSerializer.deserialize(ObjectXMLSerializer.java:261) ... 37 more Caused by: java.io.EOFException: input contained no data at org.xmlpull.mxp1.MXParser.fillBuf(MXParser.java:3003) at org.xmlpull.mxp1.MXParser.more(MXParser.java:3046) at org.xmlpull.mxp1.MXParser.parseProlog(MXParser.java:1410) at org.xmlpull.mxp1.MXParser.nextImpl(MXParser.java:1395) at org.xmlpull.mxp1.MXParser.next(MXParser.java:1093) at com.mirth.connect.donkey.util.DonkeyElement.fromXml(DonkeyElement.java:487) ... 40 more`

Imported Comment. Original Details: Author: minht Created: 2018-11-08T14:01:30.000-0800