WIFi Arduino To SQL Database Error

jamie98 · January 14, 2019, 2:18pm

Hi, currently using XAMPP to host a local database, accessed through 3 Arduino's, 2 of which are Uno WiFI V2 and one is Uno Ethernet. The Ethernet Arduino connects to the database with no issues and enters data to the database, though the WiFi version will not connect to the database itself.

/*
MySQL Connector/Arduino Example : connect by wifi using WiFi 101 shield

This example demonstrates how to connect to a MySQL server from an
Arduino using using the new WiFi Shield 101 from arduino.cc.

NOTICE NOTICE NOTICE

The new WiFi 101 library is quite large. You should use this sketch and the
shield with the new Arduino Due or Zero.

You should also use the latest Arduino IDE from arduino.cc. This sketch was
tested with release 1.6.7 from https://www.arduino.cc/en/Main/Software
running on a Due board.

Also, make sure your hardware libraries are uptodate by visiting the
boards manager and installing updates for the boards you are tying to use
(e.g. Due, Zero).

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.

Note: The MAC address can be anything so long as it is unique on your network.

Created by: Dr. Charles A. Bell
*/
#include <SPI.h>
#include <WiFiNINA.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(192,168,1,3); // IP of the MySQL server here
char user[] = "root"; // MySQL user login username
char password[] = "pass"; // MySQL user login password
int keyIndex = 0;
// WiFi card example
char ssid[] = ""; // your SSID
char pass[] = ""; // your SSID Password
int status = WL_IDLE_STATUS;
WiFiServer server(80);
WiFiClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
delay(4000);
// Begin WiFi section
int status = WiFi.begin(ssid, pass);
if ( status != WL_CONNECTED) {
Serial.println("Couldn't get a wifi connection");
while(true);
}
// print out info about the connection:
else {
Serial.println("Connected to network");
IPAddress ip = WiFi.localIP();
Serial.print("My IP address is: ");
Serial.println(ip);
}
// End WiFi section

Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(10000);
}
else
Serial.println("Connection failed.");

}

void loop() {
}

The serial monitor shows that it connects to the WiFi network but will go no further, after trying to connect to the database, after a while it displays connection failed message.

Note this is code by Dr. Charles A. Bell and isn't my own but edited, any help or advice would be appreciated, as we wish to not use PHP, even though we understand the memory constraints on arduino

wildbill · January 14, 2019, 2:29pm

See if you can get a web page (Hello world or whatever) from the server to prove out your wifi connectivity.

I haven't played with it for a while, but IIRC, I had an unpleasant surprise when trying to connect to a MySQL database from any machine other than the machine it runs on.

I assumed that like Oracle or SQL server, I could connect from anywhere using userid and password but with MySQL this proved not to be the case. It seems a silly restriction and so perhaps it was user error, but I'm fairly sure I had to set something additional up server side to allow other machines to connect to the database.

I also recall that I had to do it on a per machine basis, but that has to be rubbish - right? Right?

jamie98 · January 14, 2019, 2:41pm

Already tried that, had a web page open with text on through WiFi to control LED's previously.

wildbill · January 14, 2019, 2:57pm

This is what I was thinking of:

By default, MySQL is listening only to local connections (127.0.0.1).

But since you can connect from the Mega, that's not your problem.

wildbill · January 14, 2019, 3:00pm

What does the successful Mega code look like?

jamie98 · January 14, 2019, 3:07pm

Not using a Mega, using a Uno with an Ethernet shield, we then changed it for Uno WiFi V2.

Here is the ethernet code -

/*
  MySQL Connector/Arduino Example : complex insert

  This example demonstrates how to issue an INSERT query to store data in a
  table using data from variables in our sketch. In this case, we supply the
  values dynamically.

  This sketch simulates storing data from a sensor in a table.

  For this, we will create a special database and table for testing.
  The following are the SQL commands you will need to run in order to setup
  your database for running this sketch.

  CREATE DATABASE test_arduino;
  CREATE TABLE test_arduino.hello_sensor (
    num integer primary key auto_increment,
    message char(40),
    sensor_num integer,
    value float,
    recorded timestamp
  );

  Here we have a table that contains an auto_increment primary key, a text
  field, a field to identify the sensor, the value read, and timestamp of
  the recorded data.

  Note: Since this sketch uses test data, we place the INSERT in the setup()
        method so that it runs only once. Typically, you would have the
        INSERT in the loop() method after your code to read from the sensor.

  INSTRUCTIONS FOR USE

  1) Create the database and table as shown above.
  2) Change the address of the server to the IP address of the MySQL server
  3) Change the user and password to a valid MySQL user and password
  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
  8) After the sketch has run for some time, open a mysql client and issue
     the command: "SELECT * FROM test_arduino.hello_sensor" to see the data
     recorded. Note the field values and how the database handles both the
     auto_increment and timestamp fields for us. You can clear the data with
     "DELETE FROM test_arduino.hello_sensor".

  Note: The MAC address can be anything so long as it is unique on your network.

  Created by: Dr. Charles A. Bell
*/
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <DHT.h>
#include <SPI.h> 
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(192,168,1,3);  // IP of the MySQL *server* here
char user[] = "root";              // MySQL user login username
char password[] = "pass";        // MySQL user login password
#define DHTPIN 7     // what pin we're connected to
#define DHTTYPE DHT22   // DHT 22  (AM2302)
DHT dht(DHTPIN, DHTTYPE); //// Initialize DHT sensor for normal 16mhz Arduino

// Sample query
char INSERT_DATA[] = "INSERT INTO test_arduino.hello_sensor (message, sensor_num, value, value2) VALUES ('%s',%d,%s,%s)";
char query[128];
char temperature[10];
char humidity[10];
int chk;
float hum;  //Stores humidity value
float temp; //Stores temperature value 
EthernetClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
  dht.begin(); 
  Serial.begin(115200);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr);

}


void loop() {
  Serial.println("Connecting...");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
        hum = dht.readHumidity();
    temp= dht.readTemperature();
    //Print temp and humidity values to serial monitor
    Serial.print("Humidity: ");
    Serial.print(hum);
    Serial.print(" %, Temp: ");
    Serial.print(temp);
    Serial.println(" Celsius");
    // Initiate the query class instance
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    // Save
    dtostrf(temp, 1, 1, temperature);
    dtostrf(hum, 1, 1, humidity);
    sprintf(query, INSERT_DATA, "Temp Sensor", 1, temperature, humidity);
    
    // Execute the query
    cur_mem->execute(query);
    // Note: since there are no results, we do not need to read any data
    // Deleting the cursor also frees up memory used
    delete cur_mem;
    Serial.println("Data recorded.");
  }
  else
    Serial.println("Connection failed.");
  conn.close();
 delay(2000);                




  
}

wildbill · January 14, 2019, 3:24pm

You've ignored this:

Note: The MAC address can be anything so long as it is unique on your network.

You're trying to use the same MAC address for both. Probably for all three.

jamie98 · January 14, 2019, 3:29pm

Tried changing MAC address, no success, though only one was active at a time.
Could it be an issue with the server? with it being over WiFi instead of ethernet.

wildbill · January 14, 2019, 3:57pm

Server shouldn't care. By the time traffic gets to there (which you've already established is working) the packets are showing up over ethernet anyway (I expect).

What do you see in mysql.log or mysql.err?

How did you configure the mysql instance to accept connections from other than localhost

What are the IP addresses of the hardwired uno and the wifi one?

Topic		Replies	Views
Arduino uno wifi rev2 : failed connection to mysql UNO WiFi Rev2	11	995	March 18, 2024
Problem using WiFi Arduino To MySQL Database Networking, Protocols, and Devices	1	377	May 6, 2021
arduino and mysql connection Networking, Protocols, and Devices	2	1747	May 6, 2021
Arduino Uno Wifi and SQL General Guidance	11	8401	May 5, 2021
WiFi Arduino To MySQL Database using Wamp Programming	2	2251	May 5, 2021

WIFi Arduino To SQL Database Error

Related topics