Arduino unable to connect to database #237

PaulRB · 2024-09-01T15:45:33Z

As recommended in the wiki documentation, I tested that I can connect to the database from a different system:

paul@paul-desktop:~$ sudo mysql -h192.168.1.201 -ugranary_sensors -ppassword  --port=3306
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 122
Server version: 10.11.5-MariaDB-log Alpine Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> select * from granary_sensors.granary_sensors
    -> ;
ERROR 1142 (42000): SELECT command denied to user 'granary_sensors'@'paul-desktop.lan' for table `granary_sensors`.`granary_sensors`
MariaDB [(none)]> insert into granary_sensors.granary_sensors (sensor_name, sensor_value) values ('tt', -999);
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> exit
Bye

The above is as expected. I have granted permission to the user granary_sensors to insert records into only that one table, and nothing else. That is why the select command fails but the insert command succeeds. So far, great.

Next, I took the example sketch from this library, amended it with my desired details and uploaded it to a Wemos D1 Mini Pro (an ESP8266 board):

/*
  MySQL Connector/Arduino Example : connect by wifi

  This example demonstrates how to connect to a MySQL server from an
  Arduino using an Arduino-compatible Wifi shield. Note that "compatible"
  means it must conform to the Ethernet class library or be a derivative
  with the same classes and methods.
  
  For more information and documentation, visit the wiki:
  https://github.com/ChuckBell/MySQL_Connector_Arduino/wiki.

  INSTRUCTIONS FOR USE

  1) Change the address of the server to the IP address of the MySQL server
  2) Change the user and password to a valid MySQL user and password
  3) Change the SSID and pass to match your WiFi network
  4) Connect a USB cable to your Arduino
  5) Select the correct board and port
  6) Compile and upload the sketch to your Arduino
  7) Once uploaded, open Serial Monitor (use 115200 speed) and observe

  If you do not see messages indicating you have a connection, refer to the
  manual for troubleshooting tips. The most common issues are the server is
  not accessible from the network or the user name and password is incorrect.

  Created by: Dr. Charles A. Bell
*/
#include <ESP8266WiFi.h>           // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

IPAddress server_addr(192,168,1,201);  // IP of the MySQL *server* here
char user[] = "granary_sensors";              // MySQL user login username
char password[] = "password";        // MySQL user login password

// Sample query
char INSERT_SQL[] = "INSERT INTO granary_sensors.granary_sensors (sensor_name, sensor_value) VALUES ('TT', -999)";

// WiFi card example
char ssid[] = "granary";         // your SSID
char pass[] = "password";     // your SSID Password

WiFiClient client;                 // Use this for WiFi instead of EthernetClient
MySQL_Connection conn(&client);
MySQL_Cursor* cursor;

void setup()
{
  Serial.begin(115200);
  while (!Serial); // wait for serial port to connect. Needed for Leonardo only

  // Begin WiFi section
  Serial.printf("\nConnecting to %s", ssid);
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  // print out info about the connection:
  Serial.println("\nConnected to network");
  Serial.print("My IP address is: ");
  Serial.println(WiFi.localIP());

  Serial.print("Connecting to SQL...  ");
  if (conn.connect(server_addr, 3306, user, password))
    Serial.println("OK.");
  else
    Serial.println("FAILED.");
  
  // create MySQL cursor object
  cursor = new MySQL_Cursor(&conn);
}

void loop()
{
  if (conn.connected())
    cursor->execute(INSERT_SQL);

  delay(5000);
}

I get the following error in Serial Monitor:

14:34:55.908 -> ..............
14:35:03.128 -> Connected to network
14:35:03.128 -> My IP address is: 192.168.1.76
14:35:03.128 -> Connecting to SQL...  ...trying...
14:35:03.460 -> Error: 88 = Access denied for user 'granary_sensors'@'ESP-D091A2.lan' (using password: YES).
14:35:03.460 -> FAILED.

Can anyone please help me spot my error, or suggest next steps to diagnose?

Arduino IDE: 1.8.19
MySQL_Connector_Arduino library version installed: 1.2.0
ESP8266 board core version: 3.1.2

Permissions for the granary_sensors user:

paul@paul-desktop:~$ mysql -uroot -ppassword -h192.168.1.201 --port=3306
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 150
Server version: 10.11.5-MariaDB-log Alpine Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> SHOW GRANTS FOR granary_sensors;
+----------------------------------------------------------------------------------------------------------------+
| Grants for granary_sensors@%                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `granary_sensors`@`%` IDENTIFIED BY PASSWORD '*733B51F92EB06BE45A3761D5B53B881709A82C03' |
| GRANT INSERT ON `granary_sensors`.`granary_sensors` TO `granary_sensors`@`%` WITH GRANT OPTION                 |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

The text was updated successfully, but these errors were encountered:

ChuckBell · 2024-09-06T21:14:34Z

Hi, A few things to check… First, make sure your user account is set to use mysql_native_password. Otherwise, the connector will not connect. Second, MySQL user accounts include the user + host. If you grant access to me@<IP ADDRESS> and that address doesn’t match the host from where you’re connecting (your Arduino board), you will not be able to connect. There is a tip on this in the Wiki. Hint: use the wildcard. Finally, while unlikely the cause, this connector is not guaranteed to work with non-Oracle distributions of MySQL. You may want to test with an Oracle distro.. Dr. Bell

…

On Sep 1, 2024, at 11:45 AM, Paul ***@***.***> wrote: As recommended in the wiki documentation, I tested that I can connect to the database from a different system: ***@***.***:~$ sudo mysql -h192.168.1.201 -ugranary_sensors -ppassword --port=3306 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 122 Server version: 10.11.5-MariaDB-log Alpine Linux Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> select * from granary_sensors.granary_sensors -> ; ERROR 1142 (42000): SELECT command denied to user 'granary_sensors'@'paul-desktop.lan' for table granary_sensors.granary_sensors MariaDB [(none)]> insert into granary_sensors.granary_sensors (sensor_name, sensor_value) values ('tt', -999); Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> exit Bye ` The above is as expected. I have granted permission to the user granary_sensors to insert records into only that one table, and nothing else. That is why the select command fails but the insert command succeeds. So far, great. Next, I took the example sketch from this library, amended it with my desired details and uploaded it to a Wemos D1 Mini Pro (an ESP8266 board): `/* MySQL Connector/Arduino Example : connect by wifi This example demonstrates how to connect to a MySQL server from an Arduino using an Arduino-compatible Wifi shield. Note that "compatible" means it must conform to the Ethernet class library or be a derivative with the same classes and methods. For more information and documentation, visit the wiki: https://github.com/ChuckBell/MySQL_Connector_Arduino/wiki. INSTRUCTIONS FOR USE Change the address of the server to the IP address of the MySQL server Change the user and password to a valid MySQL user and password Change the SSID and pass to match your WiFi network Connect a USB cable to your Arduino Select the correct board and port Compile and upload the sketch to your Arduino Once uploaded, open Serial Monitor (use 115200 speed) and observe If you do not see messages indicating you have a connection, refer to the manual for troubleshooting tips. The most common issues are the server is not accessible from the network or the user name and password is incorrect. Created by: Dr. Charles A. Bell */ #include <ESP8266WiFi.h> // Use this for WiFi instead of Ethernet.h #include <MySQL_Connection.h> #include <MySQL_Cursor.h> IPAddress server_addr(192,168,1,201); // IP of the MySQL server here char user[] = "granary_sensors"; // MySQL user login username char password[] = "password"; // MySQL user login password // Sample query char INSERT_SQL[] = "INSERT INTO granary_sensors.granary_sensors (sensor_name, sensor_value) VALUES ('TT', -999)"; // WiFi card example char ssid[] = "granary"; // your SSID char pass[] = "password"; // your SSID Password WiFiClient client; // Use this for WiFi instead of EthernetClient MySQL_Connection conn(&client); MySQL_Cursor* cursor; void setup() { Serial.begin(115200); while (!Serial); // wait for serial port to connect. Needed for Leonardo only // Begin WiFi section Serial.printf("\nConnecting to %s", ssid); WiFi.begin(ssid, pass); while (WiFi.status() != WL_CONNECTED) { delay(500); Serial.print("."); } // print out info about the connection: Serial.println("\nConnected to network"); Serial.print("My IP address is: "); Serial.println(WiFi.localIP()); Serial.print("Connecting to SQL... "); if (conn.connect(server_addr, 3306, user, password)) Serial.println("OK."); else Serial.println("FAILED."); // create MySQL cursor object cursor = new MySQL_Cursor(&conn); } void loop() { if (conn.connected()) cursor->execute(INSERT_SQL); delay(5000); }` I get the following error in Serial Monitor: 14:34:55.908 -> .............. 14:35:03.128 -> Connected to network 14:35:03.128 -> My IP address is: 192.168.1.76 14:35:03.128 -> Connecting to SQL... ...trying... 14:35:03.460 -> Error: 88 = Access denied for user 'granary_sensors'@'ESP-D091A2.lan' (using password: YES). 14:35:03.460 -> FAILED. Can you please help me spot my error, or suggest next steps to diagnose? Arduino IDE: 1.8.19 MySQL_Connector_Arduino library version installed: 1.2.0 ESP8266 board core version: 3.1.2 — Reply to this email directly, view it on GitHub <#237>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB6SHYFGE22WGEFZ37ZVWQDZUMZDHAVCNFSM6AAAAABNO6HBKOVHI2DSMVQWIX3LMV43ASLTON2WKOZSGQ4TSNJUHA2DENY>. You are receiving this because you are subscribed to this thread.

