search

ChuckBell / MySQL_Connector_Arduino

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

what am i doing wrong ? #205

Closed woeperbe closed 1 year ago

woeperbe commented 1 year ago

void writeToSQL(void) { char INSERT[] = "INSERT INTO esolaredge.power (time, daypower) VALUES ('%s',%.1f)"; char query[128]; IPAddress sql_server; HTTPClient sqlclient; MySQL_Connection conn((Client*)&sqlclient);

WiFi.hostByName(EEP.sqlserver, sql_server);

if (debug) SERIAL.print("WriteToSQL: Host found.\r\n");
if (debug) SERIAL.print("WriteToSQL: IP ->");
if (debug) SERIAL.print(sql_server.toString());

if (conn.connect(sql_server, 3306, EEP.sqluser, EEP.sqlpswd, EEP.sqltable)) {
    if (debug) SERIAL.print("WriteToSQL: Host is connected.\r\n");
    delay(500);
    MySQL_Cursor* cur_mem = new MySQL_Cursor(&conn);
    sprintf(query, INSERT, EEP.sqltable, rtc.getDateTime(), lastdaypower);
    if (debug) SERIAL.printf("WriteToSQL: %s\n\r", query);
    cur_mem->execute(query);
    delete cur_mem;
    if (debug) SERIAL.print("WriteToSQL: Data recorded.\r\n");
}
else {
    if (debug) SERIAL.print("WriteToSQL: Connection failed.\r\n");
}
conn.close();

}

i use ESP32 device

this crashes every time on the connect line with:

WriteToSQL: Host found. WriteToSQL: IP ->192.168.1.15...trying... Guru Meditation Error: Core 1 panic'ed (LoadProhibited). Exception was unhandled.

Core 1 register dump: PC : 0x400f6c38 PS : 0x00060330 A0 : 0x800d6695 A1 : 0x3ffb25d0
A2 : 0x3ffb2634 A3 : 0x3ffb261c A4 : 0x00000003 A5 : 0x3ffc5d64
A6 : 0x3ffc51fb A7 : 0x00000000 A8 : 0x00000cea A9 : 0x0f01a8c0
A10 : 0x3ffb271c A11 : 0x0000000c A12 : 0x3ffb26a8 A13 : 0x3ffc4cb8
A14 : 0x00000001 A15 : 0x0000000f SAR : 0x00000004 EXCCAUSE: 0x0000001c
EXCVADDR: 0x00000030 LBEG : 0x4008b221 LEND : 0x4008b231 LCOUNT : 0xffffffff

ChuckBell commented 1 year ago

Hi. I see one thing which may be contributing. We cannot use floating point conversions in Arduino. You have to use dtostrf(). See the examples on the Wiki for how to do this. Other than that, I would make sure you're not running out of memory.

ChuckBell commented 1 year ago

