You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 [CORE6116]
#6365
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 CORE6040;
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 || ')');
- Red Line: Metadata script extracted from a database generated on Firebird 3.0;
- Green Line: Metadata script extracted from a Firebird 2.5.9 database restored on Firebird 3.0;
Submitted by: @luronumen
Is related to CORE6040
Attachments:
ISSUE.PNG
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 CORE6040;
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 || ')');
Commits: 5f1ea28 73287e3
The text was updated successfully, but these errors were encountered: