Known Issues

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

New Known Issues in NuoDB 4.0.1

This section provides a summary of the new issues identified at the release of NuoDB 4.0.1.

Issue description Workaround
When a timezone conversion is applied on the client side, using the JDBC API, to timestamps that have not been rounded to the nearest second, the conversion is ignored. The UTC timestamp is then returned in nanoseconds rather than the timestamp adjusted for the applied timezone. None.

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.
Transaction Engine (TE) crashes if a system lookup is run while data is being loaded into the database. 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

Known Issues in NuoDB 4.0

This section provides a summary of the new issues identified at the release of NuoDB 4.0.

Issue description Workaround
If a user accesses a view but does not have corresponding access to functions, tables, or views referenced in the view, an "access denied" error message displays. (9694) 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.
A database object referenced in a stored procedure, function, or trigger, which is not referenced by its schema name, may reference a database object defined in the same schema where the stored procedure, function, or trigger is being executed rather than the schema in which the function, trigger or procedure was defined. This can result in invalid results or can be used to circumvent security logic built into functions or procedures. (8938, 9687) Always use a fully qualified schema name for database objects used in functions, triggers or stored procedures.
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
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. Do not mix use of DML and DDL against the same table in a stored procedure.
When performing a rolling upgrade from NuoDB 3.2.x to NuoDB 4.0, if the getreleaseversion() function is called before the upgrade database command is executed, an internal error is returned. (9941) Ensure that the upgrade database command is executed prior to executing the getreleaseversion() function.
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.
Upgrades of NuoDB 3.4 to NuoDB 4.0 using Red Hat Packet Manager (RPM) packages will show errors and fail. This is because a directory in the 3.4 package is replaced with a file of the same name in the 4.0 package and RPM does not permit this by default. (9984)

To upgrade from NuoDB 3.4 to NuoDB 4.0 using RPM, two options are available:

(1) Run the upgrade as normal, adding the --replacefiles option to the upgrade command. Related error/warning messages will still display, but the upgrade will be successful.

(2) Back up any modified NuoDB configuration files you wish to retain (for example default.properties), uninstall the NuoDB 3.4, then proceed with a fresh installation of NuoDB 4.0.

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

As NuoDB Admin (nuoadmin) is a preview feature for both bare-metal and Docker use cases, automatic upgrade (of nuoadmin) is not supported when upgrading minor releases of NuoDB (for example NuoDB 3.3) to the next supported version (for example NuoDB 3.4). (9542) To upgrade nuoadmin, when upgrading from a minor release, manually shut down the nuoadmin processes and remove the nuoadmin management file (that is, the raft log file for each nuoadmin process) and upgrade NuoDB. Then restart the admin layer and database making reference to the existing database archive location when starting the databases.
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.
In NuoDB 3.3.1, and higher,NuoDB MigratorA command line Java program that helps database administrators migrate schemas and existing data to a NuoDB database. It interfaces with a source database over a JDBC compliant driver and is designed to support all major RDBMSs. It also interfaces with the NuoDB target database using the NuoDB JDBC driver. NuoDB Migrator can be used as a NuoDB backup/restore utility where both the source and target databases are NuoDB. may generate additional DDL index create statements that result in failure when creating NuoDB schema objects. This issue will be fixed in a future release. Currently, the workaround is to revert to the version of NuoDB Migrator that was released with NuoDB 3.1.1. Click here to download a copy of NuoDB Migrator from our NuoDB github repository and follow the installation instructions provided.
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.
NuoDB converts string literals to UTC timestamps (or DateTime types) and ignores timezone information. (4706) Provide timestamps and date-time values in the absolute (UTC timezone) value.

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

None.

Referencing rows, after they have been deleted within updatable cursors, 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.
When using multiple statements in a single call, the TRUNCATE command can be used only if it is the first statement in the call. None.
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.

If a database process, for example a TE or a SM, does not connect to an agent process, it cannot be stopped or restarted. If this happens, the database process is shown as UNREACHABLE.

Manually stop the database process using {kill -6 <pid>} in the command line, and remove the entry using the NuoDB Manager's domainstate removeprocess id <id> command. Then contact NuoDB Support to provide details of the core file obtained by the KILL command.
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.

None.

Global temporary tables are not supported. (6942)

