Invalid BLOB ID [SQLState:42000, ISC error code:335544329]

[vanicekv]

I use the JDBC Jaybird driver for my Spring Boot application and also for DBeaver - both started having a problem loading data that has a BLOB included in it. The problem always appeared after upgrading to Firebird 4.0.3 (tested on two operations).

Error: Could not extract column [85] from JDBC ResultSet [invalid BLOB ID [SQLState:42000, ISC error code:335544329]]

I have no idea where the problem could be. This never happened to us with FB version 4.0.2 and lower (or 2.5).

Since it does this in both Spring Boot and DBeaver, I would say that the problem is somewhere in the settings or the JDBC driver itself.

The fix is if I run this on the given BLOB attribute: UPDATE XY SET BLOB='' WHERE BLOB='';

But after a certain time, the problem recurs. During a full day of development and working with the database, this will happen at least once a day. Otherwise, the database goes through backup & restore every day without any problems.

Can't you think of what it could be?

[mrotteveel]

Some questions:

• Which version of Jaybird are you using?

• Did you only change the Firebird version, or also the Jaybird version when this error occurred?

• Do you have the full exception stacktrace?

[vanicekv]

• I use the Jaybird version for both (DBeaver and Spring): org.firebirdsql.jdbc:jaybird:5.0.2.java11
• The problem started appearing only after upgrading Firebird to 4.0.3, Jaybird was not upgraded.
• I will try to extract the entire stacktrace exception and send it.

It's strange that the moment we try to downgrade Firebird, it doesn't fix itself. But when using IBExpert (native database access) the error does not occur at all, only when Jaybird is used in another application. I also tried downgrading Jaybird but no change.

That application runs on more than 20 databases with version 4.0 or 2.5, Jaybird version is also 5.0.2 and without problems. As soon as FB was upgraded to 4.0.3 these errors occurred.

[mrotteveel]

That seems to indicate it is a problem with Firebird, but I can't think of an obvious cause. Have you tried querying with ISQL when the error occurs?

[vanicekv]

That's what I thought, but why would it work through IBexpert when an error appears on Jaybird? It's weird, I don't know what to think about it myself. We also have an application that uses FIB+ and there the problem does not appear at all (as in IBexpert).

I haven't tried it via ISQL, I will.

[mrotteveel]

There can be some variance in how tools retrieve or store blobs that could potentially account for this.

[mrotteveel]

Does the problem only occur with blobs that are an empty string?

[mrotteveel]

And should those blobs really be an empty string?

[mrotteveel]

If you can reproduce with DBeaver, could you try downgrading the Jaybird version used by DBeaver to 4.0.9 and then try again (you may need to restart DBeaver after downgrading the driver)? There were some changes in how Jaybird handles blobs in 5.0, so it might help pin down the problem.

[vanicekv]

From DBeaver - I probably can't get more information, the same problem is also in the Spring application:

org.jkiss.dbeaver.model.exec.DBCException: SQL Error [335544329] [42000]: invalid BLOB ID [SQLState:42000, ISC error code:335544329] at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCAbstractValueHandler.fetchValueObject(JDBCAbstractValueHandler.java:55) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetDataReceiver.fetchRow(ResultSetDataReceiver.java:126) at org.jkiss.dbeaver.model.impl.jdbc.struct.JDBCTable.readData(JDBCTable.java:203) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123) at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5142) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: java.sql.SQLSyntaxErrorException: invalid BLOB ID [SQLState:42000, ISC error code:335544329] at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:613) at org.firebirdsql.gds.ng.FbExceptionBuilder$ExceptionInformation.toSQLException(FbExceptionBuilder.java:571) at org.firebirdsql.gds.ng.FbExceptionBuilder.toSQLException(FbExceptionBuilder.java:309) at org.firebirdsql.gds.ng.AbstractFbBlob.length(AbstractFbBlob.java:356) at org.firebirdsql.jdbc.field.FBBlobField.getBytesInternal(FBBlobField.java:142) at org.firebirdsql.jdbc.field.FBBlobField.getBytes(FBBlobField.java:132) at org.firebirdsql.jdbc.field.FBBlobField.getObject(FBBlobField.java:105) at org.firebirdsql.jdbc.FBResultSet.getObject(FBResultSet.java:534) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCResultSetImpl.getObject(JDBCResultSetImpl.java:627) at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCContentValueHandler.fetchColumnValue(JDBCContentValueHandler.java:75) at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCContentValueHandler.fetchColumnValue(JDBCContentValueHandler.java:1) at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCAbstractValueHandler.fetchValueObject(JDBCAbstractValueHandler.java:49) ... 8 more

