SATISHEEEK commented 3 months ago

Describe the bug

Dear Quarkus team, we're having troubles while upgrading Quarkus 2 -> Quarkus 3.. JasperReports library started to fail on active transaction issue.

JasperReports executes subreports/tables in multi threads. Therefore I have a reason to think that my issue could be similiar to: https://stackoverflow.com/questions/68058274/transactional-does-not-work-in-sub-thread-in-quarkus

DESCRIPTION

@ApplicationScoped class is calls JasperReports engine
the transaction is active until Jasper starts to work in sub-threads
instead of static JasperFillManager.fillReport() I tried to call non-static alternative, but without any difference
same code has worked for years with Quarkus 2 :( Error occurres for Quarkus 3 (exactly: 3.7.4)

MY QUESTIONS

do you guys have any experience with combination Quarkus 3 + Jasper ?
Is there any resource to this multi-threading issue?
What shall I do? I am no able to execute any report because all of our reports have at least 1 subreport..

Thank you for your time and help!

Detailed description of my issue: https://stackoverflow.com/questions/78793479/quarkus3-jasperreports-got-java-sql-sqlexception-enlisted-connection-used-wit

ERROR LOG:

2024-07-22T21:24:13.597381608Z executor-thread-1 23 2024-07-22 23:24:13.558  ERROR net.sf.jasperreports.engine.fill.JRFillSubreport Fill 1: exception: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:
2024-07-22T21:24:13.597462370Z 
2024-07-22T21:24:13.597475177Z SELECT
2024-07-22T21:24:13.597486237Z     *
2024-07-22T21:24:13.597502662Z FROM
2024-07-22T21:24:13.597519521Z     MY_TABLE
2024-07-22T21:24:13.597530077Z ORDER BY
2024-07-22T21:24:13.597538483Z     col
2024-07-22T21:24:13.597547363Z 
2024-07-22T21:24:13.597555636Z 
2024-07-22T21:24:13.597574220Z  at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:555)
2024-07-22T21:24:13.597810783Z  at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:299)
2024-07-22T21:24:13.598088743Z  at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1317)
2024-07-22T21:24:13.598244727Z  at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:767)
2024-07-22T21:24:13.598340997Z  at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:465)
2024-07-22T21:24:13.598391111Z  at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:585)
2024-07-22T21:24:13.598464565Z  at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:416)
2024-07-22T21:24:13.598525214Z  at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:827)
2024-07-22T21:24:13.598583947Z  at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:61)
2024-07-22T21:24:13.598621143Z  at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
2024-07-22T21:24:13.598716311Z  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
2024-07-22T21:24:13.598803387Z  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
2024-07-22T21:24:13.598913843Z  at java.base/java.lang.Thread.run(Thread.java:840)
2024-07-22T21:24:13.599023284Z Caused by: java.sql.SQLException: Enlisted connection used without active transaction
2024-07-22T21:24:13.599110173Z  at io.agroal.pool.ConnectionHandler.verifyEnlistment(ConnectionHandler.java:398)
2024-07-22T21:24:13.599149982Z  at io.agroal.pool.wrapper.ConnectionWrapper.getMetaData(ConnectionWrapper.java:452)
2024-07-22T21:24:13.599212173Z  at net.sf.jasperreports.engine.query.OracleProcedureCallHandler.isOracle(OracleProcedureCallHandler.java:72)
2024-07-22T21:24:13.599341106Z  at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.isProcedureCall(JRJdbcQueryExecuter.java:566)
2024-07-22T21:24:13.599481640Z  at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:389)
2024-07-22T21:24:13.599597852Z  ... 12 more

Output of java -version 17 Quarkus version or git rev 3.7.4

mmusgrov commented 2 months ago

I don't know what your setup is, but by default transactions do not propagate to other threads unless you are using smallrye-context-propagation (https://quarkus.io/guides/context-propagation#setting-it-up). It's odd that it worked before, maybe the defaults have changed, but can you include that dependency in your pom and retest.

gastaldi commented 2 months ago

Also please give a try with the latest Quarkus (3.13.2 as of now), 3.7.4 is not maintained anymore

SATISHEEEK commented 2 months ago

@mmusgrov & @gastaldi - thank you so much for your input guys, its much appreciated.

Adding depedency alone didn't help unfortunately, but I'll do some more test and experiments this week and give feedback afterwards.

Meanwhile thank you for your tips! I'll test them asap.

BR, Jan

gastaldi commented 2 months ago

@SATISHEEEK have you been able to find a workaround? It would be nice if you could provide a small reproducer too

SATISHEEEK commented 1 month ago

Hi @gastaldi , sorry for late reply. Currently I am sharing this issue with more experienced colleague, if we're not successful I'll provide an reproducer. Thanks for keeping your eyes on our issue. Jan

