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

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

Closed
rbeckman-nextgen opened this issue May 11, 2020 · 1 comment
Milestone

Comments

@rbeckman-nextgen
Copy link
Collaborator

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

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
Copy link
Collaborator Author

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

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

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.