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

Metadata script extracted using ISQL is invalid/incorrect when table has COMPUTED BY field with COLLATION [CORE6133] #6382

Open
firebird-automations opened this issue Sep 2, 2019 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @luronumen

Attachments:
DDL.SQL
Issue.png

ACTUAL RESULT:

- Metadata script extracted using ISQL is invalid/incorrect when table has COMPUTED BY field with COLLATION

- Unable to recreate the database structure from an extracted database structure file (DDL.SQL) when there is any table with COMPUTED BY field with COLLATION:

Statement failed, SQLSTATE = 22021
unsuccessful metadata update
-ALTER TABLE ACCESSORIES failed
-Dynamic SQL Error
-SQL error code = -204
-COLLATION WIN_PTBR for CHARACTER SET NONE is not defined
After line 7 in file DDL.SQL

- The line is generating the error:
ALTER TABLE ACCESSORIES
ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (('A' || ID) COLLATE WIN_PTBR);

EXPECTED RESULT:

- The user should be able to recreated the database structure using the DDL.SQL file generated by the Firebird 3.0.5.33161 (Snapshot) isql.exe

- The isql.exe should create the DDL.SQL ALTER TABLE like this
ALTER TABLE ACCESSORIES
ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (CAST(('A' || ID) AS VARCHAR(21) CHARACTER SET WIN1252) COLLATE WIN_PTBR);
instead of
ALTER TABLE ACCESSORIES
ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (('A' || ID) COLLATE WIN_PTBR);

STEPS TO REPRODUCE THE ISSUE

1- Install the Firebird 3.0.4 and setup it;

2- Replace the Firebird 3.0.4 isql.exe by the Firebird 3.0.5.33161 (Snapshot) isql.exe version to avoid the issue CORE6040;

3- Create a Firebird 3.0.4 using WIN1252 default character set

4- Run the following commant to recreate the database structure:
"%ProgramFiles%\Firebird\Firebird_3_0\isql.exe" -user SYSDBA -password SYSDBAPWD -quiet -input "DDL.SQL" "LOCALHOST:IRIS_CIN"

==========DDL.SQL==========
SET SQL DIALECT 3;
/* CREATE DATABASE 'IRIS_CIN' PAGE_SIZE 4096 DEFAULT CHARACTER SET WIN1252; */
COMMIT WORK;
/* Table: ACCESSORIES, Owner: SYSDBA */
CREATE TABLE ACCESSORIES (ID BIGINT NOT NULL,
AID VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (CAST(NULL AS VARCHAR(1) CHARACTER SET WIN1252) COLLATE WIN_PTBR),
CONSTRAINT PK_ACCESSORIES_1 PRIMARY KEY (ID));
/* Computed fields */
ALTER TABLE ACCESSORIES
ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (('A' || ID) COLLATE WIN_PTBR);

@firebird-automations
Copy link
Collaborator Author

Modified by: @luronumen

Attachment: DDL.SQL [ 13376 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @luronumen

Attachment: Issue.png [ 13416 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

When you created the database the database character set is not used for 'A'. What was used that made it to work was that you were using WIN1252 as the connection charset.

When you extract script this is a lost information. You must extract and recreate the script using WIN1252 as connection charset.

This is a bad thing but I don't see a way to fix as each metadata command may have been created with a different connection charset.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

priority: Critical [ 2 ] => Minor [ 4 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Thank you very much for your explanations Adriano!
We will try an alternative path when we migrate our databases from FB 2.5 to 3.0.

Could you take a look at this other bug related to metadata script?
CORE6147

This is other issue that are blocking us for migrating to Firebird 3.0.

Thanks in advance,
Luciano

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Hi Adriano,

I just confirmed that this issue is NOT reproducible in Firebird 2.5.9. This means that a regression occurred in Firebird 3.0 and the fix to it may be in the Firebird 2.5.9 branch.

Best Regards,
Luciano

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Workaround to avoid this issue:

Use: ALTER TABLE ACCESSORIES ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (CAST(('A' || ID) AS VARCHAR(21) CHARACTER SET WIN1252) COLLATE WIN_PTBR);

Instead of: ALTER TABLE ACCESSORIES ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (('A' || ID) COLLATE WIN_PTBR);

The most recent firebird 3.0 isql.exe already extract the database metadata using the workaround solution. So, this issue has become minor and no longer blocks our migration to Firebird 3.0.

Now we just have the CORE6147 blocking us for the Firebird 3.0 migration.

Best Regards,
Luciano

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I disagree it's fixed in v3. As I said, it works if the connection charset is also WIN1252.

On v2.5 there is the same problem, but it uses the default character set. It's a problem too because the default character set is also changeable after part's of metadata is created.

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

1 participant