Problem to store values from Arduino MKR 1010 to mysql?

kenne76 · November 4, 2022, 4:00pm

I have problem to store values to MySQL table from Arduino MKR 1010 Wifi.
I get the following message.

trying
got: 0 retrying
trying
got: 0 retrying
ERROR!

I have also tried to turn off firewall without success.
Is my code wrong? Can someone help me with this issue?

#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <MySQL_Encrypt_Sha1.h>
#include <MySQL_Packet.h>
#include <DHT.h>
#include <DHT_U.h>
#include <dht.h>
#include <WiFiNINA.h>
#include <wifiinfo.h>

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

#define DHT22_PIN 7     
#define DHTTYPE 22
DHT deviceMonitor(DHT22_PIN,DHTTYPE) ;

IPAddress server_addr(192,168,86,39);  // IP of the MySQL *server* here
char user[] = "root";              // MySQL user login username
char password2[] = "test1234";        // MySQL user login password

char ssid[] = SECRET_SSID;        // your network SSID (name)
char password[] = SECRET_PASS;    // your network password (use for WPA, or use as key for WEP)
int status = WL_IDLE_STATUS;     // the Wifi radio's status

float hum; 
float temp;
float humError; 
float tempError;
int checkTemp;

char INSERT_DATA[] = "INSERT INTO weather (temp, hum) VALUES ('%s','%d')";
char query[128];
char temperature[10];

EthernetClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
  //Initialize serial and wait for port to open:
  Serial.begin(9600);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr);

  // attempt to connect to Wifi network:
  while (status != WL_CONNECTED) {
    Serial.print("Attempting to connect to network: ");
    Serial.println(ssid);
    // Connect to WPA/WPA2 network:
    status = WiFi.begin(ssid, password);

    // wait 10 seconds for connection:
    delay(10000);
  }

  // you're connected now, so print out the data:
  Serial.println("You're connected to the network");
  
  Serial.println("----------------------------------------");
  printData();
  Serial.println("----------------------------------------");

  if (conn.connect(server_addr, 3306, user, password)) 
  {
    delay(1000);
  }
  else
  {
     Serial.println("Mysql connection failed.");
  }
    }

void loop() 
{
  delay(5000);
  if (conn.connect(server_addr, 3306, user, password2)) {
 
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    checkTemp = deviceMonitor.read(DHT22_PIN);
    hum = deviceMonitor.readHumidity();
    temp = deviceMonitor.readTemperature();
    Serial.println((String)"Temperature: " + temp + " Celsius");
    Serial.println((String)"Humidity: " + hum +"%");
    Serial.println();
    sprintf(query, INSERT_DATA, temp, hum);
    cur_mem->execute(query);
    delete cur_mem;
    Serial.println("Data recorded.");
    delay(10000);
 }
 else
 {
   Serial.println("ERROR!");
 }
}

void printData() 
{
  Serial.println("Board Information:");
  // print your board's IP address:
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  Serial.println();
  Serial.println("Network Information:");
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print the received signal strength:
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.println(rssi);

  byte encryption = WiFi.encryptionType();
  Serial.print("Encryption Type:");
  Serial.println(encryption, HEX);
  Serial.println();
}

b707 · November 5, 2022, 7:24am

After connecting to MySQL you have to select database to work with. I don't see something similar in your code.

kenne76 · November 5, 2022, 8:01pm

I granted full right to the user root in MySQL and now I got this error instead.

    ERROR: Timeout waiting for client.
    Error: -1 = Mysql connection failed.

And I also have selected hopefully the database in the right way!

IPAddress server_addr(192,168,86,39);  
char database[] ="spaceinformation";
char user[] = "root";
char password2[] = "test1234";

  if (conn.connect(server_addr, 3306, database, user, password)) 
  {
    Serial.println("Mysql connection successful.");
    delay(1000);
  }
  else
  {
     Serial.println("Mysql connection failed.");
  }
    }

Can someone help me with this problem?

b707 · November 5, 2022, 8:35pm

Please show all your revised code

kenne76 · November 5, 2022, 9:03pm

Here is the "full" code.

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <MySQL_Encrypt_Sha1.h>
#include <MySQL_Packet.h>

#include <Ethernet.h>

#include <DHT.h>
#include <DHT_U.h>
#include <dht.h>
#include <WiFiNINA.h>
#include <WiFiClient.h>
#include <wifiinfo.h>

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

#define DHT22_PIN 7     
#define DHTTYPE 22
DHT deviceMonitor(DHT22_PIN,DHTTYPE) ;

IPAddress server_addr(192,168,86,39);  // IP of the MySQL *server* here
char database[] ="spaceinformation";
char user[] = "root";              // MySQL user login username
char password2[] = "test1234";        // MySQL user login password

char ssid[] = SECRET_SSID;        // your network SSID (name)
char password[] = SECRET_PASS;    // your network password (use for WPA, or use as key for WEP)
int status = WL_IDLE_STATUS;     // the Wifi radio's status

float hum; 
float temp;
float humError; 
float tempError;
int checkTemp;

char INSERT_DATA[] = "INSERT INTO weather (temp, hum) VALUES ('%s','%d')";
char query[128];
char temperature[10];

WiFiClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
  //Initialize serial and wait for port to open:
  Serial.begin(9600);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr);

  // attempt to connect to Wifi network:
  while (status != WL_CONNECTED) {
    Serial.print("Attempting to connect to network: ");
    Serial.println(ssid);
    // Connect to WPA/WPA2 network:
    status = WiFi.begin(ssid, password);

    // wait 10 seconds for connection:
    delay(10000);
  }

  // you're connected now, so print out the data:
  Serial.println("You're connected to the network");
  
  Serial.println("----------------------------------------");
  printData();
  Serial.println("----------------------------------------");

  if (conn.connect(server_addr, 3306, database, user, password)) 
  {
    Serial.println("Mysql connection successful.");
    delay(1000);
  }
  else
  {
     Serial.println("Mysql connection failed.");
  }
}

void loop() 
{
  delay(5000);
  if (conn.connect(server_addr, 3306, database, user, password2)) {
 
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    checkTemp = deviceMonitor.read(DHT22_PIN);
    hum = deviceMonitor.readHumidity();
    temp = deviceMonitor.readTemperature();
    Serial.println((String)"Temperature: " + temp + " Celsius");
    Serial.println((String)"Humidity: " + hum +"%");
    Serial.println();
    sprintf(query, INSERT_DATA, temp, hum);
    cur_mem->execute(query);
    delete cur_mem;
    Serial.println("Data recorded.");
    delay(10000);
 }
 else
 {
   Serial.println("ERROR!");
 }
}

void printData() 
{
  Serial.println("Board Information:");
  // print your board's IP address:
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  Serial.println();
  Serial.println("Network Information:");
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print the received signal strength:
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.println(rssi);

  byte encryption = WiFi.encryptionType();
  Serial.print("Encryption Type:");
  Serial.println(encryption, HEX);
  Serial.println();
}

b707 · November 5, 2022, 9:53pm

Could you show the output of your program?
When do you get the errors?

kenne76 · November 6, 2022, 6:35pm

I get error message after the Serial.println("Mysql connection failed."); row from
the code below.

 if (conn.connect(server_addr, 3306, database, user, password)) 
  {
    Serial.println("Mysql connection successful.");
    delay(1000);
  }
  else
  {
     Serial.println("Mysql connection failed.");
  }
}

The output is

...trying...
ERROR: Timeout waiting for client.
Error: -1 = Mysql connection failed.

Do you need more information?

b707 · November 6, 2022, 6:41pm

Did you try to connect to Mysql with same user, password and database manually, via telnet on port 3306?

Also
Did your board connects to the WiFi successfully?
Did you get the message:

wildbill · November 6, 2022, 7:13pm

What do your server logs say?

Also, by default, MySQL does not accept connections from external devices, you have to configure it to allow them.

Marvado · March 26, 2023, 12:51pm

A little late perhaps, but you got a typing error.

You request to open the connection with the wifi password. You should use password2

Anyways, it doesn't help

system · September 22, 2023, 12:52pm

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Unable to connect to mySQL database with MKR1010 over wifi Programming	11	3376	May 6, 2021
[Connection Problem] Connecting to MySQL database remotely through WiFi MKR 1000 WiFi	2	3700	May 7, 2021
Can't get MKR1010 WIFI to add data to MySQL database. Home Automation	1	863	May 6, 2021
Wireless Sensor network with Arduino and MySQL server General Guidance	6	2255	May 5, 2021
Arduino uno wifi rev2, raspberry and mysql Programming	6	1740	May 5, 2021

Problem to store values from Arduino MKR 1010 to mysql?

Related topics