MySQL Bugs: #107543: Cannot execute a SELECT statement that writes to an OUTFILE

Bug #107543	Cannot execute a SELECT statement that writes to an OUTFILE
Submitted:	13 Jun 2022 1:15	Modified:	4 Aug 2022 10:39
Reporter:	Ferindo Middleton Jr	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.29	OS:	Windows (10)
Assigned to:		CPU Architecture:	x86

Description:

I used to use "mysql-connector-java-5.1.36-bin.jar" and trying to to upgrade to "mysql-connector-java-8.0.29.jar".

Using mysql-connector-java-8.0.29.jar, when I execute a select statement that writes the result to an local OUTFILE on the server. If I use the executeUpdate, I get the message "Can not issue executeUpdate() or executeLargeUpdate() with statements that produce result sets" if I do the statement as a query, I get message that the result set is not scrollable.

I can use executeUpdate() in this manner and execute a statement writing to file under mysql-connector-java-5.1.36-bin.jar.

It appears that there is no way to write results to a file using this connector.

How to repeat:
Try using mysql-connector-java-5.1.36-bin.jar and use executeUpdate() to run a select query that writes to a local file on the server. You can do this.

Then try using executeUpdate() using mysql-connector-java-8.0.29.jar to run a select query that writes to a local file on the server and you get error that operation is not allowed because there a resultset. And then if you try to execute it as a command, you get error saying that's not allowed because the result is not scrollable.

Suggested fix:
Allow executeUpdate() to be run on a select statement that writes to a file under mysql-connector-java-8.0.29.jar the same way you can mysql-connector-java-5.1.36-bin.jar

Hi,

does turning on/off server side prepared statements change anything ?

Thanks

I tried turning on/off server side prepared statements. That didn't help. I still get the same error trying to write the query to a file.

The full story is I'm using Java Standard Tag Library (JSTL) in java server pages within a webapp running on Tomcat to perform these operations. So I used to be able to write query results to a file just fine using <sql:update> tag snippets in my JSPs. All my other SQL queries and updates work just fine when I update the driver to mysql-connector-java-8.0.29.jar, except for the ones that try and write to file. I even got loading files to my database tables to work by adding the allowLoadLocalInfile=true parameter to the URL the Tomcat config uses to connect to MySQL.

However, adding useServerPrepStmts=true or useServerPrepStmts=false to the connection URL Tomcat uses to connect gives the same error.

I even tried using <sql:query> tag with server-side prepared statements turned off and on and I get the other error: "Not a navigable ResultSet."

I sifted through the "MySQL Connector/J 8.0 Developer Guide" up on https://dev.mysql.com/. I didn't see much about operations that write to file. Is this just not supported in the newer driver?

I've been doing this fine for years since the release of mysql-connector-java-5.1.36-bin.jar. However, I've been using older MySQL, Tomcat, and the Connector/J. I've upgraded all three to the newest versions but the operations I run with writing results to a local file just seem to not be doable with Connector/J 8.0.

If there's no way to write to a file on the server using this connector, is there some way to write query results directly into another table column as a stream or binary BLOB of the file output using this connector? I think if I could atleast get the query output saved into another table's column, I have a servlet I put together to manually download a file as input stream from a BLOB value that was previously saved in a table.

Ferindo