Database problem

[Sirc]

Hi,

I have problem when I try to create or update host, I get error

- Error in query [UPDATE hosts SET hostid='10537',host='XXXX' ,name='XXXXX',status='0',pr oxy_hostid=NULL,tls_connect='1',tls_accept='1',tls _psk_identity='',tls_psk='',tls_issuer='',tls_subj ect='' WHERE hostid='10537'] [The user specified as a definer ('skip-grants user'@'skip-grants host') does not exist] [zabbix.php:22 → require_once() → ZBase->run() → ZBase->processRequest() → CController->run() → CControllerHostUpdate->doAction() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->update() → CHost->massUpdate() → DB::update() → DBexecute() → trigger_error() in include/db.inc.php:367]
- SQL statement execution has failed "UPDATE hosts SET hostid='10537',host='XXXXX ,name='XXXXX' ,status='0',pr oxy_hostid=NULL,tls_connect='1',tls_accept='1',tls _psk_identity='',tls_psk='',tls_issuer='',tls_subj ect='' WHERE hostid='10537'".

I think it broke after updating to 6.0.12. (Ubuntu 20.04.5)

I tried to do

mysql> grant all privileges on zabbix.* to 'zabbix'@'localhost';
mysql> SET GLOBAL log_bin_trust_function_creators = 1;

but the problem was not resolved.

But, when I added skip-grant-tables to mysql (8.0.31) config the problem does not appear.

Can I should permanently add skip-grant-tables to mysql configuration?

Best regards
Sirc

[tim.mooney]

Can I should permanently add skip-grant-tables to mysql configuration?

I would not recommend that.

At the MySQL> prompt, run the following command and follow up in this thread with the output but leave out whatever the encrypted password is.

SHOW GRANTS FOR 'zabbix'@'localhost';

If we can see what the actual GRANTS are, we might be able to spot if the permissions have been messed up because of a typo.

[Sirc]

Result of command

mysql> SHOW GRANTS FOR 'zabbix'@'localhost';
+------------------------------------------------------------+
| Grants for zabbix@localhost |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `zabbix`@`localhost` |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO `zabbix`@`localhost` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

[tim.mooney]

That looks correct to me, so I don't think that my first guess was correct. It's nothing some privilege that got messed up by a typo in the GRANT command you issued.

It's not clear to me what the actual problem is or why it goes away when you disable all access controls.

[Sirc]

Problem is, when I try change anytihg in configuration existsing hosts or add new host to monitoring, I get error as I wrote in fisrt post.

[Sirc]

Maybe, should I recreate the database? Is it possible to backup and restore configurations for monitoring hosts?
Any suggestions?

[gferradas]

Maybe, should I recreate the database? Is it possible to backup and restore configurations for monitoring hosts?
Any suggestions?

Hi there is an option in zabbix to import hosts and groups i do not know what format has to have the file or how to get the information you looking but the option is there.


Thats the information you can import into but like i said before i dont know where to get it

[ItOfficeIzzuesZab]

Im having the same issues here

[ArtisB]

In my case there were four triggers i had to drop from information_schema database.

drop trigger zabbix.items_name_upper_insert;
drop trigger zabbix.items_name_upper_update;
drop trigger zabbix.hosts_name_upper_insert;
drop trigger zabbix.hosts_name_upper_update;

Have good day!

[jsrozo]

In my case there were four triggers i had to drop from information_schema database.

drop trigger zabbix.items_name_upper_insert;
drop trigger zabbix.items_name_upper_update;
drop trigger zabbix.hosts_name_upper_insert;
drop trigger zabbix.hosts_name_upper_update;

Have good day!

Hi,

in this error is necessary rebuilt the trigger in schema data base.

You can see the triggers whit the command in db:
[HASHTAG="t2922"]mariadb[/HASHTAG]
show triggers in zabbix;


--Delete triggers in schema database
[HASHTAG="t2922"]mariadb[/HASHTAG]
use zabbix
drop trigger zabbix.items_name_upper_insert;
drop trigger zabbix.items_name_upper_update;
drop trigger zabbix.hosts_name_upper_insert;
drop trigger zabbix.hosts_name_upper_update;


--Create new triggers in BD zabbix
[HASHTAG="t2922"]mariadb[/HASHTAG]
use zabbix
create trigger hosts_name_upper_insert before insert on hosts for each row set new.name_upper=upper(new.name);
create trigger items_name_upper_insert before update on hosts for each row set new.name_upper=upper(new.name);

--execute unique command in mariadb

delimiter //
create trigger hosts_name_upper_update before update on hosts for each row begin if new.name <> old.name then set new.name_upper = upper(new.name); end if; end //
create trigger items_name_upper_update before update on items for each row begin if new.name<>old.name then set new.name_upper=upper(new.name); end if; end //
delimiter ;