ERROR: Class requires connected server (ESP32)

[oussama415]

Hello, please I am working on a project using the library MySQL_Connector_Arduino in its latest version 1.2.0 with ESP32 , and I encounter an error during the insertion or updating in the database.

The problem is that this error does not appear all the time, sometimes it sometimes appears not despite that I have an excellent shared wifi here is the error: ERROR: Class requires connected server.

Please i need your help

Thanks in advance.

[ChuckBell]

The connection to the server is dropping. Try moving the connect/disconnect to the loop like I demonstrate on the Wiki. You want to connect at the start of the loop, do your work, update/insert/etc with MySQL, then disconnect (close the connection and cursors) then delay and.. repeat. See examples.

On Sun, Jun 14, 2020 at 10:21 AM oussama415 notifications@github.com wrote:

Hello, please I am working on a project using the library MySQL_Connector_Arduino in its latest version 1.2.0 with ESP32 , and I encounter an error during the insertion or updating in the database.

The problem is that this error does not appear all the time, sometimes it sometimes appears not despite that I have an excellent shared wifi here is the error: ERROR: Class requires connected server.

Please i need your help

Thanks in advance.

— 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/143, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYCVD4QVC7HVXWSPOQTRWTMINANCNFSM4N5PC3ZA .

[ChuckBell]

That assumes you're using Oracle's MySQL. Some variants exhibit this behaviour and the connect/disconnect can help with that incompatibility.

On Sun, Jun 14, 2020 at 3:11 PM Charles Bell drcharlesbell@gmail.com wrote:

The connection to the server is dropping. Try moving the connect/disconnect to the loop like I demonstrate on the Wiki. You want to connect at the start of the loop, do your work, update/insert/etc with MySQL, then disconnect (close the connection and cursors) then delay and.. repeat. See examples.

On Sun, Jun 14, 2020 at 10:21 AM oussama415 notifications@github.com wrote:

Hello, please I am working on a project using the library MySQL_Connector_Arduino in its latest version 1.2.0 with ESP32 , and I encounter an error during the insertion or updating in the database.

The problem is that this error does not appear all the time, sometimes it sometimes appears not despite that I have an excellent shared wifi here is the error: ERROR: Class requires connected server.

Please i need your help

Thanks in advance.

— 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/143, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYCVD4QVC7HVXWSPOQTRWTMINANCNFSM4N5PC3ZA .

[oussama415]

Thanks for your reply Sir.

I'm really lost with this problem, I tried to do what you told me, I did two tests it worked very well after the problem appeared again. When I connect with the database, it shows me this in the serial monitor Connected to server version 5.5.5-10.4.13-MariaDB

here is my code:

bool write_data(int id, float temp, double Irms, float puissance, float energie, const char *dateStr)
{

  if (conn.connected()) {
    MySQL_Cursor *cur = new MySQL_Cursor(&conn);

    delay(100);

    //  String queryStr = String("INSERT INTO db.mesures VALUES (id,"); // 1 : id client changeable // Version 2
    String queryStr = String("INSERT INTO db.mesures VALUES ("); // 1 : id client changeable // Version 
    queryStr += String(id);
    queryStr += ",";
    queryStr += String(temp);
    queryStr += ",";
    queryStr += String(Irms);
    queryStr += ",";
    queryStr += String(puissance);
    queryStr += ",";
    queryStr += String(energie);
    queryStr += ",";
    if (dateStr != "NULL") queryStr += "'"; // quote string for values
    queryStr += dateStr;
    if (dateStr != "NULL") queryStr += "'"; // quote string for values
    queryStr += ")";
    // write the data here
    Serial.println(queryStr);
    // Create an instance of the cursor passing in the connection
    cur->execute(queryStr.c_str()); 
    delete cur; 

  } else {
    Serial.println("Connect failed. Trying again on next iteration.");
    conn.close();

  }

}

[ChuckBell]

You are missing the connect(). See https://github.com/ChuckBell/MySQL_Connector_Arduino/wiki/Tips-And-Tricks#use-connectclose-in-loop.

Also, be advised that the connector is not guaranteed to work with non-Oracle variants. I don’t think that’s the issue here, but it has known to cause issues with certain queries.

On Jun 14, 2020, at 8:55 PM, oussama415 notifications@github.com wrote:

 I'm really lost with this problem, I tried to do what you told me, I did two tests it worked very well after the problem appeared again. When I connect with the database, it shows me this in the serial monitor Connected to server version 5.5.5-10.4.13-MariaDB

here is my code:

