Out of range value for column 'index_id'

Description

updating or inserting a new record in providence I get the following error:

Out of range value for column 'index_id' at row 1 in /var/www/providence/app/lib/Db/mysqli.php line 316:

In Db_mysqli->execute(Object DbStatement, Object DbStatement, ' INSERT INTO ca_sql_search_word_index (table_num, row_id, field_table_num, field_num, field_container_id, field_row_id, word_id, boost, access, rel_type_id) VALUES (57, 222260, 50, 'COUNT', NULL, 0, 8, 1, 0, 0)', Array(0), ) in DbStatement line 151

index.Id in table ca_sql_search_word_index is defined as int(10) , maximal value of int in mysql is 4294967295.

BUT:

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'raubgut' AND TABLE_NAME = 'ca_sql_search_word_index'

shows 4294971152 => out of range.

Any idea what to do?

Christoph Krempe / Freie Univerität Berlin

Environment

Linux sf3 4.9.0-14-amd64 #1 SMP Debian 4.9.240-2 (2020-10-30) x86_64 GNU/Linux

Activity

Show:
Seth
November 23, 2020, 1:34 PM

Hi,

You can try resetting the auto increment counter by first deleting all rows in the table:

TRUNCATE TABLE ca_sql_search_word_index

and then using this to reset the auto increment counter:

ALTER TABLE ca_sql_search_word_index AUTO_INCREMENT = 1;

And then finally, do a reindex.

The underlying issue is that somehow you’ve gotten the index up to 4+ billion and exceeded the size of the index colums. I’ve never seen that happen before.

Christoph Christoph
November 23, 2020, 2:09 PM

By “reindex” you mean “caUtils rebuild-search-index”?

Seth
November 23, 2020, 2:18 PM

Yes exactly.

Assignee

Seth

Reporter

Christoph Christoph

Labels

None

Components

Affects versions

Priority

Blocker
Configure