The Metadata script extracted using ISQL of a database restored from a Firebird
2.5.9 Backup is invalid/incorrect when table has COMPUTED BY field
--------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-6116
URL: http://tracker.firebirdsql.org/browse/CORE-6116
Project: Firebird Core
Issue Type: Bug
Components: ISQL
Affects Versions: 3.0.5
Environment: Windows 10 x64
Firebird 3.0.5.33161 (x64)
Reporter: Luciano Mendes
Priority: Critical
ACTUAL RESULT
- The Metadata script extracted using ISQL of a database restored from a
Firebird 2.5.9 Backup is invalid/incorrect when table has COMPUTED BY field
- The COMPUTED BY field is generated as VARCHAR(0) instead of the Max size of
the COMPUTED BY field
- A Metadata script with COMPUTED BY with VARCHAR(0) size always generate an
exception when it is tried to recreate a database from it
EXPECTED RESULT
- The Metadata script extracted using ISQL of a database restored from a
Firebird 2.5.9 Backup should be valid/correct when table has COMPUTED BY field
- The COMPUTED BY field should be generated with the size of the collumns
combinations (e.g.: VARCHAR(100)) instead of size 0: VARCHAR(0);
STEPS TO REPRODUCE THE ISSUE
1- Install the Firebird 2.5.9 and setup it
2- Create a database and than running the following DDL:
CREATE TABLE USERS (ID BIGINT NOT NULL,
USER_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
FIRST_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
LAST_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
FULL_NAME COMPUTED BY ((FIRST_NAME || ' ' || LAST_NAME || ' (' ||
USER_NAME || ')') COLLATE WIN_PTBR),
CONSTRAINT PK_USERS_1 PRIMARY KEY (ID),
CONSTRAINT UK_USERS_1 UNIQUE (USER_NAME));
3- Backup the database using the gbak.exe;
4- Uninstall the Firebird 2.5.9, install the Firebird 3.0.4 and setup it;
5- Replace the Firebird 3.0.4 isql.exe by the Firebird 3.0.5.33161 (Snapshot)
isql.exe version to avoid the issue CORE-6040;
6- Restore the Database backup in the step 3 using the the Firebird 3.0.4
gbak.exe;
7- Extract the Database DDL using the Firebird 3.0.4 + Firebird 3.0.5.33161
(Snapshot) isql.exe:
/* Table: USERS, Owner: SYSDBA */
CREATE TABLE USERS (ID BIGINT NOT NULL,
USER_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
FIRST_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
LAST_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
FULL_NAME VARCHAR(0) CHARACTER SET WIN1252 COMPUTED BY (NULL) COLLATE
WIN_PTBR,
CONSTRAINT PK_USERS_1 PRIMARY KEY (ID),
CONSTRAINT UK_USERS_1 UNIQUE (USER_NAME));
/* Computed fields */
ALTER TABLE USERS
ALTER FULL_NAME TYPE VARCHAR(0) CHARACTER SET WIN1252 COMPUTED BY
((FIRST_NAME || ' ' || LAST_NAME || ' (' || USER_NAME || ')') COLLATE WIN_PTBR);
WORKAROUNG FOR FIX THIS ISSUE
- After restore a Firebird 2.5.9 backup into Firebird 3.0.5 (Snapshotp) the
user have to run the following command for fix the Firebird 3.0.5 DDL:
ALTER TABLE USERS ALTER FULL_NAME TYPE VARCHAR(100) CHARACTER SET WIN1252
COMPUTED BY (FIRST_NAME || ' ' || LAST_NAME || ' (' || UID || ')');
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
