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

gbak does not restore SP with plan, when -I switch used [CORE6350] #6591

Closed
firebird-automations opened this issue Jul 2, 2020 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Anatoliy Schegolskiy (atanas)

Duplicates CORE1725

Votes: 1

When gbak -I used in my DB, there is an error appears, see below. And restored database does not contain ANY stored procedures.

Error while parsing procedure HP_INSERT_FIOLIST's BLR.
index FIOLIST_NAME1 cannot be used in the specified plan.
there is no index FIOLIST_NAME3 for table FIOLIST.
unknown ISC error 336330835.

I suppose, the reason of this error is usage of PLAN clause in stored procedure queries. Procedure HP_INSERT_FIOLIST contains code below:

  select fl\_id
  from fiolist
  where fl\_name1 = :txt1 and
        fl\_name2 = :txt2 and
        fl\_name3 = :txt3
  PLAN \(fiolist index \(fiolist\_name1, fiolist\_name2, fiolist\_name3\)\)
  rows 1
  into :fl\_id;

The proposal is to ignore saved query plan in the stored procedure source, when command line switches -I or -M used.
The warning message can be enough.

@firebird-automations
Copy link
Collaborator Author

Modified by: Anatoliy Schegolskiy (atanas)

description: When gbak -I used, there is an error appeared, see below. And then the restore database does not contain any stored procedures.

Error while parsing procedure HP_INSERT_FIOLIST's BLR.
index FIOLIST_NAME1 cannot be used in the specified plan.
there is no index FIOLIST_NAME3 for table FIOLIST.
unknown ISC error 336330835.

I suppose, the reason is usage of plan clause in stored procedure queries.
The proposal is to ignore saved query plan in the stored procedure source, when. -I or -M switches used.

=>

When gbak -I used in my DB, there is an error appears, see below. And restored database does not contain ANY stored procedures.

Error while parsing procedure HP_INSERT_FIOLIST's BLR.
index FIOLIST_NAME1 cannot be used in the specified plan.
there is no index FIOLIST_NAME3 for table FIOLIST.
unknown ISC error 336330835.

I suppose, the reason of this error is usage of PLAN clause in stored procedure queries. Procedure HP_INSERT_FIOLIST contains code below:

  select fl\_id
  from fiolist
  where fl\_name1 = :txt1 and
        fl\_name2 = :txt2 and
        fl\_name3 = :txt3
  PLAN \(fiolist index \(fiolist\_name1, fiolist\_name2, fiolist\_name3\)\)
  rows 1
  into :fl\_id;

The proposal is to ignore saved query plan in the stored procedure source, when command line switches -I or -M used.
The warning message can be enough.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

CORE1725 ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE1725 [ CORE1725 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Anatoliy Schegolskiy (atanas)

Pavel,
Yes, but affected versions (QA, Tests) in CORE1725 need to be updated. FB 3.0.6 is affected in my case.

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