ChuckBell
commented
4 years ago Strange. Timing may be the cause there. If you try to read/write too much too fast, these wee microcontrollers can’t handle things well I’ve found. Keep me posted.
On Apr 26, 2020, at 3:53 AM, MikeAUT notifications@github.com wrote:
Hi all, I have a very weird behaviour in my sketch. I try to build a calendar. I use 2 MySQL related functions in my sketch. The first (MySQL handling) is executed every 15 seconds. It fetches the first x rows and writes the content into local variables, also it checks if a event lies in the past and deletes the respective row in the database. The second function (randomEvent) is just for testing purposes. It is called in the first run of the loop and after x seconds. It writes random event into the database. This is all working. BUT: in maxRows i set the limit of the select Query. As soon as this value is higher than 8, the nodeMCU crashes when MySQLHandling is executed. But if i also run randomEvent before MySQLHandling everything works fine. I also tried to raise maxRows to 20 and it's working. I don't know whats wrong here :(
include
include
include
WiFiClient client; MySQL_Connection conn(&client); MySQL_Cursor* cursor;
include
TimeChangeRule myDST = {"UTC", Last, Sun, Mar, 3, +120}; TimeChangeRule mySTD = {"UTC", Last, Sun, Oct, 3, +60}; Timezone myTZ(myDST, mySTD);
include
WiFiUDP Udp;
include
PulseTimer pulseCheckWiFi; PulseTimer pulse1secPulse; PulseTimer pulseMySQL; PulseTimer pulseAddRandomEvent;
// ----------- // NTP static const char ntpServerName[] = "pool.ntp.org"; const int timeZoneNtp = 0; time_t getNtpTime(); void sendNTPpacket(IPAddress &address);
// MySQL IPAddress server_addr(192, 168, 1, 5); char user[] = "arduino"; char password[] = "xxxxxxx"; char default_db[] = "arduino";
const unsigned int maxRows = 8;
// Queries char selectQuery[] = "SELECT id,UNIX_TIMESTAMP(date),text,allday,icon FROM calendar ORDER BY date, id LIMIT %d;"; char insertQuery[] = "INSERT INTO calendar (date,text,icon) VALUES (FROM_UNIXTIME(%ld),'%s',%d);"; char deleteQuery[] = "DELETE FROM calendar WHERE id=%d;";
unsigned int id[maxRows]; unsigned long date[maxRows]; String text[maxRows]; bool allday[maxRows]; unsigned int icon[maxRows]; unsigned int upcoming[maxRows];
bool initRun = true;
char ssid[] = "xxxxxxxxxxx"; char pass[] = "xxxxxxxxxxxx"; byte mac[6]; unsigned int localPort = 8888; // -----------
void setup() { Serial.begin(115200); WiFi.mode(WIFI_STA); connectWiFi(); printWiFiStatus();
// Get NTP Serial.print("IP number assigned by DHCP is "); Serial.println(WiFi.localIP()); Serial.println("Starting UDP"); Udp.begin(localPort); Serial.print("Local port: "); Serial.println(Udp.localPort()); Serial.println("waiting for sync"); setSyncProvider(getNtpTime); setSyncInterval(72000); // 20 Stunden
// Connect MySQL //if (conn.connect(server_addr, 3306, user, password, default_db)) { // delay(1000); // } // else //Serial.println("MySQL Connection failed."); }
void loop() { pulseCheckWiFi.set(10000); pulse1secPulse.set(1000); pulseMySQL.set(15000); pulseAddRandomEvent.set(10000);
if (pulseCheckWiFi.pulse) { checkWiFi(); }
if (timeStatus() != 2) { setSyncInterval(10); } else { setSyncInterval(72000); // 20 Stunden }
if (conn.connected()) {
} else { conn.close(); Serial.println("Connecting to SQL..."); if (conn.connect(server_addr, 3306, user, password, default_db)) { delay(500); Serial.println("Successful reconnect to SQL!"); } else { Serial.println("Cannot reconnect to SQL! Drat."); } }
if (pulseMySQL.pulse && timeStatus() == 2 && conn.connected()) { MySQLHandling(); }
if (pulse1secPulse.pulse) { serialOutput(); }
if (pulseAddRandomEvent.pulse || initRun) { initRun = false; randomEvent(); } } // END OF LOOP
void MySQLHandling() { // ----------------- MySQL // Clears local array for (int i = 0 ; i < maxRows; i++) { id[i] = 0; date[i] = 0; text[i] = ""; allday[i] = false; icon[i] = 0; }
MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); char query[100]; sprintf(query, selectQuery, maxRows); cur_mem->execute(query); // Fetch the columns and print them column_names cols = cur_mem->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(','); } } Serial.println();
// Read the rows and print them row_values *row = NULL; int index = 0; do { row = cur_mem->get_next_row(); if (row != NULL) { Serial.print(row->values[0]); Serial.println(); id[index] = atoi(row->values[0]); date[index] = atoi(row->values[1]); text[index] = row->values[2]; allday[index] = atoi(row->values[3]); icon[index] = atoi(row->values[4]); index++; } } while (row != NULL);
// DELETE EVENT IF LIES IN PAST for (int i = 0; i < maxRows; i++) { if ((date[i] != 0) && (date[i] < now())) { char query2[50]; sprintf(query2, deleteQuery, id[i]); cur_mem->execute(query2); } } delete cur_mem; Serial.println("MySQL Handling finished"); }
void randomEvent() { // Adds a random event with time 23:59:59 and x to y days in the future unsigned long int outDate = ((now() - second() - minute() 60 - hour() 3600) - 7200) + 86399 + (86400 (random(1, 4))); const byte NUMBER_OF_TEXT = 10; char texts[NUMBER_OF_TEXT] = {"Alpha", "Beta", "Gamma", "Delta", "Echo", "Epsilon", "Lambda", "Omega", "Phi", "Lurch"}; char* outText = texts[random(0, NUMBER_OF_TEXT)]; unsigned int outIcon = random (0, 15);
MySQL_Cursor *cur_ins = new MySQL_Cursor(&conn); char query[100]; sprintf(query, insertQuery, outDate, outText, outIcon); cur_ins->execute(query); int lastId = cur_ins->get_last_insert_id(); Serial.printf("Termin eingetragen mit der ID %d\n", lastId); delete cur_ins; }
void connectWiFi() { // attempt to connect to Wifi network: WiFi.begin(ssid, pass); Serial.println("connecting"); while (WiFi.status() != WL_CONNECTED) { delay(500); Serial.print("."); } Serial.println("WiFi Connected"); }
void printWiFiStatus() { // print the SSID of the network you're attached to: Serial.print("SSID: "); Serial.println(WiFi.SSID());
// print your WiFi shield's IP address: WiFi.macAddress(mac); Serial.print("MAC: "); Serial.print(mac[5], HEX); Serial.print(":"); Serial.print(mac[4], HEX); Serial.print(":"); Serial.print(mac[3], HEX); Serial.print(":"); Serial.print(mac[2], HEX); Serial.print(":"); Serial.print(mac[1], HEX); Serial.print(":"); Serial.println(mac[0], HEX); IPAddress ip = WiFi.localIP(); Serial.print("IP Address: "); Serial.println(ip);
// print the received signal strength: long rssi = WiFi.RSSI(); Serial.print("signal strength (RSSI):"); Serial.print(rssi); Serial.println(" dBm"); }
void checkWiFi() { if (WiFi.status() != WL_CONNECTED) { Serial.println("Connection lost. Reconnecting!"); WiFi.begin(ssid, pass); } }
/-------- NTP code ----------/ const int NTP_PACKET_SIZE = 48; // NTP time is in the first 48 bytes of message byte packetBuffer[NTP_PACKET_SIZE]; //buffer to hold incoming & outgoing packets
time_t getNtpTime() { IPAddress ntpServerIP; // NTP server's ip address
while (Udp.parsePacket() > 0) ; // discard any previously received packets Serial.println("Transmit NTP Request"); // get a random server from the pool WiFi.hostByName(ntpServerName, ntpServerIP); Serial.print(ntpServerName); Serial.print(": "); Serial.println(ntpServerIP); sendNTPpacket(ntpServerIP); uint32_t beginWait = millis(); while (millis() - beginWait < 1500) { int size = Udp.parsePacket(); if (size >= NTP_PACKET_SIZE) { Serial.println("Receive NTP Response"); Udp.read(packetBuffer, NTP_PACKET_SIZE); // read packet into the buffer unsigned long secsSince1900; // convert four bytes starting at location 40 to a long integer secsSince1900 = (unsigned long)packetBuffer[40] << 24; secsSince1900 |= (unsigned long)packetBuffer[41] << 16; secsSince1900 |= (unsigned long)packetBuffer[42] << 8; secsSince1900 |= (unsigned long)packetBuffer[43]; return secsSince1900 - 2208988800UL + timeZoneNtp * SECS_PER_HOUR; } } Serial.println("No NTP Response :-("); return 0; // return 0 if unable to get the time }
// send an NTP request to the time server at the given address void sendNTPpacket(IPAddress & address) { // set all bytes in the buffer to 0 memset(packetBuffer, 0, NTP_PACKET_SIZE); // Initialize values needed to form NTP request // (see URL above for details on the packets) packetBuffer[0] = 0b11100011; // LI, Version, Mode packetBuffer[1] = 0; // Stratum, or type of clock packetBuffer[2] = 6; // Polling Interval packetBuffer[3] = 0xEC; // Peer Clock Precision // 8 bytes of zero for Root Delay & Root Dispersion packetBuffer[12] = 49; packetBuffer[13] = 0x4E; packetBuffer[14] = 49; packetBuffer[15] = 52; // all NTP fields have been given values, now // you can send a packet requesting a timestamp: Udp.beginPacket(address, 123); //NTP requests are to port 123 Udp.write(packetBuffer, NTP_PACKET_SIZE); Udp.endPacket(); } /-------- END NTP code ----------/
void serialOutput() { Serial.println("#############"); time_t nowTime = myTZ.toLocal(now()); time_t dateReadable[maxRows]; for (int i = 0; i < maxRows; i++) { dateReadable[i] = myTZ.toLocal(date[i]); }
Serial.print("Now Time "); Serial.println(nowTime); Serial.print(year(nowTime)); Serial.print('/'); Serial.print(month(nowTime)); Serial.print('/'); Serial.print(day(nowTime), DEC); Serial.print(' '); Serial.print(hour(nowTime), DEC); Serial.print(':'); Serial.print(minute(nowTime), DEC); Serial.print(':'); Serial.println(second(nowTime), DEC); Serial.println();
for (int i = 0; i < maxRows; i++) { Serial.print(id[i]); Serial.print(" "); Serial.print(date[i]); Serial.print(" "); Serial.print(text[i]); Serial.print(" "); Serial.print(allday[i]); Serial.print(" "); Serial.print(icon[i]); Serial.print(" "); Serial.print(year(dateReadable[i])); Serial.print('/'); Serial.print(month(dateReadable[i])); Serial.print('/'); Serial.print(day(dateReadable[i]), DEC); Serial.print(' '); Serial.print(hour(dateReadable[i]), DEC); Serial.print(':'); Serial.print(minute(dateReadable[i]), DEC); Serial.print(':'); Serial.print(second(dateReadable[i]), DEC); Serial.println(); }
for (int i = 0; i < maxRows; i++) { if (upcoming[i] != 0) { Serial.print("Upcoming: "); Serial.print(i); Serial.print(" "); Serial.print(upcoming[i]); if (upcoming[i] == id[i]) { Serial.print(" "); Serial.print(date[i]); Serial.print(" "); Serial.print(text[i]); Serial.print(" "); Serial.print(allday[i]); Serial.print(" "); Serial.print(icon[i]); Serial.print(" "); Serial.print(year(dateReadable[i])); Serial.print('/'); Serial.print(month(dateReadable[i])); Serial.print('/'); Serial.print(day(dateReadable[i]), DEC); Serial.print(' '); Serial.print(hour(dateReadable[i]), DEC); Serial.print(':'); Serial.print(minute(dateReadable[i]), DEC); Serial.print(':'); Serial.print(second(dateReadable[i]), DEC); Serial.println(); } } } Serial.print("millis "); Serial.println(millis()); } — 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/132, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYF2PDC7PNA7SYU3XXLROPR5ZANCNFSM4MRDQZJQ.
MikeAUT
KimPanda15
Hi all, I have a very weird behaviour in my sketch. I try to build a calendar. I use 2 MySQL related functions in my sketch. The first (MySQL handling) is executed every 15 seconds. It fetches the first x rows and writes the content into local variables, also it checks if a event lies in the past and deletes the respective row in the database. The second function (randomEvent) is just for testing purposes. It is called in the first run of the loop and after x seconds. It writes random event into the database. This is all working. BUT: in maxRows i set the limit of the select Query. As soon as this value is higher than 8, the nodeMCU crashes when MySQLHandling is executed. But if i also run randomEvent before MySQLHandling everything works fine. I also tried to raise maxRows to 20 and it's working. I don't know whats wrong here :(