conn.connect(server_addr, 3306, user, password) fails after 8 mysql inserts

[chakthy]

conn.connect(server_addr, 3306, user, password) stops connecting to Mysql server after running the loop 8 times (8 record INSERT). The Arduino uno wifi rev2 continues running the rest of the code contained in the loop, but it fails to connect to the mysql server when requested. Resetting the Arduino solves the problem until 8 more records are created. I used " FREERAM_PRINT" to see if the issue was related to memory but it wasn't the case. What I noticed is that every time the INSERT occurs the next one seems to be slower. Could it be that the WiFi communication is getting clogged until nothing can go through?

[ChuckBell]

Possibly. Are you connecting and disconnecting in the loop() method with a delay at the end? That seems to help. Dr Bell

On Mon, Feb 18, 2019 at 6:59 PM chakthy notifications@github.com wrote:

conn.connect(server_addr, 3306, user, password) stops connecting to Mysql server after running the loop 8 times (8 record INSERT). The Arduino uno wifi rev2 continues running the rest of the code contained in the loop, but it fails to connect to the mysql server when requested. Resetting the Arduino solves the problem until 8 more records are created. I used " FREERAM_PRINT" to see if the issue was related to memory but it wasn't the case. What I noticed is that every time the INSERT occurs the next one seems to be slower. Could it be that the WiFi communication is getting clogged until nothing can go through?

— 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/85, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4IsHHxLeDyXudeZr6cEx7spHweVjks5vOz5ggaJpZM4bB1ET .

[chakthy]

I'm disconnecting at the end of the loop. I did add delays to make sure I have enough time to get everything inserted. I have checked the database and there are no open connections left behind. I added code to go back to the beginning of the code, but the only thing that seems to work at the moment is close and restart the WiFi connection.

[ChuckBell]

I've tested with this code:

void loop() { Serial.println("------------------------------------"); Serial.print("Connecting to server version: "); if (conn.connect(server_addr, 3306, user, password, "world")) { delay(1000); loop_count++; Serial.print(" > iteration# "); Serial.println(loop_count); } else Serial.println("Connection failed."); Serial.println("Disconnecting."); conn.close(); delay(2000); }

It has run hundreds of iteration with a failure rate of < 3%. So, out of 100 attempts it fails 2 or 3 times.

Here's some sample output from the serial monitor: ...

Connecting to server version: 8.0.13

iteration# 116 Disconnecting.

Connecting to server version: 8.0.13 iteration# 117 Disconnecting.

...

I also added an insert statement to simply insert data in a small table as follows. It too runs for many iterations.

CREATE TABLE t1 ( a int(11) NOT NULL AUTO_INCREMENT, b int(11) DEFAULT NULL, PRIMARY KEY (a) ) ENGINE=InnoDB;

... char query[] = "INSERT INTO test_arduino.t1 VALUES (NULL, %d)"; int loop_count = 0; char buffer[256]; ... void loop() { Serial.println("------------------------------------"); Serial.print("Connecting to server version: "); if (conn.connect(server_addr, 3306, user, password, "world")) { delay(1000); loop_count++; MySQL_Cursor *cur = new MySQL_Cursor(&conn); Serial.print(" > iteration# "); Serial.println(loop_count); sprintf(buffer, query, loop_count); Serial.print(" > "); Serial.println(buffer); cur->execute(buffer); cur->close(); delete cur; } else Serial.println("Connection failed."); Serial.println("Disconnecting."); conn.close(); delay(2000); }

This test runs a lot longer then 8 inserts, but of the test runs I made, the average number of inserts until the failure (lockup) is about 35.

So, then I added a flush() in the code and that seemed to help. Add it in two places:

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 try these tests and see what you get? With the flush(), I got fewer connection failures and all INSERTS worked without lockups (well, up to about 125 iterations before I stopped the test).

[chakthy]

I did try the flush directly into the sketch but made no difference on my code. I'll try your recommendations on making the changes on the other files. I'm not sure if the amount of variables on my Insert might be causing a lower number compared to what you got before getting it to lockup . INSERT INTO RFID.log (var1,var2,var3,var4, date) VALUES ('%d','%d','%d','%d',NOW())"

[ChuckBell]

How long is the resulting INSERT string? Can you post an example?

On 2/25/19 6:24 PM, chakthy wrote:

I did try the flush directly into the sketch but made no difference on my code. I'll try your recommendations on making the changes on the other files. I'm not sure if the amount of variables on my Insert might be causing a lower number compared to what you got before getting it to lockup . INSERT INTO RFID.log (var1,var2,var3,var4, date) VALUES ('%d','%d','%d','%d',NOW())"

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/85#issuecomment-467225785, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4DVrzWKIrULVJtodnqW0IHpnvthVks5vRHCSgaJpZM4bB1ET.

[chakthy]

Something like this INSERT INTO RFID.log (var1,var2,var3,var4, date) VALUES ('115','47','56','36',NOW())"

[ChuckBell]

Well, that doesn’t seem too long to me. Just check that your variable where you store the string isn’t overflowing. Beyond that, any luck?

On Feb 26, 2019, at 9:58 PM, chakthy notifications@github.com wrote:

