Cannot add new hosts - ids table locked

[haubuchon]

Since today, I cannot add new hosts.

I get the message:

Error in query [SELECT nextid FROM ids WHERE table_name='hosts' AND field_name='hostid' FOR UPDATE] [Lock wait timeout exceeded; try restarting transaction]

I have versions 6.4.5, with mysql database.

Any ideas what I can do ?

Thanks.

[tim.mooney]

That sounds like there's an underlying database issue somewhere. Did you look to see if there's anything in /var/log/mysql*.log or /var/log/mysql/*.log (different distros keep the MySQL or MariaDB logs in different places)?

Have you tried restarting mysqld and Zabbix (or just a full reboot of the system?)

There aren't issues with the underlying volume, are there (I/O errors, volume full, etc)?

There's a note in the Zabbix 6.4.0 "Known Issues" about a possible deadlock under some situations with MySQL < 8.0.29. Are you running MySQL 8.0.0 - 8.0.28?

[haubuchon]

Thank you for the help.

1- I am using docker containers for all components, including mysql. It seems that the mysql log is not enabled. The log file is empty. I'll try to enable it.
2- Yes, I restarted all components of Zabbix except the server itself. I will but I doubt it will change anything. Running on AWS EC2 instance
3- Volume is not full and I do not believe thare are issues (AWS, EBS volumes)
4- I am using 8.0.31

I still have the problem and would not want to loose all my configs by restarting from scratch.

[haubuchon]

FYI, I have tried the following:

1- Do a fresh install on another server
2- backup mysql zabbix data from actual server
3- stop zabbix server and web engine om new server
4- restore mysql data into new mysql instance
5- retart zabbix processes

All my data is there but I still cannot create or clone a host.

I really do not know what my options are now.

Thanks for the help !

[tim.mooney]

I've never seen the problem you are describing, so I don't know what the fix is.

I do know that I would want MySQL logging enabled. Without that, you don't have anything to help narrow down what the problem might be.

You may want to do some general web searches with "MySQL" and the initial error message you included ("Lock wait timeout exceeded; try restarting transaction").

[haubuchon]

NOTE: I reposted this part as a new thread)

I was able to do some advanced troubleshooting and I think I have discovered a bug. If someone can test this scenario with 6.4.5 on Linux, I would appreciate. Than I can submit the bug. It was working with 6.4.3.

It has to do with applying a custom template to a host.

Steps: (I tested from a freshly installed version with no data).

1. create a new host (nothing special)
2. Create a new template
3. In that template, create a Web scenario
4. In this scenario add a step (iURL: www.google.com, Follow redirects: ON, Required status Code: 200)
5. In this scenario, create a dependant Item: (Type: Dependant Item, Master Item: Response Code for step "your step" of scenario "your scenario")
6. In this scenario item, create a Preprocessing: (Name: JavaSript, Parameters: return value == 200 ? "Good" : "Bad" ; )
7. In host, add this new template

My result: web interface goes in a loop and returns with a timeout error after 5 minutes or so) No error in log.

Note: If I ceate this web scenatio and item directly in the host, everything works.