Netdata not working with MySQL because SHOW USER STATISTICS MariaDB only command

[werowe]

Netdata is not working with MySQL, only MariaDB. This is because this command SHOW USER_STATISTICS in this file only works in MariaDB.

Problem code is https://github.com/netdata/netdata/blob/bfae683664b488df91940a81760459db2b18983c/collectors/python.d.plugin/mysql/mysql.chart.py

My install is http://walkercodetutorials.codes:19999

[ilyam8]

Hi @werowe

Netdata is not working with MySQL, only MariaDB.

I think it works, and it works on your install - i see MySQL on the dashboard.

If some query doesnt work, module excludes it from list of queries.

[werowe]

I used MariaDB instead.

That query is part of the install script. It should recognize that the installation is MySQL and skip the SHOW USER STATISTICS command. But it doesn’t, so you can’t start MySQL job. It looks like a bug to me. Of course that’s not logical since users as obviously using MySQL. But as of last week on Ubuntu 18.04 and the latest version of MySQL that’s the error I got. You can run the module in debug more or look at /var/log/errors.log and see it say Invalid SQL syntax.

[ilyam8]

But it doesn’t, so you can’t start MySQL job.

Yes, it should remove that query if it raises an error.

netdata/collectors/python.d.plugin/python_modules/bases/FrameworkServices/MySQLService.py

Lines 142 to 150 in 757ce94

	try:
	cursor.execute(query)
	except (MySQLdb.ProgrammingError, MySQLdb.OperationalError) as error:
	if self.__is_error_critical(err_class=exc_info()[0], err_text=str(error)):
	cursor.close()
	raise RuntimeError
	self.error('Removed query: {name}[{query}]. Error: error'.format(name=name,
	query=query,
	error=error))

Of it response is empty

netdata/collectors/python.d.plugin/mysql/mysql.chart.py

Lines 822 to 826 in bfae683

	if 'user_statistics' in raw_data:
	if raw_data['user_statistics'][0]:
	data.update(self.get_userstats(raw_data))
	else:
	self.queries.pop('user_statistics')

---

Could you do as netdata user:

./python.d.plugin debug trace mysql

[werowe]

I purged MariDB and installed MySQL 5.7. Results. Not sure. Debug log looks good, but item not added to dashboard. Seems error only occurs when you use Python pymysql and not

sudo apt-get install libmysqlclient-dev
pip install mysqlclient

Debug log started to show output but item not added to dashboard. Might not be added to dashboard because it might think that dashboard item successfully added in first try with first Python client.

Moved web interface to http://walkercodetutorials.com:19999/ . MySQL not showing up on console and errors in log. Yet, when installed 2nd Python library mysqlclient after first starting with pymysql

./python.d.plugin mysql debug trace

started to both throw errors but got a match on mysql[debiancnf] and then it started echoing stats, thus it was working. And it looked like it successfully removed the query:

2019-12-14 09:05:13: python.d ERROR: mysql[debiancnf] : Removed query: user_statistics[SHOW USER_STATISTICS;]. Error: error

ProgrammingError: (1064, "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 'USER_STATISTICS' at line 1")

Started with this andcorresponding output https://pastebin.com/Bw0FdUna

sudo pip install pymysql

Then added this and corresponding output: https://pastebin.com/BKDxwJJi

sudo apt-get install libmysqlclient-dev
pip install mysqlclient

mysql -u netdata -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 88
Server version: 5.7.28-0ubuntu0.16.04.2 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

All of these errors disappeared from the log when I added the other, i.e., second, Python lib mysqlclient instead of using pymysql

grep mysql /var/log/netdata/error.log

2019-12-14 08:41:20: python.d INFO: plugin[main] : mysql[tcp_netdata] : check failed
2019-12-14 08:41:20: python.d ERROR: mysql[tcpipv4_netdata] : Can't establish connection to MySQL: (1045, u"Access denied for user 'netdata'@'localhost' (using password: NO)")
2019-12-14 08:41:20: python.d INFO: plugin[main] : mysql[tcpipv4_netdata] : check failed
2019-12-14 08:41:20: python.d ERROR: mysql[tcpipv6_netdata] : Can't establish connection to MySQL: (2003, "Can't connect to MySQL server on '::1' ([Errno 111] Connection refused)")

