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
Linux sf3 4.9.0-14-amd64 #1 SMP Debian 4.9.240-2 (2020-10-30) x86_64 GNU/Linux
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.
By “reindex” you mean “caUtils rebuild-search-index”?
Yes exactly.