mysql.size error

[mururoa]

Hello,
On Centos (5/6) I have an error for the user parameter for mysql mysql.size.
The configuration for the mysql template should be ok since all other parameters are ok and data are ok on the zabbix server (mysql.status, mysql.ping, mysql.version).
But for mysql.size here is what I have :

grep =mysql.size userparameter_mysql.conf
UserParameter=mysql.size[*],echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema='$1'")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name='$2'");" | HOME=/etc/zabbix mysql -N

zabbix_agentd -t mysql.size
mysql.size [t|sh: command substitution: line 0: syntax error near unexpected token `newline'
sh: command substitution: line 0: `case "" in both|""'
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'data_length+index_length' at line 1]

[mururoa]

The online seems to be wrong as well :

echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema='$1'")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name='$2'");"
-bash: command substitution: line 1: syntax error near unexpected token `newline'
-bash: command substitution: line 1: `case "$3" in both|""'

select sum( echo data_length+index_length;; data|index) echo _length;; free) echo data_free;; esac)) from information_schema.tables;

But it seems that's what comes with the package.
Where is the error ?

[olejak]

Same problem here

Hi,

I have the exact same problem with my installation.

MySQL 5.6.34 daemon running.
zabbix_agentd 3.0.5

mysql.status, mysql.ping and mysql.version is all working.

mysql.size is always returning NULL if any parameter is given. If no parameter is given it returns 10240

$ zabbix_agentd -t mysql.size[zabbix]
mysql.size[zabbix]                            [t|NULL]

$ zabbix_agentd -t mysql.size[]
mysql.size[]                                  [t|10240]

I have tried a bunch of things but have not been able to make it work.

Any suggestions to make this work would be greatly appreciated.

EDIT ADD: I'm using the userparameter_mysql.conf supplied with the source code to zabbix 3.0.5

[Atsushi]

It is a bug that does not use bash to get the value of mysql.size.
It has been fixed in newer releases. Correct the setting of UserParameter.

UserParameter=mysql.size[*],bash -c 'echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name=\"$2\"");" | HOME=/var/lib/zabbix mysql -N'

ZBX-10741
included to sources user param mysql.size[] produces error on some shells

https://support.zabbix.com/browse/ZBX-10741

[olejak]

Hi Atsushi,

Thanks your reply.

The sting you provided is the same that I have in my userparameter_mysql.conf.

I have tried to alter it so that all commands are in absolut. So instead of "bash -c" I change it to "/usr/local/bin/bash -c" (where my bash is located). The same with "mysql" and so on. It worked on mysql.status, mysql.ping and mysql.version.

I still get the same result in mysql.size.

Perhaps I'm not using the command correct?

I would expect that executing zabbix_agentd -t mysql.size[zabbix] should return the size of the database named "zabbix", but maybe I'm wrong?

[Atsushi]

Hi olejak,

Since the version of MySQL is new, it may be that the method of retrieval has been changed.
In MySQL 5.7.17, the value could not be acquired correctly.

----- added

I was wrong.
I got it even with MySQL 5.7.17.
It seems that I specified the specified database name incorrectly.

Please try to check wuth bash if the string passed to the mysql command can be processed.

ex. sqlout.sh

#!/usr/local/bin/bash
echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name=\"$2\"");"

ex. exec './sqlout.sh zabbix'
select sum(data_length+index_length) from information_schema.tables where table_schema="zabbix";

ex. exec './sqlout.sh zabbix history'
select sum(data_length+index_length) from information_schema.tables where table_schema="zabbix"and table_name="history";

[olejak]

Ok. So this is kind of embarrassing.

I found out that a colleague had added a .my.cnf file that Zabbix used instead of mine.

Now when I run the command it returns a value

zabbix_agentd -t mysql.size[zabbix]
mysql.size[zabbix]                            [t|4003627008]

Now a new problem arrives.

I have added an Item to my "Template App MySQL" with name "Size Database Zabbix" and key is "mysql.size[zabbix]". The problem is that I don't get any data.

I have run the agent in debug=4 and can see that the key is not in the output.

I can't figure out why that is.

Hope someone can cast som light on this for me.

UPDATE ADD:
If i run zabbix_agentd -p on the host i see

mysql.size                                    [t|4709296128]

I interpret that as data must be collected. I'm I wrong?