Error in indexer.delta.sh after Linux upgrade

[Peter Bernsmann]

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?

[Janos SUTO]

What mysql server and version do you have?

[Peter Bernsmann]

mysql is "Ver 14.14 Distrib 5.7.16, for Linux (x86_64)"

[Janos SUTO]

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.

[Peter Bernsmann]

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?

[Janos SUTO]

Yes.

[Peter Bernsmann]

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).

[Janos SUTO]

Can you show me the output of "select * from sph_counter;" and "show create table sph_counter;"?

[Peter Bernsmann]

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)

[Janos SUTO]

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.

[Peter Bernsmann]

I did it. But the result is the same as befor: Column 'max_doc_id' cannot be null

[Janos SUTO]

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?

[Peter Bernsmann]

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. :-)

[Janos SUTO]

Great!

[Thomas Wiese]

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

[Janos SUTO]

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