SQLFire 1.0.1 Bug Notes
Last Document Update: 24 Jan 2012

Context Navigation

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
#39455 DECIMAL and NUMERIC types can lose precision with the AVG() function There may be a loss of precision when using the AVG() function in queries with DECIMAL or NUMERIC column types. This occurs because SQLFire computes the AVG() in a distributed cluster by splitting it into a SUM and COUNT for each individual node in the cluster, but the precision of the aggregated result is not honored. An application can explicitly change the result of AVG() to the desired precision.
#39605 Atomicity for Bulk Updates SQLFire does not validate all constraints for all affected rows before applying a bulk update (a single DML statement that updates or inserts multiple rows). The design is optimized for applications where such violations are rare. A constraint violation exception that is thrown during a bulk update operation does not indicate which row of the bulk update caused a violation. Applications that receive such an exception cannot determine whether any rows in the bulk operation updated successfully. To address the possibility of constraint violations, an application should apply the bulk update within the scope of a transaction, which ensures that all rows are updated or rolled back as a unit. As an alternative, the application should select rows for updating based on primary keys, and apply updates one at a time.
#40348 Large query result might trigger OOM If a large allocation happens by the time ResourceManager is notified with critical up status or allocation is consuming the entire available memory while its under critical threshold, query processing can result into OutOfMemory. e.g. case 1: With a 4GB -max-heap JVM, assuming data stored is 3GB and critical threshold is configured to be 90%. If old generation objects consumes 3.6GB approx, only then CRITICAL_UP event will be raised by ResourceManager. A 'Select * .. order by ...' with no 'where criteria' executed by multiple clients necessarily need large temporary memory to send and receive the query result and might hit OOM. Incremental query result streaming is planned for future releases of SQLFire. Different situation will warrant various type of workaround. Like for above example, additional where criteria will avoid OOM situation.
#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 data base 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 remote WAN site. Disable BATCH_CONFLATION if you configure an AsyncEventListener or a WAN Gateway Queue.
#41371 Concurrent DriverManager.getConnection(...) calls can cause SQLExceptions with "No suitable driver found" message When multiple threads get connections concurrently using DriverManager.getConnection(...), then some threads may see a SQLException with the message, "No suitable driver found." This is caused by a race condition in Sun's JDK 1.6 DriverManager implementation. Invoke DriverManager.getDrivers() or DriverManager.getConnection(...) in the main thread at least once before calling DriverManager.getConnection(...) in any other threads.
#41385 A range query that involves primary key columns may require an index When you execute a range query that involves primary key columns, SQLFire scans the table, which can potentially cause the query to run slowly. This is because SQLFire internally stores the table in a hashMap (that is not sorted) with the primary key columns as the hash key. This design offers very low latency for primary key lookups. Range query performance can be substantially improved by adding an index on the primary key columns.
#41706 HA feature of DBSynchronizer may cause underlying DB to go out of synch due to re-application of DML operations One ramification of providing HA feature, is that a DML operation may get re-applied to the db in case of fail over of the node hosting primary DBSynchronizer. When aprimary DBSynchronizer node fails while applying the operations of the batch, some of the DMLs of the batch may have already been applied. On fail over, the new primary DBSynchronizer node would apply the failed batch, in the process reapplying some of the DML operations.In such cases, database may or may not get out of synch with SQLFire system depending upon the nature of the DML operation and how it modifies the columns, and the presence or absence of constraints To minimize the occurrence of this issue, it is suggested to have tables with primary key constraint defined.
#41738 41738: WHERE CURRENT OF clause not supported SQLFire does not support the WHERE CURRENT OF clause with an UPDATE or DELETE statement to perform positioned updates with updateable cursors. Workaround: 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.
#42019 SQLFire does not support setting or getting procedure parameters by name to CallableStatement Trying to get or set parameters using parameter name throws unimplemented exception in SQLFire (SQLState 0A000). Get or set parameters by number rather than by parameter name.
#42075 DBSynchronizer and Wan sites may go out of synch due to Foreign Key constraint violation Consider table Parent and Child , with Child having foreign key relationship on Parent. It is possible that in a particular situation, inserts into Parent and Child table happening in-order in ElasticSQL system , may reach the DBSynchronizer or Wan site in reverse order. This will result in Child table record to be inserted before Parent record in the Wan Site or external DB, resulting in Foreign Key constraint violation. This can happen if insertion into Parent and Child table are being done by separate threads. There is a window in which DML operation executed on ElasticSQL system, is remaining to be put into the internal queue of Wan or DBSynchronizer.The record is guaranteed to be put into the internal queue of Wan or DBSynchronizer only when the DML operation gets completed. To avoid this situation, the insertion into the Parent and Child tables should be done by the same thread.
#42307 DDLs don't get relayed to the DBSynchronizer DDLs like truncate table is not relayed to DBSynchronizer. truncate table removes all the rows in the table. While executing truncate table statement, AsyncEventListener callback won't 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 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.
#42459 Large number of threads performing concurrent inserts/updates/deletes on a table having global index or having child tables with foreign key references may hang Global index maintenance and foreign key dependency checks in SQLFire is performed from within the context of an insert/update/delete operation. This can cause the system to deadlock if there are a very large number of threads performing inserts/updates/delete concurrently due to product thread pools getting exhausted. Increase the thread pool size on the data store nodes using the java system properties "DistributionManager.MAX_PR_THREADS" and "DistributionManager.MAX_FE_THREADS" to at least twice the total number of threads that will be performing concurrent inserts/updates/deletes in the distributed system.
#42478 Using the DEFAULT keyword in the insert statement can fail with a ClassCastException Using the DEFAULT keyword in the VALUES expression of an insert statement can fail with a ClassCastException. Don't use explicit DEFAULT keyword rather only insert into other columns using "INSERT INTO <TABLE>(<COL1>, <COL2>, ...) VALUES (...)".
#42543 Unexpected "InternalGemFireError: unexpected mismatch of maxIDs" error when starting large number of servers concurrently When starting up a large number of servers concurrently a rare assertion error "InternalGemFireError: unexpected mismatch of maxIDs" may be seen. This indicates that there was some problem when exchanging the unique IDs assigned to each peer during bootup. Restart the failed peers, or reduce the number of concurrent peer starts.
#42672 Inconsistent behaviour 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 primary key is not the same as partitioning key in the parent table, the product may not throw a conflict exception. An application should not treat conflicts as unexpected during transactions when parent table is being updated while inserts are in progress on the child table having foreign key reference to the parent.
#42771 SYSCS_UTIL.EXPORT_TABLE/IMPORT_TABLE procedures do not work correctly with a non-default delimiter If the file being imported contains a delimiter other than a comma, then SYSCS_UTIL.EXPORT_TABLE/IMPORT_TABLE procedures may fail to work correctly with it even when the delimiter being used is passed as an argument to these procedures. Use the default comma delimiter for import and export procedures.
#42799 RENAME TABLE throws unsupported exception (SQLState 0A000) SQLFire does not support 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 though 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 though 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 SQLException with SQL State of X0Z09 while doing DML operation, may have executed successfully Consider a system with persistent partitioned table. If the system's nodes are being brought down ( or a node fails) and concurrently DML operations are happening, then 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 if the data got persisted successfully or not and accordingly application can decide whether to re-execute the operation or not.
#43169 ij fails to start with a message saying that "?/.ij.history" could not be created This problem has been observed on some 64-bit Linux installations (e.g. RedHat Enterprise Linux 6) when using a 32-bit JVM. The reason is a missing 32-bit nss_ldap library that causes failure in LDAP user lookup and, among other issues, java properties like "user.home" are set to "?" by JVM. For more details see: http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6972329 RedHat Fedora bug: https://bugzilla.redhat.com/show_bug.cgi?id=218679 Install the 32-bit nss_ldap library (e.g. 32-bit nss-pam-ldapd package on RHEL6). Alternatively use a 64-bit JVM.
#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 NoDataStoresAvailableException In rare cases, an operation receives a NoDataStoreAvailableException while recovering a failed member with persistent tables, even though the operation should be able to be satisfied using other running data stores.
#43229 Autogenerated keys with foreign key references limitation when importing data with DdlUtils over thin client The 1.0 release of SQLFire does not provide full DDLUtils support for importing data rows with autogenerated keys. If data for multiple tables is imported, where one table has explicitly set a data value for the autogenerated key column and another table import cites that key value for a foreign key reference, then the import will fail with a foreign key constraint error when using batch insert mode with the JDBC thin client driver. This is due to SQLFire modifying the original content of the autogenerated key value to a system-generated unique value, and also trying to modify any foreign key references to that same value. This process does not work when using the thin client driver with batch insert mode enabled. If you need to use batch insert mode (for example, if the data is large), then use the SQLFire peer driver rather than SQLFire thin client driver. Or, disable batch insert mode to ensure that the import succeeds using either of the two drivers.
#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/false while the client driver shows them as 1/0.
#43516 Clients may ignore constraint violations if the server goes down during DML execution The client retries a DML if the server to which it is connected happens to go down. Any constraint violation thrown during the retry is ignored by the client and in some cases genuine constraint violations may be ignored though data on servers will not violate the constraint.
#43934 Only forward-cursor result sets are supported SQLFire only supports ResultSet objects that use forward cursor movement (ResultSet.TYPE_FORWARD_ONLY).
#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.
#44021 Memory leak on shutdown of embedded database Shutting down an embedded database by passing shutdown=true as a connection property occasionally leaves parts of the closed embedded database alive in memory. This can lead to excessive memory consumption or an OutOfMemoryError of the embedded database is shutdown and restarted repeatedly.
#44098 DBsynchronizer's queue may not drain if DMLs can not be applied to underlying DB DBsynchronizer can encounter issue while applying DMLs to database. The issue can be because table is not created or does not exist. It tries same DML repeatedly till it succeeds. The DBSynchrnozer does not drain the DML statement till it is successfully applied to underlying DB.Currently this is not configurable for users, and users don't have control over this.
#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
#44132 WebLogic server cannot connect to the distributed system using Embedded Driver WebLogic custom data source for SQLFire if uses EmbeddedDriver couldn't connect to the distributed system stating 'cannot create ping socket' exception in the logs.
#44172 Exception while generating concurrent query plans With query plan enabled in multiple connections simultaneously and queries are executed in parallel across such connections might result into exception. Enable query plan capturing one connection at a time to profile queries executed by the application.
#44199 (SECMEC_USRSSBPWD) is not supported with the SQLFire thin client driver. While connecting via thin driver SQLFire currently doesn't work properly with Strong Password Substitute DRDA security mechanism.
#44202 UserName attribute is ignored by SQLFire Connection attribute UserName is silently ignored by SQLFire instead of rejecting application connection. Additional 'User' attribute must be passed by the application for SQLFire to recognize and set that as default schema instead of APP. pass in 'user=' attribute as file/system or connection property.
#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.
#44221 Long query string makes visual statistics display unreadable For a large query string, VSD shows first 254 characters of the query but that too makes statistics viewing and correlation difficult. use query aliasing if DBSynchronizer is not attached. For Updates and Deletes avoid query aliasing if DBSynchronizer is attached otherwise while executing on external database query hints won't be recognized.
#44265 shutting down an VM with critical heap threshold cancelling active queries may hang. Attempting to shutdown a critical heap threshold configured VM might hang if there are DML operations pending results. Ensure there are no outstanding queries have pending results before invoking shutdown.
#44278 GFMon incompatible with current SQLFire 1.0 GA release GFMon is incompatible with SQLFire processes.