PaulRB · 2024-09-07T16:17:44Z

Thanks for responding, Dr Bell!

I'm not sure if I am using mysql_native_password or not. I understand that is the default password method with MySQL. However, I am actually using MariaDB. I understand this is supposed to be a more-or-less "drop-in" replacement for MySQL, but I don't know if the password encryption method is the same. I will research that and also consider switching to MySQL.

Do you know if this library has been tested with MariaDB?

I am using the '%' wildcard rather than an IP address or hostname when granting access permissions to the user. You can see this in my previous post, and the fact that I can connect from a couple of different addresses (via WiFi and Ethernet) seems to show that the wildcard is working as expected.

If I do install MySQL in place of MariaDB, i'm not sure it will be an Oracle distribution or not. I will probably use a ready-made Docker app/container. I will try to find out if it is based on an Oracle distribution.

PaulRB · 2024-09-08T10:39:58Z

Hi again,

To remove any doubts about compatibility of the library with MariaDB, I have uninstalled it from the server, installed MySQL and re-created the database, tables, users etc. as before.

For information, the server in this case is a Raspberry Pi 4, with an SSD connected by USB 3 and with Raspberry Pi OS and CasaOS installed. I then installed MySQL Server Docker image from the Docker repo, using the "Custom Install" function in CasaOS (because the CasaOS app store has only MariaDB and not MySQL available).

Then I tested that I can connect and insert data from other clients on my LAN (a desktop and a laptop both running Ubuntu 22.04):

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 60
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.01 sec)

mysql> exit
Bye

Lots of mentions of Oracle there, hopefully this refers to the MySQL Server rather than the MySQL Client I installed on the desktop.

Anyway, that seems to work well. There is no noticeable delay in connecting, from a client connected to my LAN via either Ethernet or WiFi.

Back to the Arduino...

Using the Arduino code I posted previously, I get:

10:47:49.987 -> .......
10:47:53.729 -> Connected to network
10:47:53.729 -> My IP address is: 192.168.1.146
10:47:53.729 -> Connecting to SQL...  ...trying...
10:48:05.720 -> ERROR: Timeout waiting for client.
10:48:05.720 -> Error: -1 = FAILED.

Which feels like a step backward!

The "ERROR: Timeout" appears after around 3s.

I found the 3000ms timeout in MySQL_Packet.cpp and increased it to 10000ms. I then uploaded to the Arduino again and got same error message, this time after about 10s.

PaulRB · 2024-09-08T10:43:27Z

Checking the grants for the user:

paul@paul-desktop:~$ mysql -uroot -p -h192.168.1.201 --port=3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
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> show grants for 'granary_sensors'@'%';
+------------------------------------------------------------------------------+
| Grants for granary_sensors@%                                                 |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `granary_sensors`@`%`                                  |
| GRANT INSERT ON `granary_sensors`.`granary_sensors` TO `granary_sensors`@`%` |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

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

Aha! I will try to figure out how to fix that and report back.

PaulRB · 2024-09-08T13:27:01Z

I'm not having much success enabling mysql_native_password.

I tried starting MySQL with the command
mysqld --mysql-native-password=ON
but then I can't connect to it either with Adminer or MySQL Client.

I tried to get MySQL Client to use mysql_native_password like this:

paul@paul-desktop:~$ mysql -uroot -p -h192.168.1.201 --port=3306 --default-auth=mysql_native_password
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.201:3306' (111)

