debian 12 stable, running on a VM.
Problem should happen on any x86 machine.

This may be helpful in diagnosing the bug;

Doing a simple test to see that the .my.cnf config is functional and zabbix-agent is somehow to blame (on the sql database server):

sudo -u zabbix mysql -e "SELECT 1;"

Properly executes a query, proving that the zabbix linux user is allowed into the database via its .my.cnf file, in contrast to whatever zabbix_agentd is actually doing.

sudo -u zabbix mysql -e "SHOW DATABASES;"

Prints the databases, proving that the user has the rights to them as well. Even a more complicated query;

sudo -u zabbix mysql -e  "SELECT * FROM 1408_wp2020.wp_options;" 

functions just fine.

Hello npr,

Thank you for contacting with us,
We will look into that, but two questions:

• Which exact version of Debian 12 was used? the newest, 12.7?
• Could you also please test how it does perform with Zabbix Agent2?

Thank you,
Regards,
Tomasz

A quick

> cat /etc/debian_version
< 12.7 

produces the info that this debian stable is otherwise up to date.

Edit: Trying out agent2 isn't really an option because it's backwards incompatible. It utterly breaks the mysql template.

mysql.get_status_variables appears to work entirely differently, for example.

zabbix_get -s sqlserver.example.com -k "mysql.get_status_variables[localhost,3306]" 

ZBX_NOTSUPPORTED: Cannot fetch data: Error 1045: Access denied for user '3306'@'localhost' (using password: NO).

I tried to mess around with it a bit and get it to load template_db_mysql.conf for testing, but this creates an issue where the agent won't start:

zabbix_agent2 [1520024]: ERROR: cannot initialize user parameters: cannot register user parameter "mysql.ping[*]

Some of the parameters added to zabbix_agent2 happen to be named the same as the ones used in the official template for zabbix_agent... with different parameter syntax. This is really bad for backwards compatibility. Note that I'm using an adapted version of the standard zabbix mysql integration template with some custom triggers/fields/etc. So going further with this would involve recreating all of that with the new version at the least.

Agent2 is not really a viable solution to anyone who's made their own sql template.

Renaming all the parameters though, still leaves you with a non-working template after starting the zabbix client for the following reason. All the dependent items now show this:

Preprocessing failed for: mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' in...
1. Failed: cannot extract XML value with xpath "/resultset/row[field/text()='Innodb_buffer_pool_pages_free']/field[@name='Value']/text()": cannot parse xml value: Start tag expected, '<' not found 

The reason for that is a problem with mariadb upstream: for whatever reason it now dumps a line in the stderr before giving the actual output about mysql being a deprecated program name.

Zabbix reads both stdout and stderr, so this gets literally copied into zabbix, which then tries to read out that text as xml. (The real xml is on the next line).

mysql -sNX -e "show global status" | head -n 10
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
<?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">3</field>
  </row>  <row> 

Note the second line in the output above. This mangles the xml to an invalid state.

It did lead me to a thing though: the test command used by me in the report isn't actually correct for the version 1 agent, it should be:

zabbix_get -s sqlserver.example.com -k "mysql.get_status_variables[localhost, 3306, zbx_monitor, $PASSWORD]" | head -n 10

This produces the exact same output as above (with the v2 agent), and leads to all SQL items being 'NOT SUPPORTED'.

And this also leads me to a hack to solve it for me:

cp /etc/zabbix/zabbix_agentd.conf.d/template_db_mysql.conf /etc/zabbix/zabbix_agentd.conf.d/template_db_mysql.conf.bak
sed -i "s/mysql -h/mariadb -h/g" /etc/zabbix/zabbix_agentd.conf.d/template_db_mysql.conf
systemctl restart zabbix-agent

For the version 2 template, because it's written in go code, the go code would likely need to be modified to use the executable 'mariadb' rather than 'mysql' with mariadb v11+ if it otherwise works in the same way. Edit: It doesn't (uses mysql directly from go) so it shouldn't be a problem when using both v2 client and v2 template.

For version 1, to make it work with other types of mysql (regular mysql rather than mariadb), I suppose the only solutions are multiple templates or suppressing that deprecation warning.

I found some topics about this too: https://mariadb.com/kb/en/suppress-mariadb-11-deprecation-warnings/ and https://talk.plesk.com/threads/deprecated-program-name-warning-after-upgrading-to-mariadb-11-4.375785/ ... zabbix is not the only tool depending on formatted output that got burned by this.

Debian 12.8/Agent 7.0.5/MariaDB 11.4.4, everything works:

root@zero# zabbix_agentd -V |head -n 1
zabbix_agentd (daemon) (Zabbix) 7.0.5

root@zero# mariadb -V
mariadb from 11.4.4-MariaDB, client 15.2 for debian-linux-gnu (aarch64) using  EditLine wrapper

root@zero# zabbix_get -s 127.0.0.1 -k mysql.get_status_variables[localhost,3306] |head -n 5
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
<?xml version="1.0"?>

<resultset statement="show global status
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">