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.

Sign up for GitHub

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

Jump to bottom

XAER_OUTSIDE when BulkData Export on Patient is run twice #2001

Closed
prb112 opened this issue Feb 28, 2021 · 2 comments
Closed

XAER_OUTSIDE when BulkData Export on Patient is run twice #2001

prb112 opened this issue Feb 28, 2021 · 2 comments
Assignees
@prb112 @punktilious
Labels
bug Something isn't working bulk-data P3 Priority 3 - Nice To Have
Milestone
Sprint 2021-07

Comments

@prb112
Copy link
Contributor

prb112 commented Feb 28, 2021 •
edited
Loading

Describe the bug
XAER_OUTSIDE when BulkData Export on Patient is run twice

Two transactions are created which conflict on boundaries in Patient ChunkReader
Also... missing XA Datasource definitions

Global temporary tables can be used in XA transactions, but can be declared and accessed only within the scope of a single XA transaction. Derby can support access to the table only until one of the following methods of the javax.transaction.xa.XAResource interface is called:

XAResource.end
XAresource.prepare
XAResource.commit

When the XA transaction commits or aborts, the temporary table is dropped.

[err] java.sql.SQLSyntaxErrorException: Table/View 'SESSION.CODE_SYSTEMS_TMP' does not exist.
[err] 	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
[err] 	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
[err] 	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
[err] 	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
[err] 	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
[err] 	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
[err] 	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
[err] 	at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
[err] 	at org.apache.derby.iapi.jdbc.BrokeredStatement.executeQuery(Unknown Source)
[err] 	at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeQuery(WSJdbcStatement.java:566)
[err] 	at com.ibm.fhir.persistence.jdbc.derby.CreateCodeSystemsTmp.isExists(CreateCodeSystemsTmp.java:54)
[err] 	at com.ibm.fhir.persistence.jdbc.derby.CreateCodeSystemsTmp.run(CreateCodeSystemsTmp.java:28)
[err] 	at com.ibm.fhir.database.utils.common.JdbcTarget.runStatement(JdbcTarget.java:69)
[err] 	at com.ibm.fhir.database.utils.common.CommonDatabaseAdapter.runStatement(CommonDatabaseAdapter.java:466)
[err] 	at com.ibm.fhir.database.utils.derby.DerbyAdapter.runStatement(DerbyAdapter.java:302)
[err] 	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.createCodeSystemsTmp(CreateTempTablesAction.java:78)
[err] 	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.performOn(CreateTempTablesAction.java:53)
[err] 	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.configure(FHIRDbConnectionStrategyBase.java:70)
[err] 	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.getConnection(FHIRDbConnectionStrategyBase.java:139)
[err] 	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbTenantDatasourceConnectionStrategy.getConnection(FHIRDbTenantDatasourceConnectionStrategy.java:168)
[err] 	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.openConnection(FHIRPersistenceJDBCImpl.java:1622)
[err] 	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.search(FHIRPersistenceJDBCImpl.java:622)
[err] 	at com.ibm.fhir.jbatch.bulkdata.export.patient.ChunkReader.readItem(ChunkReader.java:180)
[err] 	at com.ibm.jbatch.container.artifact.proxy.ItemReaderProxy.readItem(ItemReaderProxy.java:77)
[err] 	at [internal classes]
[err] 	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
[err] 	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
[err] 	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
[err] 	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
[err] 	at java.base/java.lang.Thread.run(Thread.java:836)
[err] Caused by: 
[err] ERROR 42X05: Table/View 'SESSION.CODE_SYSTEMS_TMP' does not exist.
[err] 	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
[err] 	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
[err] 	at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
[err] 	at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
[err] 	at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
[err] 	at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
[err] 	at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
[err] 	at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
[err] 	at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
[err] 	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
[err] 	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
[err] 	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
[err] 	... 36 more

