Known Issues

Unless otherwise stated, known SQL issues are likely to affect both the Scalar SQL Engine and the Vectorized Execution Engine (VEE).

Where known issues have been reported, the associated Zendesk reference number is displayed. For example, (10318).

Known Issues in NuoDB 4.2

Issue description Workaround

During normal processing a CREATE INDEX statement will be terminated on the builder Storage Manager (SM) and restarted by a TE if another SM enters SYNCING mode. Under very rare circumstances it is possible the CREATE INDEX statement may continue running on the builder SM and never complete the index build successfully.

Manually stop the CREATE INDEX statement, drop the incomplete index, and restart the CREATE INDEX statement.

Index builds on tables with hundreds of millions of rows or more is slower on NuoDB 4.2 than NuoDB 4.1 due to the engagement of write throttling to avoid overloading the system.

None.

The 4.1.2-4 C++ driver does not free statements until the connection is closed even if the statement is closed. This can cause the client to grow in size. When it is closed, it can take significant time and cause CPU load. (11824)

Revert to using the 4.0.8-2 C++ driver. Driver and NuoDB versions do not need to match.

A Storage Manager (SM) which is building an Index on a multi-million row table can induce significant load on other SMs as they process the Index build operation. This can cause such SMs to exit due to memory exhaustion if they are CPU or disk I/O constrained. This issue has existed since NuoDB 4.0.0. A warning message is now logged if the SocketBuffer grows very large, as an indicator of the issue.

Ensure no SM is CPU or disk I/O constrained.

Archive size does not automatically reduce immediately when an Index is dropped.

Issue a SELECT on the table which has the index. The archive size will immediately start to reduce on one SM and once that SM completes, the archive size will start reducing on other SMs.

Index creation with ONLINE option during rolling upgrade procedure may fail with: ASSERT in TableIndexBuildManager.cpp:1044: FAILED: isFinished() || (!isBuilder() && isStarted()) || isNotPrepopulatedIn40(true) || (isBuilder() && !isInError() && !isFailed()).

The issue is fixed in NuoDB 4.0.5-2 and later.
If upgrading from versions below NuoDB 4.0.5-2, ensure that ONLINE option is not used if creating an index during rolling upgrade.

The maximum year date value that can be passed into the C and C++ client driver api methods is the year 2261. (11700)

A workaround to retain the year value of 2262 or greater is, for example, in C++:

NuoDB::SqlDate date(milliSeconds);
this->m_preparedStatement->setDate(colPos, &date);

The time component will be truncated, but the date value will be correct.

It is not possible to upgrade Kubernetes NuoDB Helm Charts deployments using NuoDB Helm Charts that do not define admin.bootstrapServers to deployments that do. This issue applies when all the following conditions are true:
1. Upgrading NuoDB Helm Chart deployments to 2.4.0 or greater
2. When Helm Chart deployments upgrade to NuoDB database version 4.0.7 or greater
3. The domain was created with either a NuoDB version less than 4.0.7 or a Helm charts version less than 2.4.0.
See details here: NuoDB Helm Chart Github repo issue #143.

To resolve this issue, since admin.bootstrapServers=1 is the default setting starting with release 2.4.0, admin.bootstrapServers=0 has to be explicitly set when upgrading from deployments that do not define the property. This parameter is located in the admin chart values file, values.yaml, and this file is located in the NuoDB helm chart stable/admin sub-directory.

Nuoloader Issue: If the CSV file imported includes a column value that has a double quote character that is not the first character of the column value, this will result in the CSV file being imported incorrectly. For example, if the CSV file contains values that look like: ONE,T"WO,THREE,…​

All double quotes that are not the first character of the column value must be escaped with quotes. For example, the following format will work: ONE,"T"WO",THREE,…​

When an UPDATE…​WHERE CURRENT OF <cursor> is executed on a partitioned table and the update changes the value of the partition key such that the record is moved to a partition that has not yet been scanned as part of the cursor, the update operation will be executed more than once on that record.

During UPDATE…​WHERE CURRENT OF <cursor> operations on partitioned tables, avoid updates to the partitioning key value that causes the record to fall into a different and higher partition.

When using multipart indexes the upper bound for the index is calculated incorrectly with the result that if a BETWEEN or AND has two subqueries as arguments and the second subquery returns no rows, the entire condition is evaluated to TRUE rather than FALSE.

Instructing the index to be skipped in the query will avoid the issue but lose the performance benefit of the index. Alternatively ensure that each subquery returns at least one row.

Using upper case 'YYYY' to specify year in combination with 'MM' and 'dd' leads to unexpected results.

