vFabric SQLFire 1.1.1 Release Notes
vFabric SQLFire 1.1.1 | 22 AUG 2013|
Last Document Update: 22 AUG 2013
What's in the Release Notes
Note: To upgrade from an earlier version of vFabric SQLFire, see Upgrading vFabric SQLFire.
The release notes cover the following topics:
See also the Release Notes for the previous version of vFabric SQLFire.
What's New in vFabric SQLFire 1.1.1
VMware® vFabric™ SQLFire 1.1.1 includes the following changes and enhancements. This release also includes the features documented in the vfabric SQLFire 1.1.0 Release Notes.
- SQLFire now supports pre-allocating space for disk store (.crf, .drf, and .if) files using operating system-native calls. Pre-allocating disk space helps to prevent future failures or hangs caused by disk full conditions. Enable pre-allocation for disk store files (.crf and .drf extensions) using the gemfire.preAllocateDisk system property. Enable pre-allocation for disk store metadata files (.if extension) using the gemfire.preAllocateIF property.
- An active disk space monitor was added. The monitor logs a warning
level message (every 10 seconds by default) if the available usable
space on a target disk is low. You can configure the log message
frequency using the gemfire.DISKSPACE_WARNING_INTERVAL system property.
SQLFire logs a low disk space warning in the following situations:
- For a log file directory it logs a warning if the available space is less than 100 MB.
- For a disk store directory it logs a warning if the usable space
is less than 1.15 times the space required to create a new oplog
- For the data dictionary directory it logs a warning if the remaining
space is less than 50 MB.
- A new connection
was added to provide one or more alternate locators to which a
client can connect if the primary locator is not available. This
property can be used in JDBC and ADO.NET connection URLs.
- SQLFire now supports client-side, compact thread tracing with
timings. You can configure this tracing using the following system properties:
Combine multiple debug properties using commas. For example:
sqlfire.debug.true=TraceClientStatement,TraceClientConn. See Using Trace Flags for Advanced Debugging.
- sqlfire.client.log-file specifies the name and location of the client log file.
- sqlfire.debug.true=TraceClientStatement enables basic timing logs.
- sqlfire.debug.true=TraceClientStatementMillis add wall clock timing.
- sqlfire.debug.true=TraceClientConn prints connection open/close
- A new, optimized implementation for gateway sender and
AsyncEventListener queues supports concurrent puts into the queue
without synchronizing on the tail of the queue. This can improve a queue's
performance by 5 to 10 times when using multiple (> 10) threads.
- A new, optimized implementation of GENERATED BY DEFAULT IDENTITY
columns supports concurrent access. (Previously, all access was
single-threaded). The implementation avoids any repetition of
generated values for a table, and can transparently restart from previously generated value if a primary
generation node fails.
- User names are now always stored normalized (in upper-case) in the database for both authentication and authorization. The normalized user names are also used for password encryption. This means that user names "User1" and "user1" refer to the same user. In addition, any hashed passwords that were generated using the sqlf encrypt-password command for system users (defined in sqlfire.properties or otherwise) that are not in all upper-case characters must be regenerated. Similarly, all users that were created using the SYS.CREATE_USER procedure must regenerate passwords using SYS.CHANGE_PASSWORD, if the user name was previously not in all upper-case characters.
- The SYS.SHOW_USERS system procedure was added to list all configured SQLFire users.
- The sqlfire.sql-authorization property is now enabled by default if auth-provider is configured.
- The SYS.CHANGE_PASSWORD procedure was added to support changing user passwords when using BUILTIN authentication. A user can change their own password by providing the correct old password. Admin users can change any password by providing null or an empty string for the old password. However,if a non-empty value is provided then it must match the old password for the specified user. The ability to change other users' passwords is enabled for the database owner by default. Other users can be explicitly GRANTed EXECUTE permission on the procedure. This works only when "sqlfire.sql-authorization" is true. Otherwise, any user can change another user's password by providing the correct old password of that user. When sqlfire.sql-authorization is false, then even the database owner has to provide the correct, old password.
- SQLFire system procedures in the SYS schema now use standard GRANT EXECUTE and REVOKE authorization mechanisms to allow execution for other users, when "sqlfire.sql-authorization" is set to true. (In previous releases, the procedures were either allowed for all users, or allowed for only for the database owner user.) The procedures that are now restricted to to the database owner user by default are: CREATE_USER, CHANGE_PASSWORD, DROP_USER, START_ASYNC_EVENT_LISTENER, STOP_ASYNC_EVENT_LISTENER, START_GATEWAYSENDER, STOP_GATEWAYSENDER, ADD_LISTENER, REMOVE_LISTENER, ATTACH_WRITER, REMOVE_WRITER, ATTACH_LOADER, REMOVE_LOADER, SET_QUERYSTATS, SET_CRITICAL_HEAP_PERCENTAGE, SET_CRITICAL_HEAP_PERCENTAGE_SG, SET_EVICTION_HEAP_PERCENTAGE, SET_EVICTION_HEAP_PERCENTAGE_SG, EXPORT_DDLS, SET_TRACE_FLAG, INCREMENT_TABLE_VERSION, REBALANCE_ALL_BUCKETS, and CREATE_ALL_BUCKETS.
- The sqlf.history system property was added to control the location of the file that stores a list of the commands that were executed during an interactive sqlf session. See sqlf Interactive Commands.
- The include-admins options was removed from the sqlf
shut-down-all command to optimize the shutdown and startup process
for SQLFire members. Use sqlf shut-down-all to stop all data store
members in the distributed system, leaving locators running. Then
stop individual locators using sqlf locator stop.
- The sqlf commands that initiate a connection to a distributed
system now accept the -auth-provider option to specify the
authentication mechanism to use for the connection: BUILTIN or LDAP.
Also, if you start a SQLFire member with sqlf and specify an
authentication mechanism with
-auth-provider, then SQL authorization is also enabled by default. See
Authentication and Authorization.
- The sqlf interactive command, SHOW IMPORTEDKEYS, was added to
display information about the foreign keys in a specified table, or in
all of the tables in a schema.
- The sqlf
run command was added, to enable you to execute SQL command
scripts using a single terminal command.
Troubleshooting and Diagnostics
- A new system property,
sqlfire.datadictionary.allow-startup-errors, was added to allow a
SQLFire member to start up even if encounters an error in recovering
the data dictionary. Use this option only if you cannot resolve data
dictionary recovery issues using the instructions in Troubleshooting
Member Startup Problems.
The SYS.DISKSTORE_FSYNC system procedure was added, to enable you to
manually flush and fsync all data to configured disk stores, including
data in asynchronous persistence queues.
- A new system
was added to enable or disable trace flags on all members of a
running SQLFire distributed system.
- The SYS.SET_GLOBAL_STATEMENT_STATISTICS system procedure was added to enable or disable statement and timing statistics for an entire SQLFire distributed system.
- SQLFire server and locator shutdown behavior has changed so that stopping a server or locator halts the member, even if it is in a WAITING state or is waiting for disk synchronization.
- SQLFire now logs a severe message if a gateway sender cannot connect to a remote SQLFire distributed system.
- The SYS.JARS table was added to show information about installed
JAR files. SQLFire also performs dependency checking when you
install or replace JAR files. Dependent objects, such as installed
listener implementations, are automatically recompiled when you
replace a JAR file that the listener uses.
and Loading JAR Files in SQLFire.
- The SERVER_GROUP column was renamed to SERVER_GROUPS in
for consistency with other tables. Update any queries or procedures
that access those columns to use the correct column name.
- Connection statistics are now separated by peer- and
client-initiated connections. The statistics are further separated into peer, internal, and
nested connections. Also, client-initiated connection statistics
were added for the
number of operations, idle times, DRDA thread count, and so forth.
- A new function, SYS.GET_TABLE_VERSION, and system procedure, SYS.INCREMENT_TABLE_VERSION, were added to help diagnose and resolve WAN synchronization problems caused by tables having mismatched schemas. See also WAN Replication Problems.
- A new system
enables you to gracefully shut down a gateway sender or
AsyncEventListener implementation. The procedure blocks for a
specified time, or indefinitely until the queue of a specified
gateway sender or asynceventlistener is emptied of events.
Upgrading to vFabric SQLFire 1.1.1
To upgrade from an earlier version of SQLFire to the current version, see Upgrading vFabric SQLFire in the vFabric SQLFire 1.1.1 documentation. Also review product changes documented in What's New in vFabric SQLFire 1.1.1.
- If you are upgrading from vFabric SQLFire 1.0.x, you need to run the sqlf upgrade-disk-store command to upgrade your disk stores to the 1.1.1 format.
- If you are upgrading from version 1.0.x or 1.1, you must regenerate encrypted passwords for any users that were configured using mixed-case characters. In version 1.1.1, SQLFire stores normalized (all-uppercase) user names, and the all-uppercase values are used for encryption.
Note: SQLFire 1.1.1 includes internal changes to support rolling upgrades in future releases of the product. However, because SQLFire 1.1.1 introduces normalizes database user names (requiring re-encryption for system users), you cannot perform a rolling upgrade from SQLFire 1.1 to version 1.1.1.
Basic Features of vFabric SQLFire
VMware® vFabric™ 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.
The following issues from the VMware bug tracking system were resolved in vFabric SQLFire 1.1.1 and no longer require workarounds. This release also includes all bug fixes documented in the vFabric SQLFire 1.1.0 Release Notes.
||Workaround for Previous Version
|42752, 47574, 47586||SQLFire throws errors instead of retrying operations in
HA scenarios||SQLFire sometimes would throw a
SQLNonTransientConnection Exception with "No
current connection" error, or other errors, when a member failed during
operations. The code was modified to retry the operations
in various scenarios that involve node failures.||n/a|
||Queries may occasionally lose connection when node goes down
while iterating query results.
||When a node goes down during query results iteration, SQLFire throws back a node failure exception (SQLState: X0Z01) to
the user indicating that the results may not be complete and user
may need to re-execute the query. When using the thin client driver,
it may occasionally end up losing the Connection object itself, and
so user may need to recreate the connection.
||When handling a node failure exception (SQLStatE: X0Z01) also
check if connection has gotten closed using Connection.isClosed()
and recreate the connection if that be the case.
|45609, 47824||Hang in LOB queries||Queries that
involved LOBs could hang after a call to INSTALL_JAR was executed.||n/a|
|45889||Batch DML retry failures during
HA||Occasionally a client would not failover to
another, available server.||n/a|
|46595||BindException while connecting to
ServerSocket||The code was modified to correct a
BindException caused by the distributed system repeatedly
connecting to a ServerSocket.||n/a|
|46855, 47440, 47545||Inaccurate results in left
outer join queries, GROUP BY queries||SQLFire now checks for
colocated buckets to initialize for the member
selected by a driver table.||n/a|
|46886||Inconsistent object type for smallint||The
code was modified to return the Integer type for a smallint value,
per the JDBC specification.||n/a|
|46933||Select on char() column returns
trimmed values after the column is
updated||The SQLFire code was modified to correct
the padding for CHAR columns.||n/a|
|47407||Inserts could become
lost in a WAN configuration||Retries of batch
inserts from a client were failing without
reporting an error. SQLFire now retries batch inserts,
and can failover to another available
Failure||Using ALTER TABLE to add unique key as
a foreign key constraint
could sometimes fail with SQLException(X0Y45).||n/a|
|47444||Failures in local/global
indexes||If the partitioning key in a partitioned table
is different than the primary key, batch update retries could throw an
EntryExistsException in node failure cases.||n/a|
|47462||WAN update failed to execute on
remote site||SQLFire was updates to improve WAN
replication behavior when a member that holds the primary
buckets for a partitioned table fails after an
|47465||Count with GROUP BY returned
incorrect result||SQLFire code was modified to
provide correct results in certain HA
|47476||shut-down-all issue after upgrade
from 1.0.99 to 1.1||The code was modified to
correctly send views from older server versions during an upgrade.||n/a|
|47486||Allow for specifying multiple
locators/servers in a SQLFire client JDBC URL||The
secondary-locators property was added to allow for
specifying multiple locators in a JDBC URL.||n/a|
|47497||EOFException "stream cannot be re-used"||Bulk prepared DMLs on BLOB/CLOB
columns that used stream/blob and affected multiple rows
caused EOFException "stream cannot be
|47498||Batch updates set last value in
all rows||Batch update statements that execute locally
set the same, final value in all rows.||n/a|
|47500||DdlUtils import using
write-data-to-db does not honour
ensure-fk-order||The write-data-too-db syntax was corrected
in order to provide a boolean value to ensure-fk-order.||n/a|
|47504||Incorrect STATUS field in
FabricService and SYS.MEMBERS||SQLFire was modified
so that the STATUS field is now propertly updated after an
initial status of WAITING.||n/a|
|47512||Procedure not found message
displayed for incorrect syntax||If you miss one
or more parameters when calling a procedure, SQLFire now
searches for the closest match for a procedure call, and
displays the match in the error message.||n/a|
|47514||Warnings for schema regions with
code was modified to not display unnecessary warning
messages when enable-network-partition-detection is used.||n/a|
|47515||Missing execution times from
EXPLAIN statement||In the output of
'EXPLAIN ', the execution time was not
displayed for some execution plan codes, such as QUERY-RECEIVE and
|47524||Update applied twice to a remote site
when there is no HA||A race condition could cause an event to applied
twice from WAN queue.||n/a|
|47562||Starting servers hang after
restarting locators||When locators for a
distributed system went offline and were then
restarted, adding new servers would cause the server processes to hang.||n/a|
credentials unused||The SYSCS_UTIL.IMPORT_ TABLE_EX
procedure did not use boot credentials
when it spawned new connections for parallel, multi-threaded import operations.||n/a|
|47585||Duplicate key failures during WAN
insert||Inserts could sometimes fail in WAN
deployments due to SQLFire incorrectly detecting a
possible duplicate key.||n/a|
querying SYS.MEMORYANALYTICS||Basic SELECT
statements against the SYS.MEMORYANALYTICS table could
cause an StackOverflowException.||n/a|
|47595||Infinite loop in
GemFireDistributedResultSet||An infinite loop
could occur when executing queries such as "select
count(1) from Tab having count(1) > 0".||n/a|
|47599||sqlf shut-down-all command
hangs||The sqlf shut-down-all command cound hang
if a gateway sender had not been stopped.||n/a|
|47611||ArrayIndexOutOfBounds Exception in
ArrayIndexOutOfBounds Exception could sometimes occur
ALTER TABLE statements when adding foreign key
constraints to tables that already had
|47633||Numerous DRDAConnThreads appear in
stack trace||The code was modified to clean up unused DRDAConnThreads.||n/a|
|47634, 37635||Continuous log messages for
connection, gateway sender failures||The code was
modified to reduce logging for a remote locator
connection failure when the remote site is down, and
to reduce logging when remote receiver is
|47739||SELECT does not return qualified
result set||SELECT statements could return
incorrect results in certain scenarios that involved node
|47743||Daemon processes not fully
implemented||SQLFire and GemFire background
processes for servers and locators did not fully
operate as daemons on UNIX platforms.||n/a|
|47783||NoClassDefFoundError with installed
JARs||When multiple jars were installed, then existing, loaded
classes could fail with a "Stale class loader" error.||n/a|
|47854||Enable commits to wait for 2nd
phase to complete||A "sync-commits" connection
property was added to wait for second phase transaction commits to finish completely in the
distributed system before the commit returns.||n/a|
|47862||Allow encrypted passwords in
create-user||The code was modified to allow
CREATE_USER to accept a password in encrypted form. A
CHANGE_PASSWORD system procedure was added to enable
users to change their own password.||n/a|
|47863||SCHEMA issues in DDL
replay||Problems occurred in DDL replay during
server startup with implicit user schemas INSTALL_JAR and
|47866||DDL ordering for
write-schema-to-sql||The sqlf write-schema-to-sql
command was modified to order DDLs in exactly the same
manner as SQLFire uses in server startup.||n/a|
|47873||Startup fails with
LockTimeoutException||The code was modified to
skip table initialization until DDL replay
|47945||SELECT query resultset has
duplicate rows||An index on a persistent table could become
inconsistent on a node that went down. This could occur when an update
was executed on the table while node was down or while the node was
|47976||NPE in server restart with
triggers||The code was modified to correct a
NullPointerException that occurred in server restart with
|47992||Triggers executed during disk
recovery||SQLFire code was modified to ignore
trigger execution during disk recovery.||n/a|
|48125||Provide system procedures to fix
schema versions across WAN sites||Added a new SQL system
function, SYS.GET_TABLE_VERSION, to get a table's current schema
version. Also added a new SQL system procedure,
SYS.INCREMENT_TABLE_VERSION, to increment a table's current schema
|48245||Batch update on same key in the
same batch does not guarantee the order||Fixed an issue where,
if there are multiple primary key-based updates on the same row
in a batch update, then only the first update was applied.||n/a|
The following issues affect vFabric SQLFire 1.1.
vFabric License Server Limitation
If you are installing vFabric SQLFire in a vSphere virtual machine as part of vFabric Suite, you can use the vFabric License Server to install your SQLFire licenses, or you can install the licenses locally within the SQLFire installation. However, you cannot use the vFabric License Server to manage licenses on physical machines, or with standalone SQLFire licenses such as vFabric SQLFire Enterprise.
See vFabric SQLFire Licensing.
vFabric SQLFire Issues
The following key issues have been registered as bugs in the VMware bug tracking system:
||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.
||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.
||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.
||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.
||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.
||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.
||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.
||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.
update trade.portfolio set subTotal = Cast(?) as decimal(30,20) * qty.
||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.
||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
||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
||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.
||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.
||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.
||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.
||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.
||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.
||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.
||RENAME TABLE throws unsupported exception (SQLState 0A000).
||SQLFire does not support the RENAME TABLE operation currently.
||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.
||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.
||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.
||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.
||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.
||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.
||DDLUtils cannot import tables having circular foreign key dependencies.
||Circular foreign key dependencies are not yet supported in SQLFire.
||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.
||A DML operation
that gets Offline Exception X0Z09 may not
||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.
||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.
||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).
||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.
||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;
||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.
||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.
||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.
||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.
||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.
||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.
||Carriage return (\n) character is not recognized in the sqlf command line.
||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;
||Insert an explicit CR where needed,
sqlf> SELECT TABLENAME from -- SQLFIRE-PROPERTIES<press Enter> statementAlias=My_QUERY
||TotalExecutionTime statistics might show incorrect values.
||TotalExecutionTime statistics might show a large value
||java.lang.ClassCast Exception: FormatableBitSet cannot be cast
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.
||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.
||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.
||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.|