These notes describe the difference between Derby release 10.6.1.0 and the preceding release 10.5.3.0.
Derby is a pure Java relational database engine using standard SQL and JDBC as its APIs.
Derby functionality includes:
- Embedded engine with JDBC drivers
- Network Server
- Network client JDBC drivers
- Command line tools: ij (SQL scripting), dblook (schema dump) and sysinfo (system info)
This is a feature release. The following new features were added:
- Sequence Generators - Named generators for allocating successive, evenly spaced numbers. See feature T176 of the SQL Standard.
- User-defined types - Named types bound to serializable Java objects.
- Restricted table functions - Limits on the columns and rows returned by table functions.
- XPLAIN statistics collection - Query plan statistics stored in tables for later analysis.
- GROUP BY ROLLUP - A subset of the SQL Standard ROLLUP functionality on the GROUP BY clause. See feature T431 of the SQL Standard.
- CROSS JOIN - CROSS JOIN syntax. See feature F401-04 of the SQL Standard.
- Named columns join - USING clauses in joins.
- SHOW FUNCTIONS - IJ command that lists stored functions.
- In-memory back end enhancements - Numerous improvements, including the ability to delete in-memory databases.
- ORDER BY in subqueries - Syntax for explicitly ordering rows returned by subqueries. See features F851, F852, and F855 of the SQL Standard.
- OFFSET, FETCH FIRST/NEXT in subqueries - Generalized syntax for retrieving row subsets. See features F856, F857, F858, F859, F861, F862, F863, and F864 of the SQL Standard.
- NATURAL JOIN - Support for NATURAL JOIN. See feature T431 of the SQL Standard.
- Qualified identifers in ij - Ability to reference cursors and prepared statements in other connections.
- Configurable hash algorithm - Ability to customize the hash algorithm used by BUILTIN authentication.
- Context-sniffing scripts - Ability of shipped scripts to locate Derby jars when DERBY_HOME isn't set.
- Case-insensitive strings - Ability to ignore case in string comparisons and sorts.
The following issues are addressed by Derby release 10.6.1.0. These issues are not addressed in the preceding 10.5.3.0 release.
Issue Id Description DERBY-4630 Documentation: sysinfo example in Tools guide needs to reflect DERBY-4441 DERBY-4621 Invalid conversion from Timestamp to String when calling setTimestamp() with Calendar DERBY-4619 PropertySetter fails due to an empty jre/lib directory DERBY-4618 "Syntax for the derbyrun.jar file" document page modification DERBY-4615 EmbedCallableStatement ignores Calendar in getDate, getTime and getTimestamp DERBY-4608 Unnecessary conversion of binary values to strings in SQLBinary.compare() DERBY-4607 HeapScan test commits wrong connection DERBY-4604 test lang.CollationTest.testSwedishCaseInsensitiveCollation fails with IBM's weme6.2/1.4.2. and Sun's 1.4.2 DERBY-4603 test testBuiltinAuthenticationWithConfigurableHash fails from upgradeTests.Changes10_6 with ibm's j9 DERBY-4602 10 failures and 11 errors with IBM weme6.2/j9/cdc-foundation after revision 922304 for DERBY-4483 DERBY-4600 Use ValueNodeList helper methods in CoalesceFunctionNode DERBY-4594 ArrayIndexOutOfBoundsException thrown in PreparedStatement execution DERBY-4592 Documentation: Update Sun trademarks in manuals DERBY-4591 Documentation needed for global case-insensitive setting (DERBY-1748) DERBY-4590 You can drop a file-system database from a directory named "memory" DERBY-4584 Unable to connect to network server if client thread name has Japanese characters DERBY-4579 Document the configurable hash authentication scheme DERBY-4578 Documentation: Developer's Guide topic on double-booting is mostly obsolete DERBY-4574 derby.jar not a valid R4 OSGi bundle DERBY-4572 Documentation needed for user-defined types DERBY-4571 Memory leak on server when using "SET ROLE" command DERBY-4570 test failure in ij7 with cdc foundation profile (ibm's weme6.2) DERBY-4569 failures in ij2 test with cdc/foundation (ibm's weme 6.2) DERBY-4568 Documentation needed for sequence generators DERBY-4567 Update the Tools documentation to describe the qualified identifiers which Sylvain just added to ij DERBY-4564 Replication tests do not pick up derby.tests.networkServerStartTimeout setting DERBY-4563 Avoid unnecessary use of getStream and getStreamWithDescriptor DERBY-4562 Complation of prepared statement results in Syntax Error DERBY-4554 Remove lang/scrollCursors1.sql DERBY-4553 In ij GETCURRENTROWNUMBER directly writeits result to output DERBY-4549 NPE in JBitSet DERBY-4547 Documentation: update copyright, version to 2010 and 10.6 DERBY-4543 CLOB values incorrectly stored on disk using the old header format DERBY-4542 Avoid materialization where possible when cloning CollatorSQLClob DERBY-4538 If the CREATE TRIGGER does not have the REFERENCING clause, then there is no need to keep before and after values for the triggering table DERBY-4529 BlobMemTest and ClobMemTest don't call super.tearDown() DERBY-4527 create ibm17 class for the old test harness DERBY-4525 Document the in-memory storage back end DERBY-4524 Incorrect DROP FUNCTION logic in AccessTest's tearDown() method DERBY-4523 store.AccessTest fails on phoneME DERBY-4522 Document SELECT statement requirements resulting from fix to DERBY-4191 DERBY-4520 Refactor and extend data type cloning facilities DERBY-4519 Infinite loop in StreamFileContainer.writeColumn DERBY-4518 Document ORDER BY and OFFSET/FETCH in subqueries DERBY-4515 Document and clarify the use of DataValueDescriptor.setValue(InputStream,int) DERBY-4514 j2ME test failures relating to runtimeStatisticsParser DERBY-4512 Avoid unnecessary lookup in transaction table when adding transaction DERBY-4510 Reference manual is unclear on max length for VARCHAR DERBY-4509 Convert autoincrement.sql to JUnit DERBY-4507 Write user documentation for restricted table functions. DERBY-4506 Document NATURAL JOIN in the reference manual DERBY-4505 Document that views, triggers, and constraints run with definer's rights rather than invoker's rights DERBY-4501 CLASSPATH scripts do not work as defined in Getting Started in Derby Guide DERBY-4500 CLASSPATH scripts should not exit on error DERBY-4496 Column list size mismatch with ORDER BY in INSERT statement DERBY-4495 Add NATURAL JOIN syntax DERBY-4494 Admin guide shows invalid shutdown command DERBY-4492 Localized help message from derbyrun.jar has wrong encoding DERBY-4491 The network client changes UDTs into Strings and returns their type as LONGVARBINARY. DERBY-4488 Nullpointer when performing INSERT INTO DERBY-4483 Provide a way to change the hash algorithm used by BUILTIN authentication DERBY-4482 Property to set IJ session locale not documented (assume this is derby.ui.locale) DERBY-4479 after rename table a to b then create table a statement execute cause null point exception DERBY-4477 Selecting / projecting a column whose value is represented by a stream more than once fails DERBY-4476 Use helper methods from IdUtil instead of TupleDescriptor.quoteProtectName() DERBY-4475 [patch] roleName isn't trimmed as expected DERBY-4473 [patch] remove some allocated but unused objects DERBY-4472 [patch] non short circuit op, could cause eof problems DERBY-4465 Typo in error message from SQLChar DERBY-4461 'testD151(...Derby151Test)java.sql.SQLException: Failed to start database' (and more ...) on Suse EE S 10 Linux/IBM 1.6.0, Fedora Core 5/Sun 1.6.0-b105, Win XP/Sun 1.6.0_10 DERBY-4460 test_031_derby_4413 failed in nightly regression run : ComparisonFailure: expected: 1 but was: 2 DERBY-4457 'Column value mismatch' in 'testDistinctInsertWithGeneratedColumn(...lang.DistinctTest)' on Jvm 1.5, 1.4, phoneME. DERBY-4455 Prepared statement failure with CLOB: Stream has already been read and end-of-file reached and cannot be re-used. DERBY-4451 ArrayIndexOutOfBoundsException or ASSERT FAILED when inserting generated columns out of order DERBY-4450 GROUP BY in an IN-subquery inside HAVING clause whose select list is subset of group by columns, gives NPE DERBY-4448 ArrayIndexOutOfBoundsException when trying to override generated column DERBY-4446 Clarify reference manual regarding specifying field length of LONG VARCHAR columns DERBY-4445 Document new ij SHOW FUNCTIONS command DERBY-4442 Evaluation of default value and identity in an INSERT result set evaluated too early. DERBY-4441 Change sysinfo to print out more specific JVM information DERBY-4438 'Table/View 'T1' already exists in Schema 'APP'' in lang.DropTableTest on phoneME/cvm DERBY-4435 Emptying index fails with NPE doing replacement import of empty file DERBY-4432 Memory leak when attempting to boot non-existing database with the in-memory back end DERBY-4430 Make ij's SHOW and DESCRIBE commands more db agnostic DERBY-4429 Let StressMultiTest read Threads and Minutes to run as system property DERBY-4427 TestConfiguration.existingServerSuite(....,portNumber) must use the supplied portNumber DERBY-4425 NullPointerException with INSERT INTO ... from UNION and columns generated from expressions DERBY-4424 Convert outerjoin.sql into JUnit DERBY-4423 Convert dropTable.sql into JUnit DERBY-4421 Allow Visitors to process the nodes bottom-up DERBY-4420 NullPointerException with INSERT INTO ... from EXCEPT/INTERSECT DERBY-4419 NullPointerException with INSERT INTO ... from UNION and identity columns DERBY-4416 Handle comparison of two constants as a boolean constant DERBY-4415 Make it easy to plug custom AST printers into the compiler DERBY-4414 NullPointerException with JOIN...USING and invalid table name in select list DERBY-4413 INSERT from SELECT DISTINCT gives assertFailure (sane), or NPE (insane) in presence of generated columns DERBY-4412 Make getNegation() abstract in BinaryComparisonOperatorNode and UnaryComparisonOperatorNode DERBY-4411 Scalar subquery erroneously rejected for not returning exactly one row DERBY-4410 NullPointerException when USING clause contains all columns in both join tables DERBY-4409 Make some tests run faster by using CleanDatabaseSetup + decorateSQL + rollback idiom DERBY-4407 StringIndexOutOfBoundsException in ij when result has no columns DERBY-4405 Transformation to inner join not performed for certain three-way joins DERBY-4401 Document USING clause in joins DERBY-4399 Syntax description for TableViewOrFunctionExpression lack subquery option DERBY-4398 Allow OFFSET/FETCH in subqueries DERBY-4394 DERBY-3002 DERBY-4393 lang.SequenceTest fails w/ "Sequence 'ALPHA_SEQ' already exists." on phoneME/cvm DERBY-4392 "Table/View / Constraint already exists in Schema 'APP'" in lang.CheckConstraintTest on phoneME/cvm DERBY-4391 NullPointerException when comparing indexed column with result from a set operation DERBY-4388 NullPointerException in RIGHT JOIN with NOT BETWEEN DERBY-4387 Infinite loop in PredicateList.joinClauseTransitiveClosure() DERBY-4386 JavaDoc warnings in several classes in impl/sql/execute/rts DERBY-4384 testDynamicArgsMetaData has been failing since Aug 24th on weme6.2 with NoSuchMethodError DERBY-4382 missing documentation from reference guide for ORDER BY null ordering clause (i.e. NULLS FIRST / NULLS LAST ) DERBY-4380 Subqueries not allowed in ON clause DERBY-4377 Developer's Guide statement on shutting down Derby needs qualification DERBY-4376 Simple select runs forever DERBY-4374 Document the CROSS JOIN operation in the reference manual DERBY-4372 Wrong result for simple join when index is created DERBY-4370 Implement JOIN ... USING syntax DERBY-4367 Replace Utils.min and Utils.max in the client driver with standard methods in java.lang.Math DERBY-4365 NullPointerException when preparing LEFT OUTER JOIN between VALUES statements DERBY-4363 DERBY-3002 DERBY-4361 testDefault fixture in engine.ErrorStreamTest has been failing with junit.framework.AssertionFailedError: File C:\jartest\JarResults.XXdateXX\ibm15_suites.All\system\derby.log could not be deleted DERBY-4359 DERBY-4358 DERBY-4356 testStartStopManagementFromApplication(org.apache.derbyTesting.functionTests.tests.management.ManagementMBeanTest)junit.framework.AssertionFailedError: expected:<2> but was:<%> DERBY-4355 Implement CROSS JOIN DERBY-4349 Documentation build files incorrectly specify missing stylesheet DERBY-4348 Copy table content with "INSERT INTO table SELECT FROM (...)" statement leads to corrupt data DERBY-4347 Provide a property to increase network server start timeout for JUnit tests DERBY-4342 SQLSTATE 38000 (NullPointerException) at inner self join and value(x1, x2...) DERBY-4341 Building with ant all with a different CLASSPATH defined causes the build to fail DERBY-4338 Network client raises error "executeQuery method can not be used for update" when sql is preceded by /* */ comments DERBY-4336 Remove Blob and Clob interface methods from the list of unsupported methods in UnsupportedVetter DERBY-4330 NullPointerException or assert failure when re-executing PreparedStatement after lock timeout DERBY-4326 hang on winvista with IBM 1.6 SR5 in NSSecurityMechanismTest DERBY-4322 intermittent test failure in derbynet/runtimeinfo DERBY-4318 convert inbetween.sql to JUNIT DERBY-4317 convert columnDefaults.sql to JUNIT DERBY-4313 JDBC.dropUsingDMD() may skip dropping objects DERBY-4311 BaseJDBCTestCase.assertErrorCode() never fails DERBY-4307 testRunTests in RuntimeinfoTest fails periodically with Assertion failure checking output DERBY-4304 Network Server shutdown should handle exceptions and finish the server shutdown completely DERBY-4297 'compilation time did not compute (0,0,0,0) expected:<16> but was:<0>' in testGroupBySortProps(....tests.lang.XplainStatisticsTest) DERBY-4293 Mutable public static variables DERBY-4290 suites.All hangs on phoneME/cvm - caused by cvm/phoneME error DERBY-4288 Tools guide lists too many parameters for SYSCS_EXPORT_QUERY* procedures DERBY-4284 All Columns become Nullable when Using left join DERBY-4283 setEmbeddedCP should not exit on error DERBY-4282 strange behavior with the "update ... where current of c1" in the CheckConstraintTest DERBY-4263 PropertySetter isn't able to recognize JDK without version number in path DERBY-4262 test NetScripts fails with IBM's weme6.1 DERBY-4256 allow alter table to increase the maximum size of a blob and a clob. DERBY-4253 documentation should indicate that a database cannot be upgraded if already booted in soft upgrade mode DERBY-4248 convert checkConstraint.sql to JUnit DERBY-4244 ALTER TABLE Sanity ASSERT in add column with autocommit off DERBY-4236 Update the FAQ for Derby 10.5 DERBY-4228 Remove unused pre-i18n constructors in BatchUpdateException DERBY-4223 Provide the ability to use properties with ij.runScript() DERBY-4217 DERBY-4090 DERBY-4213 sttest needs to be adjusted to not run out of disk space DERBY-4208 Parameters ? with OFFSET and/or FETCH DERBY-4199 Write exceptions to file in the fail directory as they occur with JUnit tests DERBY-4192 OFFSET and FETCH FIRST documentation improvement DERBY-4191 Lack of SELECT privilege does not prevent SELECT COUNT(*) DERBY-4190 Incorrect example for SYSCS_DIAG.CONTAINED_ROLES in the reference manual DERBY-4187 Convert altertable.sql to JUnit DERBY-4177 Javadoc for BTreeLockingPolicy should not mention "scan lock" anymore DERBY-4153 Document that starting with 10.5 network server will attempt to create the trace directory if it does not exist DERBY-4124 Broken double-checked-locking in EmbeddedDataSource.findDriver DERBY-4120 derbyclient.jar is not a complete OSGi bundle DERBY-4110 When deleting rows from a table name with its synonym name, Derby throws SQLSTATE 42X04. DERBY-4107 The Reference Guide incorrectly describes the behavior of the DATE function when applied to an integer argument DERBY-4106 The Reference Gulde claims that the INTEGER function can be applied to dates and times DERBY-4102 Assert failure or ClassCastException in EmbedBlob when retrieving BLOB >= 32K DERBY-4092 You should not be able to invoke a table function as a scalar function DERBY-4087 Clean up debug printing of the abstract syntax trees after parsing, binding and optimization DERBY-4085 DERBY-646 DERBY-4081 BTreeController.comparePreviousRecord() may fail to release latch on left-most leaf DERBY-4069 Wrong behavior when ROW_NUMBER is combined with ORDER BY DERBY-4065 DERBY-2487 DERBY-4062 DERBY-2487 DERBY-4051 The javadoc for SpaceTable refers to an alias that doesn't seem to work DERBY-4047 tests updatelocks and updatelocksJDBC30.sql are not run anywhere; should get evaluated DERBY-4044 Use helper methods from IdUtil to quote SQL identifiers in EmbedResultSet DERBY-4038 On Z/OS store/access.sql fails with encryptionAES and encryptionDES DERBY-4000 Reference manual leaves != out of list of boolean operators DERBY-3947 Cannot insert 994 character long string into indexed column DERBY-3941 Unsafe use of DataInput.skipBytes() in StoredPage and StoredFieldHeader DERBY-3882 Expensive cursor name lookup in network server DERBY-3853 Behaviour of setTypeMap() differs between embedded and client DERBY-3852 J2EEDataSourceTest may ignore some failures DERBY-3844 ASSERT failure in BasePage.unlatch() when running LobStreamsTest DERBY-3842 Convert "org.apache.derbyTesting.functionTests.tests.store.holdCursorExternalSortJDBC30.sql" to junit. DERBY-3839 Convert "org.apache.derbyTesting.functionTests.tests.store.holdCursorJDBC30.sql" to junit. DERBY-3834 Convert derbynet/runtimeinfo to JUnit DERBY-3829 Convert derbynet/sysinfo and derbynet/sysinfo_with_properties to JUnit DERBY-3809 Add a SHOW FUNCTIONS command to ij DERBY-3757 'ASSERT FAILED transaction table has null entry when running new StressMultiTest DERBY-3729 Error message is rather unrevealing when creating large databases on FAT32 drives DERBY-3656 ERROR XJ073: The data in this BLOB or CLOB is no longer available. should include the possibility that the lob has been freed DERBY-3650 internal multiple references from different rows to a single BLOB/CLOB stream leads to various errors when second reference used. DERBY-3646 Embedded returns wrong results when selecting a blob column twice and using getBinaryStream() DERBY-3645 Insert into selecting BLOB column twice leads to SQLException: Restore of a serializable or SQLData object of class error selecting from the table DERBY-3635 Cannot build SELECT LIST expressions involving row_number() DERBY-3634 Cannot use row_number() in ORDER BY clause DERBY-3610 Confusing error message when granting execute privilege DERBY-3605 Remove confusing statement "If there is an index defined on the table, the table can be renamed." DERBY-3518 Getting started guide: Incomplete instructions for compiling WwdEmbedded.java DERBY-3478 Simple column names specified as part of "AS" clause in a table expression are ignored if the table expression is a view. DERBY-3473 DERBY-3330 DERBY-3376 Test case in GrantRevokeDDLTest looks to be accidentally commented out... DERBY-3259 Tuning manual needs performance tip on compressing tables DERBY-3225 Some demo programs in -bin distributions are not mentioned in demo/programs/readme.html DERBY-3207 The bin scripts should be able to run when DERBY_HOME is not set DERBY-3092 Use java.util.concurrent in TransactionTable to improve scalability DERBY-3070 Developer's Guide upgrade topic "Soft upgrade limitations" needs updating for 10.3 DERBY-3062 Docn errors in System procedure and Create procedure descriptions DERBY-2992 getBinaryStream returns incorrect result (truncated value) if underlying blob is deleted DERBY-2895 convert lang/declareGlobalTempTableJavaJDBC30.java to JUnit DERBY-2845 Devguide tips on debugging deadlocks should be clearer DERBY-2769 Implement error handling/parameter checking in Clob.setString DERBY-2676 Class name "com.ibm.db2j.aggregates.Aggregator" refered in error message is no longer correct DERBY-2602 TIMESTAMP value is truncated when return to client DERBY-2487 Enhance Derby with EXPLAIN Functionality DERBY-2349 Accessing a BLOB column twice in an INSERT trigger leads to errors in the value on-disk DERBY-2282 Incorrect "transitive closure" logic leads to inconsistent behavior for binary comparison predicates. DERBY-2211 java/testing/README.htm needs to be updated with information on JUnit tests DERBY-2024 DERBY-1982 DERBY-2017 Client driver can insert and commit partial data when a LOB stream throws IOException or does not match the specified length DERBY-1748 Global case insensitive setting DERBY-1553 With the change for DERBY-353 IDENTITY_VAL_LOCAL documentation needs to be changed to reflect the value inserted by the user for GENERATED BY DEFAULT DERBY-1194 Clarify wording for ways to manage the Network Server in the Derby Server and Administration Guide DERBY-1160 Document use of SPACE_TABLE to tell for tables and indexes: a) the number of pages allocated b) the number of empty pages DERBY-887 Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause DERBY-870 Update documentation on setting up LDAP user authentication. DERBY-712 Support for sequences DERBY-711 The documentation should explain that Derby database files are platform-independent DERBY-700 Derby does not prevent dual boot of database from different classloaders on Linux and Mac OS X DERBY-495 COALESCE DERBY-482 GENERATED BY DEFAULT option should be documented in Derby Tools and Utilities guide under "Importing into tables with identity columns" section. DERBY-151 Thread termination -> XSDG after operation is 'complete'
Compared with the previous release (10.5.3.0), Derby release 10.6.1.0 introduces the following new features and incompatibilities. These merit your special attention.
Note for DERBY-4602: Default hash algorithm for BUILTIN authentication changed to SHA-256
Note for DERBY-4483: Strong password substitution cannot be used with new defaults for BUILTIN authentication.
Note for DERBY-4432: The in-memory back end will no longer create a database if the virtual database directory already exists.
Note for DERBY-4380: Changed error code and message when referencing column not in scope in ON clause.
Note for DERBY-4355: CROSS is now a reserved keyword and cannot be used as an unquoted identifier.
Note for DERBY-4191: Some queries require additional SELECT privileges now.
Note for DERBY-3844: Applications may no longer obtain a LOB object, or a Reader or an InputStream, from the same column more than once per row.
Note for DERBY-2769: Comprehensive validity checks for the parameters of Clob.setString have been introduced.
Note for DERBY-4602
Summary of Change
Default hash algorithm for BUILTIN authentication changed to SHA-256
Symptoms Seen by Applications Affected by Change
If a database that uses BUILTIN authentication is opened on a platform that does not support the new default hash algorithm (SHA-256), the following exception may be seen when connecting to the database or when setting the password for a user:
ERROR XBCXW: The message digest algorithm 'SHA-256' is not supported by any of the available cryptography providers. Please install a cryptography provider that supports that algorithm, or specify another algorithm in the derby.authentication.builtin.algorithm property.The default algorithm is initialized to SHA-256 when the database is created. However, if SHA-256 is not available, it is initialized to the old default (SHA-1) instead. The error message above should therefore only be seen if the database was created on a platform that supports SHA-256 and opened on a platform that doesn't support SHA-256.
Incompatibilities with Previous Release
Databases created on a platform with support for the new default algorithm (SHA-256) may now require some changes before they can be used together with BUILTIN authentication on platforms that don't support the new algorithm. In previous releases, differences in the set of supported hash algorithms did not cause a need for changes when moving databases across platforms.
Rationale for Change
The default algorithm in previous releases (SHA-1) is not considered secure enough for most uses by U.S. government agencies. SHA-256 is widely recognized as more secure than SHA-1 and is therefore used as the default if the platform on which the database is created supports the algorithm.
Application Changes Required
If a database cannot be used on a platform because of this issue, one of the following steps must be taken:
Alternative 1
Recreate the database on the platform that doesn't support SHA-256. The new database will use the more widely available SHA-1 algorithm as default.
Alternative 2
Install a Java Cryptography Extension (JCE) Provider that supports the SHA-256 algorithm.
Alternative 3
On the platform on which the database was created, change the default algorithm to SHA-1 (or to some other algorithm known to be supported on the target platform) by executing the following SQL statement:
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.authentication.builtin.algorithm', 'SHA-1')If there are any users defined at the database level before the algorithm is changed to SHA-1, their passwords will have to be set again to ensure that they are rehashed with SHA-1. For example, if there's a user called 'alice' with the password 'secret', the password must be updated with this SQL statement before it can be used on the platform with no support for SHA-256:
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.user.alice, 'secret)
Note for DERBY-4483
Summary of Change
Strong password substitution cannot be used with new defaults for BUILTIN authentication.
Symptoms Seen by Applications Affected by Change
In a database created with the new version of Derby, the BUILTIN authentication provider will by default store passwords in a way that's not compatible with the strong password substitution security mechanism. Applications that attempt to connect to the database using the Derby network client driver with securityMechanism=8 in the connection URL, will therefore fail to connect. The connection attempt will be refused with the following error message:
ERROR 08004: DERBY SQL error: SQLCODE: -1, SQLSTATE: 08004, SQLERRMC: Connection authentication failure occurred. Either the supplied credentials were invalid, or the database uses a password encryption scheme which is not compatible with the strong password substitution security mechanism. If this error started after upgrade, refer to the release note for DERBY-4483 for options.Incompatibilities with Previous Release
Applications that use BUILTIN authentication and the strong password substitution security mechanism will not be able to establish connections to the database if the database uses the new defaults for BUILTIN authentication.
Only databases created with the new version of Derby will automatically use the new defaults. Databases upgraded from previous versions of Derby will continue to use the old defaults, and they will not be affected unless the settings for BUILTIN authentication are changed manually to enable the new behaviour.
Rationale for Change
The default BUILTIN authentication scheme used in previous releases has a weakness that makes it vulnerable to attacks. In the new release, an alternative BUILTIN authentication scheme without this vulnerability has been added. Despite this new scheme's incompatibility with strong password substitution, it was made the default for databases created with the new release of Derby in order to improve out-of-the-box security.
Application Changes Required
Applications that are affected by this incompatibility can be made to work by making one of the following changes:
Alternative 1: Use another security mechanism
You can switch to another security mechanism by changing the value of the securityMechanism connection attribute. Only the strong password substitution security mechanism is incompatible with the new BUILTIN authentication. Note that if you pick one of the security mechanisms that send your credentials unencrypted over the network, you may want to enable network encryption and authentication with SSL/TLS. Details about how to change security mechanisms and how to enable SSL/TLS can be found in the Derby Server and Administration Guide.
Alternative 2: Revert to the old BUILTIN authentication behaviour
It is possible to revert to the old behaviour for BUILTIN authentication, which will make it possible to connect when using the strong password substitution security mechanism. To revert to the old behaviour, set the database property derby.authentication.builtin.algorithm to NULL (or to an empty string) by executing this SQL statement:
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.authentication.builtin.algorithm', NULL)If you have created any users before setting the above property to NULL, you will also need to set the passwords for all those users again to ensure that they are stored using the old format, since setting this property does not change how any existing passwords are stored. Users whose passwords are stored using the old format will be able to connect to the database with strong password substitution.
Note for DERBY-4432
Summary of Change
The in-memory back end will no longer create a database if the virtual database directory already exists.
Symptoms Seen by Applications Affected by Change
If database 'memory:/tmp/a/myDB' already exists, an attempt to create 'memory:/tmp/a' results in:
ERROR XJ041: Failed to create database 'memory:/tmp/a', see the next exception for details. ERROR XBM0J: Directory memory:/tmp/a already exists.Incompatibilities with Previous Release
If two (or more) in-memory databases are created, the application may fail to create the second database if the database paths are overlapping.
Rationale for Change
The fix makes the in-memory and the on-disk back ends consistent on this matter, and the change also fixes a memory leak when trying to boot a large number of non-existing in-memory databases (see DERBY-4432).
Application Changes Required
Adjust the paths of the in-memory databases if required. In some cases it may be sufficient to reorder the database creations, but this is not recommended as it would potentially have severe side-effects with the on-disk back end (a database nested within another database).
There is no way to revert to the old behavior.
Note for DERBY-4380
Summary of Change
Changed error code and message when referencing column not in scope in ON clause.
Symptoms Seen by Applications Affected by Change
In the previous releases, SQL statements that referenced columns that were not in scope in the ON clause of a JOIN, would under certain conditions fail with the following SQLState and message:
ERROR 42972: An ON clause associated with a JOIN operator is not valid.Now, the statements that used to fail with that message will instead fail with the following SQLState and message:
ERROR 42X04: Column 'T1.X' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.X' is not a column in the target table.Rationale for Change
This change was necessary because the code that decided which of the two errors to raise, contained some logic that was not compatible with the introduction of sub-queries in ON clauses. Additionally, the new message makes it easier to see what is the problem with the statement, as it mentions both why the ON clause is invalid and the name of the column that is out of scope.
Application Changes Required
Applications that check for SQLState
42972
when SQLExceptions are raised, should now check for SQLState42X04
instead.
Note for DERBY-4355
Summary of Change
CROSS is now a reserved keyword and cannot be used as an unquoted identifier.
Symptoms Seen by Applications Affected by Change
In previous releases of Derby, CROSS was not a reserved keyword, so applications could use CROSS as an identifier without quoting it. Applications that use CROSS as an identifier (for instance to name tables, views, columns, functions or procedures) and don't quote it, will now see errors similar to this one:
ERROR 42X01: Syntax error: Encountered "cross" at line 1, column 15.Incompatibilities with Previous Release
Applications that use CROSS as an unquoted identifier will experience syntax errors.
Rationale for Change
CROSS was made a reserved keyword in order to support the CROSS JOIN operator. Also, the SQL:2003 standard defines CROSS as a reserved keyword, so applications that use it as an unquoted identifier are not portable.
Application Changes Required
SQL statements where CROSS is used as an unquoted identifier must be rewritten so that CROSS is enclosed in double quotes.
Examples:
The statement
create table cross(x int)
must be rewritten tocreate table "CROSS"(x int)
. Similarly, the statementselect * from cross
will have to be rewritten toselect * from "CROSS"
.
Note for DERBY-4191
Summary of Change
Some queries require additional SELECT privileges now.
Symptoms Seen by Applications Affected by Change
In previous releases, certain queries incorrectly succeeded when the user lacked sufficient SELECT privileges. For instance, the following query succeeded even if the user did not have SELECT privilege on the table:
select 1 from anotheruser.table1And the following query succeeded if the user had UPDATE but not SELECT privilege on the column:
update anotheruser.table1 set a = ( select max(a) + 2 from anotheruser.table1 );Incompatibilities with Previous Release
Now Derby raises a SQLException for those situations. For the first query above, the user must now enjoy SELECT privilege on at least one column in the table. For the second query, the user must now enjoy SELECT as well as UPDATE privilege on the affected column.
Rationale for Change
The old behavior violated the SQL Standard. The new behavior is correct.
Application Changes Required
Database Administrators may need to grant users additional SELECT privileges.
Note for DERBY-3844
Summary of Change
Applications may no longer obtain a LOB object, or a Reader or an InputStream, from the same column more than once per row.
Symptoms Seen by Applications Affected by Change
Derby will throw an exception with SQLState
XCL18 (carrying the message "Stream or LOB value cannot be retrieved more than once") when any of the following result set methods are invoked for the second time on a given column on a row:
- getBlob (new behavior)
- getClob (new behavior)
- getObject (new behavior)
- getAsciiStream (current behavior)
- getCharacterStream (current behavior)
- getBinaryStream (current behavior)
Incompatibilities with Previous Release
Applications which obtain two LOB objects from the same result set column on a row now fail.
Rationale for Change
Obtaining several LOB objects from the same column causes resource management problems and intermittent errors (see DERBY-3844 for a description). The change is in line with the maximum portability statement found in the JavaDoc for java.sql.ResultSet.
Application Changes Required
Users must recode applications which obtain multiple LOB objects (java.sql.Blob or java.sql.Clob) on the same column. Note that for instance getCharacterStream(1) followed by getClob(1) will also raise the exception.
Note for DERBY-2769
Summary of Change
Comprehensive validity checks for the parameters of Clob.setString have been introduced.
Symptoms Seen by Applications Affected by Change
There are three possible symptoms, all observable when invoking Clob.setString:
- An exception may be raised at an earlier point in time than before.
- For failing invocations, the SQL state of the raised exception may differ.
- Invocations of Clob.setString with certain invalid parameters will now fail, whereas they succeeded before.
Incompatibilities with Previous Release
- offset less than zero or more than or equal to the length of str was accepted, but is now rejected.
- str with a value of null was accepted, but is now rejected.
- len less than zero was accepted, but is now rejected.
- offset + len more than the length of str was accepted, but is now rejected.
Rationale for Change
Make the parameter checking comply with the JDBC specification.
Application Changes Required
The following rules must be followed to avoid exceptions being raised when invoking Clob.setString:
- offset must be smaller than the length of str, and cannot be negative.
- str cannot be null.
- len must be zero or greater.
- offset + len must be smaller than the length of str.
Derby release 10.6.1.0 was built using the following environment:
- Branch - Source code came from the 10.6 branch.
- Machine - Mac OS X 10.5.8.
- Ant - Apache Ant version 1.7.1 compiled on June 27 2008.
- JDK 1.4 - Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_22-b02-329).
- Java 6 - Java(TM) SE Runtime Environment (build 1.6.0_17-b04-248-9M3125).
- Compiler - The 1.6.0_17-b04-248-9M3125 javac was used to compile all classes.
- JSR 169 - J2ME support was built using libraries from phoneME Advanced Milestone Release 2.