[ilyam8]

started to both throw errors but got a match on mysql[debiancnf] and then it started echoing stats, thus it was working. And it looked like it successfully removed the query

Yes, it works, the SHOW USER_STATISTICS; syntax error was handled, the query was removed. All is ok.

Debug log looks good, but item not added to dashboard.

Keep in mind you need to execute python.d.plugin as netdata user. Probably it works when you execute it as root - debiancnf is readable by root, not readable by netdata.

[werowe]

Thanks. I am your tech writer. I am going to update the documentation with a note to say don't use the PyMySql driver. Use mysqlclient instead. The first one does not through an error that python.d.plugin catches.

[ilyam8]

What errors are you talking about? I think module works both with python-pymysql and python-mysqldb.

i think python-pymysql had some problems with getting connection parameters from my.cnf.

netdata/collectors/python.d.plugin/python_modules/bases/FrameworkServices/MySQLService.py

Lines 53 to 58 in fc0570f

	elif conf.get('my.cnf'):
	if MySQLdb.__name__ == 'pymysql':
	# TODO: this is probablt wrong, it depends on version
	self.error('"my.cnf" parsing is not working for pymysql')
	else:
	properties['read_default_file'] = conf['my.cnf']

I think there is no problem atm and we need just to remove the check.

---

So, please, if you have time, do one more test with python-pymysql:

delete these lines

netdata/collectors/python.d.plugin/python_modules/bases/FrameworkServices/MySQLService.py

Lines 54 to 57 in fc0570f

	if MySQLdb.__name__ == 'pymysql':
	# TODO: this is probablt wrong, it depends on version
	self.error('"my.cnf" parsing is not working for pymysql')
	else:

and restart netdata.service

[werowe]

The error with PyMySQL looks like this. I will repeat the exercise in a could of days to prove it.

2019-12-07 20:47:39: python.d ERROR: mysql[local] : _get_data() returned no data or type is not ```
<dict>
2019-12-07 20:47:39: python.d INFO: plugin[main] : mysql[local] : check failed

[werowe]

ilyam8 do you work for Netdata? Perhaps you can try on your server. I am trying these commands to purge my installation but cannot get beyond this error:

Setting up mysql-server-5.7 (5.7.28-0ubuntu0.16.04.2) ...
/var/lib/dpkg/info/mysql-server-5.7.postinst: line 143: /usr/share/mysql-common/configure-symlinks: No such file or directory
dpkg: error processing package mysql-server-5.7 (--configure):
 subprocess installed post-installation script returned error exit status 1
Errors were encountered while processing:
 mysql-server-5.7

Here are the instructions I followed to purge mariadb and mysql and reinstall, plus sudo pip install pymysql:

sudo apt-get remove --purge mysql-\*
sudo apt-get remove  mariadb-common
sudo apt autoremove
sudo apt update
sudo apt upgrade

sudo apt-get install mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server-5.7 mysql-server-core-5.7 mysql-server

[ilyam8]

What distro are you on?

[werowe]

Distro of what? Ubuntu 18.04 and whatever Netdats is the current one you get with Curl.

[ilyam8]

Yes, i meant that, thanks.

---

If i understood you right, to reproduce your problem i need:

• install mysql-server
• install python-pymysql

I see the problem is not severe. I will check it later.

I think all we need to do is to remove

netdata/collectors/python.d.plugin/python_modules/bases/FrameworkServices/MySQLService.py

Lines 54 to 57 in fc0570f

	if MySQLdb.__name__ == 'pymysql':
	# TODO: this is probablt wrong, it depends on version
	self.error('"my.cnf" parsing is not working for pymysql')
	else: