I know that this is not enough but if it is monitored by Zabbix proxy and you remove proxy then it will also delete autoreg_host

zalex_ua Yes, I know that, because of constraints on DB level.

We have more than 2000000 rows in this table.

Could you advise, is it safe to perform something like this:

DELETE from autoreg_host where autoreg_hostid in (

select a.autoreg_hostid
from autoreg_host a left join hosts h
on a.host = h.host
where h.host is null

)

(not tested)

Depends. It wont crash or something, but some of auto registration events might lose the associated autoreg_host record (not likely, but in theory it can happen). What it means - if the autoregistration event was not yet processed it will be ignored. Of course when server will receive next active checks request from this host it will create new autoreg_host record and process the new event, so in most cases it would just delay host auto registration by 2 minutes (the default RefreshActiveChecks interval).

So yes, it should be safe unless you have some very specific setup/requirements.

Dear users, would you be able to run the following SQL SELECT statement and provide the output:

UPD: please see the SQL in comment below.

Hi dimir, you wrote:

would you be able to run the following SQL SELECT statement and provide the output:

The result on my system (with PostgreSQL) is following:

Error: ERROR: function from_unixtime(unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 217
SQLState: 42883
ErrorCode: 0

Oh, sorry, my bad, this function is non general-SQL. Could you try this one:

UPD: please see the SQL in the comment below.

Could you try this one

For me, the result is: 0 (zero).
At the same time, the table autoreg_host contains 621 record, some of them are very old (these hosts do not exist several years yet).

That probably means for those autoreg_host records the corresponding events are already deleted.

Please try the following one (but please include the whole output, where the query time is seen):

select count(ah.autoreg_hostid)
    from autoreg_host ah
    where not exists (
            select null
                from hosts h
                where ah.host=h.host)
        and not exists (
                select null
                    from events e
                    where ah.autoreg_hostid=e.objectid
                        and e.source=2
                        and e.object=3);

I'm interested in amount of returned records and time spent on the statement.

Please try the following one (but please include the whole output, where the query time is seen):

The result is:

Query 1 of 1, Rows read: 1, Elapsed time (seconds) - Total: 0.212, SQL query: 0.197, Reading results: 0.015

Thanks a lot! zalex_ua would it be possible for you to run that also?

<zalex_ua> asked to get. But I'm not sure about expectation.

konstantin.kornienko any chance to run the above mentioned SQL (this comment) to see the query execution time?

Fixed in development branch for 6.0.

Overview

The table autoreg_host was never cleared before this fix. After this fix the records in autoreg_host table will be handled by Housekeeper. Whenever it is run it will delete records in it that do not reference any of the autoregistration events (autoreg_host.autoreg_hostid=event.objectid) and any of the hosts (autoreg_host.host=host.host) respecting the MaxHousekeeperDelete option.

Fixed in

• 6.0.22rc1 02e9290f703
• 6.4.7rc1 1b0f8c750a8
• 7.0.0alpha5 24b522ee98f

Updated documentation:

• Upgrade notes: 6.0.22, 6.4.7

Thank you, guys!