Pivotal SQLFire 1.1.2 Release Notes

Pivotal SQLFire 1.1.2 | 12 FEB 2014

Last Document Update: 10 APR 2014

All SQLFire Documentation

What's in the Release Notes

The release notes cover the following topics:

What's New in Pivotal SQLFire 1.1.2

Pivotal SQLFire 1.1.2 is a maintenance release and includes the following updates:

  • New Homebrew and Debian package distributions.
  • Updated copyright, branding and legal text to GoPivotal, Inc.
  • Replaced VMware software licensing with Pivotal software licensing. Work with your Pivotal sales representative to ensure you have sufficient licensing to run SQLFire 1.1.2 in a production environment.
  • A new SYS.DUMP_STACKS() procedure is available to dump information such as thread stacks, locks, transaction states, and so forth. Use SYS.DUMP_STACKS('true') to dump stack information for all SQLFire members or SYS.DUMP_STACKS('false') to dump stack information only for the local member.
  • Connection pooling support for ADO.NET client applications. See About Connection Pooling.
  • Various additional bug fixes. See Resolved Issues.
  • See also What's New in SQLFire 1.1.1.

Installing Pivotal SQLFire 1.1.2

You can download Pivotal SQLFire 1.1.2 from the Pivotal SQLFire product download page.

Pivotal SQLFire 1.1.2 is now available in a variety of distribution methods including:

  • ZIP
  • RPM for RHEL
  • DEB for Ubuntu
  • Homebrew for MacOSX
  • Maven repository

For details on how to install Pivotal SQLFire 1.1.2, see Installing Pivotal SQLFire in the Pivotal SQLFire User's Guide.

Upgrading to Pivotal SQLFire 1.1.2

To upgrade from an earlier version of SQLFire to the current version, see Upgrading Pivotal SQLFire in the Pivotal SQLFire User's Guide.

Basic Features of SQLFire

Pivotal SQLFire is a memory-optimized, distributed database management system designed for applications that have demanding scalability and availability requirements. Applications can manage database tables entirely in memory, using partitioning and synchronous replication. Alternatively, they can persist tables to disk in order to reload data after shutting down or restarting the system. vFabric SQLFire can also overflow table data from memory to disk in various caching configurations.

SQLFire provides the following basic features:

See the SQLFire User's Guide for more information.

Resolved Issues

The following issues from the Pivotal bug tracking system were resolved in Pivotal SQLFire 1.1.2 and no longer require workarounds. This release also includes all bug fixes documented in the vFabric SQLFire 1.1.1 Release Notes.

