Unabe to add new records to 'poller_time' table

[ikorzha]

Cacti team I am absolutely desperate, I am still on 1.1.38 with spine at 1.18. I have a Cacti system with 5 pollers, recently I have started experiencing: "Spine: poller[1] error: sql failed! error:'1062', message:'duplicate entry", Cacti still works, but spine times out at 1minute mark on every poller.
So far I been fighting the problem by restoring from last known working sql backup of the master server, which works for about a day and then problem comes back.

Please help what can I do to resolve this issue permanently when it occurs without restore from sql backup?

,

[netniV]

How many pollers and devices per poller do you have?

[ikorzha]

netniV sorry for a slow reply:
See below table when duplicate key is taking place:

This table is is after the restore before duplicate key issue reappears:

[ikorzha]

netniV, can you please suggest a course of auction to correct this problem as I am stuck.
I even made an attempt to upgrade to 1.2 to escape the issue, but that was a total disaster, as on a first poller run it deleted all of the 658 devices and graphs and presented me with: "here you go start with a clean slate :)" Restore back 1.1.38 took a few hours..
In any case looking for your advice on this issue. P.S will glady share my DB if you willing to try to look into issue or check why upgrade deletes all of the hosts on first poller run.

[netniV]

If you can share your main DB with us, that would be great. Send it to developers@cacti.net and we will take a look because I don't see why it would suddenly delete all your devices's.

[ikorzha]

netniV I send you and email for a link to your google drive as my DB 400Megs I believe and can't be easily emailed...

[netniV]

Is that after you tar/gzip it? Then yes, you will need to send a link to download it from.

[ikorzha]

Compressed it came to be 41.9Meg
I can try to split archive to 5 pieces and mail it...

[ikorzha]

netniV, can you please check developers mail box. All 5 archive pieces were sent, please acknowledge receipt if you got it..

[cigamit]

Just a note, with 16 processes and 15 threads per process, your spine data collector is killing your central MySQL/MariaDB database. This is your primary issue. Starting in 1.2, we have moved those settings, and many of the key polling tables to the remote collectors alone. So, there will not be as much overloading, but even still, 16 *15 is too high. You are killing you central database, and that is as much the issue as anything else. I would reduce it to 1 to 2 processes and at most 20 threads. You also need to watch your max_connections on the core database server.

Lastly, since you are in the middle of things with @netniV though, I'll defer to him.

[netniV]

I will try and get your dB loaded again but if this so the same DB that we ran through last time, there were no issues with the main upgrade. I will also attempt to replicate the poller situation if I can. Will need to find a box to spin up the extra VMs though. The connections is certainly an issue though I can’t see why that would remove the devices yet.

[ikorzha]

Thank you netniV, I would wait for your assessment. Please try the this latest DB that I have uploaded today...

[cigamit]

Ope, this is an issue with running out of auto_increment keys. Wow. Near term, truncate this table to resolve.

[cigamit]

This is resolved now. The original title was: Spine: poller[1] error: sql failed! error:'1062', message:'duplicate entry

[cigamit]

We are going to revert that change. The correct change will be to do the following:

ALTER TABLE poller_time MODIFY column id bigint(20) unsigned auto_increment;

Once you do that, this problem will not happen again in your lifetime.

[ikorzha]

Cigamit, I am grateful for the fix. I have already implemented it.. My cacti is back online without poller timeouts.. It is now making a perfect sense, due to my 16 poller processes (and I can't use less as poller doesn't finish within a min ( I tested that already), in 2 years that installation was running it consumed 32 years worth! of polling time IDs hence the exaust error I was receiving.
Thank you so much for removing this limitation in cacti installation....

[thurban]

I know this is closed, but I was wondering what that "id" column is actually used for. I have the very same habbit of adding id columns to tables, but if that column isn't used anywhere then it can actually be removed completely without any impact.

The primary key could be as well the "pid, poller_id and start_time" combnation.

Any thoughts ?

[TheWitness]

That's one approach Thomas. Are there any design issues other than method though.

[netniV]

Generally speaking for primary indexing, you should have an id column. If PID is effectively the ID, then that works just as well though it may be useful to have multiple if the system reuses the same PID?