Receiving "ERROR: Timeout waiting for client." when connecting to a single table in database - mysql connector on Arduino Uno Wifi Rev2

Question

I have a MariaDb database on a local server and I am using it to store data from various Arduino's around my shop. The database currently has 12 tables. I also have another Arduino Uno Wifi Rev2 that is able to connect to its two respective tables; it collects set point information from one table and inserts data perfectly into the other.

The Arduino I'm having a problem with can insert data perfectly fine into its data table (veg2_data) but fails when trying to select data from it's sp table (veg2_sp). It can also connect to any of the other tables in my database, including the one for the first Arduino. Only veg2_sp is malfunctioning.

Here is my code:

#include <SPI.h>
#include <WiFiNINA.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include "config.h"
//Node Libraries
#include <DHT.h>
#define O_UPPER 9
#define O_LOWER 10
//Wifi
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; //Do I need this?
char ssid[] = CONFIG_SSID;
char pass[] = CONFIG_PASS;
int status = WL_IDLE_STATUS;
int keyIndex = 0;
WiFiClient client;
//MySQL
IPAddress server_addr(192, 168, 1, 133);
char user[] = CONFIG_MYSQL_USER;
char password[] = CONFIG_MYSQL_PASS;
char db[] = CONFIG_MYSQL_DB;
char query[128];
char DATA_QUERY[] = "INSERT INTO veg2_data VALUES (now(), %d, %d, %s, %s, %s, %s, %s, %s, %d);";
char SP_QUERY[] = "SELECT * FROM veg2_sp ORDER BY timestamp DESC LIMIT 1;";
char rawTime[30];
byte refreshRate = 1;
uint32_t dbContactTimer;
MySQL_Connection conn((Client *)&client);
MySQL_Cursor cur = MySQL_Cursor(&conn);
//Node Specific
bool o_UpperState, o_LowerState, daytime = 0;
byte dayTempSP = 25, dayHumiditySP = 30, nightTempSP = 10, nightHumiditySP = 20,
 o_UpperLock = 0, o_LowerLock = 0, sunrise = 7, sunset = 19;
int soilMoisture;
float intakeTemp, intakeHum, roomTemp, roomHum, exhaustTemp, exhaustHum ;
char intakeTempArray[7], intakeHumArray[7], roomTempArray[7], roomHumArray[7],
 exhaustTempArray[7], exhaustHumArray[7];
DHT exhaust(6, DHT11);
DHT intake(7, DHT11);
DHT room(8, DHT11);
void setup() {
 Serial.begin(115200);
 wifiInit();
 exhaust.begin();
 intake.begin();
 room.begin();
 pinMode(O_UPPER, OUTPUT);
 pinMode(O_LOWER, OUTPUT);
 digitalWrite(O_UPPER, LOW);
 digitalWrite(O_LOWER, LOW);
 //spCheck();
 dbContactTimer = millis();
}
void loop() {
 //Default refesh rate is 5 seconds. Some nodes need to increase refesh rate at specific times
 if (millis() - dbContactTimer >= (refreshRate * 1000)) {
 dbUpdate();
 spCheck();
 //If set to auto (2), my lights will turn on and off depending on day/night. Currently unfinished
 if (o_UpperLock == 2) {
 if (daytime) {
 digitalWrite(O_UPPER, HIGH);
 o_UpperState = 1;
 }
 else {
 digitalWrite(O_UPPER, LOW);
 o_UpperState = 0;
 }
 }
 dbContactTimer = millis();
 }
}
//------------------------------------MySql Functions-------------------------
void spCheck() { //Updates setpoints
 if (!conn.connected()) {
 conn.close();
 if (!conn.connect(server_addr, port, user, password, db)) {
 return 0;
 }
 }
 row_values *row = NULL;
 cur.execute(SP_QUERY);
 column_names *columns = cur.get_columns();
 do {
 row = cur.get_next_row();
 if (row != NULL) {
 //Serial.println(row->values[0]);
 o_UpperLock = atol(row->values[1]);
 //o_LowerLock = atol(row->values[2]);
 //dayTempSP = atol(row->values[3]);
 //dayHumiditySP = atol(row->values[4]);
 //nightTempSP = atol(row->values[5]);
 //nightHumiditySP = atol(row->values[6]);
 //sunrise = atol(row->values[7]);
 //sunset = atol(row->values[8]);
 }
 } while (row != NULL);
 if (o_UpperLock < 2) {
 if (o_UpperLock) {
 digitalWrite(O_UPPER, HIGH);
 o_UpperState = 1;
 }
 else {
 digitalWrite(O_UPPER, LOW);
 o_UpperState = 0;
 }
 }
 return 1;
}
void dbUpdate() { //Updates data
 if (!conn.connected()) {
 conn.close();
 if (!conn.connect(server_addr, port, user, password, db)) {
 return 0;
 }
 }
 
 intakeTemp = intake.readTemperature();
 dtostrf(intakeTemp, 4, 2, intakeTempArray);
 
 intakeHum = intake.readHumidity();
 dtostrf(intakeHum, 4, 2, intakeHumArray);
 
 roomTemp = room.readTemperature();
 dtostrf(roomTemp, 4, 2, roomTempArray);
 
 roomHum = room.readHumidity();
 dtostrf(roomHum, 4, 2, roomHumArray);
 
 exhaustTemp = exhaust.readTemperature();
 dtostrf(exhaustTemp, 4, 2, exhaustTempArray);
 
 exhaustHum = exhaust.readHumidity();
 dtostrf(exhaustHum, 4, 2, exhaustHumArray);
 
 soilMoisture = analogRead(A0);
 sprintf(query, DATA_QUERY, o_UpperState, o_LowerState, intakeTempArray,
 intakeHumArray, roomTempArray, roomHumArray, exhaustTempArray, exhaustHumArray, soilMoisture);
 cur.execute(query);
 return 1;
}
void wifiInit() {
 String fv = WiFi.firmwareVersion();
 if (fv < WIFI_FIRMWARE_LATEST_VERSION) {
 Serial.println("Please upgrade the firmware");
 }
 if (WiFi.status() == WL_NO_MODULE) {
 Serial.println("No wifi Module - Abort");
 while (true);
 }
 while (status != WL_CONNECTED) {
 Serial.println("Connecting to wifi");
 for (byte i = 0; i < 2; i++) {
 digitalWrite(LED_BUILTIN, HIGH);
 delay(2000);
 digitalWrite(LED_BUILTIN, LOW);
 delay(1000);
 }
 status = WiFi.begin(ssid, pass);
 }
 IPAddress ip = WiFi.localIP();
 long rssi = WiFi.RSSI();
 Serial.print("SSID: ");
 Serial.println(WiFi.SSID());
 Serial.print("IP Address: ");
 Serial.println(ip);
 Serial.print("signal strength (RSSI):");
 Serial.print(rssi);
 Serial.println(" dBm");
}

The problem is with the SP_QUERY. I can change the table to any other in my database, and the code functions fine. If I read from veg2_sp, I get the error.

Tables: The first one is the one Im having problems with, the second is the one that is currently functioning. Tables:

I'm utterly baffled.

• Juraj: firewall on PC?
• The Candy King: I thought so too, but I can still connect to the other table in the database. If I couldn't connect to anything, I'd agree, but what is stopping me from accessing a single table??

Answer 1

Just put in a little delay.

cur.execute(SP_QUERY);
delay(5);
column_names *columns = cur.get_columns();