Open/Close the connection eats 24 bytes of free Memory

[GitHubUserEH]

Hi

I use this great library (version 1.2.0) to connect my ARDUINO MEGA2560 with the Ethernet Shield to a MariaDB on my ASUSTOR NAS. This works very well. I only add records (temperature readings) with the INSERT command. To do this I open a connection to the SQL, write the data set into it and close the connection. I noticed that each open and close of the database 'eats' exactly 24 bytes of memory. Since the data acquisition should run for months without any intervention, I deliberately chose to open and close the connection.

Minimal Code in a loop which reproduces the problem:

conn.connect(server_addr, 3306, user, password); cur_mem->close();
delete cur_mem; conn.close(); delay(5000);

How can I prevent that 24 bytes of free Ram are 'lost' each time?

Thanks Eric

[ChuckBell]

Hi. Thanks for the tip. I think I’ve found the leak. I’ll let you know when I get it fixed. Or maybe if I find a workaround. Stay tuned.

On Aug 19, 2020, at 4:21 AM, GitHubUserEH notifications@github.com wrote:

 Hi

I use this great library (version 1.2.0) to connect my ARDUINO MEGA2560 with the Ethernet Shield to a MariaDB on my ASUSTOR NAS. This works very well. I only add records (temperature readings) with the INSERT command. To do this I open a connection to the SQL, write the data set into it and close the connection. I noticed that each open and close of the database 'eats' exactly 24 bytes of memory. Since the data acquisition should run for months without any intervention, I deliberately chose to open and close the connection.

Minimal Code in a loop which reproduces the problem:

conn.connect(server_addr, 3306, user, password); cur_mem->close(); delete cur_mem; conn.close(); delay(5000);

How can I prevent that 24 bytes of free Ram are 'lost' each time?

Thanks Eric

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

[GitHubUserEH]

Hi Charles

Thanks for fast respond. If You can fix the problem it is great. Perhaps you can fix a other problem as well. In the File MySQL_Cursor.h there is the tip to comment the Line #define WITH_SELECT To reduce memory footprint as well. But if I comment this line, I get a lot of compiler errors.

Best regards

Eric

Von: Dr. Charles Bell notifications@github.com Gesendet: Donnerstag, 20. August 2020 01:22 An: ChuckBell/MySQL_Connector_Arduino MySQL_Connector_Arduino@noreply.github.com Cc: GitHubUserEH eric.hofmann@gmx.fr; Author author@noreply.github.com Betreff: Re: [ChuckBell/MySQL_Connector_Arduino] Open/Close the connection eats 24 bytes of free Memory (#153)

Hi. Thanks for the tip. I think I’ve found the leak. I’ll let you know when I get it fixed. Or maybe if I find a workaround. Stay tuned.

On Aug 19, 2020, at 4:21 AM, GitHubUserEH <notifications@github.com mailto:notifications@github.com > wrote:

 Hi

I use this great library (version 1.2.0) to connect my ARDUINO MEGA2560 with the Ethernet Shield to a MariaDB on my ASUSTOR NAS. This works very well. I only add records (temperature readings) with the INSERT command. To do this I open a connection to the SQL, write the data set into it and close the connection. I noticed that each open and close of the database 'eats' exactly 24 bytes of memory. Since the data acquisition should run for months without any intervention, I deliberately chose to open and close the connection.

Minimal Code in a loop which reproduces the problem:

conn.connect(server_addr, 3306, user, password); cur_mem->close(); delete cur_mem; conn.close(); delay(5000);

How can I prevent that 24 bytes of free Ram are 'lost' each time?

Thanks Eric

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

— You are receiving this because you authored the thread. Reply to this email directly, https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-676806614 view it on GitHub, or https://github.com/notifications/unsubscribe-auth/AQVH4OB3DR3FR53HSQELZZ3SBRNCZANCNFSM4QEVNWBA unsubscribe. https://github.com/notifications/beacon/AQVH4OAZZC4PTWW4ZEDTIBTSBRNCZA5CNFSM4QEVNWBKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOFBLT7VQ.gif

[ChuckBell]

Hi,

This fixes the compile errors. Still chacing the memory thing. It's not what I thought it was. Stay tuned...

Dr. Bell

--- MySQL_Cursor.cpp 2020-09-01 20:37:51.092288500 -0400 +++ c:\Users\olias\Documents\Arduino\libraries\MySQL_Connector_Arduino\src\MySQL_Cursor.cpp 2020-09-01 20:31:32.088530900 -0400 @@ -136,10 +136,11 @@ if (!conn->buffer) return false;

+#ifdef WITH_SELECT // Reset the rows affected and last insert id before query. rows_affected = -1; last_insert_id = -1;

+#endif conn->store_int(&conn->buffer[0], query_len+1, 3); conn->buffer[3] = byte(0x00); conn->buffer[4] = byte(0x03); // command packet @@ -167,10 +168,12 @@ } else { loc2 += 8; } +#ifdef WITH_SELECT rows_affected = conn->read_lcb_int(5); if (rows_affected > 0) { last_insert_id = conn->read_lcb_int(loc2); } +#endif return true; }

On Thu, Aug 20, 2020 at 1:13 AM GitHubUserEH notifications@github.com wrote:

Hi Charles

Thanks for fast respond. If You can fix the problem it is great. Perhaps you can fix a other problem as well. In the File MySQL_Cursor.h there is the tip to comment the Line #define WITH_SELECT To reduce memory footprint as well. But if I comment this line, I get a lot of compiler errors.

Best regards

Eric

Von: Dr. Charles Bell notifications@github.com Gesendet: Donnerstag, 20. August 2020 01:22 An: ChuckBell/MySQL_Connector_Arduino < MySQL_Connector_Arduino@noreply.github.com> Cc: GitHubUserEH eric.hofmann@gmx.fr; Author author@noreply.github.com Betreff: Re: [ChuckBell/MySQL_Connector_Arduino] Open/Close the connection eats 24 bytes of free Memory (#153)

Hi. Thanks for the tip. I think I’ve found the leak. I’ll let you know when I get it fixed. Or maybe if I find a workaround. Stay tuned.

On Aug 19, 2020, at 4:21 AM, GitHubUserEH <notifications@github.com mailto:notifications@github.com > wrote:

 Hi

I use this great library (version 1.2.0) to connect my ARDUINO MEGA2560 with the Ethernet Shield to a MariaDB on my ASUSTOR NAS. This works very well. I only add records (temperature readings) with the INSERT command. To do this I open a connection to the SQL, write the data set into it and close the connection. I noticed that each open and close of the database 'eats' exactly 24 bytes of memory. Since the data acquisition should run for months without any intervention, I deliberately chose to open and close the connection.

Minimal Code in a loop which reproduces the problem:

conn.connect(server_addr, 3306, user, password); cur_mem->close(); delete cur_mem; conn.close(); delay(5000);

How can I prevent that 24 bytes of free Ram are 'lost' each time?

Thanks Eric

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

— You are receiving this because you authored the thread. Reply to this email directly, < https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-676806614> view it on GitHub, or < https://github.com/notifications/unsubscribe-auth/AQVH4OB3DR3FR53HSQELZZ3SBRNCZANCNFSM4QEVNWBA> unsubscribe. < https://github.com/notifications/beacon/AQVH4OAZZC4PTWW4ZEDTIBTSBRNCZA5CNFSM4QEVNWBKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOFBLT7VQ.gif>

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-677097088, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYDQ7BXRMXHNRV5CG2LSBSWI5ANCNFSM4QEVNWBA .

[ChuckBell]

Hi,

See if this fixes the problem. Let me know and I'll push it to the repo.

Dr. Bell

--- ./MySQL_Connection.cpp 2020-04-30 13:22:07.000000000 -0400 +++ /home/cbell/Arduino/libraries/MySQL_Connector_Arduino/src/MySQL_Connection.cpp 2020-09-02 14:16:41.983118844 -0400 @@ -113,4 +113,6 @@ client->stop(); show_error(DISCONNECTED, true); }

• if (server_version)
• free(server_version); }

