Mirth Connect
  1. Mirth Connect
  2. MIRTH-4329

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

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 3.5.1
    • Fix Version/s: 3.7.0, 3.6.2
    • Component/s: Server
    • Environment:
      Mirth Connect 3.5.1
      Oracle 10
    • Operating System:
      Linux
    • Database:
      Oracle

      Description

      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
        Unknown macro: { 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); }

        }

        Activity

        Hide
        Minh Tran added a comment -

        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:

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

        Observed Failure:

        1. After re-start mcserver, getting error below
        2. 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.<init>(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
        Show
        Minh Tran added a comment - 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.<init>(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

          People

          • Assignee:
            Nick Rupley
            Reporter:
            Fabrizio Mioli
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: