NodeMCU Lockup on failed query execution

[coznkaos]

Greetings, Dr. Bell. First, I would like to thank you for the superb contribution you have made to the developer community with your MySQL connector library. I have been exploring its use in some of my projects lately and have enjoyed using it; I'm currently using version 1.1.1.

I am hoping you can assist me with an issue I have been having with the library or perhaps point me in the right direction to resolve it. I have been using the connector to write and retrieve data using a NodeMCU based ESP8266 module through the Arduino IDE. I have added code to the sketch that will initiate reboots where necessary if a connection to the server cannot be made to re-establish the connection. However, the issue I'm having is that if the connection to the server is interrupted in the middle of a query execution, the NodeMCU (and likely arudinos would do the same) will hang and not continue with the code; the NodeMCU will lockup and will not reboot to try to reconnect to the server and rerun the sketch.

I have a MySQL server running on a Windows laptop (version 5.7) and a Raspberry Pi (version 5.5.54-0+deb8ul). If, for example, I shut down the MySQL service, I can cause the NodeMCU to lockup if it is in the middle of query execution; I have tested this by shutting down both the Windows and Pi servers during INSERT queries (can be achieved by shutting down MySQL Windows service, disconnecting power to the Pi, shutting down the servers, etc.). Presumably, this would also happen if the network connection between the node and the server were also severed. If need be, I can test this as well. It appears the problem is that the library does not allow the ESP8266 to move forward when the connection errors during query execution. I'm not sure if the NodeMCU includes any native code to restart if the device is idle for a period of time.

I have read your response to a similar inquiry from March 2016 (located at http://drcharlesbell.blogspot.com/2016/01/new-release-mysql-connectorarduino-110a.html). Here, you had mentioned that the library was designed to be lightweight and does not include the full scope of the MySQL protocol for handling timeouts or error analysis due to memory limitations. I was wondering if you have had any opportunity to incorporate at least some form of error handling or timeouts into your library, given the larger memory sizes for boards that are currently available, for connections that are lost or interrupted in the middle of query execution. Without some form of error control to recognize a lost connection/timeout that might initiate a reboot or at least continue through the code, it would be prohibitive to deploy the sensors I'm planning to use as I would have to manually restart as many as 30 sensors in distant locations if there were a database issue, computer failure/restart, power failure etc. when the sensors are trying to execute a query.

I'm hoping you can provide some guidance in how to work around this issue or perhaps incorporate some form of error handling/timeout.

Again, thanks for all you have done, Dr. Bell. 👍

[copterino]

Hello @coznkaos, @ChuckBell! I would like also say thank you to you, Dr. Bell, for contributing MySQL library to Arduino community. Recently I faced the same issue as @coznkaos and made huge refactoring to the library. Please take a look at my repository:

1. Forked library
2. Example of its usage

Currently I'm hardly testing my changes to the library and it's been 7 days since last commit (with no issues), so you can give it a try, @coznkaos. If everything is good, I will try to make pull request. Library improvement changes: Link

[ChuckBell]

Hi All,

Yes, I will take a look at this and get back to you. Give me a few days. ;)

Dr. Bell

On Apr 28, 2017, at 4:00 AM, copterino notifications@github.com wrote:

Hello @coznkaos https://github.com/coznkaos, @ChuckBell https://github.com/ChuckBell! I would like also say thank you to you, Dr. Bell, for contributing MySQL library to Arduino community. Recently I faced the same issue as @coznkaos https://github.com/coznkaos and made huge refactoring to the library. Please take a look at my repository:

Forked library https://github.com/copterino/MySQL_Connector_Arduino Example of its usage https://github.com/copterino/esp8266-weather-station Currently I'm hardly testing my changes to the library and it's been 7 days since last commit (with no issues), so you can give it a try, @coznkaos https://github.com/coznkaos. If everything is good, I will try to make pull request. Library improvement changes: Link https://github.com/copterino/MySQL_Connector_Arduino/commit/dfd31b8acc2832a7218c73bd030631cb41abe1dd — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/15#issuecomment-297934373, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4C0VTLY7X1Aqj1q2ebGnhdgnPaINks5r0ZyhgaJpZM4NK7pB.

[coznkaos]

Greetings all. Sincerely, I do appreciate your time and effort in looking into this. @copterino thank you for your submission; I've imported into my project and briefly looked at it tonight. So far, I haven't been able to cause the NodeMCU to hang/lockup; it seems that the queries execute very quickly and I am unable to interrupt the connection quick enough during query execution. Also, when the connection is interrupted, it appears to reconnect to the database without any code prompting to reconnect - is this correct? Is this a feature of the modified library? Hopefully I'll have more time to look at it this weekend and can post back. But again, a very hardy thank you to both of you. Cheers, @ChuckBell and @copterino !

[sunder11]

Copertino, I tried out your code and I like the changes you made. If I turned off my VPS where the Mysql sever is located, my nodemcu would automatically reboot after 30 seconds over and over because it could not connect the mysql server. There were also times that it would hang so I added some watchdog code. It also took about three or four seconds to write to the database. With your code it does not crash but it does not seem to try and reconnect (my connection code is not in the loop, it is in setup). So I added some code to try and reconnect 5 times in the loop if the connection could not be established and then it will reboot. The Copertino code also writes data to the database in about one second or less (it does not register on the watchdog count).

[sunder11]

Here is how I did it if interested - caveat I am no expert at this like you guys:

//INITIALIZE WATCHDOG////////////watchdogCount==30 means it will wait 30 seconds before it will automatically reset on a hang //got this watchdog code from Andreas Spiess "The Guy with the Swiss Accent"

include

Ticker secondTick; volatile int watchdogCount = 0;

void ISRwatchdog(){ watchdogCount ++; if (watchdogCount == 30){ Serial.println(); Serial.println("the watchdog bites!!!!!!!"); ESP.reset(); } } //END WATCHDOG ///BEGIN MYSQL CONNECTOR int num_fails; // variable for number of failure attempts

define MAX_FAILED_CONNECTS 5 // maximum number of failed connects to MySQL

// change unit_id below matching each alarm.//MYSQL Queries char INSERT_SQL[] = "UPDATE mydatabase.users SET alarm='yes', pwr_alarm='no', wifi_alarm='no' WHERE unit_id = '101'"; char INSERT_SQL2[] = "UPDATE mydatabase.users SET alarm='no', pwr_alarm='no', wifi_alarm='no', other_notes='0' WHERE unit_id = '101'"; //END MYSQL

void setup() {

....wifi connect code

...mysql connect code

///////SETUP WATCHDOG////////////////// secondTick.attach(1,ISRwatchdog); Serial.println("Turning on the watchdog"); //////END SETUPWATCHDOG

}

void loop {

if (Switch == HIGH) { Serial.println("Pin is HIGH"); if (conn.connected()){ Serial.println("Connection Good to MYSQL database."); Serial.println("Beginning to Record data in MYSQL database."); cursor.execute(INSERT_SQL); Serial.println("Updated MySQL database ..."); } else { Serial.println("Cant Connect to MYSQL database."); Serial.println("Retrying connection to MYSQL database."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.println("Connected to MySQL Database"); Serial.println("Beginning to Record data in MYSQL database."); cursor.execute(INSERT_SQL); Serial.println("Updated MySQL database ..."); } else { Serial.println("Connection failed again to MYSQL Database."); num_fails++; if (num_fails == MAX_FAILED_CONNECTS) { Serial.println("Ok, that's it. I'm outta here. Rebooting..."); delay(2000); ESP.reset(); } } } }

////WATCHDOG////RESET COUNT//// if (watchdogCount > 1) { Serial.printf("Watchdog counter= %d\n",watchdogCount); } watchdogCount=0; //feeding the dog ////END WATCHDOG///RESET COUNT

}

[copterino]

@coznkaos, @sunder11, thank you for good words! @sunder11, try to insert WiFi and SQL connection into loop() (see my example). I think the watchdog is a good idea as for a last resort, I would take it for my sketch too, thank you.

[sunder11]

Many thanks to Dr. Bell and Copertino, but unless I am wrong, if you have a ESP8266/NodeMCU the "connect by host name" example does not work because it was apparently designed for the arduino with ethernet shield and I am connecting by wifi.

I want to connect by hostname so my code will still be good if I change webhosts.

I made the following changes to Copertino's code that has been working fine (I can't figure out how to update OTA).

CHANGE YOUR SKETCH TO:

include

const char* host = "google.com";

// remove or comment out// IPAddress server_addr(1111,11,1,11);

Serial.print("Using the host "); Serial.println(host);

if (conn.connect(host, 3306, user, password)) { delay(1000); Serial.println("Connected to MySQL Database"); } else { Serial.println("Connection failed to MYSQL Database."); }

CHANGE MYSQL_connection.h

line 43 boolean connect(const char host, int port, char user, char *password);

CHANGE MYSQL_connection.cpp

line 57 boolean MySQL_Connection::connect(const char host, int port, char user,

line 58 char *password)

line 88 and 91 might need to be changed but I am not using the DEBUG

[ChuckBell]

Hi,

The issue with the ESP8266 and hostname connection is not because the example is written for Arduino, it’s because the ESP8266 libraries do not support the getHostByName() method in the Client class. Until that is implemented, you won’t be able to (easily) connect to MySQL by hostname using the connector on an ESP8266 board without a workaround.

Dr. Bell

On May 4, 2017, at 12:35 AM, sunder11 notifications@github.com wrote:

Many thanks to Dr. Bell and Copertino, but unless I am wrong, if you have a ESP8266/NodeMCU the "connect by host name" example does not work because it was apparently designed for the arduino with ethernet shield and I am connecting by wifi.

I want to connect by hostname so my code will still be good if I change webhosts.

I made the following changes to Copertino's code that has been working fine (I can't figure out how to update OTA).

CHANGE YOUR SKETCH TO:

const char* host = "google.com";

// remove or comment out// IPAddress server_addr(1111,11,1,11);

Serial.print("Using the host "); Serial.println(host);

if (conn.connect(host, 3306, user, password)) { delay(1000); Serial.println("Connected to MySQL Database"); } else { Serial.println("Connection failed to MYSQL Database."); }

CHANGE MYSQL_connection.h

line 43 boolean connect(const char host, int port, char user, char *password);

CHANGE MYSQL_connection.cpp

line 57 boolean MySQL_Connection::connect(const char host, int port, char user,

line 58 char *password)

line 88 and 91 might need to be changed but I am not using the DEBUG

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/15#issuecomment-299095275, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4NXsgoArjOfwlJKOjztcTQFl3jl-ks5r2VWtgaJpZM4NK7pB.

[copterino]

@sunder11, you can easily do the following trick:

IPAddress serverIP;
WiFi.hostByName("google.com", serverIP);

[sunder11]

Funny, shows what I know.

[sunder11]

Copertino, I changed the library back and tried what you suggested in my sketch but it won't compile. The Wifi.hostByName part is the issue. Did you try that code?

[copterino]

@sunder11, do you have an include of ESP8266WiFi.h in the sketch file? #include <ESP8266WiFi.h>

[sunder11]

Yes here are the libraries I have:

include

include

include

include

include

include

include

include

[sunder11]

Dr. Bell, thanks for the clarification on: "ESP8266 libraries do not support the getHostByName() method in the Client class". I can believe I stupidly spent all day and night trying to connect by host name until I gave up and figured out another way. Of course I know just enough to be dangerous about this stuff.

I wish I knew why my code modification above allows me to connect with a host name. What Library is the "host" method coming from, or perhaps it is built into the hardware?

[copterino]

@sunder11, yes I've checked this piece of code. Please create repository with your sketch (private or public) and I will help with compilation errors, if you can of course.

[sunder11]

Copterino I had given up on getting that code working because of Dr. Bell's post about the ESP8266 libraries not having support for get hostByName. So I thought what you suggested was not tested. Now that you say it works I am really confused. I must not be understanding Dr. Bells post correctly, but I don't know why your code works based on ESP8266WiFi.h when he says there is no support in the ESP8266 libraries for get hostByName. Thanks for the offer of fixing my code but I like figuring these things out for myself so I learn something (with a little help of course).

[ChuckBell]

The problem is there are many libraries for the ESP88266. So, I was speaking of the one available through the Arduino library manager. If there is another that works, please share it's URL so we can try it. This is one of the reasons I discourage use of the ESP8266 for advanced projects. But, if there is a better library that works, I'd like to hear about it. YMMV ;)

On Sat, May 6, 2017 at 14:29 sunder11 notifications@github.com wrote:

Copterino I had given up on getting that code working because of Dr. Bell's post about the ESP8266 libraries not having support for get hostByName. So I thought what you suggested was not tested. Now that you say it works I am really confused. I must not be understanding Dr. Bells post correctly, but I don't know why your code works based on ESP8266WiFi.h when he says there is no support in the ESP8266 libraries for get hostByName. Thanks for the offer of fixing my code but I like figuring these things out for myself so I learn something (with a little help of course).

— You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/15#issuecomment-299657801, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4NLkFXp-Ypt8Lnb4awMLPv9904p_ks5r3LwJgaJpZM4NK7pB .

[copterino]

Client (which is passed into MySQL_Connection constructor) is a base class for EthernetClient and WiFiClient classes and they have no support of getting host by name as a public method, but they have two versions of connect():

virtual int connect(IPAddress ip, uint16_t port) =0;
virtual int connect(const char *host, uint16_t port) =0;

So we have two options:

• Add corresponding override to MySQL_Connection class
• Resolve host into IP by hand

For example, this is how it is implemented in WiFiClient of esp8266:

int WiFiClient::connect(const char* host, uint16_t port)
{
    IPAddress remote_addr;
    if (WiFi.hostByName(host, remote_addr))
    {
        return connect(remote_addr, port);
    }
    return 0;
}

As for me, I chose second option and resolved host by hand using mentioned technique.

[sunder11]

OK I see things make sense now. I see the 2 versions of connect in WiFiclient.h. Now I understand why my modifications above work. I guess mine is the override/work around way of doing it.

[Sprinkle-Master]

Dr Bell,

I wanted to take a moment to both thank you for all of your hard work and also encourage you to implement an official Arduino update to address issue #15. I am still pretty new to all of this but learning new things every day. I have been working with the ESP8266-12F and I am excited by all of the possibilities of this little board. I have solved a lot of issues on my own or by searching around. Your connector library has helped tremendously and I tip my hat to you good sir!

I have spent much of the last week searching for a solution to this same problem. Initially, I skipped this thread because it was about a NodeMCU and I am using a plain ESP chip and a breadboard. Tonight, I finally read a bit of coznkaos' post and realized he was describing precisely what I have been struggling with.

I began to speculate last night as to the source of the problem. Again, forgive noob mistakes if I have it all wrong... ESP 8266 has both hardware and software watchdogs active by default I was puzzled as to why Connector would be able to make the sketch hang. I suspected it was one of two things:

1) The 8266 has many built-in functions that "feed the dog". Depending on what kind of loop the connector was stuck in with the error, it could be allowing background functions to continue feeding the dog and preventing the reset.

2) The error is being pulled in at the highest level, up there with the hardware watchdog, and the watchdog doesn't have priority to override and reset.

maybe I may be way out in left field somewhere but I am looking forward to testing both copterino's and sunder11's suggestions. I do hope you will address this soon in an official Arduino update. Thanks again for all of your hard work!.

[uwezi]

I recently encountered the same problem: when the execution of a MySQL command fails because of a network failure, the ESP8266 gets stuck inside cursor.execute(SQL); while obviously the watchdogs still get their regular food.

Previously on the ESP8266 I had problems with too aggressive watchdogs, now I wished it had just a bit more byte ;-)

[BladzheR]

Also I ran into NodeMCU hangup problem on the line "cursor-> execute (SQL);". First, I make a check on the connection to Wi-Fi. Then I check Ping to the server. Then I check MySQL conn. Then comes the line "cursor-> execute (SQL);", on which everything hangs if the connection to this moment with MySQL is broken. What to do?

[uwezi]

this is my current solution: an external watchdog

https://www.sciencetronics.com/greenphotons/?p=2107

[BladzheR]

this is my current solution: an external watchdog

https://www.sciencetronics.com/greenphotons/?p=2107

Thank! I also found this option [(link):] (https://github.com/esp8266/Arduino/issues/1532#issuecomment-176869292)

While this option works for me. I tested it myself and so far there were no problems.

[adman953]

I have been having this problem and have been working on it for a couple of months. I decoded my crash stack and found that the error was in MySQL_Packet.cpp line 307. Lines 306, 307, 308, and 309 are for slow arriving packets for ethernet shields. Since I am using an esp8266 12E with no ethernet shield I commented these lines out and my problems stopped. I still have the watchdog in my code so thank you for that safety net.

[ChuckBell]

HI. Ok, will consider a modification for the next release.

On Apr 18, 2019, at 10:25 PM, adman953 notifications@github.com wrote:

I have been having this problem and have been working on it for a couple of months. I decoded my crash stack and found that the error was in MySQL_Packet.cpp line 307. Lines 306, 307, 308, and 309 are for slow arriving packets for ethernet shields. Since I am using an esp8266 12E with no ethernet shield I commented these lines out and my problems stopped.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/15#issuecomment-484747593, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYDWQDEVH6ESKJ76L4TPREURTANCNFSM4DJLXJAQ.

[copterino]

@adman953, you must be using old version of the library (from 2017). Update to the latest version where speed was dramatically improved.