Hello,
We have identified issue on our side where jdbc.statementTimeout doesn't works as expected.
Technology stack
ORDS 19.4.6
Tomcat 9
Oracle 12.2
JRE 1.8
Linux
Context
We want to prevent our API service to consume database resource more that necessary, for that our requirement is to have services that should respond in less than 60 seconds. All calls that exceed 60 seconds is returned with a HTTP status 504 (timeout).
For that, we have set the HTTP timeout at web application server level and it works as excepted.
Even the HTTP timeout stop the client request, ORDS call continue to works until the SQL statement is completed or, according the documentation, until the value of jdbc.statementTimeout is reached.
In order to avoid long running ORDS call and recover the connection, we want to reduce the default statementTimeout to 90 seconds, I guess the purpose of this parameter was in that sense.
Issue
This parameter doesn't fully work as expected as ORDS wait the end of the SQL statement and not the jdbc.statementTimeout value before sending the 555 http status with the error message:
SQL Error Code: No more data to read from socket
Step to reproduce
Create ORDS resource that emulate long query: (grant the DBMS_LOCK package to your current schema)
DECLARE
l_module VARCHAR2(100) := 'demo.example';
l_resource VARCHAR2(100);
BEGIN
-- delete all REST services and disable REST services for this schema
ords.drop_rest_for_schema();
-- enable REST for this schema
ords.enable_schema();
-- delete module if exist
ords.delete_module(p_module_name => l_module);
-- define module
ords.define_module(p_module_name => l_module,
p_base_path => '/',
p_items_per_page => 0,
p_status => 'PUBLISHED',
p_comments => NULL);
-------------------------------------------------------------------------------------------------
l_resource := 'sleep';
-- define resource template
ords.define_template(p_module_name => l_module,
p_pattern => l_resource,
p_priority => 0,
p_etag_type => 'NONE',
p_etag_query => NULL,
p_comments => NULL);
-- define resource handler
ords.define_handler(p_module_name => l_module,
p_pattern => l_resource,
p_method => 'GET',
p_source_type => ords.source_type_plsql,
p_source => q'[DECLARE
BEGIN
dbms_lock.sleep(NVL(:seconds, 1));
END;
]',
p_items_per_page => 0,
p_mimes_allowed => NULL,
p_comments => NULL);
COMMIT;
END;
/
Configure ORDS to reach your database and add the jdbc.statementTimeout
<entry key="jdbc.statementTimeout">10</entry>
Run ORDS and invoke the resource to validate: /ords/your-schema/sleep
The result should be HTTP 200
Now invoke again the resource with a sleep value: /ords/your-schema/sleep?seconds=30
Expected:
HTTP 555 returned in 10 seconds (value of the jdbc.statementTimeout)
Got:
HTTP 555 returned in 30 seconds (value of the end of statment)
Important note: The behavior is correct if I run ORDS on Windows instead of Linux platform with the same technology stack.
HTTP 555 returned in 10 second with SQL Error Code: ORA-01013: user requested cancel of current operation