[WARNING ] Skipping index entry 2 due to one or more missing required fields, beginning with: url
[ERROR   ] DSRA0304E:  XAException occurred. XAException contents and details are: .
[ERROR   ] DSRA0302E:  XAException occurred.  Error code is: XAER_OUTSIDE (-9).  Exception is: null
[ERROR   ] J2CA0027E: An exception occurred while invoking start on an XA Resource Adapter from DataSource jdbc/fhir_default_default, within transaction ID {XidImpl: formatId(57415344), gtrid_length(36), bqual_length(54),
data(00000177ea1ff82e0000000128686de8f845a10b3c21b6ab18cf9b49d300c85df5fa439600000177ea1ff82e0000000128686de8f845a10b3c21b6ab18cf9b49d300c85df5fa4396000000010000000000000000000000000001)} : javax.transaction.xa.XAException
	at org.apache.derby.jdbc.EmbedXAResource.start(Unknown Source)
	at com.ibm.ws.rsadapter.impl.WSRdbXaResourceImpl.start(WSRdbXaResourceImpl.java:1086)
	at [internal classes]
	at com.ibm.fhir.persistence.jdbc.derby.CreateCodeSystemsTmp.isExists(CreateCodeSystemsTmp.java:52)
	at com.ibm.fhir.persistence.jdbc.derby.CreateCodeSystemsTmp.run(CreateCodeSystemsTmp.java:28)
	at com.ibm.fhir.database.utils.common.JdbcTarget.runStatement(JdbcTarget.java:69)
	at com.ibm.fhir.database.utils.common.CommonDatabaseAdapter.runStatement(CommonDatabaseAdapter.java:466)
	at com.ibm.fhir.database.utils.derby.DerbyAdapter.runStatement(DerbyAdapter.java:302)
	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.createCodeSystemsTmp(CreateTempTablesAction.java:78)
	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.performOn(CreateTempTablesAction.java:53)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.configure(FHIRDbConnectionStrategyBase.java:70)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.getConnection(FHIRDbConnectionStrategyBase.java:139)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbTenantDatasourceConnectionStrategy.getConnection(FHIRDbTenantDatasourceConnectionStrategy.java:168)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.openConnection(FHIRPersistenceJDBCImpl.java:1622)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.search(FHIRPersistenceJDBCImpl.java:622)
	at com.ibm.fhir.jbatch.bulkdata.export.patient.ChunkReader.readItem(ChunkReader.java:324)
	at com.ibm.jbatch.container.artifact.proxy.ItemReaderProxy.readItem(ItemReaderProxy.java:77)
	at [internal classes]
.
[ERROR   ] J2CA0030E: Method enlist caught javax.transaction.RollbackException: XAResource working outside transaction
	at com.ibm.tx.jta.impl.RegisteredResources.startRes(RegisteredResources.java:850)
	at [internal classes]
	at com.ibm.fhir.persistence.jdbc.derby.CreateCodeSystemsTmp.isExists(CreateCodeSystemsTmp.java:52)
	at com.ibm.fhir.persistence.jdbc.derby.CreateCodeSystemsTmp.run(CreateCodeSystemsTmp.java:28)
	at com.ibm.fhir.database.utils.common.JdbcTarget.runStatement(JdbcTarget.java:69)
	at com.ibm.fhir.database.utils.common.CommonDatabaseAdapter.runStatement(CommonDatabaseAdapter.java:466)
	at com.ibm.fhir.database.utils.derby.DerbyAdapter.runStatement(DerbyAdapter.java:302)
	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.createCodeSystemsTmp(CreateTempTablesAction.java:78)
	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.performOn(CreateTempTablesAction.java:53)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.configure(FHIRDbConnectionStrategyBase.java:70)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.getConnection(FHIRDbConnectionStrategyBase.java:139)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbTenantDatasourceConnectionStrategy.getConnection(FHIRDbTenantDatasourceConnectionStrategy.java:168)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.openConnection(FHIRPersistenceJDBCImpl.java:1622)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.search(FHIRPersistenceJDBCImpl.java:622)
	at com.ibm.fhir.jbatch.bulkdata.export.patient.ChunkReader.readItem(ChunkReader.java:324)
	at com.ibm.jbatch.container.artifact.proxy.ItemReaderProxy.readItem(ItemReaderProxy.java:77)
	at [internal classes]
Caused by: javax.transaction.xa.XAException
	at org.apache.derby.jdbc.EmbedXAResource.start(Unknown Source)
	at com.ibm.ws.rsadapter.impl.WSRdbXaResourceImpl.start(WSRdbXaResourceImpl.java:1086)
	... 16 more
 while trying to enlist resources from DataSource jdbc/fhir_default_default with the Transaction Manager for the current transaction, and threw a ResourceException.
