Strange issue

[Markku]

Here is the error message after modifying user "zabbix"@"%" to "zabbix"@"fr-srvmonitor" (action on zabbix interface is just to modify DNS name on the server):

Error in query [UPDATE hosts SET host='fr-srvtest',name='fr-srvtest',monitored_by='0',status='1',tls_connect='1',tls_accept='1',tls_psk_identity='',tls_psk='',tls_issuer='',tls_subject='',proxyid=NULL,proxy_groupid=NULL WHERE hostid='10527'] [The user specified as a definer ('zabbix'@'fr-srvsurv') does not exist] [zabbix.php:17 → require_once() → ZBase->run() → ZBase->processRequest() → CController->run() → CControllerHostUpdate->doAction() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->update() → CHost->updateForce() → CHost->massUpdate() → DB::update() → DBexecute() → trigger_error() in include/db.inc.php:362]
L'exécution de l'instruction SQL a échoué"UPDATE hosts SET host='fr-srvtest',name='fr-srvtest',monitored_by='0',status='1',tls_connect='1',tls_accept='1',tls_psk_identity='',tls_psk='',tls_issuer='',tls_subject='',proxyid=NULL,proxy_groupid=NULL WHERE hostid='10527'".

Hostname "fr-srvsurv" was the old name of zabbix server, before become "fr-srvmonitor".

Interesting, so where did the NAS/MariaDB get the old hostname, that is a good question. What I would do is reboot the DB server to be sure that there is nothing in any caches, and if that doesn't help, run some tcpdumps to see what the server does at the time the connection is attempted, does it do some unexpected external name resolution.

And I see the IP address connection also failed. That's also very strange.

What is the MariaDB version?

Markku

[philou31]

MariaDB version: 10.11.6-1369.

For rebooting my NAS I must wait tomorrow (end of the afternoon, after my job) because backups are made form 9:00PM to 11:00PM (french time) and deposited on NAS.

[Markku]

Btw I run my MariaDB server with "skip_name_resolve" option and IP address works, I don't think it should specifically enable IP addresses in the permissions, but maybe worth trying. Would require reconfiguring permissions for other database as well though.

Markku

[philou31]

I've rebooted my NAS.
I've tried only user "zabbix"@"fr-srvmonitor": no change, always KO.
I've tried only user "zabbix"@"192.168.1.180": no change, always KO.
And always the same record in host_cache table.

I will try to restart MariaDB with "skip_name_resolve" option.

[philou31]

"skip_name_resolve" option done.
Restart MariaDB.
KO to connect on my PhPMyAdmin.
It's normal I think, if I want to do that I must add IP addresses of all my servers (for all users) instead of their hostnames. That mean that only IP must connect to MariaDB.

[philou31]

A strange thing found on my NAS....
When I do a "grep fr-srvsurv -R /var/*" I find that:

/var/packages/MariaDB10/target/mysql/zabbix/hosts.TRG:triggers='CREATE DEFINER=`zabbix`@`fr-srvsurv` trigger hosts_name_upper_insert\nbefore insert on hosts for each row\nset new.name_upper=upper(new.name)' 'CREATE DEFINER=`zabbix`@`fr-srvwebp` trigger hosts_insert after insert on hosts\nfor each row\ninsert into changelog (object,objectid,operation,clock)\nvalues (1,new.hostid,1,unix_timestamp())' 'CREATE DEFINER=`zabbix`@`fr-srvsurv` trigger hosts_name_upper_update\nbefore update on hosts for each row\nbegin\nif new.name<>old.name\nthen\nset new.name_upper=upper(new.name);\nend if;\nend' 'CREATE DEFINER=`zabbix`@`fr-srvwebp` trigger hosts_update after update on hosts\nfor each row\ninsert into changelog (object,objectid,operation,clock)\nvalues (1,old.hostid,2,unix_timestamp())' 'CREATE DEFINER=`zabbix`@`fr-srvwebp` trigger hosts_delete before delete on hosts\nfor each row\ninsert into changelog (object,objectid,operation,clock)\nvalues (1,old.hostid,3,unix_timestamp())'
/var/packages/MariaDB10/target/mysql/zabbix/hosts.TRG:definers='zabbix@fr-srvsurv' 'zabbix@fr-srvwebp' 'zabbix@fr-srvsurv' 'zabbix@fr-srvwebp' 'zabbix@fr-srvwebp'

[philou31]

I've resolved the issue!!!!!


This issue was related to zabbix database triggers.


I've found this solution to resolve it:

- Create mysql triggers dump:

mysqldump -u root -p --routines --add-drop-trigger --no-create-info --no-data --no-create-db --skip-opt zabbix > outputfile.sql

- Change old DEFINER in the file outputfile.sql (using sed or manually as you want). For me it was old DEFINERS.

- Import all new triggers in the database:

mysql -u root -p zabbix < outputfile.sql

After these actions I've modified "zabbix"@"%" to "zabbix"@"fr-srvmonitor" and all works fine now with zabbix.

[Markku]

Wow, I must say, thanks for pushing this through. So the problem was not in connecting to the database as such, but the database triggers had saved the usernames (including the hostnames), thus reconfiguring the user permissions are not reflected in the database triggers automatically and that affected any modify operations attempted (if I got it right). Learned something new here.

Markku

[philou31]

I wanted to solve this problem at all costs, and at the same time understand where the problem came from. Done.
Now, is it a problem on MariaDB side and/or on Zabbix side, that is the question.