mysql Data only shows a few items

[Crewze]

I am running Zabbix on a RPI, monitoring a Ubuntu 20.04 Server. I have verified that I can talk to the client Ubuntu Server from the Zabbix Server. I have upgraded to Zabbix 6.2.

When I look at the latest data, I can only see a few items such as the size of my databases and the fact that it is up. All the other parameters are missing. A sample of the latest data attached.


I have checked my config several times and not sure what to do next.

Newby with this app. Any suggestions?

[Markku]

Well this interesting, there is no DSN, password or user filled in here.

That pretty much explains your problems then (and why the error in the GUI: there are < and > characters currently in the DSN that was provided in the template by default)

See the instructions I linked above for ways to configuring the database access.

Markku

[Markku]

Why do you have two "MySQL: Get status variables" items, what's the source of them? That is the master item for parsing the data for almost all other items.

See what does the red exclamation mark tell you about the error.

Did you install the MySQL template according to its instructions, with the correct configuration file+settings for the Zabbix agent?

Markku

[Crewze]

In /etc/zabbix/zabbix_agentd.d/template_db_mysql.conf I have the following:


UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping

UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show g

lobal 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(D

ATA_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 sta

tus"

[Crewze]

Some additional info that may help to find the issue.

From the RPI (Zabbix Server) I can log into the server I am trying to monitor (192.168.0.10) using the user zbx_monitor and password and query the database on the server:

pi@raspberrypi:/etc/zabbix/zabbix_agentd.d $ mysql -u zbx_monitor -p -h 192.168.0.10

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 229674

Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1-log Ubuntu 20.04




Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.




Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.




MariaDB [(none)]> show master status;

+------------------+-----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+-----------+--------------+------------------+

| mysql-bin.000007 | 162219929 | | |

+------------------+-----------+--------------+------------------+

1 row in set (0.001 sec)




MariaDB [(none)]>

[Crewze]

I am also able to run and get a reply using commands form the template:

pi@raspberrypi:~ $ mysqladmin -s -u zbx_monitor -h 192.168.0.10 -p version

Enter password:

mysqladmin Ver 9.1 Distrib 10.5.15-MariaDB, for debian-linux-gnu on aarch64

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.




Server version 10.3.34-MariaDB-0ubuntu0.20.04.1-log

Protocol version 10

Connection 192.168.0.10 via TCP/IP

TCP port 3306

Uptime: 8 days 16 hours 53 min 12 sec




Threads: 17 Questions: 11107411 Slow queries: 14 Opens: 2754 Flush tables: 3 Open tables: 1230 Queries per second avg: 14.770

[Markku]

Based on the screenshot you are using the Zabbix agent on "mySQL Main" to monitor the database, not the agent on the Zabbix server. Is that correct? If yes, it doesn't matter whether your Zabbix server can connect, it only matters that the agent on the database server can connect.

What is the error that is shown in the red exclamation mark?

Markku

[Crewze]

Yes, that is correct. mySQL Main is my main server, not the server.

The error is:

Special characters "\, ', ", `, *, ?, [, ], {, }, ~, $, !, &, ;, (, ), <, >, |, #, @, 0x0a" are not allowed in the parameters.

[Crewze]

Jusr for more info, I get the same problem when I rey to monitor the server mysql. See sceenshot. Only a few of the parameters show up.

[Markku]

How are those two identical items configured?

Special characters "\, ', ", `, *, ?, [, ], {, }, ~, $, !, &, ;, (, ), <, >, |, #, @, 0x0a" are not allowed in the parameters.

The "parameters" here mean the parameters that are given in square brackets in items that were configured as UserParameters in your agent configuration file.

So, go to the item whose name is "MySQL: Get status variables". See its key. What are the parameters that are given there? Are they coming from user macros? If yes, you need to change the macro contents to not use those characters.

Markku

[Markku]

Also note that we (the readers here) have no way to know how your templates/items are configured unless you tell it to us, so it may be a good idea to show us the erroring item configuration.

Markku

[Crewze]

Here is the template:



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"


FYI - If I run the mysql command like this, I get the values returned:



mysql -h 192.168.0.10 -u zbx_monitor -p -sNX -e "show global status"

Enter password:

<?xml version="1.0"?>




<resultset statement="show global status

" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<row>

<field name="Variable_name">Aborted_clients</field>

<field name="Value">12</field>

</row>




<row>

<field name="Variable_name">Aborted_connects</field>

<field name="Value">6</field>

</row>



..........

[Markku]

Ok there is a misunderstanding here. I'd like you to show the item configuration, in Zabbix GUI. You were showing the UserParameter configuration in the agent configuration.

So go to the "mySQL Main" host configuration, Items, open the "MySQL: Get status variables" item, and take a screenshot there.

This is because we need to find out how that UserParameter is called by Zabbix.

In your first screenshot (the Latest values list) there are two of those items, please provide them both so that we see where they are coming from, there should be the template name in the top as well.

Markku

[Crewze]

Sorry. This is new to me. Is this the screen shot you are looking for?

[Markku]

I see you have all the items in double: first provided by the "MySQL by Zabbix agent 2" template and then locally, maybe you have unlinked the template at some point without deleting the items?

Anyway, please scroll to the "MySQL: Get status variables" item and click it to open it, and show that to us.

Markku

[Markku]

Actually, I was able to locate that template in git.zabbix.com and see the item there.

Apparently the item key is:

mysql.get_status_variables["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWO RD}"]

So, one of those three macros have incorrect character(s). Check and fix those macros in the host "mySQL Main".

Markku

[Markku]

(for some reason there is an extra space in "PASSWO RD" and that cannot be fixed, it reappears even though I edit the message, this is a forum problem)