search

ChuckBell / MySQL_Connector_Arduino

Database connector library for using MySQL with your Arduino projects.
332 stars 133 forks source link

ESP8266 Fails to Connect to Database #185

Closed KellyJLW closed 2 years ago

KellyJLW commented 2 years ago

Writing code for ESP8266 and ESP32 is my winter hobby.

I just started working on my boards again this fall and code that worked successfully last winter is now failing to connect to the database. No changes has been made to the code since it worked last winter. I also have serveral other ESP8266 boards that write to my database every 10 minutes for years now that still work without issue.

Only my ESP8266 boards fail to connect after the software has been compiled and uploaded to the ESP8266 recently. Boards that have the same software uploaded last in Feb 2021 work without issue. The ESP32 boards work great even after compling the same code from last winter.

I use the Arduino IDE ver 1.8.13 to upload my sketches to the ESPs. I updated the board manager as follows last month: Board updates Arduino AVR Boards 1.8.3 installed > 1.8.4

esp32
1.0.5 installed > 1.0.6

esp8266
2.7.4 installed > 3.0.2

I haven't updated any libraries. MySQL Connector Arduino library is the same version as last winter 1.2.0.

I downgraded the boards to the orginal version but that did not solve the problem.

I get the following from the Serial monitor.

Booting... Connecting to wifi... Connected to Garfield 192.168.0.202 Attempting to connect to db ...trying...

--------------- CUT HERE FOR EXCEPTION DECODER ---------------

Exception (28): epc1=0x40203f9c epc2=0x00000000 epc3=0x00000000 excvaddr=0x00000049 depc=0x00000000

stack>>>

ctx: cont sp: 3ffffa80 end: 3fffffc0 offset: 0190 3ffffc10: 3ffffcb1 00000000 3ffffc70 3fff0184
3ffffc20: 3fff0184 3fff0090 00000001 40203fef
3ffffc30: 40240000 00000000 3ffe8821 40203fdc
3ffffc40: 3fff01c8 3ffeed94 3fff0090 4020d814
3ffffc50: fffffffc feefeffe feefeffe 3ffe8820
3ffffc60: 00000014 3fff008c 000000ff 3fffff18
3ffffc70: 4020d7e0 3ffe8821 3ffee7d0 40209700
3ffffc80: 3ffefcb4 3ffee7d0 00000000 40209724
3ffffc90: 3ffefcb4 3ffee7d0 3ffee7d0 402097b4
3ffffca0: 3ffeed94 00000000 00000000 402043a7
3ffffcb0: 00314500 00005810 40100521 4021c812
3ffffcc0: 000001f8 000001f8 3ffe85dc 40100a5b
3ffffcd0: 00000000 4bc6a7f0 dbe76c8b 00000001
3ffffce0: 3fff022c 00000020 3fff022c 40100c3a
3ffffcf0: 3ffee280 00000000 00000000 40100630
3ffffd00: 00000623 3ffeeafc 0000012c 4021d278
3ffffd10: 000001e3 000001e3 3ffe85dc 40100a5b
3ffffd20: 3ffe8821 00000000 3fff014c 3ffe8821
3ffffd30: 00000000 00000028 00000020 3ffe8821
3ffffd40: 00000000 00000028 3fffff18 402044ea
3ffffd50: 00000003 3ffefcb4 3fffff18 4020425e
3ffffd60: 0000005b 3ffffde0 3ffffd9c 00000003
3ffffd70: 00000001 3ffee904 3fffff18 40203cda
3ffffd80: 4020dd78 6700a8c0 3ffffdb0 3ffffda0
3ffffd90: 3ffffde0 00000cea 3ffe8828 3ffe8821
3ffffda0: 00000000 40202c58 00000084 000000cc
3ffffdb0: 000000a8 000000af 3ffee704 3ffeea94
3ffffdc0: 3fffdad0 3ffee704 3ffffee0 4020131b
3ffffdd0: 000000e8 000000e1 4000050c 3ffe890d
3ffffde0: 4020dd78 6700a8c0 00000000 4021c784
3ffffdf0: 3ffef924 00000008 00000020 40100c64
3ffffe00: 00000018 3ffffe74 00000001 00000000
3ffffe10: 00000002 3ffef1a4 00000004 4021d251
3ffffe20: ffffffff 3fffc6fc 00000001 3ffeed94
3ffffe30: 00000000 4bc6a7f0 4624dd2f 0005adb9
3ffffe40: 00000000 00000000 4bc6a7f0 00000000
3ffffe50: 3ffef924 3ffefe54 40100521 00000000
3ffffe60: 000004e9 0000014c 00000001 4020677e
3ffffe70: 3ffee888 00000000 00000000 3ffe8981
3ffffe80: 3ffe893d 00000000 3fff005c 00000003
3ffffe90: 3ffe893d 41445055 64204554 4d5f6f62
3ffffea0: 6f745379 65676172 6c62742e 616f425f
3ffffeb0: 20736472 20544553 72616f62 73614c64
3ffffec0: 6c6e4f74 20656e69 6f6e203d 2c292877
3ffffed0: 616f6220 72506472 63656a6f 203d2074
3ffffee0: 27732527 6f62202c 49647261 25273d50
3ffffef0: 202c2773 72616f62 43414d64 7325273d
3fffff00: 48572027 20455245 72616f62 20444964
3fffff10: 6425203d 3ffe8900 3fff0184 0000005b
3fffff20: 3fffff3c 3ffefe84 5d357161 19017b17
3fffff30: 4939351d 6d506843 07356925 4020dbc4
3fffff40: 00000000 00001388 3ffee73c 00000000
3fffff50: 00000000 3fff014c 00000000 433a3438
3fffff60: 38413a43 3a46413a 453a3845 39310031
3fffff70: 36312e32 2e302e38 00323032 a8cc84e8
3fffff80: 40e1e8af ca00a8c0 4020d0b8 4020d0b4
3fffff90: 3fffdad0 00000000 3ffee904 40201406
3fffffa0: feefeffe feefeffe 3ffeea80 4020ad00
3fffffb0: feefeffe feefeffe 3ffe85d8 40100eed
<<<stack<<<

This is the function that attempts to connect to my database and update a mysql table: void handleUpdatetblBoards() { /***** mysql variables ****/ char UPDATE_DATA[] = "UPDATE dbo_MyStorage.tbl_Boards SET boardLastOnline = now(), boardProject = '%s', boardIP='%s', boardMAC='%s' WHERE boardID = %d"; char query[180];

char myIPAddress[15];
WiFi.localIP().toString().toCharArray(myIPAddress, 15); // convert ip address to char array

byte macAddr[6];
WiFi.macAddress(macAddr);
char myMacAddress[18];
sprintf(myMacAddress, "%2X:%2X:%2X:%2X:%2X:%2X", macAddr[0], macAddr[1], macAddr[2], macAddr[3], macAddr[4], macAddr[5]);

/***************** Create wifi client  ***************/
WiFiClient client;
MySQL_Connection conn((Client *)&client);

Serial.println("Attempting to connect to db"); // this code just added Dec 4, 2021

/***************** Connect to mysql client  ***************/

if (conn.connect(server_addr, 3306, DB_USER, DB_PWD)) {
    delay(1000);
            Serial.println("Connected to db"); // this code just added Dec 4, 2021
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);    // Initiate the query class instance
    sprintf(query, UPDATE_DATA, versionID, myIPAddress, myMacAddress, boardID); // Save
    //cur_mem->execute(query);  // Execute the query
    delete cur_mem;             // Deleting the cursor also frees up memory used
} else {                        // error treatment
    digitalWrite(led1, LOW);
    Serial.println("Connection Failed!!!"); // this code just added Dec 4, 2021
    delay(60000);
    ESP.restart();      // Here we tell the ESP to reboot
}
conn.close();

}

The code fails right at the attempt to connect to the database if (conn.connect(server_addr, 3306, DB_USER, DB_PWD)) It doesn't even go to the else statement where it writes digitalWrite(led1, LOW); and Serial.println("Connection Failed!!!");

This is the model of the ESP8266 KeeYees Development Board WiFi WLAN Wireless Module for ESP8266 NodeMCU ESP-12E CP2102 Compatible with Arduino

I use Board Type: NodeMCU 1.0 (ESP-12E Module). Same as I have always used with these boards. If I comment out the call to handleUpdatetblBoards() the code uploads successfully and it runs without issue.

Please assist.

woeperbe commented 2 years ago

Stay on 2.7.4 for esp8266 and it will work!

Greetings, Marc Buffet

On 4 Dec 2021, at 18:24, KellyJLW @.***> wrote:



Writing code for ESP8266 and ESP32 is my winter hobby.

I just started working on my boards again this fall and code that worked successfully last winter is now failing to connect to the database. No changes has been made to the code since it worked last winter. I also have serveral other ESP8266 boards that write to my database every 10 minutes for years now that still work without issue.

Only my ESP8266 boards fail to connect after the software has been compiled and uploaded to the ESP8266 recently. Boards that have the same software uploaded last in Feb 2021 work without issue. The ESP32 boards work great even after compling the same code from last winter.

I use the Arduino IDE ver 1.8.13 to upload my sketches to the ESPs. I updated the board manager as follows last month: Board updates Arduino AVR Boards 1.8.3 installed > 1.8.4

esp32 1.0.5 installed > 1.0.6

esp8266 2.7.4 installed > 3.0.2

I haven't updated any libraries. MySQL Connector Arduino library is the same version as last winter 1.2.0.

I downgraded the boards to the orginal version but that did not solve the problem.

I get the following from the Serial monitor.

Booting... Connecting to wifi... Connected to Garfield 192.168.0.202 Attempting to connect to db ...trying...

--------------- CUT HERE FOR EXCEPTION DECODER ---------------

Exception (28): epc1=0x40203f9c epc2=0x00000000 epc3=0x00000000 excvaddr=0x00000049 depc=0x00000000

stack>>>

ctx: cont sp: 3ffffa80 end: 3fffffc0 offset: 0190 3ffffc10: 3ffffcb1 00000000 3ffffc70 3fff0184 3ffffc20: 3fff0184 3fff0090 00000001 40203fef 3ffffc30: 40240000 00000000 3ffe8821 40203fdc 3ffffc40: 3fff01c8 3ffeed94 3fff0090 4020d814 3ffffc50: fffffffc feefeffe feefeffe 3ffe8820 3ffffc60: 00000014 3fff008c 000000ff 3fffff18 3ffffc70: 4020d7e0 3ffe8821 3ffee7d0 40209700 3ffffc80: 3ffefcb4 3ffee7d0 00000000 40209724 3ffffc90: 3ffefcb4 3ffee7d0 3ffee7d0 402097b4 3ffffca0: 3ffeed94 00000000 00000000 402043a7 3ffffcb0: 00314500 00005810 40100521 4021c812 3ffffcc0: 000001f8 000001f8 3ffe85dc 40100a5b 3ffffcd0: 00000000 4bc6a7f0 dbe76c8b 00000001 3ffffce0: 3fff022c 00000020 3fff022c 40100c3a 3ffffcf0: 3ffee280 00000000 00000000 40100630 3ffffd00: 00000623 3ffeeafc 0000012c 4021d278 3ffffd10: 000001e3 000001e3 3ffe85dc 40100a5b 3ffffd20: 3ffe8821 00000000 3fff014c 3ffe8821 3ffffd30: 00000000 00000028 00000020 3ffe8821 3ffffd40: 00000000 00000028 3fffff18 402044ea 3ffffd50: 00000003 3ffefcb4 3fffff18 4020425e 3ffffd60: 0000005b 3ffffde0 3ffffd9c 00000003 3ffffd70: 00000001 3ffee904 3fffff18 40203cda 3ffffd80: 4020dd78 6700a8c0 3ffffdb0 3ffffda0 3ffffd90: 3ffffde0 00000cea 3ffe8828 3ffe8821 3ffffda0: 00000000 40202c58 00000084 000000cc 3ffffdb0: 000000a8 000000af 3ffee704 3ffeea94 3ffffdc0: 3fffdad0 3ffee704 3ffffee0 4020131b 3ffffdd0: 000000e8 000000e1 4000050c 3ffe890d 3ffffde0: 4020dd78 6700a8c0 00000000 4021c784 3ffffdf0: 3ffef924 00000008 00000020 40100c64 3ffffe00: 00000018 3ffffe74 00000001 00000000 3ffffe10: 00000002 3ffef1a4 00000004 4021d251 3ffffe20: ffffffff 3fffc6fc 00000001 3ffeed94 3ffffe30: 00000000 4bc6a7f0 4624dd2f 0005adb9 3ffffe40: 00000000 00000000 4bc6a7f0 00000000 3ffffe50: 3ffef924 3ffefe54 40100521 00000000 3ffffe60: 000004e9 0000014c 00000001 4020677e 3ffffe70: 3ffee888 00000000 00000000 3ffe8981 3ffffe80: 3ffe893d 00000000 3fff005c 00000003 3ffffe90: 3ffe893d 41445055 64204554 4d5f6f62 3ffffea0: 6f745379 65676172 6c62742e 616f425f 3ffffeb0: 20736472 20544553 72616f62 73614c64 3ffffec0: 6c6e4f74 20656e69 6f6e203d 2c292877 3ffffed0: 616f6220 72506472 63656a6f 203d2074 3ffffee0: 27732527 6f62202c 49647261 25273d50 3ffffef0: 202c2773 72616f62 43414d64 7325273d 3fffff00: 48572027 20455245 72616f62 20444964 3fffff10: 6425203d 3ffe8900 3fff0184 0000005b 3fffff20: 3fffff3c 3ffefe84 5d357161 19017b17 3fffff30: 4939351d 6d506843 07356925 4020dbc4 3fffff40: 00000000 00001388 3ffee73c 00000000 3fffff50: 00000000 3fff014c 00000000 433a3438 3fffff60: 38413a43 3a46413a 453a3845 39310031 3fffff70: 36312e32 2e302e38 00323032 a8cc84e8 3fffff80: 40e1e8af ca00a8c0 4020d0b8 4020d0b4 3fffff90: 3fffdad0 00000000 3ffee904 40201406 3fffffa0: feefeffe feefeffe 3ffeea80 4020ad00 3fffffb0: feefeffe feefeffe 3ffe85d8 40100eed <<<stack<<<

This is the function that attempts to connect to my database and update a mysql table: void handleUpdatetblBoards() { /***** mysql variables ****/ char UPDATE_DATA[] = "UPDATE dbo_MyStorage.tbl_Boards SET boardLastOnline = now(), boardProject = '%s', boardIP='%s', boardMAC='%s' WHERE boardID = %d"; char query[180];

char myIPAddress[15]; WiFi.localIP().toString().toCharArray(myIPAddress, 15); // convert ip address to char array

byte macAddr[6]; WiFi.macAddress(macAddr); char myMacAddress[18]; sprintf(myMacAddress, "%2X:%2X:%2X:%2X:%2X:%2X", macAddr[0], macAddr[1], macAddr[2], macAddr[3], macAddr[4], macAddr[5]);

/** Create wifi client / WiFiClient client; MySQL_Connection conn((Client *)&client);

Serial.println("Attempting to connect to db"); // this code just added Dec 4, 2021

/** Connect to mysql client /

if (conn.connect(server_addr, 3306, DB_USER, DB_PWD)) { delay(1000); Serial.println("Connected to db"); // this code just added Dec 4, 2021 MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); // Initiate the query class instance sprintf(query, UPDATE_DATA, versionID, myIPAddress, myMacAddress, boardID); // Save //cur_mem->execute(query); // Execute the query delete cur_mem; // Deleting the cursor also frees up memory used } else { // error treatment digitalWrite(led1, LOW); Serial.println("Connection Failed!!!"); // this code just added Dec 4, 2021 delay(60000); ESP.restart(); // Here we tell the ESP to reboot } conn.close();

}

The code fails right at the attempt to connect to the database if (conn.connect(server_addr, 3306, DB_USER, DB_PWD)) It doesn't even go to the else statement where it writes digitalWrite(led1, LOW); and Serial.println("Connection Failed!!!");

This is the model of the ESP8266 KeeYees Development Board WiFi WLAN Wireless Module for ESP8266 NodeMCU ESP-12E CP2102 Compatible with Arduino

I use Board Type: NodeMCU 1.0 (ESP-12E Module). Same as I have always used with these boards. If I comment out the call to handleUpdatetblBoards() the code uploads successfully and it runs without issue.

Please assist.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/185, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGJCWEH4QP4C2HJGVBDUPJFEXANCNFSM5JLY2QOA.

KellyJLW commented 2 years ago

That worked. Thank you. Now getting timed out errors when trying to connect to the database. Again the code is the same that worked in Feb 2021. I'm troubleshooting that now.

I had to move from: if (conn.connect(server_addr, 3306, DB_USER, DB_PWD)) { delay(1000); Serial.println("Connected to db"); // this code just added Dec 4, 2021 MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); // Initiate the query class instance sprintf(query, UPDATE_DATA, versionID, myIPAddress, myMacAddress, boardID); // Save //cur_mem->execute(query); // Execute the query delete cur_mem; // Deleting the cursor also frees up memory used } else { // error treatment digitalWrite(led1, LOW); Serial.println("Connection Failed!!!"); // this code just added Dec 4, 2021 delay(60000); ESP.restart(); // Here we tell the ESP to reboot } conn.close();

to: while (conn.connect(server_addr, 3306, DB_USER, DB_PWD) != true) { delay(200); Serial.print ( "." ); } sprintf(query, UPDATE_DATA, versionID, myIPAddress, myMacAddress, boardID); Serial.println("Recording data."); Serial.println(query); MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); delete cur_mem; conn.close();

Now the connection to the db works and updates the table. Very strange why the old code no longer works when it worked without fail 8 months ago.

Ok now the old code is working. Super confused here.