None.
The NuoDB JDBC driver does not support scrollable cursors. 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 JDBC driver does not support the setNetworkTimeout property. (7430, 7865) None.
The following host properties are not displayed by the NuoDB Manager command:
agentPeerTimeout, binDir, log, regionBalancerLocalFallback, requireConnectKey, and trackprocessOnReconnect.
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.
It is not possible to use the same cursor name twice inside a stored procedure, even after closing that cursor. None.
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
Creating multiple FOREIGN KEY constraints referencing the same table, including the same column, results in an error indicating a duplicate value in an unique index. (9588) None.
The schema generated by NuoDB Migrator generates errors when it is loaded into a NuoDB database where the TRANSACTIONAL_LOCKS and ENFORCE_TABLE_LOCKS_FOR_DDL system properties are enabled (set to true). (9599) To load a generated SQL file into a NuoDB database which has the TRANSACTIONAL_LOCKS and ENFORCE_TABLE_LOCKS_FOR_DDL system properties enabled, the file must be manually modified to include the LOCK TABLE command before each DDL command. Alternatively, disable the ENFORCE_TABLE_LOCKS_FOR_DDL property to load the schema and then re-enable it after the schema has been loaded.
Altering a sequence to change the start value to a value less than, or equal to, a value that has already been used (for example, ALTER SEQUENCEseqSTART WITHvalue) may cause duplicate values to be generated. Upgrade to NuoDB 3.4 where this issue has been resolved.
Enabling statement caching in the NuoDB DataSource can result in statement timeouts. Statement caching is disabled by default, it is recommended that statement caching not be enabled.
NuoDB Hibernate drivers do not support Java 11. None.
Altering an existing column from GENERATED ALWAYS may result in the column having duplicate keys after subsequent inserts into the table. Do not alter an existing column defined with GENERATED ALWAYS.
Adding an NOT NULL column constraint to an existing table with data succeeds even when the column contains NULL values. Before loading data, create the column with the NOT NULL constraint.
In NuoDB 3.2.3, NuoDB MigratorA command line Java program that helps database administrators migrate schemas and existing data to a NuoDB database. It interfaces with a source database over a JDBC compliant driver and is designed to support all major RDBMSs. It also interfaces with the NuoDB target database using the NuoDB JDBC driver. NuoDB Migrator can be used as a NuoDB backup/restore utility where both the source and target databases are NuoDB. may generate additional DDL index create statements that result in failure when creating NuoDB schema objects. This issue will be fixed in a future release. Currently, the workaround is to revert to the version of NuoDB Migrator that was released with NuoDB 3.1.1. Click here to download a copy of NuoDB Migrator from our NuoDB github repository and follow the installation instructions provided.
If the system is under heavy load when a LOCK TABLE command is issued and new TEs are started, it is possible for started TE(s) to process the lock request prior to processing the START TRANSACTION request for the locking transaction. To preserve consistency in this case, the newly started TE is automatically shut down. To avoid this behavior, use NuoDB 3.3.
LOCK was introduced as a new reserved keyword in NuoDB 3.2.2. At this time, the new keyword is not documented which may result in issues when upgrading to NuoDB 3.2.2. Rename objects using the LOCK keyword, or postpone your upgrade until NuoDB 3.2.3, or NuoDB 3.3.1 and higher, where LOCK is no longer a reserved keyword.
Using query timeouts (SET QUERY TIMEOUT) when committing a transaction, or acquiring a table lock, may result in lost connections. Turn off query timeout before committing a transaction or acquiring a table lock.

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.

When working with updatable cursors (cursors created using the FOR UPDATE clause), there is an issue with connection resets.

Do not reference rows after they have been deleted.
Unexpected results occur when working with updatable cursors. Update rows immediately after fetching them.
Custom data types created with CREATE DOMAIN cannot be referenced by stored procedures. None.
The ALTER TABLE statement can create only one unnamed check constraint. Either add the unnamed check constraints when using the CREATE TABLE statement or use named check constraints.

The trailing spaces in string values (stored in a covering index) get trimmed.

None.
It is not possible to change the TABLE GENERATED clause syntax after a table has been created. None.
If an expression calls a user-defined-function, the supplied DEFAULTexpression is not checked to ensure it matches the type of the column it applies to. None.