ID Description
48074Handle CAST like relational operators in the expression resolver, to allow CREATE TABLE with PARTITION BY (CAST ... ).
48279Support using standard permissions via GRANT/REVOKE for SQLFire-specific system procedures in the SYS schema (CREATE_USER, CHANGE_PASSWORD, DROP_USER, START_ASYNC_EVENT_LISTENER, STOP_ASYNC_EVENT_LISTENER, and so forth).
48259Honor user-specified CMSInitiatingOccupancyFraction and other properties that are set by default in SQLFire.
48335Fixed a problem where a member tried to recover a table from disk even after the table was dropped, if the member was not around during the drop.
48212DDL UUID persistence now uses the DataDictionary in SQLFire, which allows the datadictionary to be moved around independently of the default diskstore files.
#44793 A carriage return/line feed character (\n\r) is not recognized in the sqlf command line utility. For example, a sqlfire comment line is terminated by a new line. But a \n won't be recognized in the prompt. SELECT TABLENAME from -- SQLFIRE-PROPERTIES statementAlias=My_QUERY \n SYS.SYSTABLES;
46714Reset an identity column value after DROP TABLE or ALTER TABLE.
44274, 46115, 47651SQLFire now throws errors when starting/stopping non-existent gateways or async queues, or when attaching non-existent gateways or queues to tables. Warning are logged when starting or stopping queues or gateways that are already started or stopped.
48190Fixed a problem where a delete operation failed in SQLF but the event was still placed in an AsyncEventQueue.
48366, 48340, 48338, 48339Separated the statistics of peer- vs client- initiated connections in ConnectionStats. Further split peer statistics into peer, internal, and nested connections. Added client-initiated connection statistics for number of operations such as idle times, DRDA thread count, and so forth to ConnectionStats.
47649Added the underlying reason to authentication exceptions.
47872Corrected retry behavior to fix a problem where batch inserts occasionally disappeared during HA failover.
47062, 45930, 46886A SMALLINT column now returns Integer in all cases as per the JDBC specification.
47078Fix for data inconsistency across WAN configurations with GatewaySenders.
48250 Fixed a problem in the internal LIFO eviction algorithm used for queues that caused eviction in serial queue to fail to keep up with inserts.
48377Corrected a problem where user authentication and the CREATE, CHANGE, and DROP user procedures did not normalize user names, while GRANT and REVOKE did normalize them.
48431 Fixed an issue in which a proper connection clean up did not happen after an error was encountered in a trigger statement. This resulted in an inconsistent data dictionary if a table was subsequently created on the same connection.
48473 SQLFire persists the last modified time for an entry so that it is used after restart, and entries with expiration setting do not get destroyed unexpectedly. SQLFire also sets the last access time to the last modified time on recovery.
48314 Added ALTER table privilege type to GRANT and REVOKE.
48442Fixed an assertion failure when statement stats are enabled.
47492 Corrected a problem where SQLFire was not able to start or join a server JVM due to XBM09
48553Fixed a problem in handling extra columns in a GROUP BY query
48422Corrected a problem in handling hangs in ReferencedKeyReplyProcessor.waitForResult during delete.
47943Fixed FETCH a problem with NEXT XX with OFFSET that might return wrong results.
48653Fixed an NPE in VMIdAdvisor.removeId while processing a departed member.
48592Fixed an expiration destroy propagation problem that was due to serialization error.
48675Corrected a problem where DBSynchronizer "initparams" did not accept user=username.
48532Corrected a problem where an insert operation hung in HA testing when using GENERATED BY DEFAULT columns.
48263Fixed a problem where SQLFier missed and update when batch update was used and the statement was internally re-prepared.
48671 Fixed StatementPlanDUnit failures and non-working "explain " from the command-line.
48761Fixed a SQLFire cluster restart issue.
46883Fixed a deadlock that occurred between expiration and rebalancing.

Known Issues

The following key issues have been registered as bugs in the Pivotal bug tracking system:

ID Bugnote Title Bugnote Description Workaround
#39408 The CREATE TABLE statement does not support null columns in unique or primary key multicolumn constraints. You cannot create a unique or primary key constraint on a column that contains null values. n/a
#40656 SQLFire does not support sharing a single connection with multiple threads. Sharing a connection with multiple threads can lead to different kinds of problems in SQLFire. In particular if an operation is performed by one thread and a commit or rollback operation is performed by another thread, then a "LockNotHeldException" may be thrown during commit/rollback. n/a
#40919 Batch conflation can cause data mismatch when synchronizing to an external database or WAN site. If batch conflation is turned on, operations may be overidden while they are being applied to an external database (with DBSynchronizer) or propagated to a remote WAN site. Disable BATCH_CONFLATION if you configure an AsyncEventListener or a WAN gateway queue.
#41272 ResultSet.get methods using column names can return an incorrect column value if the same column name appears in more that one table in the projection. If a ResultSet.getObject(), or other getter methods such as getInt()/getString() are used to pass the column name instead of index, and the column name appears more than once in the projection for more than one table, then the result set returned for one of the tables may not be correct. If the same column name appears more than once in a projection for different tables, use ResultSet.get() methods and pass the column index in the projection.
#41279 If a DDL is executed from a node and it goes down while sending the DDL commit message to other nodes in the system, the DDL may have been executed on some nodes but not on others. In rare scenarios, if the node firing a DDL fails while sending a DDL commit message to other nodes, the commit may go to some nodes but not to others. DDL may execute successfully on some nodes but not on others. You may need to manually restart the nodes that have inconsistent data. If those nodes cannot be easily determined, one option is to leave one locator running and bring down other nodes, so that on restart all nodes will sync the DDLs against that locator.
#41706 HA feature of DBSynchronizer may cause underlying DB to go out of synch due to re-application of DML operations. A ramification of high availability is that a Data Manipulation Language (DML) operation may get reapplied to the database in case of failover of the node hosting primary DBSynchronizer. When a primary DBSynchronizer node fails while applying the operations of the batch, some DMLs of the batch may have already been applied. On failover, the new primary DBSynchronizer node would apply the failed batch, in the process reapplying some of the DML operations.In such cases, the database may or may not get out of synch with the SQLFire system, depending upon the nature of the DML operation, how it modifies the columns, and the presence or absence of constraints. To minimize the occurrence of this issue, have tables with primary key constraint defined.
#41738 WHERE CURRENT OF clause not supported. vFabric SQLFire does not support the WHERE CURRENT OF clause with an UPDATE or DELETE statement to perform positioned updates with updateable cursors. Construct an explicit UPDATE or DELETE statment using a primary key filter. Or, use SELECT ... FOR UPDATE with the SQLFire peer driver to obtain an updateable result set.
#42013 Parameter inference might interpret a bind value with different precision. Parameter type inference in an expression is based on the net possible highest value that can be returned from that expression instead of the type of target column value. This might result in parameter value truncation.

