Tuesday, February 4, 2014

Announcing MySQL Connector/Arduino 1.0.1 Beta

I've completed a new release of the Connector/Arduino. The new version supports a few refinements and a new feature.

  • New! disconnect() method - enables disconnect from server. Note: you must call mysql_connect() to reconnect.
  • Better error handling for dropped packets. No more random reboots when bad packet appears.
  • Library can recover from short-term loss of connectivity. Along with bad packets is a check to make sure what is received is valid making the connector ignore garbage packets associated with a dropped connection.
  • Detection of Out of Memory condition. Should there not be enough memory to allocate the buffer for the Connector, you will see an OOM error (enable the serial monitor to see the errors). This reduces random reboots when memory gets too low.
I made this release because a number of people were running into problems with noisy, tenuous, or just plain bad network connections. Also, some do not want to hold the connection open on the database server. This release addresses all of these issues.

Did you say disconnect?


Yes, that's right, you can now disconnect from the server should you want to write sketches that connect to the MySQL server for only a brief period then sleep, calculate the distance to Alpha-centuri, make coffee, etc. It is also helpful for those sketches that will update the database only once every few minutes, hours, or days permitting you to connect, run a query, then disconnect on the interval.

But wait...what about when the Ethernet shield goes wonky?


I have also devised a way to overcome the problem of the Ethernet shield controller going away. That is, if your Arduino looses connectivity for more than a few seconds (about 15-30), the Ethernet shield could wig out and fail to respond. The rest of your sketch will continue to run but calls to the Ethernet library will be ignored (how rude).

So...what to do? In short, we need to reboot the Ethernet shield. You could make a hardware-based connection to the reset button but some have reported problems with this solution. And it is a hard reset for the Arduino too - they are inseparable.

Rather than use hardware, I've devised a way to force the Arduino to reload its software. This won't fix any hardware issues like the reset button will but it will restore the Ethernet shield to proper operation.

Ok, I'm sold. How do I do it?


First, you need a variable and a define to set a threshold.

int num_fails;
#define MAX_FAILED_CONNECTS 5


Next, you need a method that will force the Arduino to reload. In this case, I use the tricky jump-to-zero address code which is sort of like a bootstrap (but not quite). Anyway, it works!

void soft_reset() {
  asm volatile("jmp 0");
}


To use this in your sketch, modify the loop() method (or where ever you put your MySQL connector code) as follows:

Note: this assumes your initial mysql_connect() call is in setup() like I originally intended. Modify the following accordingly if that is not the case.

void loop() {
  delay(1000);
  if (my_conn.is_connected()) {
    my_conn.cmd_query(QUERY_SQL); // <-- br="" goes="" here="" query="" your="">    delay(1000);
    num_fails = 0;
  } else {
    my_conn.disconnect();
    Serial.println("Connecting...");
    if (my_conn.mysql_connect(server_addr, 3306, user, password)) {
      delay(500);
      Serial.println("Success!");
      num_fails = 0;
    } else {
      num_fails++;
      Serial.println("Connect failed!");
      if (num_fails == MAX_FAILED_CONNECTS) {
        Serial.println("Ok, that's it. I'm outta here. Rebooting...");
        delay(2000);
        soft_reset();
      }
    }
  }
}


Notice the counter num_fails is incremented any time the connection to the MySQL server fails and is reset when it succeeds. If num_fails reaches the value of MAX_FAILED_CONNECTS, the sketch will call the soft_reset() method and the Arduino will be reloaded (not the same as restarting or resetting - keep that in mind if you use components that require a true reset to initialize).

So now if your sketch runs happily for a time but looses its connection to the database server for a long period, it will reboot itself and therefore reestablish a connection - assuming the network or server is back up.

Note: this code is in the hello_mysql_reboot sketch in the examples folder.

Enjoy!