geoand commented 1 month ago

@SATISHEEEK any updates on this?

Thanks

SATISHEEEK commented 1 month ago

@geoand Hi guys, unfortunately we were not able to find any solution.

So I am currently building a clean project with the same structure so we can share the code sample with you. I'll upload it asap. Thanks!

geoand commented 1 month ago

🙏🏽

nderwin commented 1 month ago

I had the same issue, and traced it down to the default class used to run subreports - https://github.com/TIBCOSoftware/jasperreports/blob/a8ec0e6cbbf04d36878c8f783f10c2ff88afeff5/core/src/main/java/net/sf/jasperreports/engine/fill/JRThreadSubreportRunner.java#L60

I did find a workaround by using a different runner for the subreports; adding the property JRSubreportRunnerFactory.SUBREPORT_RUNNER_FACTORY to the report context, set to net.sf.jasperreports.engine.fill.JRContinuationSubreportRunnerFactory; then I used the net.sf.jasperreports:jasperreports-javaflow dependency instead of net.sf.jasperreports:jasperreports.

There's not as many versions of that dependency published as the main library, however.

geoand commented 3 weeks ago

Let's close this for now, but if a sample becomes available please add it and we can reopen

nderwin commented 3 weeks ago

Here's a simple example using a database that shows the error, and the 2 changes that I mentioned before that make it work.

Run the app in dev mode (./mvnw clean compile quarkus:dev), then curl localhost:8080/hello/report.

jasperreports-with-quarkus.zip

gastaldi commented 3 weeks ago

That's an issue for https://github.com/quarkiverse/quarkus-jasperreports, which handles this integration better. May I ask you to file an issue there instead?

nderwin commented 3 weeks ago

@gastaldi I'll leave that to the OP, however, my reproducer isn't using the quarkus-jasperreports extension, just the naked jasperreports dependency.

Maybe this whole issue could be moved there?

gastaldi commented 3 weeks ago

Maybe this whole issue could be moved there?

I don't think GitHub allows us to move issues between repositories in different organizations, at least the UI doesn't let me do that

gastaldi commented 2 weeks ago

@melloware FYI

melloware commented 2 weeks ago

Thanks I am going to test this next on my list to make sure it works!

melloware commented 2 weeks ago

I just opened this ticket: https://github.com/quarkiverse/quarkus-jasperreports/issues/100

melloware commented 2 weeks ago

I got to the bottom of this and added an Integration Tests in Quarkus JasperReports extension. There is no need to be running a report inside a transaction as reports do not INSERT/UPDATE/DELETE data only select.

Database Datasource

When utilizing an Agroal Datasource to generate your report, ensure it is not within an active transaction. JasperReports employs threads for sub-reports and various features, and a connection cannot be passed across thread boundaries while in a transaction. Failing to do so will result in the following error: Enlisted connection used without active transaction.

Caused by: java.sql.SQLException: Enlisted connection used without active transaction
2024-07-22T21:24:13.599110173Z  at io.agroal.pool.ConnectionHandler.verifyEnlistment(ConnectionHandler.java:398)
2024-07-22T21:24:13.599149982Z  at io.agroal.pool.wrapper.ConnectionWrapper.getMetaData(ConnectionWrapper.java:452)
2024-07-22T21:24:13.599212173Z  at net.sf.jasperreports.engine.query.OracleProcedureCallHandler.isOracle(OracleProcedureCallHandler.java:72)
2024-07-22T21:24:13.599341106Z  at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.isProcedureCall(JRJdbcQueryExecuter.java:566)
2024-07-22T21:24:13.599481640Z  at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:389)

[!IMPORTANT]
In your service you must use @Transactional(Transactional.TxType.NEVER) above the method that is filling your report.

@Inject
DataSource datasource;

@Transactional(Transactional.TxType.NEVER)
public byte[] text() throws JRException, SQLException {
    JasperPrint jasperPrint = null;
     try (final Connection connection = datasource.getConnection()) {
         params.put(JRParameter.REPORT_CONNECTION, connection);

         jasperPrint = JRFiller.fill(DefaultJasperReportsContext.getInstance(), SimpleJasperReportSource.from(mainReport, null, new SimpleRepositoryResourceContext()), params);
     }
    final JRTextExporter exporter = new JRTextExporter();
    exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    exporter.setExporterOutput(new SimpleWriterExporterOutput(outputStream));
    exporter.exportReport();
    return outputStream.toByteArray();
}

quarkusio / quarkus

Quarkus 3 + JasperReports - java.sql.SQLException: Enlisted connection used without active transaction #42210

Describe the bug

Database Datasource