Known Issues

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.

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.

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.

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 uppercase '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', lowercase 'yyyy' must be used.

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.

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 time zones 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 time zones 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.

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

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

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.

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

None.

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.

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.

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.

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.

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.

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.

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.

NuoDB does not support the NULLS FIRST ordering clause.

None.

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.

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

None.

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

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.

After a TRUNCATE or DROP TABLE statement, a Storage Manager (SM) may not reclaim table catalog disk space due to the presence of atoms in the memory on the Transaction Engine (TE). To check for this condition, run

If the query returns results, then the condition exists. (13084)

To resolve this issue, use one of the following two workarounds:

When DELETE or TRUNCATE statements are used, the SM may not reclaim the disk space. (12251)

Run nuoarchive check --repair during database downtime.