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

BLOB not found in SuperClassic and Classic on Uncommitted Transactions [CORE4678] #4987

Closed
firebird-automations opened this issue Jan 30, 2015 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Israel Lopez (ilopezc)

Is related to CORE3305

I'm supporting a commercial product that happens to use Firebird SQL. As part of new vendor updates, the Firebird installation was upgraded from 2.5.2.26540 to 2.5.3.26778.

The customer who uses the commercial product uses a feature in the database schema known as a "memo" which is stored as a BLOB datatype. Specifically: BLOB SUB_TYPE 1 SEGMENT SIZE 100 CHARACTER SET ASCII

The users started reporting errors in the application, which upon inspection was a Stacktrace message through JayBird, through Hibernate, and through the application code. They were receiving the following error message "BLOB not found." Because no other changes were made, and no other customers reported the issue on the same new version, we had believed it was hardware. While waiting for the hardware specialists to respond I created a test application to confirm.

The test code is hosted here: https://github.com/ilopez/Firebird.TestBlobs
Binaries are available here: https://github.com/ilopez/Firebird.TestBlobs/releases/download/0.1/Debug.zip

Arguments are TestFirebirdBlobs.exe [1] [2] [3]
1 - Path to DB
2 - Username
3 - Password

The application drops the DB, and creates a new DB, then applies the selected schema I was testing.

For testing; two connections are created, one for inserting, and another for selecting. For the first connection, I insert records, but do not commit and close the connection. Then I create a new second connection and do a simple count on the table. Using this code, and switching the MEMO type field from VARCHAR(256) to the BLOB type, I can confirm and replicate the results the users were seeing.

On SuperClassic and Classic for 2.5.3.26778 I receive a BLOB not found when using the second connection, on 2.5.2.26540 I receive no errors for both VARCHAR and BLOB types.

Command Example output and testing results:
https://gist.github.com/ilopez/7d36e860a7a31373cca9

It appears there has been some kind of regression that exposes the first connection's inserts, to the second connections reads, and therefore we receive BLOB not found errors. Writing this separate code, confirms that it was not the commercial software's code OR the customer's hardware causing this issue.

My workaround is going to be to revert back to 2.5.2.26540.

====== Test Details ======

Confirmed on WI-V2.5.2.26540 (official release):
exception on ROLLBACK raises with text:

Statement failed, SQLSTATE = HY000
BLOB not found

No reconnect is required, all can be done in one ISQL attachment.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Have you reached out to the commercial product vendor for support?

That would seem to be the best place to start.

Certainly, no equivalent error has been reported by other Firebird installs, so it would seem that the problem is in the application layer, not Firebird engine.

@firebird-automations
Copy link
Collaborator Author

Commented by: Israel Lopez (ilopezc)

Hi Sean,
I did reach out, no comment yet. That is why wrote the C#⁠ code to confirm while we were waiting on a response. Later today when the system is not as busy I'll roll back to 2.5.2 and see what happens.

The commercial product is written in Java and uses the JayBird driver. My testing application is in C#⁠ .NET using the ADO .NET driver.

-Israel

@firebird-automations
Copy link
Collaborator Author

Commented by: Israel Lopez (ilopezc)

I have an update.

From the Commercial Vendor they consider this not their issue. I can see why. I narrowed down the problem to me creating an index on a BLOB using a CAST and SUBSTRING.

If I remove the following index declaration from the Schema.

CREATE INDEX MEMO_IDX1 ON MEMO COMPUTED BY (UPPER(TRIM(CAST(SUBSTRING(memo from 1 for 128) as VARCHAR(128)))));

The test process works on all versions, and Firebird modes. I'm looking for any other JIRA #⁠s that may relate to this issue. I believe CORE4673 may be related to this. Similar situation, using computed indices.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Israel,

Please post the exact error message reported by the engine, and include the database page size and charset used for the database.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Are you trying to define the MEMO column and the INDEX with a transaction commit in between each command?

@firebird-automations
Copy link
Collaborator Author

Commented by: Israel Lopez (ilopezc)

Hi Sean,
I create the database each time using this DDL https://github.com/ilopez/Firebird.TestBlobs/blob/master/TestFirebirdBlobs/schema.sql

So the table, column, and the INDEX are created before I start my test. In a separate connection I perform the insert, and in another connection I perform the select.

If I use ISQL this is what I get when the database is in the failed state, in regards to the exact error message reported by the engine.

C:\Program Files\Firebird\database\bin>isql -user sysdba -p masterkey Z:\temp\TESTDB.fdb
Database: Z:\temp\TESTDB.fdb, User: sysdba
SQL> select count(*) from memo;

   COUNT 

============
Statement failed, SQLSTATE = HY000
BLOB not found
SQL>

The database was created using the "NONE" charset, and a page size of 4096.

GSTAT header

Database header page information:
Flags 0
Checksum 12345
Generation 49
Page size 4096
ODS version 11.2
Oldest transaction 7
Oldest active 39
Oldest snapshot 39
Next transaction 41
Bumped transaction 1
Sequence number 0
Next attachment ID 6
Implementation ID 26
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Jan 29, 2015 16:53:47
Attributes force write

Variable header data: 
    \*END\*

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Please try the v2.5.4 snapshot build and report back. It has one reason of the "BLOB not found" error fixed. Your description looks unrelated at the first glance, but who knows.

@firebird-automations
Copy link
Collaborator Author

Commented by: Israel Lopez (ilopezc)

Dimitry,
It appears fixed in the 2.5.4.26831 build. I ran my tests, and there seems to be no error in all Firebird server modes.
-Israel

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.4 [ 10585 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE3305 [ CORE3305 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

Test Details: Confirmed on WI-V2.5.2.26540 (official release):
exception on ROLLBACK raises with text:

Statement failed, SQLSTATE = HY000
BLOB not found

No reconnect is required, all can be done in one ISQL attachment.

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