On Tue, Sep 1, 2020 at 8:40 PM Charles Bell drcharlesbell@gmail.com wrote:

Hi,

This fixes the compile errors. Still chacing the memory thing. It's not what I thought it was. Stay tuned...

Dr. Bell

--- MySQL_Cursor.cpp 2020-09-01 20:37:51.092288500 -0400 +++ c:\Users\olias\Documents\Arduino\libraries\MySQL_Connector_Arduino\src\MySQL_Cursor.cpp 2020-09-01 20:31:32.088530900 -0400 @@ -136,10 +136,11 @@ if (!conn->buffer) return false;

+#ifdef WITH_SELECT // Reset the rows affected and last insert id before query. rows_affected = -1; last_insert_id = -1;

+#endif conn->store_int(&conn->buffer[0], query_len+1, 3); conn->buffer[3] = byte(0x00); conn->buffer[4] = byte(0x03); // command packet @@ -167,10 +168,12 @@ } else { loc2 += 8; } +#ifdef WITH_SELECT rows_affected = conn->read_lcb_int(5); if (rows_affected > 0) { last_insert_id = conn->read_lcb_int(loc2); } +#endif return true; }

On Thu, Aug 20, 2020 at 1:13 AM GitHubUserEH notifications@github.com wrote:

Hi Charles

Thanks for fast respond. If You can fix the problem it is great. Perhaps you can fix a other problem as well. In the File MySQL_Cursor.h there is the tip to comment the Line #define WITH_SELECT To reduce memory footprint as well. But if I comment this line, I get a lot of compiler errors.

Best regards

Eric

Von: Dr. Charles Bell notifications@github.com Gesendet: Donnerstag, 20. August 2020 01:22 An: ChuckBell/MySQL_Connector_Arduino < MySQL_Connector_Arduino@noreply.github.com> Cc: GitHubUserEH eric.hofmann@gmx.fr; Author <author@noreply.github.com

