FTS$MANAGEMENT.FTS$REBUILD_INDEX arithmetic exception, numeric overflow, or string truncation

[livius2]

Hi

i run 'FTS$UPDATE_INDEXES' but it raise an error.

arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 56, actual 60

I have tried also rebuilding all but same error.

this worked and stopped, like some data cuse problem?

Error: *** IBPP::SQLException ***
Context: Statement::Execute( EXECUTE PROCEDURE FTS$MANAGEMENT.FTS$FULL_REINDEX )
Message: isc_dsql_execute2 failed

SQL Message : -802
Arithmetic overflow or division by zero has occurred.

Engine Code    : 335544321
Engine Message :
arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 56, actual 60
At procedure 'FTS$MANAGEMENT.FTS$REBUILD_INDEX'
At procedure 'FTS$MANAGEMENT.FTS$FULL_REINDEX' line: 129, col: 7

[livius2]

If the field size was changed after index was created, i must drop the index and create again? Or EXECUTE PROCEDURE FTS$MANAGEMENT.FTS$REBUILD_INDEX(name) should do the job? I ask because it do not do the job...

I have identified field and row (good that we have audit tables) and found that 60 chars are in field which was previously 56 and now it is VarChar(100).

[sim1984]

The FTS index metadata does not store information about the size of the fields. It simply stores the table name and its fields. Then, a query is built on them to extract data. This query is prepared and then executed, and the index is updated based on its results. The problem may be that UDR caches prepared queries to increase performance. As is known, the lifetime of the UDR cache is tied to the lifetime of the connection. That is, in your case, it is enough to reconnect.

P.S. I think all these caches should be deleted. After all, indexes are not often updated this way. And in 5.0, re-preparing a query is not that expensive.

[livius2]

We always do metadata changes in exlusive single connection (under different database name) and put database back to the users. So no connections after database structure changes. I do not suppose it is cache problem.

But i suppose you mean that new connection to database (during active all other connections) is ok? I done this in new connection already i above error messages.

[sim1984]

No. I assume that before increasing the size, the FTS index was built, and then it was tried to be built again. In any case, there is no information about the sizes in the index metadata. So the error is unlikely to be related to this. You can completely delete the index and try to build it from scratch. One more question, what version of UDR are you using? The latest one? If so, a lot has changed there, including packages for rebuilding indexes.

[livius2]

I am on: SELECT FTS$STATISTICS.FTS$LUCENE_VERSION () FROM RDB$DATABASE;

3.0.8

Is it latest? I do not see in repo the place with this number, git search did not return anything for "3.0.8"

[livius2]

It was because view was not altered after size of field was changed.