Closed madmacks59 closed 2 years ago
I just saw the details in issue #85 and will add the flush statements you mentioned into the .cpp file and see if that helps.
I've opened MySQL_Cursor.cpp (Ver 1.1.1a) and MySQL_Cursor.cpp (Ver 1.1.1a) and I'd like to confirm the line references you want the Flush statements add after.
In MySQL_Cursor.cpp you say insert at line 140, and that looks like a valid location. In MySQL_Packet.cpp you say insert at line 158, but that definitely doesn't look reasonable (hanging outside the surrounding functions).
Here's a cut/paste from the other issue (#85)
MySQL_Cursor.cpp @ line#140
...
conn->client->write((uint8_t*)conn->buffer, query_len + 5);
conn->client->flush();
...
And in MySQL_Packet.cpp @ line#158
...
client->write((uint8_t*)buffer, size_send);
client->flush();
...
Can you confirm you're referencing versions 1.1.1a of the library files? And would it be possible to add a few previous/post lines of code from the libraries wrapping the new statements so I can be sure I'm adding the code in the correct place?
Inadvertently closed...so reopening...
I see a couple of things here. First, I have to tell you that the connector is not guaranteed to work with any variant of MySQL. It was not designed to support anything other than Oracle's MySQL. In fact, I cannot help with any issues directly related to using a non-Oracle variant of MySQL.
That said, the common practice in long-running sketches is to open and close the connection to MySQL only when you need it. So, move the connect to inside the loop() and use it only when you must read/write the data from/to MySQL. That should help greatly in stability (unless the problem is in the variant code).
Second, this is the diff for the 1.1.1a code:
diff --git a/src/MySQL_Cursor.cpp b/src/MySQL_Cursor.cpp index 3f7b39d..ba6641b 100644 --- a/src/MySQL_Cursor.cpp +++ b/src/MySQL_Cursor.cpp @@ -138,6 +138,7 @@ boolean MySQL_Cursor::execute_query(int query_len)
// Send the query conn->client->write((uint8_t*)conn->buffer, query_len + 5);
conn->client->flush();
// Read a response packet and check it for Ok or Error. conn->read_packet(); diff --git a/src/MySQL_Packet.cpp b/src/MySQL_Packet.cpp index 9272859..af70a05 100644 --- a/src/MySQL_Packet.cpp +++ b/src/MySQL_Packet.cpp @@ -153,6 +153,7 @@ void MySQL_Packet::send_authentication_packet(char user, char password)
// Write the packet client->write((uint8_t*)buffer, size_send);
Use this as a guide to make the changes. In other words, add only those lines with '+' (minus the +) at the location described by the context (lines without markings). As you can see, the flush() calls are after the write() calls.
Hmmm... + came out as a dot. So, use the dot, Luke...
LOL...I will master...
Couple of things. I will try (do, not try) and update the .cpp files using the diff info you provided, which is greatly appreciated. And, I will start looking for an Oracle MySQL package for my server. I’m running “MySQL” on a RaspberPi and AFAIKT the package built for Pi doesn’t use “My” but rather “Maria”, and as we all know daughters aren’t identical at a genetic level even if they have the same parents.
Most people I’ve checked with say “but they’re functionally identical” and “Maria is even better” but I wonder if, at the binary level, that is in fact true. And I suppose your drivers work at the lower level and not at the call level.
Also, I’ll adopt the processing changes you recommend. I’m new to the “Arduino” world so best practices on that platform are new to me. I’m used to opening DB connections and maintaining them for the life of the program so as to cut down on overhead calls, but I guess on my set up that’s really not a big deal and if the recommended changes improves stability I’m all for it.
Thanks again, and I’ll post back my results...
And, thank you very!
So, I updated MySQL_Cursor.cpp as follows:
boolean MySQL_Cursor::execute_query(int query_len) { ... //// Send the query (ORIGINAL BLOCK) //for (int c = 0; c < query_len+5; c++) // conn->client->write(conn->buffer[c]);
// Send the query (NEW BLOCK) for (int c = 0; c < query_len+5; c++) { conn->client->write(conn->buffer[c]); conn->client->flush(); } ... }
and MySQL_Packet.cpp
//// Write the packet (ORIGINAL PACKET) //for (int i = 0; i < size_send; i++) // client->write(buffer[i]);
// Write the packet (NEW PACKET) for (int i = 0; i < size_send; i++) { client->write(buffer[i]); client->flush(); }
These changes didn't seem to help stabilize the program and I still had DB lock ups as the program ran over longer periods of time.
I then made the code changes you recommended by moving the db connect into the main loop and now connect and disconnect each time I write to the DB. This seems to help a lot, but I still connection fails. I am able to trap these connection problems in the logic and try to reset things and keep processing. I also added a set of counters to track sensor errors and DB connect/write errors. When a set threshold of errors is reached I am now trying to reset the Arduino (and it's shields) via software. Because it's a software reset I'm not sure it will resolve all issues, but it's a step in a better direction. If that doesn't work I'll work on setting up a triggerable power reset that will cut and reset power via a pin trigger on the Arduino. I'll post my updated code once I get a set of better code runs completed.
I've also started trying to find a stable build of "Oracle" MySQL for my Raspberry Pi so I can try to eliminate MariaDB as a possible source of instability. But I do have one request/thought. As most of the open source world has left the "Oracle fork" of MySQL wouldn't it be a good idea to look at supporting MariaDB officially? I know that Raspbian and many other Linux derivatives now use MariaDB rather than Oracle... Just a thought, and thank you for your "unofficial support" and suggestions; they're really appreciated!
I’ll try and reason out some suggestions later, but PM me about the Oracle MySQL issue if you’d like. d r c h a r l e s b e l l at g m a i l dot c o m
On Mar 21, 2019, at 6:51 PM, madmacks59 notifications@github.com wrote:
So, I updated MySQL_Cursor.cpp as follows:
boolean MySQL_Cursor::execute_query(int query_len) { ... //// Send the query (ORIGINAL BLOCK) //for (int c = 0; c < query_len+5; c++) // conn->client->write(conn->buffer[c]);
// Send the query (NEW BLOCK) for (int c = 0; c < query_len+5; c++) { conn->client->write(conn->buffer[c]); conn->client->flush(); } ... }
and MySQL_Packet.cpp
//// Write the packet (ORIGINAL PACKET) //for (int i = 0; i < size_send; i++) // client->write(buffer[i]);
// Write the packet (NEW PACKET) for (int i = 0; i < size_send; i++) { client->write(buffer[i]); client->flush(); }
These changes didn't seem to help stabilize the program and I still had DB lock ups as the program ran over longer periods of time.
I then made the code changes you recommended by moving the db connect into the main loop and now connect and disconnect each time I write to the DB. This seems to help a lot, but I still connection fails. I am able to trap these connection problems in the logic and try to reset things and keep processing. I also added a set of counters to track sensor errors and DB connect/write errors. When a set threshold of errors is reached I am now trying to reset the Arduino (and it's shields) via software. Because it's a software reset I'm not sure it will resolve all issues, but it's a step in a better direction. If that doesn't work I'll work on setting up a triggerable power reset that will cut and reset power via a pin trigger on the Arduino. I'll post my updated code once I get a set of better code runs completed.
I've also started trying to find a stable build of "Oracle" MySQL for my Raspberry Pi so I can try to eliminate MariaDB as a possible source of instability. But I do have one request/thought. As most of the open source world has left the "Oracle fork" of MySQL wouldn't it be a good idea to look at supporting MariaDB officially? I know that Raspbian and many other Linux derivatives now use MariaDB rather than Oracle... Just a thought, and thank you for your "unofficial support" and suggestions; they're really appreciated!
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/93#issuecomment-475432604, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4OL2_TkQXMxm9-Pw2yvnuAEFBVHeks5vZA0OgaJpZM4b3fPA.
I am glad my suggestions have helped and I think you're on the right track. The software or watchdog reset has worked for me in those cases where I want the Arduino to restart in the event of a catastrophic failure (like dropping connections, slow/contested network, power, etc.). Please keep us posted on your progress.
On 3/21/19 6:51 PM, madmacks59 wrote:
So, I updated MySQL_Cursor.cpp as follows:
boolean MySQL_Cursor::execute_query(int query_len) { ... //// Send the query (ORIGINAL BLOCK) //for (int c = 0; c < query_len+5; c++) // conn->client->write(conn->buffer[c]);
// Send the query (NEW BLOCK) for (int c = 0; c < query_len+5; c++) { conn->client->write(conn->buffer[c]); conn->client->flush(); } ... }
and MySQL_Packet.cpp
//// Write the packet (ORIGINAL PACKET) //for (int i = 0; i < size_send; i++) // client->write(buffer[i]);
// Write the packet (NEW PACKET) for (int i = 0; i < size_send; i++) { client->write(buffer[i]); client->flush(); }
These changes didn't seem to help stabilize the program and I still had DB lock ups as the program ran over longer periods of time.
I then made the code changes you recommended by moving the db connect into the main loop and now connect and disconnect each time I write to the DB. This seems to help a lot, but I still connection fails. I am able to trap these connection problems in the logic and try to reset things and keep processing. I also added a set of counters to track sensor errors and DB connect/write errors. When a set threshold of errors is reached I am now trying to reset the Arduino (and it's shields) via software. Because it's a software reset I'm not sure it will resolve all issues, but it's a step in a better direction. If that doesn't work I'll work on setting up a triggerable power reset that will cut and reset power via a pin trigger on the Arduino. I'll post my updated code once I get a set of better code runs completed.
I've also started trying to find a stable build of "Oracle" MySQL for my Raspberry Pi so I can try to eliminate MariaDB as a possible source of instability. But I do have one request/thought. As most of the open source world has left the "Oracle fork" of MySQL wouldn't it be a good idea to look at supporting MariaDB officially? I know that Raspbian and many other Linux derivatives now use MariaDB rather than Oracle... Just a thought, and thank you for your "unofficial support" and suggestions; they're really appreciated!
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/93#issuecomment-475432604, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4OL2_TkQXMxm9-Pw2yvnuAEFBVHeks5vZA0OgaJpZM4b3fPA.
Hi there Something like that here. The only thing I've checked looking at mysql logs is that there is no proper connection closing even calling close() method. The connection is closed for the client, but I cannot see a "Quit" in the server logs as I see if I do the same with php. After a while, a cascade of aborted connections in the logs that, surprisingly, do their thing (INSERT in my case). I think the problem is to send that "quit" packet to the server, but my code skills are not enough to build that by myself. Or maybe I'm completely wrong... Maybe if you, Dr. Bell, or anyone else could guide me I'll try to do it. Regards
Hi. I suggest looking at the send_authentication_packet() is MySQL_Packet.h/.cpp and similar methods.
On Jan 6, 2020, at 7:39 AM, Miguel Ángel Contreras notifications@github.com wrote:
Hi there Something like that here. The only thing I've checked looking at mysql logs is that there is no proper connection closing even calling close() method. The connection is closed for the client, but I cannot see a "Quit" in the server logs as I see if I do the same with php. After a while, a cascade of aborted connections in the logs that, surprisingly, do their thing (INSERT in my case). I think the problem is to send that "quit" packet to the server, but my code skills are not enough to build that by myself. Or maybe I'm completely wrong... Maybe if you, Dr. Bell, or anyone else could guide me I'll try to do it. Regards
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/93?email_source=notifications&email_token=AB6SHYGOCOD7CUV3FFUVSFTQ4MRAVA5CNFSM4G656PAKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEIFKQNY#issuecomment-571123767, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYG3JGF3YPQCOZ6V6FDQ4MRAVANCNFSM4G656PAA.
I have a project that involves a Mega2560, Ethernet 2 Shield, Freetronics 1602 LCD and a ultrasonic sensor. The project is reading the level of my well tank and then updating a MySQL DB on a periodic basis. Everything has been working so far but a couple of days ago I started QA testing the project by leaving it running 24x7 in my off grid cabin prior to installing in the water tank. I started getting random hang, sometime after only 10-15 minutes, sometimes after hours or process. For testing I have the sketch reading and updating every minute; but only update to the DB if the sensor readings have changed since the last DB write. I had issues with the sensor being unstable and had to update the code to use a library called NewPing, that seemed to resolve the out of bounds sensor readings. Now I'm seeing my DB writes hang the sketch, randomly. I've started monitoring my MySQL (10.1.37-MariaDB-0+deb9u1) logs and see the connections and inserts running. I don't see any fails or the DB dropping the user, so as far as I can tell I'm still maintaining a DB connection. I've also monitored my router to make sure the Arduino is connected (as well as the MySQL server), and all seems OK. Based on adding a slew of more LCD display I see the process hang at the "Writing To DB" message which is directly before my "cur_mem->execute(query);" statement. Right now I'm stuck! Below is the sketch if you have time to take a look. If you have any questions or comments please let me know. And if there's a better way to provide a readable version of the sketch let me know. The below looks like a mess without the formatting...ugh...