MySQL connection

Projects General Guidance
1

Is anyone here currently, successfully, connecting an Arduino directly to MySQL?

Please describe what Arduino library you used, any additional modifications you needed to make and any configuration changes on the server side.

When I say "directly" I don't mean making a HTTP GET/POST request that runs a server-side PHP script that connects to the MySQL database.

I have attempted to use 3 of the libraries available in the Library Manager. For 2 of them, I suspect this is because the password encryption method they assume is now disabled by default in newer versions of MySQL, and may even be deprecated in future versions.

I have had intermittent success with the 3rd library. Sometimes it connects successfully but most of the time it fails with timeout errors. But this library is now archived in github.

image
image1020×856 74.7 KB

(I haven't tried ESP32_MySQL because I want to use Wemos D1 Mini ESP8266).

2

yes of course, mine :rofl: :rofl:

The third one you see in the screenshot you posted.

What type of MySQL server are you using?

1 Like
3

Thanks for responding!

I'm using MySQL 8.4.2, running on a Raspberry Pi 4 with Raspberry Pi OS and CasaOS installed.

I tried to use the "mysql_create_insert" example sketch from your library but I could not get it to compile on Wemos D1 Mini.

So I tried this code, which I made by simplifying that example sketch:

#if defined(ESP8266)
  #include <ESP8266WiFi.h>
#else
 #include <WiFi.h>
#endif

#include <MySQL.h>
#include "secrets.h"

WiFiClient client;
MySQL sql(&client, dbHost, dbPort);
DataQuery_t data;

const char* query = "insert into granary_sensors.granary_sensors (sensor_name, sensor_value) values ('TT', -999);";

void setup() {
  Serial.begin(115200);
  Serial.println("******************************************************");
  Serial.print("Connecting to WiFI");

  WiFi.begin(ssid, wifiPwd);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  Serial.println("\nWiFi connected, IP address: ");
  Serial.println(WiFi.localIP());

  //Open MySQL session
  Serial.print("Connecting to ");
  Serial.println(dbHost);

	if (sql.connect(user, password, database)) {
    Serial.println("Connected OK");
    if (sql.query(data, query)) {
      Serial.println("Query executed OK");
    }
    else {
      Serial.println("Query execution failed");
    }
  }
  else {
    Serial.println("Connect failed");
  }
}

void loop() {
}

I get the following serial output:

15:19:54.244 -> .......
15:19:57.954 -> WiFi connected, IP address: 
15:19:57.954 -> 192.168.1.146
15:19:57.954 -> Connecting to 192.168.1.201
15:19:57.987 -> Connected to MySQL server version 
15:19:59.975 -> Authentication failed
15:19:59.975 -> Connect failed

But I can connect using another PC:

paul@paul-desktop:~$ mysql -ugranary_sensors -p -h192.168.1.201 --port=3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.2 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

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> insert into granary_sensors.granary_sensors (sensor_name, sensor_value) values ('TT', -999);
Query OK, 1 row affected (0.03 sec)

mysql> exit
Bye
4

At the moment I have two servers running a MySQL server, and in fact on the Linux server I'm using a MariaDB (Ver 15.1 Distrib 10.5.23-MariaDB, for debian-linux-gnu) which is a fork of the original created when MySQL was acquired by Oracle.

On the Window server instead I have a MySQL (Oracle) v8.0.35

As soon as I can, I'll try to install MySQL Oracle on the Linux system too and try to understand what the differences are in the connection handshake.

5

That would be very helpful, thanks.

I would be equally happy using MariaDB or MySQL. I did try MariaDB at first, but got the same problems, so I uninstalled MariaDB and installed MySQL instead, thinking that MariaDB could be the problem.

Let me know if there's anything I can do to help figure out the problem. I could re-install MariaDB for further tests, if that helps.

6

With MariaDB it should have worked, strange.
I just checked with an ESP8266 and it works as it should.

image
image1093×291 7.5 KB

Try to check what authentication method you are using for your specific user.
On my DB for the user 'user1' I have set 'mysql_native_password'

SELECT User, Host, plugin 
FROM mysql.user 
WHERE User = 'user1';
7

Ah, I have been fighting with this f*!% plugin all day!

It's not enabled by default when I install MySQL. If I try to enable it, it appears to affect all users and all databases, even the root user, and I cannot connect any user to the database using Adminer, MySQL Client or whatever.

How do I enable mysql_native_password without it applying to all databases and users?

I will try your query and report back.

8

Is mysql_native_password used with MariaDB also?

I have read on Oracle website that mysql_native_password is deprecated and might be removed from future versions. What will you do then?

9

Yes it is, but it's also the simpler authentication methods as I know.

My library is based on other open source libraries that I have taken inspiration from, so the connection handshake is essentially the same.

To implement support for other authentication methods I would need to dig deeper and analyze the different protocols and unfortunately I don't have much time to dedicate to my libraries at the moment.

I will definitely add this to the to-do list for the near future.

1 Like
10 
mysql> SELECT User, Host, plugin 
    -> FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| granary_sensors  | %         | caching_sha2_password |
| granarypi        | %         | caching_sha2_password |
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
7 rows in set (0.00 sec)

mysql>

So I'm not using mysql_native_password for any users at the moment.

mysql> alter user 'granary_sensors'@'%' identified with mysql_native_password by 'password';
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

How do I get mysql_native_password loaded without it being used for all users?

I have tried starting up MySQL with the following:

mysqld --mysql-native-password=ON

But then I can't connect to it even using root.

11

Check if plugin is available

SHOW PLUGINS;

then you can load:

INSTALL PLUGIN mysql_native_password SONAME 'auth_socket.so';

To make it load automatically at startup you will also need to edit the configuration file

[mysqld]
plugin_load_add = auth_socket.so
1 Like
12 
mysql> SHOW PLUGINS;
+----------------------------------+----------+--------------------+---------+---------+
| Name                             | Status   | Type               | Library | License |
+----------------------------------+----------+--------------------+---------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| caching_sha2_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha2_cache_cleaner               | ACTIVE   | AUDIT              | NULL    | GPL     |
| daemon_keyring_proxy_plugin      | ACTIVE   | DAEMON             | NULL    | GPL     |
| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLES                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESTATS                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_INDEXES                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESPACES               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_COLUMNS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_VIRTUAL                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CACHED_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA               | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| TempTable                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                        | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndbcluster                       | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndbinfo                          | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
| ngram                            | ACTIVE   | FTPARSER           | NULL    | GPL     |
| mysqlx_cache_cleaner             | ACTIVE   | AUDIT              | NULL    | GPL     |
| mysqlx                           | ACTIVE   | DAEMON             | NULL    | GPL     |
| mysql_native_password            | DISABLED | AUTHENTICATION     | NULL    | GPL     |
+----------------------------------+----------+--------------------+---------+---------+
48 rows in set (0.00 sec)

Aha! There is is at the bottom, but showing as disabled.

mysql> INSTALL PLUGIN mysql_native_password SONAME 'auth_socket.so';
ERROR 1125 (HY000): Function 'mysql_native_password' already exists

Good news, I don't need to install it, just enable it somehow.

Thanks for the help so far!

13

I think you need to check the mysql configuration file.

1 Like
14

I think this is it (/etc/my.cnf)

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.4/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

host-cache-size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

Because I'm using CasaOS on the Pi, MySQL is running in a Docker container. I'm very new to CasaOS, Docker & containers. If I need to change it, I'm not yet sure how I do that. nano and vi are not available, for example.

15

According to this page, I should add

mysql_native_password=ON

to the [mysqld] section of the MySQL config file.

But when I tried adding

--mysql_native_password=ON

to the command line to start up MySQL, I was unable to connect with any user name including root. I assumed this was because it forces that password authentication to be used for all users. I don't want to do that, I only want to make it enabled so I can set it to be used for one particular user. I'm worried that putting this option in the config file will have the same effect.

@cotestatnt can you share your MySQL config file please, so I can see how that should be done?

16

Running a handful of devices in my home, consisting of a mix of ESP8266 and ESP32. They do SQL inserts and queries every few minutes into a standard MariaDB database. No particular modifications to the DB on server side, other than obviously allowing the DB users to log in from anything else but localhost. Server runs locally on a Linux box.

17

At the moment I can check only Windows server.

[mysqld]

# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory

# shared-memory-base-name=MYSQL

# The Pipe the MySQL Server will use.
# socket=MYSQL

# The access control granted to clients on the named pipe created by the MySQL Server.
# named-pipe-full-access-group=

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Path to installation directory. All paths are usually resolved relative to this.
# basedir="C:/Program Files/MySQL/MySQL Server 8.0/"

# Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
# character-set-server=


# Administers multifactor authentication (MFA) capabilities. It applies to the authentication
# factor-related clauses of CREATE USER and ALTER USER statements used to manage MySQL account
# definitions, where "factor" corresponds to an authentication method or plugin associated
# with an account.
authentication_policy=mysql_native_password,,

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
1 Like
18

Thanks @rsmls. As mentioned earlier, I have tried this library (when I was also using MariaDB) and it was the only one of the 3 that was able to connect and insert data, but I found that it only worked intermittently, with long periods where is failed to connect because of timeout errors. Later, it would randomly start working again for a few times and then revert to timeout errors for long periods.

During these periods of timeout errors, I was able to connect instantly from other PC/laptops connected via ethernet and/or wi-fi.

Any ideas what might cause this?

19

Nope, something funny seems to be going on in your case. Did a higher log level on this library yield any insight?

It's been running A-OK for me for years now, having handled thousands and thousands of queries without fail. The only time I ran into trouble was when one particular query became extremely slow to run because a table grew too large and I didn't have an appropriate index on the table. Added an index, restructured the query a bit and off it went again.

20

Only that 10 attempts were made each time and all failed or timed out.

I did find some timeout and retry parameters in that library. I tried changing them, but the changes did not take effect. For example I changed the retries limit to 5, but after re-uploading, it continued to make 10 attempts. Maybe I should try that again.