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: | |
Category: | Connector / J | Severity: | S3 (Non-critical) |
Version: | 8.0.29 | OS: | Windows (10) |
Assigned to: | CPU Architecture: | x86 |
[13 Jun 2022 1:15]
Ferindo Middleton Jr
[13 Jun 2022 10:18]
MySQL Verification Team
Hi, does turning on/off server side prepared statements change anything ? Thanks
[19 Jun 2022 17:57]
Ferindo Middleton Jr
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
[22 Aug 2023 21:03]
Axyoan Marcelo
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)
[18 Oct 2023 18:46]
Filipe Silva
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.
[19 Nov 2023 1:00]
Bugs System
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".