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:	18 Nov 2023 18:46
Reporter:	Ferindo Middleton Jr	Email Updates:
Status:	No Feedback	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

Posted by developer:
 
Hi,

Have you tried using executeQuery() instead?
executeUpdate() is supposed to be used for INSERT, UPDATE, or DELETE statements or an SQL statement that returns nothing. 
executeQuery() should be able to work just fine with SELECT statements (including those writing to an outfile)

Hi Ferindo,

Thank you for taking the time to report this issue.

Like Axyoan explained, Statement.executeUpdate() is supposed to execute queries that don't produce results and SELECT typically returns results. Given that the connector doesn't fully parse queries, the specific case of SELECT ... INTO, which doesn't produce results, is ruled out.

The alternative is to execute it using Statement.executeQuery(), or Statement.execute() and check that this call returns 'false'.

The library you are using, though, seems to try to always read the ResultSet produced by the call to Statement.executeQuery(), even though it's not navigable. Is there an API to access Statement.execute()? If so, then this should work for you. If not, then maybe you could try enabling multi-queries ("allowMultiQueries=true") and introduce a fake query before the main one, something like "SELECT 1; SELECT ... INTO OUTFILE ...".

Please let us know if this works for you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".