rtindex.py hangs at duplicate id

[Marcel Pötter]

Hi,

‌

I migrated prom sphinx to manticore search, following your how-to.

I cleared the old index files and startet rtindex.py as the piler user.

Afer about 93000 of 230000 messages, rtindex.py keeps logging this:

piler rtindex.py[18112]: Error (1064, "duplicate id '93432'")

And no new messages appear in the web view.

It seems, rtindex hangs on this message.

A restart doesn´t fix it.

When I kill rtindex i get:

piler mariadbd[577]: 2022-12-23 17:46:55 23421 [Warning] Aborted connection 23421 to db: 'piler' user: 'piler' host: 'localhost' (Got an error reading communication packets)

And:

Traceback (most recent call last):
File "/usr/local/libexec/piler/rtindex.py", line 60, in process_batch
sphx_cursor.executemany(SQL_INSERT_QUERY, rows)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 235, in executemany
return self._do_execute_many(q_prefix, q_values, q_postfix, args,
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 259, in _do_execute_many
rows += self.execute(sql + postfix)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 226, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1064, "duplicate id '93432'")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/local/libexec/piler/rtindex.py", line 115, in <module>
main()
File "/usr/local/libexec/piler/rtindex.py", line 111, in main
process_batch(opts)
File "/usr/local/libexec/piler/rtindex.py", line 72, in process_batch
time.sleep(SLEEP_DELAY)
KeyboardInterrupt

I think the MySQLdb._exceptions.ProgrammingError: (1064, "duplicate id '93432'")

is relevant?

I´ve started rtindex in verbose-mode, but there is no more information.

Is there a way to skip this message or show, why it doesnt want to index it?

Best regards!

[Janos SUTO]

Try deleting the id 93432 from the manticore index.

[Marcel Pötter]

Thanks for the reply.

How would I do that? Inside MySQL?

Could you help me regarding the syntax?

Thanks a lot!

‌

[Janos SUTO]

mysql -h0 -P9306

delete from piler1 where id=93432;

[Marcel Pötter]

Thanks, that worked partially:

Now:

piler rtindex.py[18112]: Error (1064, "duplicate id '93433'")

and if I go forth, it goes to the next and next…

Is it safe to completely clear this table?

Like delete * from piler1;

or something like that?

Thanks again for your help!

I tried:

delete from piler1 where id>93432;

seems to have worked for now, willl update here!

[Janos SUTO]

Try delete from piler1 where id>=93433;

You should not remove its contents, it’s the manticore index, you need it to have search results in the gui.

Also, I think about getting rid of this utility, and let piler write directly to the manticore index data. I’ll create a test release or branch, and let you know how to use it.

‌

[Marcel Pötter]

:-) same idea at the same time!

Please do so, I will give it a try!

At the moment, indexing seems to work

Dez 28 21:26:28 piler rtindex.py[18225]: 1000 records inserted

Thanks again for your help!

[Janos SUTO]

I’ve just finished the rt1 branch (https://bitbucket.org/jsuto/piler/get/rt1.tar.gz). It has a few new piler.conf variables that you need to add:

rtindex=1
sphxhost=127.0.0.1
sphxport=9306
sphxdb=piler1

This forces the piler daemon and other binaries to write directly to the manticore rt index table. Note that you don’t need the rtindex.py utility any more.

Let me know how it goes, I’ll release 1.4.3 soon with this feature.

‌

[Marcel Pötter]

Hi,

just installed and tested:

Works like a charm!

Mails appear instantly in the search dialogue without rtindex.py

[Janos SUTO]

I’m glad you like it. I think about moving the tags and notes to rt index as well.

[Marcel Pötter]

I’m glad you like it. I think about moving the tags and notes to rt index as well.

Would be awesome and clear up the crontab a bit.

Thank you very much for all the effort you put into Piler!

I´m selling the enterprise edition where I can!