Betreff: Re: [ChuckBell/MySQL_Connector_Arduino] Open/Close the connection eats 24 bytes of free Memory (#153)

Hi. Thanks for the tip. I think I’ve found the leak. I’ll let you know when I get it fixed. Or maybe if I find a workaround. Stay tuned.

On Aug 19, 2020, at 4:21 AM, GitHubUserEH <notifications@github.com mailto:notifications@github.com > wrote:

 Hi

I use this great library (version 1.2.0) to connect my ARDUINO MEGA2560 with the Ethernet Shield to a MariaDB on my ASUSTOR NAS. This works very well. I only add records (temperature readings) with the INSERT command. To do this I open a connection to the SQL, write the data set into it and close the connection. I noticed that each open and close of the database 'eats' exactly 24 bytes of memory. Since the data acquisition should run for months without any intervention, I deliberately chose to open and close the connection.

Minimal Code in a loop which reproduces the problem:

conn.connect(server_addr, 3306, user, password); cur_mem->close(); delete cur_mem; conn.close(); delay(5000);

How can I prevent that 24 bytes of free Ram are 'lost' each time?

Thanks Eric

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

— You are receiving this because you authored the thread. Reply to this email directly, < https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-676806614> view it on GitHub, or < https://github.com/notifications/unsubscribe-auth/AQVH4OB3DR3FR53HSQELZZ3SBRNCZANCNFSM4QEVNWBA> unsubscribe. < https://github.com/notifications/beacon/AQVH4OAZZC4PTWW4ZEDTIBTSBRNCZA5CNFSM4QEVNWBKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOFBLT7VQ.gif>

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-677097088, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYDQ7BXRMXHNRV5CG2LSBSWI5ANCNFSM4QEVNWBA .

[GitHubUserEH]

Hi Charles

I can confirm that the Memory leak is gone. Great, thanks.

But i have still a compile Error when i remark the #define WITH_SELECT. I implemented your suggestion (hopfully right) but i get now a new compile error. If i remove the remarks the compile is working.:

MEGA2560_MySQL_Test.ino: 89:11: error: 'class MySQL_Cursor' has no member named 'close cur_mem->close();
^~~~~

This is the Test Loop i use:

void loop() {

Serial.println(freeRam());

conn.connect(server_addr, 3306, user, password);

MySQL_Cursor* cur_mem = new MySQL_Cursor(&conn);
cur_mem->close();   
delete cur_mem;
conn.close(); 

delay(1000);

}

[ChuckBell]

Hi.

Aha. Yeah, that’s broken. This should fix it.

Dr. Bell

diff --git a/src/MySQL_Cursor.h b/src/MySQL_Cursor.h index 5447da3..418efac 100644 --- a/src/MySQL_Cursor.h +++ b/src/MySQL_Cursor.h @@ -74,11 +74,13 @@ class MySQL_Cursor {

ifdef WITH_SELECT

public: void close(); +#ifdef WITH_SELECT column_names get_columns(); row_values get_next_row(); void show_results(); int get_rows_affected() { return rows_affected; } int get_last_insert_id() { return last_insert_id; } +#endif

private: void free_columns_buffer();

On Sep 3, 2020, at 5:35 AM, GitHubUserEH notifications@github.com wrote:

Hi Charles

I can confirm that the Memory leak is gone. Great, thanks.

But i have still a compile Error when i remark the #define WITH_SELECT. I implemented your suggestion (hopfully right) but i get now a new compile error. If i remove the remarks the compile is working.:

MEGA2560_MySQL_Test.ino: 89:11: error: 'class MySQL_Cursor' has no member named 'close cur_mem->close(); ^~~~~

This is the Test Loop i use:

void loop() {

Serial.println(freeRam());

conn.connect(server_addr, 3306, user, password);

MySQL_Cursor* cur_mem = new MySQL_Cursor(&conn); cur_mem->close(); delete cur_mem; conn.close();

delay(1000); }

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-686373464, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYHJNA64YK7IQ6EXZYDSD5PPZANCNFSM4QEVNWBA.

[GitHubUserEH]

Hi Charles

Still same Compile Error when i remark the Line #define WITH_SELECT.

MEGA2560_MySQL_Test.ino: In function void loop() Error compiling .ino project source Build failed for project 'MEGA2560_MySQL_Test'

MEGA2560_MySQL_Test.ino: 89:11: error: 'class MySQL_Cursor' has no member named 'close cur_mem->close() ^~~~~

[ChuckBell]

Hmmm... works Ok for me. Here's the complete .diff. Please try that one. :) If it works, I'll push it to the repo. Thanks for your patience.

On Fri, Sep 4, 2020 at 1:47 AM GitHubUserEH notifications@github.com wrote:

Hi Charles

Still same Compile Error when i remark the Line #define WITH_SELECT.

MEGA2560_MySQL_Test.ino: In function void loop() Error compiling .ino project source Build failed for project 'MEGA2560_MySQL_Test'

MEGA2560_MySQL_Test.ino: 89:11: error: 'class MySQL_Cursor' has no member named 'close cur_mem->close() ^~~~~

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-686923684, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYCCU5FTMKG53Z75O23SEB5NXANCNFSM4QEVNWBA .

[GitHubUserEH]

Hi Charles

I can not see the complete diff in Your previous post?

I have installed a new Arduino 1.8.13 Compiler Version in the Windows 10 Sandbox and only add Your Library and the test sketch. No other changes. I select MEGA2560 as the Board. Same Error. In this dropbox link i included the patched library and the very small test project. I can compile this without the remarks, but not with the remarked line in the MySQL_Cursor.h

https://www.dropbox.com/s/44rnjcukxg1j4y9/Mega2560_MySQL_Test.zip?dl=0

I still get this Compile-Error:

C:\Users\WDAGUtilityAccount\Documents\Arduino\Mega2560_MySQL_Test\Mega2560_MySQL_Test.ino: In function 'void loop()': Mega2560_MySQL_Test:46:12: error: 'class MySQL_Cursor' has no member named 'close' cur_mem->close(); ^~~~~ exit status 1 'class MySQL_Cursor' has no member named 'close'

[ChuckBell]

Hi,

You've not applied the .diff correctly. Here are the files as they should be modified.

Dr. Bell

On Tue, Sep 15, 2020 at 3:53 AM GitHubUserEH notifications@github.com wrote:

Hi Charles

I can not see the complete diff in Your previous post?

I have installed a new Arduino 1.8.13 Compiler Version in the Windows 10 Sandbox and only add Your Library and the test sketch. No other changes. I select MEGA2560 as the Board. Same Error. In this dropbox link i included the patched library and the very small test project. I can compile this without the remarks, but not with the remarked line in the MySQL_Cursor.h

https://www.dropbox.com/s/44rnjcukxg1j4y9/Mega2560_MySQL_Test.zip?dl=0

I still get this Compile-Error:

C:\Users\WDAGUtilityAccount\Documents\Arduino\Mega2560_MySQL_Test\Mega2560_MySQL_Test.ino: In function 'void loop()': Mega2560_MySQL_Test:46:12: error: 'class MySQL_Cursor' has no member named 'close' cur_mem->close(); ^~~~~ exit status 1 'class MySQL_Cursor' has no member named 'close'

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-692536035, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYBTOJKSM6IW5UNCNSTSF4MN7ANCNFSM4QEVNWBA .

/* Copyright (c) 2012, 2016 Oracle and/or its affiliates. All rights reserved.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

MySQL_Cursor.h - Run queries on a MySQL Server

This header file defines a cursor class for running queries on a MySQL server. You can issue any command using SQL statements for inserting or retrieving data.

Change History:

Version 1.0.0a Created by Dr. Charles A. Bell, April 2012. Version 1.0.0b Updated by Dr. Charles A. Bell, October 2013. Version 1.0.1b Updated by Dr. Charles A. Bell, February 2014. Version 1.0.2b Updated by Dr. Charles A. Bell, April 2014. Version 1.0.3rc Updated by Dr. Charles A. Bell, March 2015. Version 1.0.4ga Updated by Dr. Charles A. Bell, July 2015. Version 1.1.0a Created by Dr. Charles A. Bell, January 2016. Version 1.1.1a Created by Dr. Charles A. Bell, January 2016. Version 1.1.2b Created by Dr. Charles A. Bell, November 2016. Version 1.2.0 Created by Dr. Charles A. Bell, March 2020. */

ifndef MYSQL_QUERY_H

define MYSQL_QUERY_H

include

define WITH_SELECT // Comment this if you don't need SELECT queries.

                         // Reduces memory footprint of the library.

define MAX_FIELDS 0x20 // Maximum number of fields. Reduce to save memory. Default=32

ifdef WITH_SELECT

// Structure for retrieving a field (minimal implementation). typedef struct { char db; char table; char *name; } field_struct;

// Structure for storing result set metadata. typedef struct { int num_fields; // actual number of fields field_struct *fields[MAX_FIELDS]; } column_names;

// Structure for storing row data. typedef struct { char *values[MAX_FIELDS]; } row_values;

endif // WITH_SELECT

class MySQL_Cursor { public: MySQL_Cursor(MySQL_Connection connection); ~MySQL_Cursor(); boolean execute(const char query, boolean progmem=false);

private: boolean execute_query(int query_len);

public: void close();

ifdef WITH_SELECT

column_names *get_columns();
row_values *get_next_row();
void show_results();
int get_rows_affected() { return rows_affected; }
int get_last_insert_id() { return last_insert_id; }

private: void free_columns_buffer(); void free_row_buffer(); bool clear_ok_packet();

char *read_string(int *offset);
int get_field(field_struct *fs);
int get_row();
boolean get_fields();
int get_row_values();
column_names *query_result();

boolean columns_read;
int num_cols;
column_names columns;
row_values row;
int rows_affected;
int last_insert_id;

endif

MySQL_Connection *conn;

};

endif

/* Copyright (c) 2012, 2016 Oracle and/or its affiliates. All rights reserved.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

MySQL_Cursor.cpp - Run queries on a MySQL Server

Change History:

Version 1.0.0a Created by Dr. Charles A. Bell, April 2012. Version 1.0.0b Updated by Dr. Charles A. Bell, October 2013. Version 1.0.1b Updated by Dr. Charles A. Bell, February 2014. Version 1.0.2b Updated by Dr. Charles A. Bell, April 2014. Version 1.0.3rc Updated by Dr. Charles A. Bell, March 2015. Version 1.0.4ga Updated by Dr. Charles A. Bell, July 2015. Version 1.1.0a Created by Dr. Charles A. Bell, January 2016. Version 1.1.1a Created by Dr. Charles A. Bell, January 2016. Version 1.1.2b Created by Dr. Charles A. Bell, November 2016. Version 1.2.0 Created by Dr. Charles A. Bell, March 2020. */

include

const char BAD_MOJO[] PROGMEM = "Bad mojo. EOF found reading column header."; const char ROWS[] PROGMEM = " rows in result."; const char READ_COLS[] PROGMEM = "ERROR: You must read the columns first!"; const char NOT_CONNECTED[] PROGMEM = "ERROR: Class requires connected server.";

/* Constructor

Requires an instance of the MySQL_Connection class to communicate with a MySQL server.

connection[in] Connection to a MySQL server - must be connected. / MySQL_Cursor::MySQL_Cursor(MySQL_Connection connection) { conn = connection;

ifdef WITH_SELECT

columns.num_fields = 0; for (int f = 0; f < MAX_FIELDS; f++) { columns.fields[f] = NULL; row.values[f] = NULL; } columns_read = false; rows_affected = -1; last_insert_id = -1;

endif

}

/ Destructor / MySQL_Cursor::~MySQL_Cursor() {

ifdef WITH_SELECT

close();

endif

}

/* execute - Execute a SQL statement

This method executes the query specified as a character array. It copies the query to the local buffer then calls the execute_query() method to execute the query.

If a result set is available after the query executes, the field packets and rows can be read separately using the get_field() and get_row() methods.

query[in] SQL statement (using normal memory access) progmem[in] True if string is in program memory

Returns boolean - True = a result set is available for reading / boolean MySQL_Cursor::execute(const char query, boolean progmem) { int query_len; // length of query

if (!conn->connected()) { conn->show_error(NOT_CONNECTED, true); return false; }

if (progmem) { query_len = (int)strlen_P(query); } else { query_len = (int)strlen(query); } if (conn->buffer != NULL) free(conn->buffer);

conn->buffer = (byte *)malloc(query_len+5);

// Write query to packet if (progmem) { for (int c = 0; c < query_len; c++) conn->buffer[c+5] = pgm_read_byte_near(query+c); } else { memcpy(&conn->buffer[5], query, query_len); }

// Send the query return execute_query(query_len); }

/* execute_query - execute a query

This method sends the query string to the server and waits for a response. If the result is a result set, it returns true, if it is an error, it processes the error packet and prints the error via Serial.print(). If it is an Ok packet, it parses the packet and returns false.

query_len[in] Number of bytes in the query string

Returns boolean - true = result set available, false = no result set returned. */ boolean MySQL_Cursor::execute_query(int query_len) { if (!conn->buffer) return false;

// Reset the rows affected and last insert id before query.

ifdef WITH_SELECT

rows_affected = -1; last_insert_id = -1;

endif

conn->store_int(&conn->buffer[0], query_len+1, 3); conn->buffer[3] = byte(0x00); conn->buffer[4] = byte(0x03); // command packet

// 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(); int res = conn->get_packet_type(); if (res == MYSQL_ERROR_PACKET) { conn->parse_error_packet(); return false; } else if (res == MYSQL_OK_PACKET || res == MYSQL_EOF_PACKET) { // Read the rows affected and last insert id. int loc1 = conn->buffer[5]; // Location of rows affected int loc2 = 5; if (loc1 < 252) { loc2++; } else if (loc1 == 252) { loc2 += 2; } else if (loc1 == 253) { loc2 += 3; } else { loc2 += 8; }

ifdef WITH_SELECT

rows_affected = conn->read_lcb_int(5);
if (rows_affected > 0) {
  last_insert_id = conn->read_lcb_int(loc2);
}

endif

return true;

}

// Not an Ok packet, so we now have the result set to process.

ifdef WITH_SELECT

columns_read = false;

endif

return true; }

ifdef WITH_SELECT

/* Close

Takes care of removing allocated memory. */ void MySQL_Cursor::close() { free_columns_buffer(); free_row_buffer(); }

/* get_columns - Get a list of the columns (fields)

This method returns an instance of the column_names structure that contains an array of fields.

Note: you should call free_columns_buffer() after consuming the field data to free memory. / column_names MySQL_Cursor::get_columns() { free_columns_buffer(); free_row_buffer(); num_cols = 0; if (get_fields()) { columns_read = true; return &columns; } else { return NULL; } }

/* get_next_row - Iterator for reading rows from a result set

This method returns an instance of a structure (row_values) that contains an array of strings representing the row values returned from the server.

The caller can use the values however needed - by first converting them to a specific type or as a string. / row_values MySQL_Cursor::get_next_row() { int res = 0;

free_row_buffer();

// Read the rows res = get_row_values(); if (res != MYSQL_EOF_PACKET) { return &row; } return NULL; }

/* show_results - Show a result set from the server via Serial.print

This method reads a result from the server and displays it via the via the Serial.print methods. It can be used in cases where you may want to issue a SELECT or SHOW and see the results on your computer from the Arduino.

It is also a good example of how to read a result set from the because it uses the public methods designed to return result sets from the server. / void MySQL_Cursor::show_results() { column_names cols; int rows = 0;

// Get the columns cols = get_columns(); if (cols == NULL) { return; }

for (int f = 0; f < columns.num_fields; f++) { Serial.print(columns.fields[f]->name); if (f < columns.num_fields-1) Serial.print(','); } Serial.println();

// Read the rows while (get_next_row()) { rows++; for (int f = 0; f < columns.num_fields; f++) { Serial.print(row.values[f]); if (f < columns.num_fields-1) Serial.print(','); } free_row_buffer(); Serial.println(); }

// Report how many rows were read Serial.print(rows); conn->show_error(ROWS, true); free_columns_buffer();

// Free any post-query messages in queue for stored procedures clear_ok_packet(); }

/* clear_ok_packet - clear last Ok packet (if present)

This method reads the header and status to see if this is an Ok packet. If it is, it reads the packet and discards it. This is useful for processing result sets from stored procedures.

Returns False if the packet was not an Ok packet. */ bool MySQL_Cursor::clear_ok_packet() { int num = 0;

do { num = conn->client->available(); if (num > 0) { conn->read_packet(); if (conn->get_packet_type() != MYSQL_OK_PACKET) { conn->parse_error_packet(); return false; } } } while (num > 0); rows_affected = -1; last_insert_id = -1; return true; }

/* free_columns_buffer - Free memory allocated for column names

This method frees the memory allocated during the get_columns() method.

NOTICE: Failing to call this method after calling get_columns() and consuming the column names, types, etc. will result in a memory leak. The size of the leak will depend on the size of the combined column names (bytes). */ void MySQL_Cursor::free_columns_buffer() { // clear the columns for (int f = 0; f < MAX_FIELDS; f++) { if (columns.fields[f] != NULL) { free(columns.fields[f]->db); free(columns.fields[f]->table); free(columns.fields[f]->name); free(columns.fields[f]); } columns.fields[f] = NULL; } num_cols = 0; columns_read = false; }

/* free_row_buffer - Free memory allocated for row values

This method frees the memory allocated during the get_next_row() method.

NOTICE: You must call this method at least once after you have consumed the values you wish to process. Failing to do will result in a memory leak equal to the sum of the length of values and one byte for each max cols. */ void MySQL_Cursor::free_row_buffer() { // clear the row for (int f = 0; f < MAX_FIELDS; f++) { if (row.values[f] != NULL) { free(row.values[f]); } row.values[f] = NULL; } }

/* read_string - Retrieve a string from the buffer

This reads a string from the buffer. It reads the length of the string as the first byte.

offset[in] offset from start of buffer

Returns string - String from the buffer / char MySQL_Cursor::read_string(int offset) { char str; int len_bytes = conn->get_lcb_len(conn->buffer[offset]); int len = conn->read_int(offset, len_bytes); if (len == 251) { // This is a null field. str = (char )malloc(5); strncpy(str, "NULL", 4); str[4] = 0x00; offset += len_bytes; } else { str = (char )malloc(len+1); strncpy(str, (char )&conn->buffer[offset+len_bytes], len); str[len] = 0x00; offset += len_bytes+len; } return str; }

/* get_field - Read a field from the server

This method reads a field packet from the server. Field packets are defined as:

Bytes Name

---

n (Length Coded String) catalog n (Length Coded String) db n (Length Coded String) table n (Length Coded String) org_table n (Length Coded String) name n (Length Coded String) org_name 1 (filler) 2 charsetnr 4 length 1 type 2 flags 1 decimals 2 (filler), always 0x00 n (Length Coded Binary) default

Note: the sum of all db, column, and field names must be < 255 in length / int MySQL_Cursor::get_field(field_struct fs) { int len_bytes; int len; int offset;

// Read field packets until EOF conn->read_packet(); if (conn->buffer && conn->buffer[4] != MYSQL_EOF_PACKET) { // calculate location of db len_bytes = conn->get_lcb_len(4); len = conn->read_int(4, len_bytes); offset = 4+len_bytes+len; fs->db = read_string(&offset); // get table fs->table = read_string(&offset); // calculate location of name len_bytes = conn->get_lcb_len(offset); len = conn->read_int(offset, len_bytes); offset += len_bytes+len; fs->name = read_string(&offset); return 0; } return MYSQL_EOF_PACKET; }

/* get_row - Read a row from the server and store it in the buffer

This reads a single row and stores it in the buffer. If there are no more rows, it returns MYSQL_EOF_PACKET. A row packet is defined as follows.

Bytes Name

---

n (Length Coded String) (column value) ...

Note: each column is store as a length coded string concatenated as a single stream

Returns integer - MYSQL_EOF_PACKET if no more rows, 0 if more rows available */ int MySQL_Cursor::get_row() { // Read row packets conn->read_packet(); if (conn->buffer && conn->buffer[4] != MYSQL_EOF_PACKET) return 0; return MYSQL_EOF_PACKET; }

/* get_fields - reads the fields from the read buffer

This method is used to read the field names, types, etc. from the read buffer and store them in the columns structure in the class. */ boolean MySQL_Cursor::get_fields() { int num_fields = 0; int res = 0;

if (conn->buffer == NULL) { return false; } num_fields = conn->buffer[4]; // From result header packet columns.num_fields = num_fields; num_cols = num_fields; // Save this for later use for (int f = 0; f < num_fields; f++) { field_struct field = (field_struct )malloc(sizeof(field_struct)); res = get_field(field); if (res == MYSQL_EOF_PACKET) { conn->show_error(BAD_MOJO, true); return false; } columns.fields[f] = field; } conn->read_packet(); // EOF packet return true; }

/* get_row_values - reads the row values from the read buffer

This method is used to read the row column values from the read buffer and store them in the row structure in the class. */ int MySQL_Cursor::get_row_values() { int res = 0; int offset = 0;

// It is an error to try to read rows before columns // are read. if (!columns_read) { conn->show_error(READ_COLS, true); return MYSQL_EOF_PACKET; } // Drop any row data already read free_row_buffer();

// Read a row res = get_row(); if (res != MYSQL_EOF_PACKET) { offset = 4; for (int f = 0; f < num_cols; f++) { row.values[f] = read_string(&offset); } } return res; }

endif // WITH_SELECT

/* Copyright (c) 2012, 2016 Oracle and/or its affiliates. All rights reserved.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

MySQL_Connection.cpp - Library for communicating with a MySQL Server over Ethernet. (formerly mysql.cpp)

Change History:

Version 1.0.0a Created by Dr. Charles A. Bell, April 2012. Version 1.0.0b Updated by Dr. Charles A. Bell, October 2013. Version 1.0.1b Updated by Dr. Charles A. Bell, February 2014. Version 1.0.2b Updated by Dr. Charles A. Bell, April 2014. Version 1.0.3rc Updated by Dr. Charles A. Bell, March 2015. Version 1.0.4ga Updated by Dr. Charles A. Bell, July 2015. Version 1.1.0a Created by Dr. Charles A. Bell, January 2016. Version 1.1.1a Created by Dr. Charles A. Bell, January 2016. Version 1.1.2b Created by Dr. Charles A. Bell, November 2016. Version 1.2.0 Created by Dr. Charles A. Bell, March 2020. */

include

include

include

define MAX_CONNECT_ATTEMPTS 3

define CONNECT_DELAY_MS 500

define SUCCESS 1

const char CONNECTED[] PROGMEM = "Connected to server version "; const char DISCONNECTED[] PROGMEM = "Disconnected.";

/* connect - Connect to a MySQL server.

This method is used to connect to a MySQL server. It will attempt to connect to the server as a client retrying up to MAX_CONNECT_ATTEMPTS. This permits the possibility of longer than normal network lag times for wireless networks. You can adjust MAX_CONNECT_ATTEMPTS to suit your environment.

server[in] IP address of the server as IPAddress type port[in] port number of the server user[in] user name password[in] (optional) user password db[in] (optional) default database

Returns boolean - True = connection succeeded / boolean MySQL_Connection::connect(IPAddress server, int port, char user, char password, char db) { int connected = 0; int retries = MAX_CONNECT_ATTEMPTS;

// Retry up to MAX_CONNECT_ATTEMPTS times. while (retries--) { Serial.println("...trying..."); connected = client->connect(server, port); if (connected != SUCCESS) { Serial.print("...got: "); Serial.print(connected); Serial.println(" retrying..."); delay(CONNECT_DELAY_MS); } else { break; } }

if (connected != SUCCESS) return false;

read_packet(); parse_handshake_packet(); send_authentication_packet(user, password, db); read_packet(); if (get_packet_type() != MYSQL_OK_PACKET) { parse_error_packet(); return false; }

show_error(CONNECTED);

Serial.println(server_version);

free(server_version); // don't need it anymore return true; }

/* close - cancel the connection

This method closes the connection to the server and frees up any memory used in the buffer. */ void MySQL_Connection::close() { if (connected()) { client->flush(); client->stop(); show_error(DISCONNECTED, true); } if (server_version) free(server_version); }

[GitHubUserEH]

Hi Charles

I have now replaced the 3 Files you provide me. The compile ist runnig a s long as i not remark the Line in MySQL_Cursor.h

define WITH_SELECT // Comment this if you don't need SELECT queries.

With the remarked line i get the following Compile Error:

C:\Users\WDAGUtilityAccount\AppData\Local\Temp\cc9Nhzg6.ltrans0.ltrans.o: In function loop': C:\Users\WDAGUtilityAccount\Documents\Arduino\Mega2560_MySQL_Test/Mega2560_MySQL_Test.ino:46: undefined reference toMySQL_Cursor::close()' collect2.exe: error: ld returned 1 exit status exit status 1 Fehler beim Kompilieren für das Board Arduino Mega or Mega 2560.

The Sketch and library i use with the new modified library you can download here: https://www.dropbox.com/s/btq363hojhsokt5/Mega2560_MySQL_Test.zip?dl=0

[ChuckBell]

Strange. I’ll take another look. Thanks for your patience.

On Wed, Sep 16, 2020 at 02:15 GitHubUserEH notifications@github.com wrote:

Hi Charles

I have now replaced the 3 Files you provide me. The compile ist runnig a s long as i not remark the Line in MySQL_Cursor.h

define WITH_SELECT // Comment this if you don't need SELECT queries.

With the remarked line i get the following Compile Error:

C:\Users\WDAGUtilityAccount\AppData\Local\Temp\cc9Nhzg6.ltrans0.ltrans.o: In function loop': C:\Users\WDAGUtilityAccount\Documents\Arduino\Mega2560_MySQL_Test/Mega2560_MySQL_Test.ino:46: undefined reference to MySQL_Cursor::close()'

collect2.exe: error: ld returned 1 exit status

exit status 1

Fehler beim Kompilieren für das Board Arduino Mega or Mega 2560.

The Sketch and library i use with the new modified library you can download here: https://www.dropbox.com/s/btq363hojhsokt5/Mega2560_MySQL_Test.zip?dl=0

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-693197350, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYD4IRY2JKESMNMMF2LSGBJZRANCNFSM4QEVNWBA .

[ChuckBell]

Try this one. :)

On Wed, Sep 16, 2020 at 7:48 AM Charles Bell drcharlesbell@gmail.com wrote:

Strange. I’ll take another look. Thanks for your patience.

On Wed, Sep 16, 2020 at 02:15 GitHubUserEH notifications@github.com wrote:

Hi Charles

I have now replaced the 3 Files you provide me. The compile ist runnig a s long as i not remark the Line in MySQL_Cursor.h

define WITH_SELECT // Comment this if you don't need SELECT queries.

With the remarked line i get the following Compile Error:

C:\Users\WDAGUtilityAccount\AppData\Local\Temp\cc9Nhzg6.ltrans0.ltrans.o: In function loop': C:\Users\WDAGUtilityAccount\Documents\Arduino\Mega2560_MySQL_Test/Mega2560_MySQL_Test.ino:46: undefined reference to MySQL_Cursor::close()'

collect2.exe: error: ld returned 1 exit status

exit status 1

Fehler beim Kompilieren für das Board Arduino Mega or Mega 2560.

The Sketch and library i use with the new modified library you can download here: https://www.dropbox.com/s/btq363hojhsokt5/Mega2560_MySQL_Test.zip?dl=0

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-693197350, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYD4IRY2JKESMNMMF2LSGBJZRANCNFSM4QEVNWBA .

[GitHubUserEH]

Hmm...

Which one? Can not see any new code?

[ChuckBell]

It was attached to my last email. Must've gotten stripped. Here's the diff instead.

--- /home/cbell/Downloads/Mega2560_MySQL_Test/mods/MySQL_Cursor.h 2020-09-21 14:24:40.897917391 -0400 +++ MySQL_Cursor.h 2020-09-21 14:35:14.229652487 -0400 @@ -72,14 +72,16 @@ boolean execute_query(int query_len);

public:

• void close(); +#ifndef WITH_SELECT
• void close() {}; +#endif

  ifdef WITH_SELECT

• void close(); column_names get_columns(); row_values get_next_row(); void show_results(); int get_rows_affected() { return rows_affected; } int get_last_insert_id() { return last_insert_id; }

• private: void free_columns_buffer(); void free_row_buffer();

On Tue, Sep 22, 2020 at 2:58 AM GitHubUserEH notifications@github.com wrote:

Hmm...

Which one? Can not see any new code?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/153#issuecomment-696544334, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYBXFAPBUPOKXRTAK2TSHBDK5ANCNFSM4QEVNWBA .

[GitHubUserEH]

Yes you got it... 👍

Now it works with and without remarks. Great. Thanks.

Eric

[Mumbaso21]

Hi guys, i still having the memory leak. If you connect and disconnect all the time and check for the RAM, you can see that each connection the RAM goes down. I'm on platformIO, library 1.2.0

If you plug this code into a sketch:

void loop()
{
  if (connection.connect(mysql_server, mysql_port, mysql_user, mysql_password, mysql_db))
  {
    connection.close();
  };
  Serial.print(" RAM: ");
  Serial.println(get_free_memory());
}

int get_free_memory()
{
  extern char __bss_end;
  extern char *__brkval;
  int free_memory;
  if ((int)__brkval == 0)
    free_memory = ((int)&free_memory) - ((int)&__bss_end);
  else
    free_memory = ((int)&free_memory) - ((int)__brkval);
  return free_memory;
}

You get this output:

Connecting to db: 
...trying...
5.5.5-10.4.32-MariaDB
Connection successfull.
 RAM: 5718
Connecting to db: 
...trying...
5.5.5-10.4.32-MariaDB
Connection successfull.
 RAM: 5705
Connecting to db: 
...trying...
5.5.5-10.4.32-MariaDB
Connection successfull.
 RAM: 5692
Connecting to db: 
...trying...
5.5.5-10.4.32-MariaDB
Connection successfull.
 RAM: 5679

Please check it out

[Mumbaso21]

This code seems to fix the issue. I saw this fix from the MySql_MariaDB_Generic library, please update the code. Put it in the MySQL_Packet.h file.

virtual ~MySQL_Packet()
{
  if (buffer)
  {
    free(buffer);
  }
};

[ChuckBell]

Confirmed. Will add it to the list for next release.