For example, consider the following query: update trade.portfolio set subTotal = ? * qty.

If the qty column is of an Integer type and subTotal is a decimal(30,20) type, then SQLFire expects the parameter in the expression to be an integer. Performing stmtsetDecimal(1, 10.03) truncates the parameter value to 10.
Use explicit cast operator for the parameter (?) to higher precision. That is,. update trade.portfolio set subTotal = Cast(?) as decimal(30,20) * qty.
#42019 SQLFire does not support setting or getting procedure parameters by name to CallableStatement. Trying to get or set parameters using the parameter name throws an unimplemented exception in SQLFire (SQLState 0A000). Get or set parameters by number rather than by parameter name.
#42035 On a Windows system, if the home directory of a user is on a share, in some cases the sqlf tool may fail to start and cannot create .sqlf.history in user's home. This issue has been observed in Windows environments where the sqlf tool is running inside VMware Fusion and using a share as user home directory. When sqlf tries to create the .sqlf.history file in the user home directory, it fails and the sqlf tool fails to start. Disable the sqlf history by setting ".sqlf.history" Java system property to empty string, or change the home directory to not point to a share.
#42075 DBSynchronizer and WAN sites may go out of synch due to foreign key constraint violation. When a child table has a foreign key relationship with the parent table, it is possible that a sequence of inserts into the parent and child tables in the vFabric SQLFire system may reach the DBSynchronizer or WAN site in reverse order. This results in the child table record being inserted before the parent record in the WAN site or external DB, which causes a foreign key constraint violation.

This situation can occur if insertion into parent and child tables is executed by separate threads. There is a window in which a DML operation executed on the vFabric SQLFire system has not yet been placed into the internal queue of the WAN or DBSynchronizer. The record is guaranteed to be put into the internal queue of the WAN or DBSynchronizer only after the DML operation is complete.
To avoid this situation, the insertion into the parent and child tables should be done by the same thread.
#42307 DDLs do nott get relayed to the DBSynchronizer. DDLs like a truncate table statement are not relayed to DBSynchronizer. Truncate table removes all the rows in the table. When executing a truncate table statement, AsyncEventListener callback will not be invoked.
#42400 No total ordering guarantee for DML in separate threads. SQLFire preserves the order of DML statements applied to the distributed system (and queued to AsyncEventListeners or remote WAN sites) only for a single thread of execution. Updates from multiple threads are preserved in first-in, first-out (FIFO) order. Otherwise, SQLFire provides no "total ordering" guarantees.

This means that if two separate threads concurrently update rows on a parent table and child table, respectively, the order in which SQLFire queues those updates to an AsyncEventListener or WAN gateway may not match the order in which the tables were updated in the main distributed system.

This mismatch can cause a foreign key constraint violation in a backend database (for example, when using DBSynchronizer) or in a remote WAN system that does not occur when the tables are initially updated. These types of "out of order" updates do not occur when multiple threads concurrently update the same key of a partitioned table.
An application should always use a transaction for any operation that updates multiple rows.
#42531 vFabric SQLFire does not prevent a subquent node with a different setting of sqlfire.sql-authorization from joining the cluster. A locator booted with sqlfire.sql-authorization property set to true could allow a new node to join the cluster even though its sqlfire.sql-authorization is set to false. sqlfire.sql-authorization property should be set consistently in the application.
#42538 Presence of triggers that manipulate rows can cause data inconsistency across WAN sites. If triggers are defined on tables configured for WA N, the DML generated as part of trigger action is also propagated to the remote WAN site. Thus the receiving WAN site will receive DML triggers twice. First the trigger is executed by the incoming user-initiated DML operation. The second trigger is the implicit trigger DML ( caused by the trigger onWAN site1) getting executed. An example of a trigger action that can cause a problem is of the form , Col1 = Col1 +1. This will cause the Cl1 value to be incremented twice on the receiving WAN site. Do not create triggers on tables configured for WAN propagation. Avoid trigger action that modifies the column value relative to its existent value.
#42564 If a user's access right to a database object is continuously granted and revoked, the user may not have the last assigned privilege. It is possible in some cases a user may not have the last assigned privilege, if the user's access right to a database object is continuously granted and revoked. Limit the frequency of granting and revoking a user's privilege to a database object.
#42672 Inconsistent behavior of conflict exception (SQLState: X0Z02) thrown by product in transactions for parent and child row updates having foreign key relationship. SQLFire may throw a conflict exception (SQLState: X0Z02) when a parent table entry is being UPDATED in a transaction and a child table is inserting a row having a foreign key reference to that parent row. However, for cases when the primary key is not the same as partitioning key in the parent table, the product might not throw a conflict exception. An application should not treat conflicts as unexpected during transactions when the parent table is being updated while inserts are in progress on a child table that has foreign key reference to the parent.
#42799 RENAME TABLE throws unsupported exception (SQLState 0A000). SQLFire does not support the RENAME TABLE operation currently.
#42803 SQLFire does not support ResultSets with holdability as HOLD_CURSORS_ OVER_COMMIT . Creating a JDBC Statement with ResultSet holdability as ResultSet. HOLD_CURSORS_OVER_COMMIT is not supported in SQLFire although the product does not throw an exception during creation of such a statement.
#42814 SQLFire does not support the ESCAPE keyword in the LIKE clause. The ESCAPE keyword in the LIKE clause is currently unsupported in SQLFire although it does not throw an exception.
#43096 SQLFire primary member failures can lead to data inconsistency issues. During primary to secondary event propagation, the failure of the primary SQLFire member can cause table and global index data inconsistency issues. n/a
#43097 Uncertainty whether SQLException with SQL State of X0Z09 during DML operation allows the operation to execute successfully. In a system with a persistent partitioned table, if the system's nodes are being brought down (or a node fails) and DML operations are occurring concurrently, the DML operation may throw SQLException with Sql state of X0Z09. In such cases there will be uncertainty about the outcome of the operation. The operation may or may not have persisted the data. In the event of graceful shutdown, stop the nodes only when all the DML operations have terminated. In case of SQLException with state X0Z09 with node failures, verify whether the data got persisted successfully, and accordingly the application can re-execute the operation or not.
#43188 Updateable result sets not supported JDBC client driver. You cannot use updateable result sets when you are connected with the SQLFire JDBC client driver. Updateable result sets are supported only when using SELECT ... FOR UPDATE with the SQLFire JDBC peer driver.
#43214 Unexpected NoDataStoresAvailable exception. In rare cases, an operation receives a NoDataStoreAvailableException while recovering a failed member with persistent tables, even though the operation should succeed with other running data stores.
#43232 DDLUtils cannot import tables having circular foreign key dependencies. Circular foreign key dependencies are not yet supported in SQLFire.
#43261 Inconsistency between client driver and peer driver when displaying boolean column values. The peer driver displays boolean column values as true or false while the client driver shows them as 1 or 0.
#43754 A DML operation that gets Offline Exception X0Z09 may not have failed. If all copies of a bucket to host the affected data by a DML operation are gone, the DML operation gets Offline Exception (X0Z09) if the table is persistent. The application should not assume that DML operation is failed, as it is possible the operation is persisted before the exception is thrown. This piece of data is recovered from persistent table once the data node is rebooted. n/a
#43863 Query plan sometimes omits peer client member id. Query plans for statements executed from peer clients can sometimes substitute the statement id for the peer client member id in the portion of the query plan that was executed on the peer client itself. n/a
#43991 CAST may not be honored in aggregates. In queries having aggregates, the CAST operator on the aggregated value may not be honored by SQLFire in some cases. For example:

select cid, cast(max(price) as decimal(30,1)) from trade.txhistory where tid =? GROUP BY cid, type" against table txhistory with datatype of price decimal(30,20);

