[ZBX-20725] mysql template does not work in 6.0.1 Created: 2022 Mar 10  Updated: 2024 Apr 10  Resolved: 2024 Mar 01

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Agent (G)
Affects Version/s: 6.0.1
Fix Version/s: 6.0.28rc1, 6.4.13rc1, 7.0.0beta2, 7.0 (plan)

Type: Problem report Priority: Trivial
Reporter: John Stones Assignee: Denis Rasikhov
Resolution: Fixed Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

KVM Ubuntu 20.04 Linux 5.4.0-80-generic #90-Ubuntu SMP Fri Jul 9 22:49:44 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
abbix_agentd (daemon) (Zabbix) 6.0.1
Revision a80cb13868 1 March 2022, compilation time: Feb 27 2022 18:51:30

Attachments: PNG File grants.png     PNG File mysql.png    
Team: Team INT
Story Points: 1

 Description   

The MySQL template "MySQL by Zabbix agent" does not work

In /var/lib/zabbix created .my.cnf

[client]
user='zabbix_monitor'
password='password'{}

Also tried

[client]
user='zabbix_monitor'
password='password'{}

host = localhost

{{And }}

[mysql]
user='zabbix_monitor'
password='password'{}{}

 

In mysql create user with grant

GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON . TO 'zabbix_monitor'@'localhost';

auth method correct too

zabbix_monitor | localhost | mysql_native_password | *72A13047AB1160DB1323E12A0862763A907DF629

 

template_db_mysql.conf have

UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version
UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases"
UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"

 

Also tried this from https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/380651-zabbix-4-2-3-template-db-mysql-access-denied-for-user-zabbix-error

 

UserParameter=mysql.ping, mysqladmin --defaults-extra-file='/var/lib/zabbix/.my.cnf' ping | grep -c alive
UserParameter=mysql.get_status_variables, mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sNX -e "show global status"
UserParameter=mysql.version, mysql -V
UserParameter=mysql.db.discovery, mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sN -e "show databases"
UserParameter=mysql.dbsize[*], mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$1'"
UserParameter=mysql.replication.discovery[*], mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sN -e "show slave status"

 

But in server logs see

Preprocessing failed for: ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: NO)
1. Failed: cannot extract XML value with xpath "/resultset/row[field/text()='Aborted_clients']/field[@name='Value']/text()": cannot parse xml value: Start tag expected, '<' not found

 

In Similar server and settings works fine in other KVM server. Please help

 Comments   
Comment by Mark Oudsen [ 2022 Mar 18 ]

Similar issue here while using MariaDB 10.6.x (issues starting appearing after upgrade from 10.4.x to 10.6.x to have a supported setup for Zabbix 6.0 LTS).

Did some root cause analysis and found a resolution for it: add the protocol to the .my.cnf ... (in my case /var/lib/zabbix/.my.cnf)

 

[client]
protocol=tcp
user=<user>
password=<password>

https://mariadb.com/kb/en/mysql-command-line-client/

Reason why this started "failing" in 10.6.x is that MariaDB no longer wants to 'guess' which type of connection should be used ...

Zabbix 6.0.2 LTS
MariaDB 10.6.7
Picked the latest template for mysql from Zabbix GIT

Comment by Mark Oudsen [ 2022 Mar 18 ]

PS: figured this out by looking at all default settings with 

mysql -?

 for my server(s).

host                              (No default value)
html                              FALSE
xml                               FALSE
line-numbers                      TRUE
unbuffered                        FALSE
column-names                      TRUE
sigint-ignore                     FALSE
port                              0
progress-reports                  TRUE
prompt                            \N [\d]>
protocol
quick                             FALSE
raw                               FALSE
reconnect                         TRUE

Note the line with protocol set to 'empty'. Fill it with one of the options allowed (like "tcp") and the WARNING statement disappears hence resulting in valid processable output for the mysql discovery again.

Comment by John Stones [ 2022 Aug 08 ]

Did not work @hoppa 

Comment by Denis Rasikhov [ 2024 Feb 23 ]

Could not reproduce with latest versions (MariaDB 10.11.6, MySQL 8.0.36), maybe it was some kind of a bug in some specific MariaDB client versions. Although it wouldn't hurt to add this option to configuration example, as the template only uses TCP connection anyway.

Comment by Denis Rasikhov [ 2024 Mar 01 ]

Fixed in:

Generated at Tue Apr 15 13:19:23 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.