Another possibility: are you calling this method too frequently? In my experience, the Arduino platform(s) cannot handle frequent calls to MySQL (networking isn't all that fast). I recommending calling inserts no more than once per minute and the longer delay the better. Just a suggestion there. ;)

woeperbe commented 1 year ago

On a ESP32 there is floating point conversion. The conversion works fine i tested it

Greetings, Marc Buffet

On 22 Feb 2023, at 16:58, Dr. Charles Bell @.***> wrote:



Hi. I see one thing which may be contributing. We cannot use floating point conversions in Arduino. You have to use dtostrf(). See the examples on the Wiki for how to do this. Other than that, I would make sure you're not running out of memory.

— Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/205#issuecomment-1440305226, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGJ6NWMN3HQAP5HDZGLWYYZSBANCNFSM6AAAAAAVEDMUF4. You are receiving this because you authored the thread.Message ID: @.***>

woeperbe commented 1 year ago

This function will be called one time every 24 hours

Greetings, Marc Buffet

On 22 Feb 2023, at 17:13, Dr. Charles Bell @.***> wrote:



Another possibility: are you calling this method too frequently? In my experience, the Arduino platform(s) cannot handle frequent calls to MySQL (networking isn't all that fast). I recommending calling inserts no more than once per minute and the longer delay the better. Just a suggestion there. ;)

— Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/205#issuecomment-1440330556, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGO3CC24FBWGOOFO5DTWYY3JRANCNFSM6AAAAAAVEDMUF4. You are receiving this because you authored the thread.Message ID: @.***>

ChuckBell commented 1 year ago

Ok. Cool! Then I’d check out the memory to see if you are running low. Sent from my iPadOn Feb 22, 2023, at 11:19, Marc Buffet @.***> wrote: This function will be called one time every 24 hours

Greetings,

Marc Buffet

On 22 Feb 2023, at 17:13, Dr. Charles Bell @.***> wrote:



Another possibility: are you calling this method too frequently? In my experience, the Arduino platform(s) cannot handle frequent calls to MySQL (networking isn't all that fast). I recommending calling inserts no more than once per minute and the longer delay the better. Just a suggestion there. ;)

—

Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/205#issuecomment-1440330556, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGO3CC24FBWGOOFO5DTWYY3JRANCNFSM6AAAAAAVEDMUF4.

You are receiving this because you authored the thread.Message ID: @.***>

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>

woeperbe commented 1 year ago

I am using a esp32 (NodeMcu 32S)

And it is crashing on the line that opens the connection …. The same code on Nodemcu esp8266 is working fine ???

Greetings, Marc Buffet

On 22 Feb 2023, at 17:25, Dr. Charles Bell @.***> wrote:



Ok. Cool! Then I’d check out the memory to see if you are running low. Sent from my iPadOn Feb 22, 2023, at 11:19, Marc Buffet @.***> wrote: This function will be called one time every 24 hours

Greetings,

Marc Buffet

On 22 Feb 2023, at 17:13, Dr. Charles Bell @.***> wrote:



Another possibility: are you calling this method too frequently? In my experience, the Arduino platform(s) cannot handle frequent calls to MySQL (networking isn't all that fast). I recommending calling inserts no more than once per minute and the longer delay the better. Just a suggestion there. ;)

—

Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/205#issuecomment-1440330556, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGO3CC24FBWGOOFO5DTWYY3JRANCNFSM6AAAAAAVEDMUF4.

You are receiving this because you authored the thread.Message ID: @.***>

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>

— Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/205#issuecomment-1440352138, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGODKR5MZWVTSFMDWH3WYY4V5ANCNFSM6AAAAAAVEDMUF4. You are receiving this because you authored the thread.Message ID: @.***>

woeperbe commented 1 year ago

[image0.png]

Greetings, Marc Buffet

On 22 Feb 2023, at 17:25, Dr. Charles Bell @.***> wrote:



Ok. Cool! Then I’d check out the memory to see if you are running low. Sent from my iPadOn Feb 22, 2023, at 11:19, Marc Buffet @.***> wrote: This function will be called one time every 24 hours

Greetings,

Marc Buffet

On 22 Feb 2023, at 17:13, Dr. Charles Bell @.***> wrote:



Another possibility: are you calling this method too frequently? In my experience, the Arduino platform(s) cannot handle frequent calls to MySQL (networking isn't all that fast). I recommending calling inserts no more than once per minute and the longer delay the better. Just a suggestion there. ;)

—

Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/205#issuecomment-1440330556, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGO3CC24FBWGOOFO5DTWYY3JRANCNFSM6AAAAAAVEDMUF4.

You are receiving this because you authored the thread.Message ID: @.***>

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>

— Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/205#issuecomment-1440352138, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGODKR5MZWVTSFMDWH3WYY4V5ANCNFSM6AAAAAAVEDMUF4. You are receiving this because you authored the thread.Message ID: @.***>

woeperbe commented 1 year ago

I can give you the complete source if you like …. The sql database is accessible on the internet Also the needed solaredge api is included to test

Greetings, Marc Buffet

On 22 Feb 2023, at 17:25, Dr. Charles Bell @.***> wrote:



Ok. Cool! Then I’d check out the memory to see if you are running low. Sent from my iPadOn Feb 22, 2023, at 11:19, Marc Buffet @.***> wrote: This function will be called one time every 24 hours

Greetings,

Marc Buffet

On 22 Feb 2023, at 17:13, Dr. Charles Bell @.***> wrote:



Another possibility: are you calling this method too frequently? In my experience, the Arduino platform(s) cannot handle frequent calls to MySQL (networking isn't all that fast). I recommending calling inserts no more than once per minute and the longer delay the better. Just a suggestion there. ;)

—

Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/205#issuecomment-1440330556, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGO3CC24FBWGOOFO5DTWYY3JRANCNFSM6AAAAAAVEDMUF4.

You are receiving this because you authored the thread.Message ID: @.***>

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>

— Reply to this email directly, view it on GitHubhttps://github.com/ChuckBell/MySQL_Connector_Arduino/issues/205#issuecomment-1440352138, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKXOBGODKR5MZWVTSFMDWH3WYY4V5ANCNFSM6AAAAAAVEDMUF4. You are receiving this because you authored the thread.Message ID: @.***>

woeperbe commented 1 year ago

I found the problem ;-)

i used HTTPClient in place of WiFiClient

char INSERT[] = "INSERT INTO %s (time, daypower) VALUES ('%s',%.1f)"; char query[128]; IPAddress sql_server; WiFiClient sqlclient; MySQL_Connection conn((Client*)&sqlclient);

WiFi.hostByName(EEP.sqlserver, sql_server);

if (debug) SERIAL.print("WriteToSQL: Host found.\r\n");
if (debug) SERIAL.print("WriteToSQL: IP ->");
if (debug) SERIAL.print(sql_server.toString());

if (conn.connect(sql_server, 3306, EEP.sqluser, EEP.sqlpswd)) {
    if (debug) SERIAL.print("WriteToSQL: Host is connected.\r\n");
    delay(500);
    MySQL_Cursor* cur_mem = new MySQL_Cursor(&conn);
    sprintf(query, INSERT, EEP.sqltable, rtc.getDateTime().c_str(), lastdaypower);
    if (debug) SERIAL.printf("WriteToSQL: %s\n\r", query);
woeperbe commented 1 year ago

You can close the case