gives second value as decimal(30,20) instead of decimal (30,1).
#44006 Hang in primary rebalancing. In rare cases, rebalancing primaries may lead to a hang in BucketAdvisor.becomePrimary on one of the members. Stop the hung member and restart it.
#44074 When using DBSynchronizer, if multiple potentially overlapping DMLs are executed in different transactions executing concurrently, then they may result in potentially different rows being affected in SQLFire and backend DB. In a situation where multiple DMLs are executing concurrently that potentially overlap in SQLFire but do not actually conflict, then replay of those DMLs using DBSynchronizer may lead to different rows being affected in the backend DB as compared to SQLFire. As an example consider a bulk delete that uses some criteria on column and an update that changes the same column: update foo set col1=val1 where col1=val0; delete from foo where col1=val1; n/a
#44110 DBSynchronizer and automatic generation of identity columns in third party database If cached tables require automatic generation of identity column values, then you should use SQLFire to automatically generate the key values but disable generated keys in the backend database. If the backend database also generates key values, then inserts can fail if SQLFire has already generated the key. Disable auto generated keys in the backend database.
#44199 SECMEC_USRSSBPWD is not supported with the SQLFire thin client driver. While connecting via a thin driver, SQLFire currently does nott work properly with Strong Password Substitute DRDA security mechanism.
#44207 Non-prepared stored procedures perform slower than prepared statements. Only insert, update, delete, and select statements with constants are considered for pattern matching, and avoid complete compilation. Stored procedures with constant parameters (without a prepare call) may be slow due to complete compilation (parsing and optimization). Configure the stored procedure with dynamic parameter and bind constant values across multiple executions.
#44261 If a DML operation is cancelled due to low memory with SQLState XCL54, the DML operation may have been successful before the low memory condition is hit. In some cases it is possible that DML operations fail with low memory condition (SQLState: XCL54), but the operation may still have completed when the exception is thrown. Users should not assume that a low memory exception always implies that the operation was unsuccessful. Check for the changes explicitly, if required. Use transactions when it is expected that DMLs may take substantial memory and can get cancelled due to low memory conditions.
#44534 Index Creation with Concurrent DML on WAN Causes Incorrect Result Sets In a WAN configuration, if you attempt to create an index on a table while performing concurrent DML statements, queries against the table return incorrect result sets. n/a
#44657 Some MBeans not available in JMX Agent. Certain SQLFire MBeans are not served from the SQLFire JMX Agent in this release. These MBeans include:

- JDBCMBean for montoring the JDBC driver version information.

- ManagementMBean for monitoring the management status.

- NetworkServerMBean for monitoring statistics associated with a SQLFire member's Network Server (client) connections.

VersionMBean for monitoring the SQLFire member version and product information.
To access any of the listed SQLFire MBeans, you must connect directly to the local SQLFire process rather than to the SQLFire Agent. For example, with jconsole you would need to connect directly to the local process id of the SQLFire member (such as com.vmware.sqlfire.tools.internal .SqlfServerLauncher) rather than connecting to the Agent as a remote process.
#44804 TotalExecutionTime statistics might show incorrect values. TotalExecutionTime statistics might show a large value sometimes. n/a
#44814 java.lang.ClassCast Exception: FormatableBitSet cannot be cast to com.vmware.sqlfire. internal.catalog.types. ReferencedColumns DescriptorImpl A very rare ClassCastException saying FormatableBitSet or GenericPreparedStatement cannot be cast to com.vmware.sqlfire.internal.catalog.types. ReferencedColumnsDescriptorImpl is seen when preparing a query on remote node. Users will need to re-execute the statement when this exception occurs. n/a
#44872 No query plans for bulk insert/update/delete with or without subqueries affecting multiple rows. Query plan may not be returned for a bulk insert/update/delete operation that gets distributed to one or more members. n/a
#44878 A system user's credentials may fail to start a new sqlfire node. When the first locator is booted, a list of username/password credentials could be set. These credentials could be used to boot new sqlfire nodes to join in the cluster. When booting new nodes using these credentials, sometimes they may be rejected by sqlfire. Application could set only one username/password pair to start all locators and nodes in the cluster.
#44878 Foreign Key limitation with ALTER TABLE. If a table has already contained data, you cannot add a Foreign Key constraint with ALTER TABLE if the reference key contains a unique column and is a superset of the partitioning column. Add Foreign Key constraints to tables before adding data to the table.