Problem to store values from Arduino MKR 1010 to mysql?

Open EK76 opened this issue 3 years ago • 1 comments

When I try to connect to a MySQL database then I get the following error message.

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

I have granted full right to the user root in MySQL with native password. And I also have selected hopefully the database in the right way! I a'm using the Arduino MKR 1010 card with this "project".

Can someone help me? Here is the complete 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);
   Serial.begin(115200);
   Serial.setTimeout(5000);
  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, password2)) 
  {
    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();
}

Nov 06 '22 18:11 EK76

The first thing I noticed is you’re using the connect() function incorrectly. The database parameter is in the wrong place.

Other than that, please refer to the troubleshoot strategies in the Wiki for how to diagnose connection problems.

On Nov 6, 2022, at 1:28 PM, EK76 @.***> wrote:

When I try to connect to a MySQL database then I get the following error message.
ERROR: Timeout waiting for client.
Error: -1 = Mysql connection failed.
I have granted full right to the user root in MySQL with native password. And I also have selected hopefully the database in the right way! I a'm using the Arduino MKR 1010 card with this "project".

Can someone help me? Here is the complete 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); Serial.begin(115200); Serial.setTimeout(5000); 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, password2)) { 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(); }

— Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/196, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYGXRRXEX4AUK24TSYTWG72EJANCNFSM6AAAAAARYRLQ54. You are receiving this because you are subscribed to this thread.

Nov 06 '22 19:11 ChuckBell