Node Red and Mariadb

Could someone please help me? I’ve recently retired and decided to further my skills in making projects by building her indoors a weather station. All has gone swimmingly thus far - hardware is operational and data being read. I use a raspberry pi to capture the data and then its put into a sql database using MariaDB.

I’ve now reached the point where I need to display the data in a meaningful format that the missus can understand.

Node-red seems the obvious choice and I’ve had no problem displaying current data using MQTT. Now I wish to display historical data. Easy, I thought. Connect to my database using the MySQL node and pull in the data as needed. But no, it don’t work. ER_NOT_SUPPORTED_AUTH_MODE. Client does not support auth protocol requested by server. Consider upgrading MariaDB client.
I’ve read loads on this but what little hair I have left is gradually being pulled out to extinction,

Please could some nice people explain to me in simple terms how I can get around this problem and restore my wife’s faith in me.

I’m using a Pi 3, running mariadb 10.1.37 on Raspbian 9.0.

Sorry to prattle on.

Derek

Grafana is generally recognised as the best tool for showing historical data. It can read data from a mysql db but you might also like to consider switching to Influx, which is optimised for time series data and has built in features for reducing the sample rate for old data as well purging it.

I assume you are using node-red-node-mysql.

It will work nicely if you create a new user in MariaDB (better if you use MySQL workbench) and configure this user in the node dialog config window, like shown below (using your nickname as the user)

I think it is due to the mysql node library still not supporting the latest server version8 default auth method - see this open issue https://github.com/mysqljs/mysql/pull/1962 (plus the many linked sub issues)