Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Aug 6, 2019 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @luronumen

Attachment: ISSUE.PNG [ 13368 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

From ISSUE.png:

- 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;

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE6040 [ CORE6040 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

Fix Version: 3.0.6 [ 10889 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Retest result on Firebird 3.0.5.33220 (Official Firebird 3.0.5): - FAILED (#⁠motorolablocker)

This issue will be tested in Firebird 3.0.6 (Snapshot) soon.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Retest result using the isql.exe from Firebird 3.0.6.33288 (snapshot): PASSED!

Thank you very much for the fix Adriano!

Best Regards,
Luciano

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants