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-issue-importer opened this issue Aug 6, 2019 · 9 comments

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Aug 6, 2019

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 6, 2019

Modified by: @luronumen

Attachment: ISSUE.PNG [ 13368 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 6, 2019

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 24, 2019

Modified by: @dyemanov

Link: This issue is related to CORE6040 [ CORE6040 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 24, 2019

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 20, 2020

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 20, 2020

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 21, 2020

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 21, 2020

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 21, 2020

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants