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!

20 comments:

  1. can i ask how do you determine the server address? it's the only thing that's bothering me and i would appreciate some help

    ReplyDelete
  2. There are many ways. If you are running Linux, Unix, or Mac OS X, use this:

    ifconfig

    For Windows use this:

    ipconfig

    You will find the IP address in the output of these commands.

    You can also do this in a mysql client:

    show variables like 'hostname';

    Then use ping (from a terminal) to ping the hostname shown. The output will show the IP address.


    ReplyDelete
  3. Are there any docs on this connector to browse through?

    ReplyDelete
    Replies
    1. In the code, yes and in my blogs. Also in my book, "Beginning Sensor Networks" (Apress). ;)

      Delete
  4. Hi, i was wondering if you made that work with GPRS shield

    ReplyDelete
  5. Hi. No, not had time to get to it.

    ReplyDelete
  6. Hi Chuk,
    thank you for this excellent job,
    the mysql connector work fine with arduno ethernet shield!

    i have the Adafruit CC3000 WiFi module (very useful and more inexpensive!)
    and I'm trying to change mysql.h and mysql.cpp for this module, but i don't understand where i put the client call.

    WiFi Shield (Ethernet Shield) Client Call:
    client.connect(server, port);

    Adafruit CC3000 Client Call:
    Adafruit_CC3000_Client client = cc3000.connectTCP(ip, port);

    can you help me?

    thank you

    Marco

    ReplyDelete
  7. Hi. Look in mysql.cpp and replace the client* calls with equivalent cc3000* calls. You will need to change the includes as well. Just search for client. and you should find them all (about 9 in all).

    ReplyDelete
    Replies
    1. Hi, can you clarify better this?
      So, one example, instead of:
      connected = client.connect(server, port) is
      connected = cc3000.connect(server, port);

      ?

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
  9. After the invention of Jesus, this is the best one!

    Thank's Chuck!

    ReplyDelete
  10. Hello Mr. Chuck,

    I have purchased your book beginning wireless sensor networks. I am currently working on chapter 7. Interfacing Arduino with MySQL and storing values from DHT22.. I was able to successfully execute the projects in the book till now. However, I am stuck with this project because the serial monitor on the Arduino is stuck at "Connecting.." and does not connect to the server and input the sensor values.

    I tried insert "Hello" values to the MySQL database and that worked. I tried reading just the temperature and humidity values and that worked. However, when I try to read sensor values and enter them into the database, it doesn't work. I have configured the IP address of the server and log in credentials correctly.

    ReplyDelete
    Replies
    1. It is a memory problem. I think the 1.0.1beta version may be over the limit. Please try using the older 1.0.0 version of the library. Let me know if this works. You can send me email directly at d r c h a r l e s b e l l at g m a i l dot c o m but please be patient as I am way behind on emails (I've been ill lately).

      Delete
  11. Hi start using this new version, thank you for the good job!

    ReplyDelete
  12. Chuck, you know how to write in mysql the curdate and curtime without using a rtc module?
    I want to use the time from the server..
    tnk you!

    ReplyDelete
    Replies
    1. Add a timestamp column. See online MySQL reference manual.

      Delete
  13. Hello,

    I have exactly the same problem that someone describe, how i change the library to work with c3000 wifi shield, is possible that you make available a version for that, or some example?
    Thank's !

    ReplyDelete
  14. I have since purchased a C3000 shield and taken a look. It is not a trivial change. I will try to work on it when I get time but no promises. ;)

    In the meantime, look through the connector code and look for all client.* calls. You need to find equivalent calls for those methods.

    I think the hardest will be modifying the connector to do the proper connection and setup for the c3000. It is a pity the developers thought to make it so different from the Arduino provided libraries. Indeed, I think it likely the differences are significant enough to warrant creating a special version for the c3000 library.

    If you make any progress before me, please share! :)

    ReplyDelete
    Replies
    1. Dr. Chuck,

      As you and another person in this blog had suggested, I am using the Arduino Mega as a possible solution to tackle the memory problem with the connector. Even though the new beta that you have released works in connecting to the server and writing sensor values when using Uno, when I switched out the Uno with the Mega, I am unable to do this. The XBee's are communicating and returning values when I try to output just the sensor data on the serial monitor. The same code works for Uno when creating a web server, but doesn't when using a Mega. I am unable to figure out what the issue is.

      Delete
    2. There must be another pin assignment problem. I am not in a position to research it at this time but I will try to find time.
      Suggestions:

      In the meanwhile, break the problem into smaller chunks. First, you know the DHT sensors work. Now, remove that code and leave only the MySQL code. Check that it works. If they both do but don't when combined, there is a conflict.

      I seem to recall something wonky possible when using the Serial module and XBees. You may want to change it to use the software serial. Google for that. ;)

      Delete