InterBase 2017 Readme
Go Up to Main Page
This release introduces bug fixes. See Resolved Defects for a complete list of bug fixes.
This release introduces new features see New Features for details.
This Readme covers the following sections:
- System Requirements
- New Features
- Migration Issues for InterBase 2017
- Migration Issues for InterBase XE7
- Migration Issues for XE3
- Migration Issues for InterBase XE and earlier
- Known Issues
- Resolved Defects
- Additional Resources
- Note: Click Previous Releases for all releases prior to InterBase 2017.
Contents
New Features
New ODS version
Databases created with InterBase 2017 use ODS 17.
isql
- New
RECONNECT
command
isql
supports a new command,RECONNECT
. You can useRECONNECT
inisql
and SQL scripts, to reconnect to the latest successfully connected database. See RECONNECT for more information.
- New
-names
Command-line Option
isql
supports a new command line option,-names <character set name>
. You can use this option to specify the character set to use for current database connection. See Command-line Options for more information.
Monitor all online databases
InterBase 2017 provides monitoring of all the online databases in a server with a single connection. Administrators can use the server level statistics to better monitor an instance usage.
Note:This feature is only available with ODS 17 and above.
To use this feature:
- Connect to the admin database on the system
- Run performance monitoring queries on any TMP$ tables as you would in a single database. You will see cumulative data for all databases that are currently online.
Restrictions
- Run UPDATE operations on TMP$DATABASE for SWEEP, FLUSH and RECLAIM for database other than admin
- Run UPDATE operations on TMP$TRANSACTIONS for COMMIT and ROLLBACK for database other than admin
Subscriptions reflect ALTER TABLE changes
Subscriptions now reflect changes when a column is added with ALTER TABLE. For example, using the following code FOO_SUBS shows the newly added column BID.
CREATE TABLE FOO (AID INT);
CREATE SUBSCRIPTION FOO_SUBS ON FOO;
INSERT INTO FOO VALUES (1);
ALTER TABLE FOO ADD BID INT;
UPDATE FOO SET BID=2;
COMMIT;
Note: Databases must be ODS 17.
Exclusive Isolation Level
Exclusive Isolation allows transactions to acquire exclusive lock on a target table, and be the only ones able to execute SELECT
, INSERT
, UPDATE
, and DELETE
on a table. Refer to Exclusive Isolation Level for more information.
SQL Derived Table Support
InterBase now supports derived tables, a highly used SQL development feature. The derived table syntax enhances the abilities of existing applications to use InterBase as a back end RDMBS. For more information refer to derived tables documentation.
Truncate Table
The Truncate Table command allows users and applications to empty the contents of a database table. This feature is useful for tables where rows require frequent deletion. The Truncate Table command performs faster, requires less I/O, and journals and archives much less information than an equivalent DELETE FROM
table command. For more information refer to Truncate Table.
Transaction Wait Time
Specify a period of time transactions wait for acquiring lockable resources. Now is possible to specify a time period that transactions wait for a lockable resource to be acquired. For more information refer to Wait time.
Single Line Comment
You can add a single line comment to a SQL statement using two dashes, for more information and examples refer to Comment for more information.
--This is a comment line.
Migration Issues for InterBase 2017
- InterBase 2017 creates databases with ODS 17 by default, but it can connect to ODS 13 and on databases for transactional workload.
- InterBase 2017 allows backup of ODS 11 and ODS 12 databases using the
gbak
command-line tool only using client/server mode, not service manager mode. You can use this feature when migrating from old InterBase ODS 11 and 12 databases. - New InterBase keywords introduced in this release include:
DEFERRED
andIMMEDIATE
are unreserved Keywords.EXCLUSIVE
,EXCLUSIVITY
,RECURSIVE
, andTRUNCATE
are reserved keywords in InterBase 2017. - ODS 17 enables support for Change Views subscription definitions on the whole table row to survive subsequent
ALTER TABLE
operations. If you'd like to benefit from this, please backup/restore your ODS 16 databases to ODS 17.
Migration Issues for InterBase XE7
- It is important to note that older clients (old IBConsole) will surface exceptions when connecting to newer ODS 16 databases since they will encounter data type not defined as INTEGER. The workaround is to install the newer IBConsole and connect with older and newer InterBase servers. The newer IBConsole is backward compatible for this purpose.
- The InterBase Lock Table size needs to be increased by changing the IBconfig parameter as every entry occupies more room. To increase the InterBase Lock Table size, edit the ibconfig file and change the V4_LOCK_MEM_SIZE entry.
- InterBase XE7 is released with ODS 16. The new engine only supports ODS >= 13. So with this change, all supported platforms will support the same ODS versions (13 to 16).
- Some 64bit UDF libraries built with older Visual Studio versions (2005 and earlier) have been reported to not work with InterBase XE7. If you have any old UDF libraries, please do extra testing of these with the new InterBase version. Or, rebuild your UDF 64-bit libraries with the latest Visual Studio versions.
- The new keyword ROW is "reserved" as a SQL keyword token.
Migration Issues for InterBase XE3
Windows Registry and Program Files Changes
InterBase XE3 introduces changes for better Windows compatibility. InterBase XE3 allows default installation of the product under the "Program Files" location and no longer uses the "Borland" subkey in the Windows Registry hierarchy. It now uses the "Embarcadero" subkey to track instance specific information. We strongly advise and encourage you to migrate your applications to the new environment as soon as possible.
- Program Files Install Location
- InterBase versions since 2009 installed the product in a different location other than the default Windows location of "Program Files". This was because of Windows UAC guidelines that put restrictions around applications without administrative privilege from writing under the "Program Files" file system folder. InterBase XE3, for server-based editions, now conforms to the Windows Application compatibility guidelines. The default install location has changed from C:\Embarcadero\InterBase to C:\Program Files\Embarcadero\InterBase.
- Program Data Location
- InterBase XE3 now delivers the program data files under %ALLUSERSPROFILE%\Embarcadero\InterBase directory. For each individual instance of an install, a folder is created under this and all files requiring write access are delivered here. For example, if you install InterBase XE3 with the defaults and get 'gds_db' instance, your InterBase write location and files include the following:
- Directory: %ALLUSERSPROFILE%\Embarcadero\InterBase\gds_db
- Files:
- admin.ib (this is where you will deliver an older admin.ib file if you are migrating from an older InterBase version)
- license/ (this is where you will deliver InterBase license files)
- Examples subfolder structure with all related files
- OTW SSL related files under "secure" subfolder
- runtime created files such as interbase.log, *.lck, *.env
Migration Issues for InterBase XE and earlier
- Be sure to back-up all databases, including the security database, before uninstalling the previous version.
- We recommend that you upgrade your InterBase clients to 10.0.0.292 or above if you are targeting a SUSE 11 SP1 (or above) server. There are known problems with older clients waiting for events from a SUSE 11 SP1 server.
- Back-up the ibconfig file if it has been customized.
- This version creates new databases with ODS version 15.
- Please only provide 64-bit UDF libraries for a 64-bit edition, if any, of InterBase. Note that the 64-bit server will not be able to load any 32-bit UDF libraries that you may have. The UDF library that is provided (OOTB, ib_udf) is already built for 64-bit and is installed with the product.
- InterBase XE increased the number of records that can be stored in a table. The current count has 32-bit limitations. If you want to get a count of the number of record values beyond 32-bit, you need to take the following steps:
- If a table does have more than 2G rows then the developer must code a CAST(COUNT(*) AS NUMERIC(18,0)) to get a 64-bit count.
- InterBase XE implemented stronger password protection. If you have older InterBase clients (locally or from remote systems) communicating with this installation, please note the following while upgrading your system to InterBase XE3:
- If you have an existing user authentication database (from previous InterBase versions), use that database file (copied to the InterBase XE folder) and authentication for local/remote clients will work correctly.
- If you use the new admin.ib from InterBase XE, note that this database has "SHA-1" strength passwords (by default). If you want to continue using the (weaker) DES password algorithm (previous InterBase releases), use the ALTER DATABASE command referred to in the Stronger Password Protection topic.
- If you want to use "stronger password length" (provided in InterBase XE), you will need to recreate your user accounts AND install the new InterBase clients in remote machines connecting to this InterBase XE server. This is because the "old" InterBase clients are not capable of computing the "SHA-1" passwords and thus pass in the "DES" strength passwords which in turn do not match what the InterBase XE server expects. This gives you the error "Your user name and password are not defined...".
- InterBase XE updated SSL parameter names. The old OTW client properties have been replaced by new OTW properties. Please refer to the Operations Guide"Setting up OTW Encryption" and Table 5.2 located in Chapter 5.
- Restoring database results in the error "unassigned code"
- When restoring a database with InterBase XE, get the error unassigned code. With databases with a long lineage or databases backed up with InterBase 2009 and restored with InterBase XE, each case has different metadata security settings. So when selecting a system table (for example: RDB$RELATIONS) you get the error message: no permission for read/select access to table RDB$RELATIONS by user SYSDBA.
- This error occurs with databases which have these criteria:
- Originally created with a version of InterBase prior to InterBase 6.5.
- Backed up with a version of InterBase prior to InterBase 2009.
- Readmeta.sql has not been previously applied.
- This behavior is exhibited due to stricter enforcement of meta data rights in InterBase XE during the restore of a database.
- Solution
- To resolve this problem, execute readmeta.sql against your database before you back it up. readmeta.sql may be found in \examples\security. You can execute readmeta.sql against your database using isql or IBConsole.
- Explanation:
- The problem occurs with databases that have a long lineage. The two cases are (1) a database has a restore history of IB6->IB7->IB2007->IB2009->IBXE; and (2) a database backed up and restored as IB2009->IBXE. Each case has different metadata security settings. The first instance never had metadata security because it originated from IB6. However with the second instance, it was created (not restored) by IB2009 with a full complement of security privileges for all system tables.
- With the first case, the database was backed up and restored and with each succeeding release, the new release would install privileges for the new system tables in that release (think RDB$USERS, RDB$ENCRYPTIONS, RDB$ROLES, etc.). But it could not alter the original system tables because it had no way of knowing if the database owner had already altered their security privileges. For example, a user might have revoked all privileges to RDB$TRIGGERS and RDB$PROCEDURES to conceal their trigger and stored procedure code.
- Also, in the first case, a SYSDBA may have run readmeta.sql years ago and refined the metadata from that baseline to a custom security profile. InterBase cannot override that customization by automatically resetting it after the XE restore. InterBase XE cannot assume that every database it restores should unconditionally install the default metadata privileges because it does not know the history of individual databases.
- So it is recommended to run readmeta.sql, which sets the default or starting point for configuring it the way you want it. This advice is independent of whether you are migrating to XE.
- Example using isql:
isql "path to database" -user sysdba -password masterkey -i readmeta.sql
- Executing readmeta.sql with IBConsole
- Connect to your database with IBConsole.
- Tools | Interactive SQL ...
- Query | Load Script, select readmeta.sql, click OK.
- Executing readmeta.sql with IBConsole
- Error: No Permission for read/select access to table RDB$XXXX by user SYSDBA
- Databases originally created before InterBase 6.5 may have the following error: No permission for read/select access to table RDB$XXXX by user SYSDBA with InterBase XE.
- Solution:
- InterBase XE enforces tighter meta data security and this error may result from doing meta data operations on databases originally created with versions of InterBase prior to version 6.5. Meta data operations involve requesting information about system objects such as listing system objects or updating them and using the Performance Monitor.
- To resolve this error you need to perform two similar operations. The first operation grants rights to system tables. To do so, execute readmeta.sql from the examples\security folder in your InterBase install directory. You can execute readmeta.sql against your database using isql or IBConsole.
- Example using isql:
isql "path to database" -user sysdba -password masterkey -i readmeta.sql
- Executing readmeta.sql with IBConsole
- Connect to your database with IBConsole.
- Tools | Interactive SQL ...
- Query | Load Script, select readmeta.sql, click OK.
- Executing readmeta.sql with IBConsole
- Second, you need to grant rights to system temporary tables if you are going to do performance monitoring. Due to potential security concerns, most installations will want to grant rights for system temporary tables only to sysdba and the database owner, which is what is presented below. If you wish for all users to be able to view system temporary tables, modify this example to GRANT TO PUBLIC. Some installations will want only specific users to have rights, in which case a more customized script may be needed.
- To grant rights for system temporary tables, save the following as a text file, then execute it the same as readmeta.sql above.
create procedure granttmp as declare variable stmt varchar(1024); declare variable ownername varchar(66); declare variable tablename varchar(66); begin select rdb$owner_name from rdb$relations where rdb$relation_name = 'RDB$RELATIONS' into :ownername; For select rdb$relation_name from rdb$relations where rdb$system_flag>0 and rdb$relation_name starts with 'TMP$' into :tablename do begin stmt = 'grant all on ' || tablename || ' to sysdba'; execute statement stmt; stmt = 'grant all on ' || tablename || ' to ' || ownername; execute statement stmt; end end; execute procedure granttmp; drop procedure granttemp; commit; exit;
Known Issues
- Issue:Server-side Performance Monitoring via admin.ib works using ISQL command-line tool, but throws a "Key violation" exception when used via IBConsole Performance Monitoring UI
Resolved Defects
External Defect # | Description |
---|---|
(QC)SalesForce # | |
Internal Defect # | |
InterBase 2017 Update 4: Hotfix 1 October 2020, bugs fixed in version 2017 Update 4 Hotfix1, build 13.4.0.350. | |
INTB-3889 | Fix isc_info_db_sep_external info item to return status for external, internal, or no SEP |
SF Case 811259
INTB-3887 |
It is not possible to fetch alias information simultaneously from multiple clients |
IBP-142
INTB-3881 |
Multiple service API attachments at the same time crash the server |
INTB-3878 | Simultaneous service api attachments causes a server crash in PWD_REQUEST |
IBP-141
INTB-3857 |
Database restore reports "No current record for fetch operation" |
SF Case 787085
INTB-3782 |
InterBase Server crash when executing many simultaneous database alias service requests |
INTB-3258 | [IBConsole] Admin.ib Performance Monitoring cause key violation |
INTB-2928 | [IBConsole] Error when using Find in the Text Viewer window |
InterBase 2017 Update 4: July 2020, bugs fixed in version 2017 Update 4, build 13.4.0.346. | |
INTB-3839 | gstat crash when mistyped option "-ser" is provided instead of "-user". |
SF Case 00792965
INTB-3807 |
Exception not called from trigger. |
SF Case 00784575
INTB-3775 |
InterBase server crash on attach database. |
INTB-3755 | InterBase hangs when performing an online dump. |
INTB-3682 | Ubuntu: ibmgr could not shutdown ibserver normally. |
INTB-3636 | BLOB performance, temporary memory leak issue. |
SF Case 696794
INTB-3586 |
InterBase crashes when attaching to a database. |
SF Case 00675257
INTB-3512 |
InterBase hangs occasionally. |
IBP-108
SF Case 00673566 INTB-3508 |
Server Crash on usage of Derived Table and Stored Procedure. |
IBP-106
SF Case 00671345 INTB-3505 |
InterBase crashes during short term recovery if GROUP COMMIT is turned on. |
SF Case 00667105
INTB-3502 |
InterBase server crash when ending transactions. |
SF Case 00667114
INTB-3501 |
Continuous return of single row using an index for ORDER BY results in client hang. |
SF Case 00661894
INTB-3493 |
Stored Procedure called in a trigger hangs, when only a single row needs to be returned using an index. |
SF Case 00661330
INTB-3492 |
Change View subscriptions on tables tracking DELETE may result in duplicate/lost rows during GC. |
SF Case 00657237
INTB-3484 |
Erroneous select from stored procedure causes server crash. |
InterBase 2017 Update 3: October 2018, bugs fixed in version 2017 Update 3. | |
INTB-3477 | After a database bugcheck it is not possible to make new connections. |
INTB-3476 | Memory release error during database disconnect blocks other connections |
IBP-94
INTB-3466 |
Connection limit decrease when archive_database and archive_journals are run in parallel. |
IBP-86
SF Case 00411826 INTB-3454 |
InterBase crashes when database is in progress to shutdown |
IBP-84
INTB-3449 |
Installing InterBase ODBC driver from May'2018 release, GDS32.DLL is 13.5.0.344 in c:\windows\syswow64. |
INTB-3446 |
It is not possible to link the iOS InterBase Togo library in RAD Studio when the active SDK is 11.3. |
INTB-3445 |
ib_install.exe (build 13.2.0.322) creates wrong file "C:\Program" when installing VCREDIST 2013. |
IBP-82
INTB-3442 |
IBConsole/server hang with a Group By on a Case expression. |
SF Case 607704
INTB-3410 |
In-memory database cache hit rate drastically goes down post full logical backup. |
IBP-71
INTB-3408 |
InterBase English installer on Windows hangs (or runs for very long time) when running Visual C++ 2013 runtime redistributable install. |
InterBase 2017 Update 2 Hotfix 1: April 2018, bugs fixed in version 2017 Update 2. | |
INTB-3438 | IBExpert parameterized queries fail when using IB 2017 Update 2 client. |
SF Case 00629199
INTB-3437 |
InterBase client using InterBase 2017 Update 2 (13.2.0.318) gds32.dll but built with UniDAC data access components cannot access a stored procedure with no input parameters. |
SF Case 00628627
INTB-3432 |
UDFs truncate binary data. |
INTB-3426 |
isc_dsql_batch_update() raises error when used with batch insert of multiple records. |
InterBase 2017 Update 2: March 2018, bugs fixed in version 2017 Update 2. | |
INTB-3416 |
IBConfig parameter SORTMEM_BUFFER_SIZE with large value can corrupt indexes. |
SF Case 00607704
INTB-3410 |
In-memory database cache hit rate drastically goes down post full logical backup. |
[IBP-72]
INTB-3409 |
Wrong Plan with order by clause. |
SF Case 00613902
INTB-3406 |
Change View leads to crashes and duplicate primary keys. |
[IBP-68]
INTB-3398 |
Parameterized query's parameter position order inconsistent when used in sub-queries. For more information refer to Order of prepared parameters |
SF Case 00597541
INTB-3395 |
InterBase 2017 crashes with sub-query in SELECT column list. |
SF Case 00594472
INTB-3392 |
COMMIT RETAIN does not release a named SAVEPOINT. |
INTB-3392 | InterBase 2017 Update 1 crashes when garbage collecting inactive cloned requests. |
[IBP-66]
INTB-3388 |
COMMIT RETAIN does not release a named SAVEPOINT. |
InterBase 2017 Update 1 Hotfix 1: October 2017, bugs fixed in version 2017 Update 1. | |
INTB-3375 | interbase.log message 'pthread_cond_broadcast failed with errno =22' |
SF Case 00594472
INTB-3392 |
InterBase 2017 Update 1 crashes when garbage collecting inactive cloned requests' |
InterBase 2017 Update 1: September 2017, bugs fixed in version 2017 Update 1. | |
INTB-3382 | Inconsistent query results for table with updated records, and doing a SELECT ... ORDER BY using an index. |
INTB-3336 | No Stored Procedures in Views. |
INTB-3330 | Outer join with table and stored procedure crashes server. |
INTB-3326 | isc_dsql_fetch did not return change records. |
IBP-60
SF Case 573995 INTB-3314 |
InterBase 64bit cannot be installed on Windows 10 64 bit - v.1703, a.k.a. Creators Update |
INTB-3308 | index unexpectedly deleted (260) error when executing a stored procedure using the index. |
INTB-3287 | Transaction Inventory Page cleanup could cause database corruption in extreme case. |
SF Case 561733
INTB-3280 |
InterBase 2017 IBConsole Help | About identifies as XE7. |
SF Case 558139
INTB-3265 |
Change View subscription may slow down backup performance. |
InterBase 2017: Mar 2017, bugs fixed in version 2017. | |
INTB-3262 | InterBase XE7 Linux 64bit installer will add duplicate "LD_LIBRARY_PATH" configuration in /etc/profile |
INTB-3196 | Cannot DROP GLOBAL TEMPORARY TABLE after COMMIT |
SF Case 536921
INTB-3168 |
InterBase backup hangs at a table with a Change View subscription. |
INTB-3155 | Online dump failure: disk writes would not settle down |
IBP-37
INTB-3154 |
SELECT COUNT(*) FROM TMP$STATEMENTS - conversion error |
IBP-36
INTB-3153 |
APPDATA_DIRECTORY IBConfig setting broken by Windows 10 Update |
IBP-32
INTB-3145 |
Shadow feature is blind to activated shadow. |
SF Case 528267
INTB-3144 |
Query significantly slower when ORDER BY is optimized to use index |
QC 138129
INTB-3142 |
Database shutdown causes abnormal termination on a database with Journals and Linger interval set. |
INTB-3104 | "expression evaluation not supported" on CASE statement |
INTB-3058 | [WISE installer] Installer does not respect provided INSTANCE/TCP_PORT values from properties file when /s option is not provided. |
INTB-3044 | IBConsole does not show proper value for "Page Buffers" in Database Properties form. |
IBP-27
INTB-3043 |
IBConsole Trigger Editor returns error when doing CREATE or ALTER TRIGGER. |
IBP-26
Case IBP-26 INTB-3040 |
Change View Subscription causes columns in a group by to be null |
SF Case 501670
INTB-3032 |
Change View Subscription causes columns in a group by to be null. |
IBP-24
SF Case 514550 INTB-3023 |
archive_recover does not work when database has ODS 15. |
INTB-3000 | GROUP BY does not accept column alias names. |
INTB-2971 | Database corruption during fast expansion of single table from multiple clients. |
INTB-2970 | Deadlock at database header page when enabling database shadowing. |
SF Case 454420
INTB-2909 |
Back up fails when database contains a change view subscription. |
INTB-2900 | [Change Views] Alter TABLE add column should reflect subscription which subscribe to altered table |
INTB-2879 | Windows 10 update deletes Files in AppData Directory. |
INTB-2742 | InterBase crashes when running execute statement procedures under stress. |
INTB-2679 | TMP$DATABASE fields TMP$CURRENT_MEMORY and TMP$CACHE_POOL_MEMORY wrapping over signed 32bit integer values |
INTB-2670 | "sort error: not enough memory" reported while restoring a large database |
SF Case 437482
INTB-2647 |
Many concurrent users run faster as sysdba user vs non sysdba user. |
INTB-2464 | IBConsole truncate strings when displaying. |
Click here for list of resolved defects in InterBase XE7
Additional Resources
Embarcadero Technologies Technical Support
- If you have a valid maintenance contract with Embarcadero Technologies, the Embarcadero Technical Support team is available to assist you with any problems you have with our applications. Our maintenance contract also entitles registered users of Embarcadero Technologies products to download free software upgrades during the active contract period. Evaluators receive free technical support for the term of their evaluation. We encourage you to visit the Support section of our Web site.
Embarcadero Technologies on the Web
- Evaluators receive free technical support for the term of their evaluation. To download evaluations of Embarcadero Technologies products or to learn more about our company and our products, visit us at http://www.embarcadero.com/.