Something like this INSERT INTO RFID.log (var1,var2,var3,var4, date) VALUES ('115','47','56','36',NOW())"

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/85#issuecomment-467704236, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4I6xz_RnxGcJlHUp9hZBIjdnleGVks5vRfRhgaJpZM4bB1ET.

[chakthy]

Just to make sure the changes you did were before // Read the rows res = get_row_values(); if (res != MYSQL_EOF_PACKET) { return &row; }

and after // Write the packet for (int i = 0; i < size_send; i++) client->write(buffer[i]); }

[chakthy]

I want to try the same code using the Ethernet connection and see if it fails too. I got a ENC28J60 module, not sure if the Ethernet.h library works with that. I was trying to use your basic_insert code.

[ChuckBell]

Just to make sure the changes you did were before // Read the rows res = get_row_values(); if (res != MYSQL_EOF_PACKET) { return &row; }

and after // Write the packet for (int i = 0; i < size_send; i++) client->write(buffer[i]); }

Sorry, I've lost the thread on this. We were talking about adding a flush(). Was this something different?

[chakthy]

That code is from the files you recommended to change to add the flush(). I was unsure the changes were supposed to be on those lines.

[ChuckBell]

Here is the diff. Use it as your guide. It shows the context of where the new lines (marked with +) go.

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);

• client->flush(); }

[ChuckBell]

Hmmm... + came out as a dot. So, use the dot, Luke...

[chakthy]

Thanks for all the help and I apologize for not getting back to you sooner. It looks like the connection is more stable but still a little bit unreliable. I started to do some tests using the ethernet connection with the Arduino UNO and the ENC28J60. I used the code below. For some reasons I get the board connected to the network but then it gets disconnected. Sometimes it doesn't even connect.

include

include

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(x,x,x,x); // IP of the MySQL server here char user[] = "user"; // MySQL user login username char password[] = "password"; // MySQL user login password

EthernetClient client; MySQL_Connection conn((Client *)&client);

void setup() { Serial.begin(115200); while (!Serial); // wait for serial port to connect

Ethernet.begin(mac_addr,IPAddress(X,X,X,X)); Serial.println("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); // You would add your code here to run a query once on startup. } else Serial.println("Connection failed."); conn.close(); }

void loop() {

}

Because it was behaving erratically I used this code to test the ethernet connection first, which works OK. I left some pieces of code because for some reason if I remove the code that I was not using from the "loop" function, the code compiles but doesn't work. On the code I added the mysqlconnector but it prevents the board to connect to the network. Any ideas on what might be causing the problem?

define MACADDRESS 0x00,0x01,0x02,0x03,0x04,0x05

define MYIPADDR 192,168,15,17

define MYIPMASK 255,255,255,0

define MYDNS 8,8,8,8

define MYGW 192,168,15,1

define LISTENPORT 1000

define UARTBAUD 115200

if defined(MBED)

include

include "mbed/millis.h"

define delay(x) wait_ms(x)

define PROGMEM

include "mbed/Print.h"

endif

include

// The connection_data struct needs to be defined in an external file.

include

include

include "utility/logging.h"

include

uint8_t mac[6] = {MACADDRESS}; uint8_t myIP[4] = {MYIPADDR}; uint8_t myMASK[4] = {MYIPMASK}; uint8_t myDNS[4] = {MYDNS}; uint8_t myGW[4] = {MYGW};

//byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(X,X,X,X); // IP of the MySQL server here char user[] = "user"; // MySQL user login username char password[] = "password"; // MySQL user login password

EthernetClient client; MySQL_Connection conn((Client *)&client);

EthernetServer server = EthernetServer(LISTENPORT);

if defined(ARDUINO)

void setup() {

endif

if defined(MBED)

int main() {

endif

Serial.begin(115200); // Serial.println("Connecting..."); // initialize the ethernet device //Ethernet.begin(mac,myIP); Ethernet.begin(mac,myIP,myDNS,myGW,myMASK); // start listening for clients server.begin();

//if (conn.connect(server_addr, 3306, user, password)) { // delay(1000); // You would add your code here to run a query once on startup. // } // else // Serial.println("Connection failed."); // conn.close();

if defined(ARDUINO)

}

void loop() {

endif

if defined(MBED)

while(true) {

endif

size_t size;

if (EthernetClient client = server.available()) { if (client) { while((size = client.available()) > 0) { uint8_t msg = (uint8_t)malloc(size); size = client.read(msg,size);
} } } }

if defined(MBED)

}

endif

[ivaring]

Hi there, I have to say, awesome library!, thank you very much. One question about it, I need to get data from Arduino to a MySQL server installed on Raspberry, so the access to server is going to be through localhost and not from remote (at leas by the time). Is it possible to configure MySQL Connector Library for sending data to the server using serial between Arduino and Raspberry instead of having an Ethernet Connection?, thanks!.

[ivaring]

Hi there again, i tried to connect my Arduino to an Apache Raspberry Server with MySQL and phpmyadmin but it was unsatisfactory. I always get connection failed message from MySQL connector library on the sketch, I'm ablet get connected to the database through any browser from other PC on the same network. All privileges are granted on the user, I configured all devices with an Ethernet connection so I can't figure what is happening.