Error in indexer.delta.sh after Linux upgrade
In had to upgrade my piler-server from Ubuntu 14.04 to 16.04. After that anything seems to be fine, but the indexing cron job indexer.delta.sh fails with an error:
"ERROR: index 'delta1': sql_query_pre[1]: Column 'max_doc_id' cannot be null (DSN=mysql://piler:***@localhost:3306/piler)."
Sphinx is Version 2.2.9-id64-release (rel22-r5006)
How can I resolve this error?
Comments (16)
-
repo owner -
reporter mysql is "Ver 14.14 Distrib 5.7.16, for Linux (x86_64)"
-
repo owner The max_doc_id shouldn't be null according to the schema definition. I suggest you to dump that table, drop it, then recreate it.
-
repo owner -
assigned issue to
-
assigned issue to
-
reporter I found max_doc_id in the mysql table 'sph_counter'. In sph_counter there is one row, both fields are not null. Is this the table you want me to recreate?
-
repo owner Yes.
-
reporter I recreated the table. At the first try indexer.delta.sh didn´t throw an error.
But the problem didn´t go away:
piler@host:/usr/local/libexec/piler$ ./indexer.delta.sh
ERROR: index 'delta1': sql_query_pre[1]: Column 'max_doc_id' cannot be null (DSN=mysql://piler:***@localhost:3306/piler).
-
repo owner Can you show me the output of "select * from sph_counter;" and "show create table sph_counter;"?
-
reporter mysql> select * from sph_counter; +------------+------------+ | counter_id | max_doc_id | +------------+------------+ | 1 | 1324959 | +------------+------------+ 1 row in set (0,00 sec) mysql> show create table sph_counter; +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sph_counter | CREATE TABLE `sph_counter` ( `counter_id` bigint(20) NOT NULL, `max_doc_id` bigint(20) NOT NULL, PRIMARY KEY (`counter_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec)
-
repo owner OK, try the following: drop the table again, create it, then add the value, eg. insert into sph_counter (counter_id, max_doc_id) values(1, 1324959); and let me know how it goes after two delta indexing attempt.
-
reporter I did it. But the result is the same as befor: Column 'max_doc_id' cannot be null
-
repo owner Unfortunately I've run out of ideas, though it's very strange, as ubuntu 16 is supported, and it should work with it. One more trick to try: how about switching to the latest mariadb 10.x release?
-
reporter The Installation process of mariadb wasn't able to migrate the mysql databases. So I had to dump and import the data. Now anything seems to be fine. :-)
-
repo owner - changed status to resolved
Great!
-
we had this error on an fresh install on Ubuntu 16.04. How did you resolv this? If i start the indexer i get the error. indexing index 'delta1'... ERROR: index 'delta1': sql_query_pre[1]: Column 'max_doc_id' cannot be null (DSN=mysql://piler:***@localhost:3306/piler).
We use mysql from the ubuntu release, i also tried the insert from janos. NO luck THX
-
repo owner Set the following to be the sql_query_pre in the delta source:
REPLACE INTO sph_counter SELECT 1, IFNULL(MAX(id), 0) FROM sph_index
- Log in to comment
What mysql server and version do you have?