| Bug #92874 | Column 'Key_name' not found | ||
|---|---|---|---|
| Submitted: | 21 Oct 2018 8:54 | Modified: | 4 Jan 2019 17:40 |
| Reporter: | Antonio Mercurio | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 8.0.12 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | CallableStatement, Key_name, ResultSet.CONCUR_UPDATABLE | ||
[22 Oct 2018 7:44]
Alexander Soklakov
Hi Antonio, Please provide a reproducible test case and full stack trace so we could find the reason. Thanks, Alex
[22 Oct 2018 19:47]
Antonio Mercurio
How to repeat:
step 1:
create the following table into MySQL database:
CREATE TABLE `Orders` (
`orderID` int(11) NOT NULL,
`description` varchar(50) DEFAULT NULL,
`dataOrder` date DEFAULT NULL,
`statusOrder` varchar(3) DEFAULT NULL,
PRIMARY KEY (`orderID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
step 2:
create the following stored procedure into MySQL database:
CREATE DEFINER=`root`@`localhost` PROCEDURE `dbquery`.`cur_Orders`(pi_parametro INT)
BEGIN
DECLARE s VARCHAR(20);
IF pi_parametro = 1 THEN
SELECT orderID,
description,
dataOrder,
statusOrder
FROM Orders
ORDER BY orderID;
ELSE
SELECT orderID,
description,
dataOrder,
statusOrder
FROM Orders
ORDER BY 3;
END IF;
END
step3:
create java function like this:
private Connection dbconn = null;
private HttpSession datibasisessio = null;
private Statement stmt = null;
private ResultSet rs = null;
boolean is_rs = false;
int tmp;
stmt = dbconn.prepareCall("{call cur_Orders(1)}",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE,
ResultSet.CLOSE_CURSORS_AT_COMMIT);
is_rs = sql_propositio.execute();
if (is_rs == true) {
rs = sql_propositio.getResultSet();
tmp = rs.getConcurrency();
}
rs.updateString(2, "ciao");
rs.updateRow();
full stack trace:
ott 22, 2018 9:42:30 PM org.apache.catalina.core.ApplicationContext log
GRAVE: dbquery: doPost
com.mysql.cj.jdbc.exceptions.NotUpdatable: Column 'Key_name' not found.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
at com.mysql.cj.jdbc.result.UpdatableResultSet.generateStatements(UpdatableResultSet.java:575)
at com.mysql.cj.jdbc.result.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1213)
at com.mysql.cj.jdbc.result.UpdatableResultSet.updateInt(UpdatableResultSet.java:1529)
at org.apache.tomcat.dbcp.dbcp2.DelegatingResultSet.updateInt(DelegatingResultSet.java:479)
at org.apache.tomcat.dbcp.dbcp2.DelegatingResultSet.updateInt(DelegatingResultSet.java:479)
at dbquery.dbquery.transdatibasiscriptio(dbquery.java:301)
at dbquery.dbquery.doPost(dbquery.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:651)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:501)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:754)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1376)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1135)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:844)
ott 22, 2018 9:42:30 PM org.apache.catalina.core.ApplicationContext log
GRAVE: dbquery: messaggio principale nella doPost
dbquery.dbqueryException: Column 'Key_name' not found.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
at dbquery.dbquery.transdatibasiscriptio(dbquery.java:394)
at dbquery.dbquery.doPost(dbquery.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:651)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:501)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:754)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1376)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1135)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:844)
Caused by: com.mysql.cj.jdbc.exceptions.NotUpdatable: Column 'Key_name' not found.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
at com.mysql.cj.jdbc.result.UpdatableResultSet.generateStatements(UpdatableResultSet.java:575)
at com.mysql.cj.jdbc.result.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1213)
at com.mysql.cj.jdbc.result.UpdatableResultSet.updateInt(UpdatableResultSet.java:1529)
at org.apache.tomcat.dbcp.dbcp2.DelegatingResultSet.updateInt(DelegatingResultSet.java:479)
at org.apache.tomcat.dbcp.dbcp2.DelegatingResultSet.updateInt(DelegatingResultSet.java:479)
at dbquery.dbquery.transdatibasiscriptio(dbquery.java:301)
... 25 more
[22 Oct 2018 19:49]
Antonio Mercurio
Script MySQL and java
Attachment: My_SQL_bug_callableStatement_updatable_resultSet.txt (text/plain), 7.78 KiB.
[4 Dec 2018 17:40]
Filipe Silva
Hi Antonio, Your code doesn't run but once you fix it, it works. Note that your are initializing the variable `stmt` in stmt = dbconn.prepareCall(... But then you call: ... sql_propositio.execute(); And you are missing at least one ResultSet navigation instruction, such as `rs.next()` after getting the result set. I don't know where this `sql_propositio` comes from but definitely your test case doesn't expose the behavior you're describing. Please check your code again, fix it, and report back to us if it still returns errors.
[5 Jan 2019 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".
Description: When I use a resultset created by CallableStatement like this: rs = dbconn.prepareCall("{call cur_Orders(1)}", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT); and I'm trying to update the resultset, thorws the following exception: Column 'Key_name' not found.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details. How to repeat: step 1: create a callable statement with resultset updatable; ste 2: return resultset and try to update it. Suggested fix: there seems to be a problem when reading database metadata. In fact the column 'Key_name' is used in the method getPrimaryKeys of class DatabaseMetaData.java