I also tried on 4.0.9 in DBeaver but still the same error.

IBExpert - it's really empty:

Obviously no problem via isql:

[mrotteveel]

This makes we wonder if there is some sort of NULL vs empty problem going on, but then I would expect ISQL to either show NULL, or return the same error.

What is the definition (DDL) of the column?

[mrotteveel]

Would it be possible for me to - privately - get a copy of an affected database? I will also need to know if you're using Linux or Windows.

[vanicekv]

CREATE DOMAIN D$OBRAZEK AS BLOB SUB_TYPE 0 SEGMENT SIZE 80;

As for the null value vs empty - I tried that, I didn't find a connection that would have an effect. Anyway, it can be null or empty.

Interestingly, it helps to do this update and it temporarily fixes it: UPDATE ADRKTGM01 SET OBRLOGO='' WHERE OBRLOGO=''; So if it was null then this won't help.

[mrotteveel]

There have been some bugs in the past where NULL values behaved as empty/zero values or vice versa, but I don't see how that would work for a blob column given how they work.

[vanicekv]

I don't know about providing a copy now, we would have to check if it is possible (company database). Anyway, it showed up on both Windows and Linux OS.

[vanicekv]

Mainly, I did not manage to find out how the error can be triggered, what causes it.

[mrotteveel]

OK, I'll do some experiments to see if I can reproduce it in some way. Do you know if the record was modified through Jaybird, or through another application (e.g. that FIB+ application)?

[vanicekv]

I have no idea, rather I would say from observation that it happens after an intervention from Jaybird, but I can't confirm it 100%. We are looking for a way to send you a sample database for testing.

[vanicekv]

I did the test now. I have a database that ran on 4.0.2, but with FB 4.0.3 the backup&restore has not yet run at all. I would say that she is not "spoiled". I took it, put it on FB 4.0.2, DBeaver loads the data fine (Jaybird 5.0.2).

I also ran a backup&restore of that database on FB 4.0.3 and copied it to FB 4.0.2 - DBeaver has a problem, invalid BLOB ID error.

Therefore, downgrading to 4.0.2 will not help - as soon as backup&restore to 4.0.3 is started, the data will be modified.

[vanicekv]

Another insight - backup&restore that database back under Firebird 4.0.2 will not help, the data is then obviously corrupted.

[vanicekv]

DBeaver vs IBexpert:

[vanicekv]

@mrotteveel I have prepared a sample database with the faulty data for you, please give me an email where I can send it. With this database you should probably see the same thing as me in DBeaver.

[mrotteveel]

You can send it to mrotteveel (at) lawinegevaar (dot) nl.

[mrotteveel]

Jaybird is explicitly treating blob-id 0 as an invalid value. I'm wondering if maybe it shouldn't.

[mrotteveel]

I've posted a question to firebird-devel to verify this. I'll also see what happens if I remove the check(s) handling blob id 0 as invalid.

That said, it would still be good to have a reproducible case that demonstrates how these values end up in the database.

[mrotteveel]

It seems that ISQL explicitly ignores blobs which have the first 4 bytes (gds_quad_high) set 0.

[mrotteveel]

Ignore that last comment, that was a specific case in how it shows metadata information. However, server-side, it seems blob id 0 is always handled as an empty blob (in blb::open2 in src/jrd/blb.cpp).

[mrotteveel]

@vanicekv The current consensus in the discussion (at https://groups.google.com/g/firebird-devel/c/uwVRyUxKoQA/) is that blob id 0 should not occur in the database, so likely this is a bug where the NULL flag is disregarded somehow, somewhere, and the value should actually be NULL. However, we'll need a reproducible test case to identify the problem.

Have you been able to find yet how and where these values are introduced?

[vanicekv]

@mrotteveel Thank you for describing the situation. So far, I have made adjustments to Spring's access to BLOB values and their storage in the database. I am waiting to see if the error appears again.

I suspect that in the case of an empty string, BLOB ID 0 could have been stored in the database. But as I say, I'm still waiting to see if the error doesn't appear again.

It's strange that until now there was no problem with this until updating to FB 4.0.3, so something must have changed there.

[mrotteveel]

In #764 (and #765 for Jaybird 5.0.3), I made change to allow Jaybird to read blobs with id 0 without error. However, having a reproducible test case for how these values are introduced in the database would be needed to fix the real bug (as there shouldn't be blobs with id 0 in a database).

[mrotteveel]

I'm going to close this issue. If you find out how to reproduce this (and if it is specific to Jaybird), please feel free to reopen. If you find out how to reproduce it, and it is a server problem, please create an issue at https://github.com/FirebirdSQL/firebird/issues.