MySQL_Connector_Arduino
MySQL_Connector_Arduino copied to clipboard
ChuckBell
Trying to print column names gives me a mess of ascii
When I run my code, I get this:
ERROR: Timeout waiting for client. Bad mojo. EOF found reading column header. ,,,⸮⸮⸮⸮-⸮ ⸮!⸮bs⸮⸮ ⸮y⸮⸮⸮⸮%)⸮⸮⸮.⸮/⸮-⸮⸮⸮⸮$⸮'Y⸮⸮Y?P⸮P>h⸮⸮@⸮/#/4/D'X_⸮⸮F⸮7⸮'⸮⸮⸮⸮@S⸮⸮⸮~⸮⸮bs⸮⸮⸮P⸮⸮⸮⸮fw⸮⸮⸮⸮⸮bs⸮H⸮w⸮g⸮⸮⸮⸮e⸮B⸮ϓ⸮⸮⸮'⸮⸮⸮⸮⸮⸮⸮⸮Q⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮Z⸮⸮⸮⸮⸮⸮⸮⸮⸮eZ⸮⸮R⸮⸮'x'⸮⸮⸮⸮,,,,,,,,,,,,,,,,,,,%,,,,,,,,,,,,,,⸮⸮O,⸮⸮O,,,,,,,%,,,,,,,,,,,,,,,,,,,,,,,,,,,,⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮II=I⸮ You must read the columns first! ERROR: Timeout waiting for client.
My code:
//Common Libraries
#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 insert[128];
char DATA_QUERY[] = "INSERT INTO veg2_data VALUES (now(), %d, %d, %s, %s, %s, %s, %s, %s, %d);";
char SP_QUERY[] = "SELECT * FROM veg1_sp ORDER BY timestamp DESC LIMIT 1;";
char rawTime[30];
byte refreshRate = 1;
uint32_t dbContactTimer;
MySQL_Connection conn((Client *)&client);
//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() {
if (o_UpperLock == 2) {
if (daytime) {
digitalWrite(O_UPPER, HIGH);
o_UpperState = 1;
}
else {
digitalWrite(O_UPPER, LOW);
o_UpperState = 0;
}
}
if (millis() - dbContactTimer >= (refreshRate * 1000)) {
dbUpdate();
spCheck();
dbContactTimer = millis();
}
}
//------------------------------------MySql Functions-------------------------
void spCheck() {
if (!conn.connected()) {
conn.close();
if (!conn.connect(server_addr, port, user, password, db)) {
return 0;
}
}
MySQL_Cursor *cur = new MySQL_Cursor(&conn);
cur->execute(SP_QUERY);
row_values *row = NULL;
column_names *cols = cur->get_columns();
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(cols->fields[f]->name);
if (f < cols->num_fields-1) {
Serial.print(',');
}
}
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;
}
}
delete cur;
return 1;
}
void dbUpdate() {
if (!conn.connected()) {
conn.close();
if (!conn.connect(server_addr, port, user, password, db)) {
return 0;
}
}
MySQL_Cursor *cur = new MySQL_Cursor(&conn);
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(insert, DATA_QUERY, o_UpperState, o_LowerState, intakeTempArray,
intakeHumArray, roomTempArray, roomHumArray, exhaustTempArray, exhaustHumArray, soilMoisture);
cur->execute(insert);
delete cur;
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");
}
There is t anything in the connector to generate such output. What version of MySQL are you using?
What board are you using? Maybe this is a memory issue?
On Mon, Apr 12, 2021 at 14:58 devinjcampbell @.***> wrote:
When I run my code, I get this:
ERROR: Timeout waiting for client. Bad mojo. EOF found reading column header. ,,,�⸮⸮⸮⸮-⸮� ⸮!⸮b�s�⸮�⸮� ⸮y⸮⸮⸮ ⸮%)�⸮⸮⸮�.⸮/⸮-��⸮�⸮� �⸮�⸮��$⸮'Y�⸮⸮Y?P⸮P>h⸮��⸮@@.*** https://github.com/s⸮⸮⸮~⸮��⸮�b�s�⸮�⸮⸮P⸮⸮⸮�⸮�f�w�⸮�⸮⸮�⸮⸮�b�s�⸮�H⸮w⸮g⸮⸮⸮⸮e⸮B⸮ϓ⸮⸮��⸮'⸮�⸮⸮⸮⸮�⸮⸮�⸮�Q⸮�⸮⸮�⸮�⸮�⸮��⸮�⸮�⸮�⸮�Z⸮⸮⸮⸮⸮⸮⸮⸮�⸮��eZ⸮⸮R⸮�⸮'x'�⸮⸮⸮�⸮,,,,,,,,,,,,,,,,,,,%,,,,,,,,,,,,,,⸮⸮�O,⸮⸮�O,,,,,,,%,,,,,,,,,,,,,,,,,,,,,,,,,,,,⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮II=I⸮ You must read the columns first! ERROR: Timeout waiting for client.
My code:
//Common Libraries
#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 insert[128];
char DATA_QUERY[] = "INSERT INTO veg2_data VALUES (now(), %d, %d, %s, %s, %s, %s, %s, %s, %d);";
char SP_QUERY[] = "SELECT * FROM veg1_sp ORDER BY timestamp DESC LIMIT 1;";
char rawTime[30];
byte refreshRate = 1;
uint32_t dbContactTimer;
MySQL_Connection conn((Client *)&client);
//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() {
if (o_UpperLock == 2) {
if (daytime) { digitalWrite(O_UPPER, HIGH); o_UpperState = 1; } else { digitalWrite(O_UPPER, LOW); o_UpperState = 0; }}
if (millis() - dbContactTimer >= (refreshRate * 1000)) {
dbUpdate(); spCheck(); dbContactTimer = millis();}
}
//------------------------------------MySql Functions-------------------------
void spCheck() {
if (!conn.connected()) {
conn.close(); if (!conn.connect(server_addr, port, user, password, db)) { return 0; }}
MySQL_Cursor *cur = new MySQL_Cursor(&conn);
cur->execute(SP_QUERY);
row_values *row = NULL;
column_names *cols = cur->get_columns();
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(cols->fields[f]->name); if (f < cols->num_fields-1) { Serial.print(','); }}
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; }}
delete cur;
return 1;
}
void dbUpdate() {
if (!conn.connected()) {
conn.close(); if (!conn.connect(server_addr, port, user, password, db)) { return 0; }}
MySQL_Cursor *cur = new MySQL_Cursor(&conn);
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(insert, DATA_QUERY, o_UpperState, o_LowerState, intakeTempArray,
intakeHumArray, roomTempArray, roomHumArray, exhaustTempArray, exhaustHumArray, soilMoisture);
cur->execute(insert);
delete cur;
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");
}
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/169, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYG3DBTHMMQ2UAWT73LTIM7GXANCNFSM42Z3OJVQ .
Also, please post more from your serial monitor . I’d like to know if this occurs during the select or someplace else or at random.
Also, check the SQL separately to ensure there are no permissions or SQL errors.
Hint: I always print the query to the serial monitor during testing. Might help. ;)
On Mon, Apr 12, 2021 at 14:58 devinjcampbell @.***> wrote:
When I run my code, I get this:
ERROR: Timeout waiting for client. Bad mojo. EOF found reading column header. ,,,�⸮⸮⸮⸮-⸮� ⸮!⸮b�s�⸮�⸮� ⸮y⸮⸮⸮ ⸮%)�⸮⸮⸮�.⸮/⸮-��⸮�⸮� �⸮�⸮��$⸮'Y�⸮⸮Y?P⸮P>h⸮��⸮@@.*** https://github.com/s⸮⸮⸮~⸮��⸮�b�s�⸮�⸮⸮P⸮⸮⸮�⸮�f�w�⸮�⸮⸮�⸮⸮�b�s�⸮�H⸮w⸮g⸮⸮⸮⸮e⸮B⸮ϓ⸮⸮��⸮'⸮�⸮⸮⸮⸮�⸮⸮�⸮�Q⸮�⸮⸮�⸮�⸮�⸮��⸮�⸮�⸮�⸮�Z⸮⸮⸮⸮⸮⸮⸮⸮�⸮��eZ⸮⸮R⸮�⸮'x'�⸮⸮⸮�⸮,,,,,,,,,,,,,,,,,,,%,,,,,,,,,,,,,,⸮⸮�O,⸮⸮�O,,,,,,,%,,,,,,,,,,,,,,,,,,,,,,,,,,,,⸮⸮⸮⸮⸮⸮⸮⸮⸮⸮II=I⸮ You must read the columns first! ERROR: Timeout waiting for client.
My code:
//Common Libraries
#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 insert[128];
char DATA_QUERY[] = "INSERT INTO veg2_data VALUES (now(), %d, %d, %s, %s, %s, %s, %s, %s, %d);";
char SP_QUERY[] = "SELECT * FROM veg1_sp ORDER BY timestamp DESC LIMIT 1;";
char rawTime[30];
byte refreshRate = 1;
uint32_t dbContactTimer;
MySQL_Connection conn((Client *)&client);
//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() {
if (o_UpperLock == 2) {
if (daytime) { digitalWrite(O_UPPER, HIGH); o_UpperState = 1; } else { digitalWrite(O_UPPER, LOW); o_UpperState = 0; }}
if (millis() - dbContactTimer >= (refreshRate * 1000)) {
dbUpdate(); spCheck(); dbContactTimer = millis();}
}
//------------------------------------MySql Functions-------------------------
void spCheck() {
if (!conn.connected()) {
conn.close(); if (!conn.connect(server_addr, port, user, password, db)) { return 0; }}
MySQL_Cursor *cur = new MySQL_Cursor(&conn);
cur->execute(SP_QUERY);
row_values *row = NULL;
column_names *cols = cur->get_columns();
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(cols->fields[f]->name); if (f < cols->num_fields-1) { Serial.print(','); }}
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; }}
delete cur;
return 1;
}
void dbUpdate() {
if (!conn.connected()) {
conn.close(); if (!conn.connect(server_addr, port, user, password, db)) { return 0; }}
MySQL_Cursor *cur = new MySQL_Cursor(&conn);
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(insert, DATA_QUERY, o_UpperState, o_LowerState, intakeTempArray,
intakeHumArray, roomTempArray, roomHumArray, exhaustTempArray, exhaustHumArray, soilMoisture);
cur->execute(insert);
delete cur;
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");
}
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/169, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYG3DBTHMMQ2UAWT73LTIM7GXANCNFSM42Z3OJVQ .
The arduino is an Arduino Uno Wifi Rev2 The database is a MariaDB v10.3.24
More details: I am able to connect to other tables in this database, with both select and insert commands. Only veg2_sp returns a fail.
It seems to only happen on select for this table
& I will try that last tip
Edit: More of the monitor
Connecting to wifi SSID: xxxxxxxxxxxxxxxxxx IP Address: xxxxxxxxxxxxxxx signal strength (RSSI):-58 dBm ...trying... Connected to server version 5.5.5-10.3.24-MariaDB Error: 45 = Unknown column 'NAN' in 'field list'. SELECT * FROM veg2_sp ORDER BY timestamp DESC LIMIT 1; ERROR: Timeout waiting for client. Bad mojo. EOF found reading column header. ,,,⸮⸮⸮⸮-⸮ ⸮!⸮⸮⸮⸮⸮ ⸮⸮/ߑϑ⸮⸮⸮⸮ߐϐ⸮k⸮⸮⸮⸮⸮
Sorry, I cannot validate the issue with a variant of MySQL (not allowed). Please try using Oracle's MySQL and let me know if the issue is still there.