0

I am using the below mysql example sketch to upload data directly to mysql db.

#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "mysql.h"

/* Setup for Ethernet Library */
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
// Set the static IP address to use if the DHCP fails to assign
IPAddress ip(192, 168, 1, 177);
IPAddress server_addr(192, 168, 1, 3);
//char server_addr[] = "192.168.1.3"; 

/* Setup for the Connector/Arduino */
Connector my_conn; // The Connector/Arduino reference

char user[] = "root";
char password[] = "secret";
char INSERT_SQL[] = 
 "INSERT INTO test_arduino.hello VALUES ('Hello, MySQL!', NULL)";

void setup() {
  Ethernet.begin(mac_addr, ip);
  Serial.begin(9600);
  delay(1000);
  Serial.println("Connecting...");
  if (my_conn.mysql_connect(server_addr, 3306, user, password))
  {
    delay(500);
    Serial.println("Connection Successfull,inserting to database.");
     /* Write Hello, World to MySQL table test_arduino.hello */
     my_conn.cmd_query(INSERT_SQL);
     Serial.println("Query Success!"); 
  } 
  else
    Serial.println("Connection failed.");
}

void loop() {
}

I am getting the error in serial Monitor:

Connecting...

Error: 76 = Access denied for user 'root'@'192.168.1.177' (using password: YES).

Connection failed.

even when I have all prvialages on root user for the ip of client !

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.177';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host FROM mysql.user WHERE User = 'root';
+---------------+
| host          |
+---------------+
| 127.0.0.1     |
| 192.168.1.177 |
| ::1           |
| localhost     |
| raspberrypi   |
+---------------+
5 rows in set (0.00 sec)

I ran the following query :

mysql> SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip,  @@hostname as hostname, @@port as port, DATABASE() as current_database;

and got the below result !

+-----------+----------+------+------------------+
| ip        | hostname | port | current_database |
+-----------+----------+------+------------------+
| localhost | ceres    | 3306 | test_arduino     |
+-----------+----------+------+------------------+
1 row in set (0.00 sec)

however I am trying to access the test_arduino database from a different IP other than the one mentioned above, does that mean where it says localhost, it is supposed to have 192.168.1.177 as well ? if so How do I insert ip to database test_arduino ?

then what am I missing ? please help.

  • Did you do flush privileges; after grant? – Chupo_cro Dec 17 '16 at 12:17
  • Yes off course , I did flush permissions. – Ciasto piekarz Dec 17 '16 at 12:24
  • Try to investigate if this is the case. – Chupo_cro Dec 17 '16 at 12:31
  • How do I check on stored procedures ? I also later grant on 'root'@'%' which shows up in list of host shown above. But I m not sql guy don't know how to get rid of host from above list like raspberrypi host is not even there, I just realised ceres host is missing – Ciasto piekarz Dec 17 '16 at 12:37
  • For that you can find notes here and here. – Chupo_cro Dec 17 '16 at 12:44
1

I finally figured out

By default it may proabbly have an IP address 127.0.0.1 bind to the 3306 port . This won't let your Arduino Ethernet shield connect to mysql server running on a different machine. In my case, I have mysql server running on raspberry pi 3, and it has IP 192.168.1.145. So for arduino to push directly to databse on remote mysql server, you should have mysql server IP 192.168.145 bind to 3306 port of mysql server running on it. So that MySQL server is visible to remote client on local network on which your arduino Ethernet shield is connected to via LAN. Then you login to mysql server , and create a user :

CREATE USER 'myuser'@'192.168.156' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

then you grant privileges as shown below.

GRANT ALL ON *.* TO 'myuser'@'192.168.156';
GRANT ALL ON *.* TO 'myuser'@'%';

Important Note: the ip address shown in above mysql queries 192.168.156 is the ip address of my ethernet client establishing connection with database on mysql remote server.

Thats it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.