The Arduino now shows these errors:

14:26:10.096 -> Connected to network
14:26:10.096 -> My IP address is: 192.168.1.146
14:26:10.096 -> Connecting to SQL...  ...trying...
14:26:10.096 -> ...got: 0 retrying...
14:26:10.593 -> ...trying...
14:26:10.593 -> ...got: 0 retrying...
14:26:11.089 -> ...trying...
14:26:11.089 -> ...got: 0 retrying...
14:26:11.586 -> FAILED.

I also came across this warning on this page:

Note
The mysql_native_password authentication plugin is deprecated and subject to removal in a future version of MySQL.

which makes me think even if I can get this working, if I upgrade MySQL in future, it might stop working again.

ChuckBell · 2024-09-17T15:14:07Z

Hi, The mysql_native_password is set per user with CREATE USER or ALTER USER. You must also install the plugin and enable it on the server. See this link <https://dev.mysql.com/doc/refman/8.4/en/native-pluggable-authentication.html#native-pluggable-authentication-installation>. CREATE USER 'wei'@'localhost' IDENTIFIED BY ‘password' IDENTIFIED WITH mysql_native_password; Dr. Bell

…

On Sep 8, 2024, at 9:27 AM, Paul ***@***.***> wrote: I'm not having much success enabling mysql_native_password. I tried starting MySQL with the command mysqld --mysql-native-password=ON but then I can't connect to it either with Adminer or MySQL Client. I tried to get MySQL Client to use mysql_native_password like this: ***@***.***:~$ mysql -uroot -p -h192.168.1.201 --port=3306 --default-auth=mysql_native_password Enter password: ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.201:3306' (111) The Arduino now shows these errors: 14:26:10.096 -> Connected to network 14:26:10.096 -> My IP address is: 192.168.1.146 14:26:10.096 -> Connecting to SQL... ...trying... 14:26:10.096 -> ...got: 0 retrying... 14:26:10.593 -> ...trying... 14:26:10.593 -> ...got: 0 retrying... 14:26:11.089 -> ...trying... 14:26:11.089 -> ...got: 0 retrying... 14:26:11.586 -> FAILED. I also came across this warning on this page: <https://dev.mysql.com/doc/refman/8.4/en/native-pluggable-authentication.html#native-pluggable-authentication-installation> Note The mysql_native_password authentication plugin is deprecated and subject to removal in a future version of MySQL. which makes me think even if I can get this working, if I upgrade MySQL in future, it might stop working again. — Reply to this email directly, view it on GitHub <#237 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB6SHYBXBJIG23CSWA2JG6DZVRGDVAVCNFSM6AAAAABNO6HBKOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMZWGY4DMNBXGY>. You are receiving this because you commented.

PaulRB · 2024-09-17T18:30:23Z

Hi, and thanks for responding again.

I have found that the plugin is installed by default in MySQL 8.4, but disabled, so can't be used for use with ALTER USER etc. I've not been able to figure out how to enable it. I found various commands like options and configuration file options, but when I try any of them, I can no longer connect to the database as any user, not even root. My theory at the moment is that these options/configurations set it to be the required authentication for all users. But as I can no longer connect to the database from other PC/laptop using MySQL client or Adminer, I can't confirm or rule that out.

Have you tested your library by connecting it to MariaDB instead of MySQL? MariaDB has the mysql_native_password plugin enabled by default.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Arduino unable to connect to database #237

Arduino unable to connect to database #237

PaulRB commented Sep 1, 2024 •

edited

Loading

ChuckBell commented Sep 6, 2024 via email

PaulRB commented Sep 7, 2024

PaulRB commented Sep 8, 2024

PaulRB commented Sep 8, 2024

PaulRB commented Sep 8, 2024

ChuckBell commented Sep 17, 2024 via email

PaulRB commented Sep 17, 2024

Arduino unable to connect to database #237

Arduino unable to connect to database #237

Comments

PaulRB commented Sep 1, 2024 • edited Loading

ChuckBell commented Sep 6, 2024 via email

PaulRB commented Sep 7, 2024

PaulRB commented Sep 8, 2024

PaulRB commented Sep 8, 2024

PaulRB commented Sep 8, 2024

ChuckBell commented Sep 17, 2024 via email

PaulRB commented Sep 17, 2024

PaulRB commented Sep 1, 2024 •

edited

Loading