Issue Details (XML | Word | Printable)

Key: CORE-5600
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Blocker Blocker
Assignee: Vlad Khorsun
Reporter: bug tracker
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Invalid blob id when add a new blob column of type text and update another field

Created: 29/Aug/17 12:46 PM   Updated: 29/Jun/19 09:14 AM
Component/s: None
Affects Version/s: 3.0.2
Fix Version/s: 3.0.3, 4.0 Beta 1

File Attachments: None
Image Attachments:

1. 2017_08_30__fbbug_5600__ibprovider_test-all_is_ok.png
(39 kB)

2. screenshot.png
(25 kB)
Environment: Windows 10 Enterprise
Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Kovalenko Dmitry added a comment - 29/Aug/17 02:58 PM
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.

Kovalenko Dmitry added a comment - 30/Aug/17 04:31 AM - edited
What tools do you use?

bug tracker added a comment - 30/Aug/17 05:42 AM
I use SQL Manager for InterBase and Firebird
My Firebird version is 3.0.2.32703

bug tracker added a comment - 02/Sep/17 10:30 AM - edited
I still have the problem.
Any help would be appreciated.

Vlad Khorsun added a comment - 02/Sep/17 11:33 AM
Do you have explicit COMMIT after DDL statements ?

Pavel Zotov added a comment - 02/Sep/17 11:41 AM
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; -- ### [ 1 ] ### 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

bug tracker added a comment - 02/Sep/17 12:24 PM - edited
Yes I do COMMIT.

bug tracker added a comment - 29/Oct/17 11:20 AM
Is there any way to fix this problem?

Vlad Khorsun added a comment - 31/Oct/17 05:49 PM
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.