[ERROR   ] DSRA0304E:  XAException occurred. XAException contents and details are: .
[ERROR   ] DSRA0302E:  XAException occurred.  Error code is: XAER_OUTSIDE (-9).  Exception is: null
[ERROR   ] J2CA0027E: An exception occurred while invoking start on an XA Resource Adapter from DataSource jdbc/fhir_default_default, within transaction ID {XidImpl: formatId(57415344), gtrid_length(36), bqual_length(54),
data(00000177ea1ff82e0000000128686de8f845a10b3c21b6ab18cf9b49d300c85df5fa439600000177ea1ff82e0000000128686de8f845a10b3c21b6ab18cf9b49d300c85df5fa4396000000010000000000000000000000000001)} : javax.transaction.xa.XAException
	at org.apache.derby.jdbc.EmbedXAResource.start(Unknown Source)
	at com.ibm.ws.rsadapter.impl.WSRdbXaResourceImpl.start(WSRdbXaResourceImpl.java:1086)
	at [internal classes]
	at com.ibm.fhir.persistence.jdbc.derby.CreateCodeSystemsTmp.run(CreateCodeSystemsTmp.java:34)
	at com.ibm.fhir.database.utils.common.JdbcTarget.runStatement(JdbcTarget.java:69)
	at com.ibm.fhir.database.utils.common.CommonDatabaseAdapter.runStatement(CommonDatabaseAdapter.java:466)
	at com.ibm.fhir.database.utils.derby.DerbyAdapter.runStatement(DerbyAdapter.java:302)
	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.createCodeSystemsTmp(CreateTempTablesAction.java:78)
	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.performOn(CreateTempTablesAction.java:53)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.configure(FHIRDbConnectionStrategyBase.java:70)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.getConnection(FHIRDbConnectionStrategyBase.java:139)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbTenantDatasourceConnectionStrategy.getConnection(FHIRDbTenantDatasourceConnectionStrategy.java:168)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.openConnection(FHIRPersistenceJDBCImpl.java:1622)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.search(FHIRPersistenceJDBCImpl.java:622)
	at com.ibm.fhir.jbatch.bulkdata.export.patient.ChunkReader.readItem(ChunkReader.java:324)
	at com.ibm.jbatch.container.artifact.proxy.ItemReaderProxy.readItem(ItemReaderProxy.java:77)
	at [internal classes]
.
[ERROR   ] J2CA0030E: Method enlist caught javax.transaction.RollbackException: XAResource working outside transaction
	at com.ibm.tx.jta.impl.RegisteredResources.startRes(RegisteredResources.java:850)
	at [internal classes]
	at com.ibm.fhir.persistence.jdbc.derby.CreateCodeSystemsTmp.run(CreateCodeSystemsTmp.java:34)
	at com.ibm.fhir.database.utils.common.JdbcTarget.runStatement(JdbcTarget.java:69)
	at com.ibm.fhir.database.utils.common.CommonDatabaseAdapter.runStatement(CommonDatabaseAdapter.java:466)
	at com.ibm.fhir.database.utils.derby.DerbyAdapter.runStatement(DerbyAdapter.java:302)
	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.createCodeSystemsTmp(CreateTempTablesAction.java:78)
	at com.ibm.fhir.persistence.jdbc.connection.CreateTempTablesAction.performOn(CreateTempTablesAction.java:53)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.configure(FHIRDbConnectionStrategyBase.java:70)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbConnectionStrategyBase.getConnection(FHIRDbConnectionStrategyBase.java:139)
	at com.ibm.fhir.persistence.jdbc.connection.FHIRDbTenantDatasourceConnectionStrategy.getConnection(FHIRDbTenantDatasourceConnectionStrategy.java:168)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.openConnection(FHIRPersistenceJDBCImpl.java:1622)
	at com.ibm.fhir.persistence.jdbc.impl.FHIRPersistenceJDBCImpl.search(FHIRPersistenceJDBCImpl.java:622)
	at com.ibm.fhir.jbatch.bulkdata.export.patient.ChunkReader.readItem(ChunkReader.java:324)
	at com.ibm.jbatch.container.artifact.proxy.ItemReaderProxy.readItem(ItemReaderProxy.java:77)
	at [internal classes]
