Reconnecting to mysql connector

[Chris Watters]

Hello !

I've just created my first Arduino app with the MySQL connector and so far it is working fine. But I have a question regarding how to reconnect to the MySQL server if the connection breaks for whatever reason (server restart, etc)

First the details.
Within the decalrations section I have the following:

/* Setup for Ethernet Library */
byte mac_addr[] = { 0x90, 0xA2, 0xDA, 0x0E, 0xF4, 0x79 };
byte ip[] = { 192, 168, 1, 201 };
IPAddress server_addr(192, 168, 1, 100);
/* Setup for the Connector/Arduino */
Connector my_conn;
char user[8] = "arduino";
char password[8] = "arduino";

Within the Setup routine, I have the following code to initialise ethernet and MySQL (works fine):

void setup()
{
Ethernet.begin(mac_addr, ip);
delay(500);
Serial.println("Ethernet begin");
my_conn.mysql_connect(server_addr, 3306, user, password);
delay(500);
}

Next, I have the RTC to fire an interrupt every five minutes to write some data to the MySQL client, as follows:

void WriteData()
{

if (my_conn.is_connected() == 0) {
my_conn.mysql_connect(server_addr, 3306, user, password);
delay(500);
}
// Some code here to construct the INSERT statement
my_conn.cmd_query(buf);
}
}


I tried, within the WriteData routine, to include some code to test if the connection is alive or not. If it's dead, then it should reconnect.

I test this by killing the connection in MySQL Server after the first INSERT command and seeing what happens.

What happens is that 'is_connected' returns zero (as expected), but the 'mysql_connect' instruction does not reconnect as expected. The instruction seems to execute, by observation that the code after is executed, but by the time the cmd_query command is executed, the connection is still not alive and the Arduino board resets itself.

Perhaps this is not so bad (restart server, then Arduino will 'self reset', but it's not by design!). It would be nice if I could do the folllwing in the Write Data function to connect to the server, do some stuff and then disconnect:

void WriteData()
{
if my_conn.mysql_connect(server_addr, 3306, user, password) {
delay(500);
// Some code here to construct the INSERT statement
my_conn.cmd_query(buf);
my_conn.close()
}
}


So, why would my Arduino reset itself if cmd_query is performed without a valid connection? Or is it mysql_connector (or even Ethernet library) that is doing this?

Is it possible to implement code (myconn.close) as I suggested just above, or is there some other method to achieve this functionality ? I searched for the 'close' function but didn't find anything.

Thanks for reading and answering my queries !