java.sql.SQLException: setMaxRows() out of range. 1000000000 > 50000000

Issue Description

A customer encountered an error when running a report, the exception in the logs was: java.sql.SQLException: setMaxRows() out of range. 70000000 > 50000000. The stacktrace indicated that the exception was coming from a java package under com.mysql.jdbc, the customer's database was mysql.

Resolution

The exception is being thrown from the Mysql JDBC Driver. Prepared Statements are the means by which TIBCO JasperReports® Server executes sql statements. When using "Ad Hoc Dataset Row Limit" the JasperReports® Server calls the setMaxRows method of the JDBC driver to add a limit statement to the query. The customer had set 70 million as their "Ad Hoc Dataset Row Limit", unfortunately the Mysql JDBC driver has a hard-coded maximum of 50 million for their limit statement that can be appended.

The workaround, if 50 million rows isn't enough, is to ensure your db connection is using the MariaDB driver. JasperReports® Server publishes the MariaDB driver by default, so this might be as easy as removing all Mysql *.jar files from the lib dir of your appserver and from the WEB-INFlib dir of JasperReports® Server. Other options include forcing your db connection to be JNDI-based in which you specify org.mariadb.jdbc.Driver as the class name for the jdbc driver of the new JNDI resource.

Ref. Case 01793407