Caused by: javax.transaction.xa.XAException
	at org.apache.derby.jdbc.EmbedXAResource.start(Unknown Source)
	at com.ibm.ws.rsadapter.impl.WSRdbXaResourceImpl.start(WSRdbXaResourceImpl.java:1086)
	... 15 more
 while trying to enlist resources from DataSource jdbc/fhir_default_default with the Transaction Manager for the current transaction, and threw a ResourceException.
[ERROR   ] DECLARE GLOBAL TEMPORARY TABLE SESSION.code_systems_tmp (  code_system_name VARCHAR(1024) ) NOT LOGGED
enlist: caught Exception

To Reproduce
Steps to reproduce the behavior:

  1. Go to '...'
  2. Click on '....'
  3. Scroll down to '....'
  4. See error

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.
@prb112 prb112 added bug Something isn't working bulk-data labels Feb 28, 2021
@prb112 prb112 added this to the Sprint 2021-03 milestone Feb 28, 2021
@prb112 prb112 self-assigned this Feb 28, 2021
@prb112 prb112 linked a pull request Feb 28, 2021 that will close this issue
Improve ImportExport with JavaBatch #1999
Merged
@prb112 prb112 added P1 Priority 1 - Must Have P2 Priority 2 - Should Have and removed P1 Priority 1 - Must Have labels Mar 2, 2021
prb112 added a commit that referenced this issue Mar 2, 2021
@prb112
Improve ImportExport with JavaBatch
fd77939 
- Change Path Prefix for Hmac Signed AWS Signatures
- Fix Tmp Table error with XAER_OUTSIDE when BulkData Export on Patient
is run twice #2001
- Add logging and toString for CheckPoint/Transient data elements for
Export
- There used to be an else path here where since is null, then set
<code>builder.fhirSearchFromDate("1970-01-01T00:00:00Z");</code>.
	- It drove needless parameters in the query. By removing this path, we
let the db use the optimal selectivity.
- **ImplNote** only create FHIRPerstistence once per Job, not once per
readItem
- FileWrapper - marked the File Creation/Write as APPEND/CREATE
otherwise there is dataloss in the write to disk.
- Update BulkData.xml to explictly call out the XADatasources
- Normalize the treatment of the ChunkReaders on Export and Handling of
FILL Buffers
- DownloadUrl when output type is fiel, it points to a Local Directory
instead of defaulting to a presigned URL
- For Https Exports, a preflight check shortcircuits:
	- If inputs are null, then we know we want to stop this right away.
	- We just don't export to HTTPS. Only S3 Compatible and File
- Remove GSON dependency
- Add Dynamic Trust Property to turn off Cert validation wqhen using the
batch api
- Update Configuration with a md file
- Add a src/test/sh/
- Add Preflight validation s3,https,Nop,File
- Modify the Core encryption key so it is static across all instances.
- Removed out of date batch sql

Signed-off-by: Paul Bastide <pbastide@us.ibm.com>
@prb112 prb112 removed a link to a pull request Mar 4, 2021
Improve ImportExport with JavaBatch #1999
Merged
@prb112 prb112 removed this from the Sprint 2021-03 milestone Mar 5, 2021
@prb112 prb112 removed their assignment Mar 5, 2021
@lmsurpre lmsurpre added P3 Priority 3 - Nice To Have and removed P2 Priority 2 - Should Have labels Mar 15, 2021
@lmsurpre
Copy link
Member

We can only reproduce this when we mix db types (e.g. derby and db2). Therefor, I reduced the priority.

@prb112 prb112 added this to the Sprint 2021-07 milestone May 27, 2021
@prb112
Copy link
Contributor Author

prb112 commented May 27, 2021

FYI - I tried this with the recent SP changes and datasource changes, and under this specific combination the issue is resolved. It's no longer valid.

@prb112 prb112 closed this as completed May 27, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@prb112 prb112

@punktilious punktilious

Labels
bug Something isn't working bulk-data P3 Priority 3 - Nice To Have
Projects
None yet
Milestone
Sprint 2021-07
Development

No branches or pull requests
3 participants
@prb112 @lmsurpre @punktilious