[#CORE-5600] Invalid blob id when add a new blob column of type text and update another field

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

This issue relate to:

~~CORE-6090~~ BLOB fields may be suddenly set to NULLs during UPDATE after a table format change

~~CORE-6089~~ BLOBs are unnecessarily copied during UPDATE after a table format change

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:

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

Kovalenko Dmitry added a comment - 30/Aug/17 04:31 AM - edited

What tools do you use?

[ Permalink | « Hide ]

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

[ Permalink | « Hide ]

bug tracker added a comment - 02/Sep/17 10:30 AM - edited

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

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 02/Sep/17 11:33 AM

Do you have explicit COMMIT after DDL statements ?

[ Permalink | « Hide ]

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

[ Permalink | « Hide ]

bug tracker added a comment - 02/Sep/17 12:24 PM - edited

Yes I do COMMIT.

[ Permalink | « Hide ]

bug tracker added a comment - 29/Oct/17 11:20 AM

Is there any way to fix this problem?

[ Permalink | « Hide ]

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.