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

Invalid blob id when add a new blob column of type text and update another field [CORE5600] #5866

Closed
firebird-automations opened this issue Aug 29, 2017 · 17 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: bug tracker (ty)

Relate to CORE6089
Relate to CORE6090

Attachments:
screenshot.png
2017_08_30__fbbug_5600__ibprovider_test-all_is_ok.png

I can create the issue using folowing steps:
step 1:
-------
CREATE TABLE Operation ( ID BIGINT NOT NULL,Operation_Type BIGINT NOT NULL,DATE_CREATE BIGINT NOT NULL,DATE_OPERATION BIGINT NOT NULL,Code BIGINT NOT NULL,NAME BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,D_VALUE DOUBLE PRECISION DEFAULT 0 NOT NULL )

step 2:
-------
ALTER TABLE Operation ADD CONSTRAINT operation_pk_1 PRIMARY KEY ( ID )

step 3:
-------
execute block
as
declare i bigint = 1;
begin
while (i <= 20) do
begin
update or insert into Operation(ID, Operation_Type,DATE_CREATE,DATE_OPERATION,Code,NAME,D_VALUE) values (:i,0,132345666243423423,132345666243423423,:i,'test',1);
i = i + 1;
end
end

step 4:
-------
ALTER TABLE Operation ADD surname BLOB SUB_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL

step 5:
-------
update operation set code=2 where id=2

When update is done if I run Select * from Operation, the error 'Invalid Blob Id' appears.

Commits: 2e1f3f8 8f893fd 2769de8

@firebird-automations
Copy link
Collaborator Author

Commented by: @ibprovider

I ran this scenario via IBProvider (fb.direct mode, commit after each step) - the problem not detected. "select * from Operations" returns 20 records.

Another test (commit after second step), also works without problem.

@firebird-automations
Copy link
Collaborator Author

Modified by: bug tracker (ty)

Attachment: screenshot.png [ 13175 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @ibprovider

What tools do you use?

@firebird-automations
Copy link
Collaborator Author

Modified by: @ibprovider

Attachment: 2017_08_30__fbbug_5600__ibprovider_test-all_is_ok.png [ 13176 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: bug tracker (ty)

I use SQL Manager for InterBase and Firebird
My Firebird version is 3.0.2.32703

@firebird-automations
Copy link
Collaborator Author

Commented by: bug tracker (ty)

I still have the problem.
Any help would be appreciated.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Do you have explicit COMMIT after DDL statements ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

I do confirm that error also occured using ISQL:

set echo on;

reCREATE TABLE Operation \( 
    ID BIGINT NOT NULL,
    Operation\_Type BIGINT NOT NULL,
    DATE\_CREATE BIGINT NOT NULL,
    DATE\_OPERATION BIGINT NOT NULL,
    Code BIGINT NOT NULL,
    NAME BLOB SUB\_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL,
    D\_VALUE DOUBLE PRECISION DEFAULT 0 NOT NULL 
\);

ALTER TABLE Operation ADD CONSTRAINT operation\_pk\_1 PRIMARY KEY \( ID \);

set term ^;
execute block as
    declare i bigint = 1;
begin
    while \(i <= 20\) do
    begin
        update or insert into Operation\(ID, Operation\_Type,DATE\_CREATE,DATE\_OPERATION,Code,NAME,D\_VALUE\) 
        values \(:i,0,132345666243423423,132345666243423423,:i,'test',1\);
        i = i \+ 1;
    end
end
^
set term ;^


commit; \-\- We need this, otherwise get lock conflict on no wait transaction / \-unsuccessful metadata update / \-object TABLE "OPERATION" is in use


ALTER TABLE Operation ADD surname BLOB SUB\_TYPE TEXT CHARACTER SET UTF8 DEFAULT '' NOT NULL;

update operation set code=2 where id=2;


\-\-commit; \-\- #&#x2060;#&#x2060;#&#x2060; \[ 1 \] #&#x2060;#&#x2060;#&#x2060; NB: this will suppress 'invalid blob id' error \(temp workaround ?\.\.\)

set blob all;
set list on;
Select \* from Operation where id in \(1,2,3\);
quit;

===

If we uncomment line " --commit; -- #⁠#⁠#⁠ [ 1 ] #⁠#⁠#⁠" than no errors will be.
Checked on: WI-T4.0.0.687; WI-V3.0.3.32798

@firebird-automations
Copy link
Collaborator Author

Commented by: bug tracker (ty)

Yes I do COMMIT.

@firebird-automations
Copy link
Collaborator Author

Commented by: bug tracker (ty)

Is there any way to fix this problem?

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

The issue is that id of temporary blob was stored within the record, i.e. temporary blob was not materialized when created from default value.
It should be fixed now.
Note, it is very bad idea to assign default values to the new blob field, it could make high memory\disk space usage when
table with such is field is read. Better to assign some real values to the new blob field soon after ALTER TABLE.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.3 [ 10810 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@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

Modified by: @dyemanov

Link: This issue relate to CORE6089 [ CORE6089 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE6090 [ CORE6090 ]

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