Maximum number of open statements (1000) exceeded

{problem}
When executing a SQL query against a NuoDB database the client receives the following message: "Maximum number of open statements (1000) exceeded".

Diagnosis

By default, the maximum number of open statements and resultsets in NuoDB is 1000 per connection. The current value for these properties can be found in the System.Properties table in the MAX_CONNECTION_OPEN_STATEMENTS and MAX_CONNECTION_OPEN_RESULTSETS fields. Below is an example output of this information from NuoDB 2.6-4:

./nuosql test --user dba --password goalie

SQL> select * from system.properties;
PROPERTY VALUE
------------------------------ ----------------------

MAX_QUERY_COUNT 10
MIN_QUERY_TIME 10.000000
OPT_STATS_INDEX TRUE
MAX_STMT_CACHE_SIZE 500
TABLE_CHG_TOLERANCE 0.100000
MAX_CONNECTION_OPEN_RESULTSETS 1000
MAX_CONNECTION_OPEN_STATEMENTS 1000
INDEX_ENCODING -1
OPT_MAX_SCAN_REOPTIMIZE 20
SKIP_UNCOMMITTED_INSERTS FALSE
UDF_CACHE_SIZE 50
COMPAT_CARDINALITY_CLAMP FALSE
MAX_MATERIALIZED_QUERY_SIZE 67108864
DB_TRACE OFF
DB_TRACE_TABLE TRACESCHEMA.TRACETABLE
DB_TRACE_PATTERN
DB_TRACE_MIN_TIME 0.000000
DB_TRACE_PROCEDURES
MULTILEVEL_INDEX_STRUCTURE FALSE

{problem}

{solution}
To resolve this issue, you will want to examine if the client application is closing open statements after they are done processing. NuoDB will not close these statements automatically so this must be handled on the client side by the application if you are encountering these limits. Please refer to the Documentation page on Client Development for further details on how to close connections with each of the supported drivers.

In the case where individual connections require more than 1000 open statements or resultsets these properties can be modified to non-default values. For example the following code allows up to 10 000 open statements or result sets per connection.

./nuosql test --user dba --password goalie

SQL> set system property MAX_CONNECTION_OPEN_RESULTSETS = 10000;
SQL> set system property MAX_CONNECTION_OPEN_STATEMENTS = 10000; 

{solution}

Have more questions? Submit a request

Comments