bool write_data(int id, float temp, double Irms, float puissance, float energie, const char *dateStr) {

if (conn.connected()) { MySQL_Cursor *cur = new MySQL_Cursor(&conn);

delay(100);

//  String queryStr = String("INSERT INTO db.mesures VALUES (id,"); // 1 : id client changeable // Version 2
String queryStr = String("INSERT INTO db.danone_mesures VALUES ("); // 1 : id client changeable // Version 1
queryStr += String(id);
queryStr += ",";
queryStr += String(temp);
queryStr += ",";
queryStr += String(Irms);
queryStr += ",";
queryStr += String(puissance);
queryStr += ",";
queryStr += String(energie);
queryStr += ",";
if (dateStr != "NULL") queryStr += "'"; // quote string for values
queryStr += dateStr;
if (dateStr != "NULL") queryStr += "'"; // quote string for values
queryStr += ")";
// write the data here
Serial.println(queryStr);
// Create an instance of the cursor passing in the connection
cur->execute(queryStr.c_str()); 
delete cur; 

} else { Serial.println("Connect failed. Trying again on next iteration."); conn.close();

}

} — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

[ChuckBell]

You may want to experiment by taking out the sting class and use a c string. I am concerned about how the floats are being formatted. Either that or print the query string and attempt to execute it with the MySQL client to test that it works correctly.

I’d also check to make sure you’re not running out of memory.

On Jun 14, 2020, at 8:55 PM, oussama415 notifications@github.com wrote:

 I'm really lost with this problem, I tried to do what you told me, I did two tests it worked very well after the problem appeared again. When I connect with the database, it shows me this in the serial monitor Connected to server version 5.5.5-10.4.13-MariaDB

here is my code:

bool write_data(int id, float temp, double Irms, float puissance, float energie, const char *dateStr) {

if (conn.connected()) { MySQL_Cursor *cur = new MySQL_Cursor(&conn);

delay(100);

//  String queryStr = String("INSERT INTO db.mesures VALUES (id,"); // 1 : id client changeable // Version 2
String queryStr = String("INSERT INTO db.danone_mesures VALUES ("); // 1 : id client changeable // Version 1
queryStr += String(id);
queryStr += ",";
queryStr += String(temp);
queryStr += ",";
queryStr += String(Irms);
queryStr += ",";
queryStr += String(puissance);
queryStr += ",";
queryStr += String(energie);
queryStr += ",";
if (dateStr != "NULL") queryStr += "'"; // quote string for values
queryStr += dateStr;
if (dateStr != "NULL") queryStr += "'"; // quote string for values
queryStr += ")";
// write the data here
Serial.println(queryStr);
// Create an instance of the cursor passing in the connection
cur->execute(queryStr.c_str()); 
delete cur; 

} else { Serial.println("Connect failed. Trying again on next iteration."); conn.close();

}

} — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

[ReinhardDaemon]

Hi,

I am having the same problem. I make queries to my remote DB every 10 seconds (INSERT INTO ..). I print the query string to the serial monitor, as well, and the query string is OK. Sometimes it runs for hours, sometimes only for minutes. I currently check, if WiFi is still connected, immediately before the query is done:

` int status;

    status = WiFi.status();

    Serial.print("   WIFI status: ");

    if (status == WL_CONNECTED) {

      Serial.println("CONNECTED");

    }

    if (status != WL_CONNECTED) {

      Serial.println("ERROR");

    }`

Can not tell you the result so far, it is still running... UPDATE: Now it happened after approximately 1 hour, but WiFi was always connected (and is still)

Are MySQL DBs known to have timeouts or disconnect a user after some time, after some queries? (for "security" reasons)?

[oussama415]

Thanks so Much @ChuckBell it works for me after adding the connect function. The problem is that after each disconnection the program leaves the insertion function, after when I added the connect function, it always searches, it connects, then it does the processing

Thank you very much.

[oussama415]

@ReinhardDaemon You should add the connect function like that :

 MySQL_Cursor *cur = new MySQL_Cursor(&conn);

  delay(100); 
  if (conn.connected()) {
      // do something your work 

    cur->execute(query); 
    delete cur; 
  } else {
    conn.close();
    Serial.println("Connecting...");
    delay(200); //
    if (conn.connect(server_addr, 3306, db_user, db_password)) {
      delay(500);
      Serial.println("Successful reconnect!");
    } else {
      Serial.println("Cannot reconnect! Drat.");
    }
  }

and it will work fine

[ReinhardDaemon]

@oussama415

Thank you for the input. Now, in my main loop I always check for WiFi and MySQL DB connect and will do a reconnect, if necessary. Test is just running. I will inform you about the result. As I mentioned, sometimes it happens after minutes, sometimes after 1 hour. So we have to be patient...

UPDATE: After 4991 seconds: WIFI status: CONNECTED MYSQL CONNECT STATUS: 0 ERROR Trying to reconnect to MySQL DB... Connecting to MySQL ... ...trying... Connected to server version 5.7.30-0ubuntu0.16.04.1 Successful reconnect!

[ChuckBell]

Closed due to inactivity. Please open a new ticket if this is still relevant.