'YYYY' should only ever be used with 'ww' (week of year). For date formats involving 'MM' and 'dd', lower case 'yyyy' must be used.

When using rownum()` with a NOT BETWEEN predicate the results after the upper boundary value are not returned.

There is no workaround

If a query contains an AND within a WHERE clause and both sides of the AND are subquery predicates on the same column, the WHERE clause will match rows that should not have been matched. The first condition in the WHERE clause is ignored entirely.

There is no workaround.

Scalar SQL Engine: A value exceeding the type precision can be assigned to a var without error and is rounded to match the precision of the type.

Scalar SQL Engine: None.

Vectorized Execution Engine (VEE): This issue does not affect VEE.

When running a union query NuoDB, if the user specified DISTINCT in the first branch, DISTINCT is applied to the result of the entire union, rather than just in the branch where it was specified.

If the intention is not to apply DISTINCT to the entire UNION query, ensure that the branch to which DISTINCT is to be applied is not the first branch in the UNION query.

Setting a user role to 'inactive' takes effect only if executed by a user to which the role has been assigned. The same user can set the user role to active with the result that the command is ineffective.

The role to which the user has been assigned can be revoked.

Instead of returning an error, the DROP SCHEMA CASCADE command will return success even if the schema contains objects that belong to another user which are not dropped as part of the command. Those objects that could not be dropped will continue to exist.

There is no workaround.

OpenJDK 8 update 8u232-b09-0ubuntu1~18.04.1 breaks Timestamp values for timezones that change DST observance. The following issue is likely to be encountered in any NuoDB installation, using OpenJDK 8. When an application uses timestamp values for timezones that change DST periods over time, the application is likely to encounter time calculations that are incorrect by one hour if they occur in the period between the min and max change dates. It appears that openJDK 8 has no record of past DST and all past DST changes are calculated using the current DST settings. See https://bugs.launchpad.net/ubuntu/+source/openjdk-8/+bug/1859217.

There is no workaround.

Removal of a storage group, before the adding of the storage group is complete, can cause an SM to exit.

Confirm that the storage group exists (and is in a RUNNING state) before trying to remove the storage group.

If an attempt is made to delete an archive that is being used by a running process, the "Unable to delete archive: Cannot remove archiveId=ArchiveId [<ID>] with running SM startId=<ID>" error message is displayed. If you then proceed with deleting the database with running db processes, no additional error message is returned. Once the database is deleted, the archive can be deleted as well without an error, yet the process is left running.

Follow the recommended order of events to shut down and delete objects. If planning to delete a database archive with the intent to either shut down its SM or delete the database, first shut down the SM and then delete the archive.

Performing a hot copy operation may result in the following message:

At line 1: hotcopy: Request failed: mgmt request timeout (3s)

This message is ambiguous and may lead customers to believe that the hot copy command failed. In reality, message is a warning message indicating that the SM process is unable to process the hot copy operation within 3 seconds.

None. Despite the message, the hot copy operation may actually be started.

Running a stored procedure that performs multiple DELETE FROM …​ WHERE CURRENT OF operations, without advancing the cursor forward, causes the following:

  • The stored procedure eventually stops processing, and hangs in the same DELETE statement, with both the TE and the SM reporting 100% CPU usage.

  • Memory usage grows with no indication as to what is consuming the memory.

  • Killing the statement triggers ROLLBACK, which takes a considerable amount of time to complete.

Ensure the cursor is advanced between DELETE FROM …​ WHERE CURRENT OF operations.

If multiple cursors perform operations on the same row, and an UPDATE operation on the row follows a DELETE operation, the UPDATE does not return an error. Instead, the DELETE succeeds and then the UPDATE succeeds as if the DELETE never occurred.

None.

An error generated by the create unique index command is potentially misleading.
If a user attempts to create an index (I1) that fails for some reason, a subsequent attempt to create the index generates a message similar to that seen below:

SQL> create table t1 (f1 integer);
SQL> insert into t1 values (1),(1);
SQL> create unique index i1 on t1(f1) online;
Error 42000: Uniqueness violation encountered while populating index I1, key = '1'
SQL> create unique index i1 on t1(f1) online;
Error 42000: an index named I1 already exists in schema USER, on table T1

The index does exist but is incomplete. The user needs to drop the index before attempting to recreate it.

None

Comparisons for < (less than) or ⇐ (less than or equal to) operations on indexed string values, where the comparison string contains trailing spaces (for example "A "), does not return strings where the string contains trailing binary values 0x01 to 0x20 in place of the trailing space (for example "A0x01").

None.

The python driver does not support dates prior to 1900-01-01. If the python driver is used to process dates prior to 1900-01-01, it will result in incorrect dates. (9575)

If the database does not have dates prior to 1900-01-01, the python driver can be used. If the database can contain dates prior to 1900-01-01, then python should not be used and another tool that uses a different driver (for example JDBC) must be used.

Creating multiple FOREIGN KEY constraints referencing the same table, including the same column, results in an error indicating a duplicate value in a unique index. (9588)

None.

NuoDB SQL expects a single argument; the database name. If multiple arguments are entered, the last value is interpreted as the database name.

None.

When inserting values into an automatically generated field with small precision, an "Invalid table generator defined. Column type must be redefined" error is returned. (9737)

None

Scalar SQL Engine: Use of DML and DDL on the same table within a stored procedure may return unexpected results. DML statements are executed against the version of the table that existed at the time the stored procedure is called and do not contain the changes from the DDL statements.

Scalar SQL Engine: Do not mix use of DML and DDL against the same table in a stored procedure.

Vectorized Execution Engine (VEE): This issue does not affect VEE.

Cursors cannot be used in a stored procedure when it is declared against a (temporary or permanent) table which is also created in the stored procedure. This results in a "cursor not declared" error. (9908)

Create the table outside of the stored procedure where the cursor is declared.

If during a NuoDB Manager (nuodbmgr) session, either the domain or database process state changes, the show domain command may not reflect the current state of the domain, especially if a partition has occurred. (9486)

Restart the nuodbmgr session to resolve this issue.

NuoDB does not support joins against another join. For example:

SELECT * FROM table1 INNER JOIN (table2 inner join table3 ON table2.col1 = table3.id) ON table1.col2 = table2.col2 T

This may impact Hibernate-based applications where this type of syntax is automatically generated. (9514)

The first join needs to be converted to a full SELECT command. For example:

SELECT * FROM table1 INNER JOIN (SELECT * FROM table2 INNER JOIN table3 ON table2.col1 = table3.id) join1 ON table1.col2 = join1.col2

If using multiple statements in a single client connection, query results may contain changes from both statements. For example a SELECT clause in one statement may return modifications made by another active statement.

Avoid use of multiple statements that read and modify the same table in the same connection.

The NuoDB Datasource does not age out connections on borrow or return. This means that in order for the maxAge Datasource property to have an effect, timeBetweenEvictionRunsMillis must be set. Even if timeBetweenEvictionRunsMillis has been set, connections can live longer than the value set for maxAge because they will not be aged out by the eviction thread if they are on loan to the application when the eviction job runs.

None.

The XA DataSource does not support connection caching like the regular DataSource.

None.

When deploying NuoDB in a docker-based environment, and NuoDB Storage Manager and or Transaction Engine containers are created using docker run --hostname, it is possible (when selecting the hostname column from the SYSTEM.NODES table) that the value displayed may be the container’s IP address instead of its hostname value.

None.

Temporary table index statistics are not automatically updated in the way that regular table index statistics.

To manually update temporary table statistics, use the ANALYZE command.

Concurrent overlapping DELETE statements with a LIMIT clause may return less rows than specified by the limit. This is because NuoDB acquires locks for the delete after the limit is applied. Some of the rows to be deleted may have been already deleted by a different DELETE statement.

None.

If Transaction Engines (TEs) are started on systems with different locale settings, the TE may exit with an error message similar to:

Node startup failed: unable to convert string "10,00000" into type "double"' or the dual table may be empty.

Ensure that all systems used to run TEs for the same database have the same locale settings.

The NuoDB Query Optimizer does not prune partitions for queries using the LEFT clause with parameters. For example, the following query will scan partitions of a table named t1:

SELECT * FROM t1 WHERE LEFT(f1,2) = ?;

None.

A SELECT statement, which fetches a BLOB column type, returns only the decimal value for each character in the BLOB.

None.

Referencing rows, after they have been deleted within updatable cursors (that is, cursors created using the FOR UPDATE clause), will lead to a connection reset.

Do not reference rows after they have been deleted.

In an updatable cursor, if a row is modified between the cursor fetch and the UPDATE…​ WHERE CURRENT OF operations, unexpected results may occur.

Update rows immediately after fetching them.

Columns named HELP may pick up leading spaces from the query used to create them.

Use single quotes around the name HELP, or use an alternative name for the column.

An error is not returned when attempting to alter a sequence which sets the start value to less than that of the ceiling.

None. (While no error is returned, the operation does not change the start value.)

Using the nuosql command line, you cannot create tables with different cases, for example MyTable and mytable.

Use another client to create tables with an identical name but using a different case. Alternatively, create tables with different names.

A Can’t follow the specified Use_Index hint(s) error is returned when quoted table or index names in index hints are not defined completely in upper case.

None.

When a schema that does not exist is dropped, no error message is returned.

None.

Temporary tables are incorrectly listed in the SYSTEM schema but cannot be accessed using the SYSTEM schema.

Temporary tables must be accessed using the USER schema.

Scalar SQL Engine: When using multiple statements in a single call, the TRUNCATE command can be used only if it is the first statement in the call.

Scalar SQL Engine: None.

Vectorized Execution Engine (VEE): This issue does not affect VEE.

The ALTER TABLE command is not supported when working with temporary tables. (6363)

Create a permanent table for any table that may require altering.

When dropping a domain, the same case (as was used to create the domain) for the domain name must be used. Otherwise, the domain is left in the DOMAINS system table.

Recreate the domain and drop the domain using the same case that was used to create it.

DDL statements are not supported in stored procedures, except when using dynamic SQL. (6313)

DDL statements can be accessed in separate stored procedures which are executed after DDL stored procedures have been executed.

SELECT statements, with extra parentheses around a subquery, return an error.

Remove the extra set of parentheses.

When a storage group is removed from a Storage Manager (SM), the on-disk files for the data removed are not automatically deleted.

Use NuoDB Check to remove on-disk files.

NuoDB does not support the NO PAD collation option. As a result, unexpected results occur when comparing two strings where one is a substring of the other. For example:
"abc" = "abc"
"abc" > "abcX", where X is a non-printing character with value less than ASCII 32 (SPACE) (6854)

None.

The following system tables are case sensitive: DEPENDENCIES, PRIVILEGES, PROCEDURES, PARTITIONEDTABLES, PARTITIONIDS, PARTITIONCRITERIA, and LOCALATOMSEQUENCES.

None.

NuoDB Loader does not support the loading of binary values cast into binary columns. (6866)

None.

Global temporary tables are not supported. (6942)

None.

The NuoDB JDBC driver does not support scrollable cursors. (7266, 9110, 9239)

None.

NuoDB Manager’s capture command does not store altAddress values if advertiseAlt is set to true.

The local DNS IP address is used instead.

NuoDB does not support recursive DDL triggers.

None.

The return type of DATE_ADD and DATE_SUB functions with STRING arguments is dependent on the format of the string.

To avoid inconsistent results, do not use the DATE_ADD and DATE_SUB functions with STRING data type columns

NuoDB does not support the NULLS FIRST ordering clause.

None.

The incorrect Failed to apply license: Error applying license: failed to set configuration: default.license error is returned when there is no raft leader among the brokers. (8234)

Ensure that all brokers in the raft membership are started and that there are no connectivity issues.

TEs use the system locale settings to define the character encoding used by the TE. If the system locale is different across the database nodes, this can result in unexpected behavior. For example, a TE may report data conversion errors on start-up (bootstrap) if it is using a locale that is different from the existing TEs in the database.

Ensure that all systems to be use for the database have the same locale setting.

Scalar SQL Engine: It is not possible to use the same cursor name twice inside a stored procedure, even after closing that cursor.

Scalar SQL Engine: None.

Vectorized Execution Engine (VEE): This issue does not affect VEE.

The use of BINARY literal values as partitioning keys for STRING type columns, in CREATE TABLE statements, is not supported.

None.

If two or more SMs enter a SYNCING state concurrently, on occasion, they may not transition out of that SYNCING state (as indicated by NuoDB Manager’s show domain summary command). The log messages of a SM stuck in the SYNCING state will include a SYNC: <number>: STARTED message but not have a corresponding SYNC: <number>: COMPLETED message. (8679)

Kill one of these SMs to allow the other one to transition out the SYNCING state. Generally, try not to start two or more SMs concurrently. Instead, start SMs one at time after they have been synchronized.

Table partitioning does not support partitions based on sub-second precision.

Do not create table partitions with sub-second partitions.

The information provided with the –help or -h options in NuoDB SQL has not been updated.

Refer to support documentation in the Using NuoDB SQL Command Line section of NuoDB’s online documentation

NuoDB Hibernate drivers do not support Java 11.

None.

The type declarations TIME (precision) and TIMESTAMP (precision) ignore the specified value for precision. Values with this type use the precision with which they were created (default is 6). (8523)

None.

Custom data types created with CREATE DOMAIN cannot be referenced by stored procedures.

None.