Wednesday, April 2, 2014

Announcing MySQL Connector/Arduino 1.0.2 Beta

I've completed a new release of the Connector/Arduino! The new release contains some major improvements with memory handling.
  • The library has been trimmed to save memory.
    • Static strings moved to PROGMEM strings
    • Unused structures removed (e.g. ok_packet)
    • Moved two more methods to optional compilation
  • The WITH_SELECT is turned *OFF* by default. If you want to use select queries, be sure to uncomment this in the mysql.h file.
  • Added a CHANGES.txt file to track changes between releases.

Memory, What Memory?


If you have used previous versions of the connector in medium to large sketches or have long query strings or even many variables, chances are you have hit the memory limit for your wee Arduino board.

This can manifest itself in a number of ways. Most notably, the sketch may work for you until you add more code or more sensors in which it can fail to connect to the server. It can also exhibit random reboots or random freezes. If this is happening to you, it is most likely a memory issue.

That is, the old version of the connector consumed nearly 70% of available dynamic memory - the memory used for variables. When the Arduino exceeds its memory limit, sketches will exhibit strange behavior or the board may reboot.

Many people have encountered this so I've worked hard to try and squeeze more memory out of the connector. Which isn't easy considering it must keep a buffer of the data being sent to (or received from) the server.

Let's see an example. The old version of the connector (1.0.1b), when compiled with the hello_mysql example for a Uno, consumes about 68% of available dynamic memory leaving only 637 bytes for your own variables. That's fine for a simple sketch but if you want to do complex queries building INSERT statements from several sensors or including other libraries for additional features (like an LCD), you're not going to be happy.

While you can (and should) limit your memory use and even make use of PROGMEM for your static strings (and calling cmd_query_P()), it still isn't enough free memory for larger sketches. The following is the compile message generated by the beta release of the Arduino IDE (1.5.4).

Sketch uses 22,376 bytes (69%) of program storage space. Maximum is 32,256 bytes.
Global variables use 1,411 bytes (68%) of dynamic memory, leaving 637 bytes for local variables. Maximum is 2,048 bytes.

 

Now, with the new version of the library and SELECT turned on, the connector consumes only 58% of dynamic memory as shown below. While that is better, it isn't quite where we need to be.

Sketch uses 22,152 bytes (68%) of program storage space. Maximum is 32,256 bytes.
Global variables use 1,197 bytes (58%) of dynamic memory, leaving 851 bytes for local variables. Maximum is 2,048 bytes.

 

If we turn off the SELECT feature with the new version, we get a little better.

Sketch uses 20,736 bytes (64%) of program storage space. Maximum is 32,256 bytes.
Global variables use 1,064 bytes (51%) of dynamic memory, leaving 984 bytes for local variables. Maximum is 2,048 bytes.

 

Ah, now we're cooking. The dynamic memory usage is down to 51%. Much better.


Choosing the Right Board


One of the things users new to the connector have struggled with is choosing the right Arduino board for their project. The connector is a non-trivial library that consumes (relatively) a lot of memory. If you want to write a really big sketch using lots of variables, strings, etc., you will need to use a board with more memory.

This is especially true when you combine the connector with other libraries like those made for some sensors, shields, and more. The combined memory for the connector and the other libraries can consume a lot of dynamic memory leaving you very little to use for your own variables.

While most solve the problem by switching to a Mega board, that has its own issues because some of the pins differ from the smaller (memory-wise, not size-wise) boards. A few Google searches will quickly find solutions to these problems (hint: software serial).

So which board should you choose? I've done some research for you and have compiled a simple MySQL sketch using the new version of the connector on a variety of boards. The following lists the memory usage reports from the Arduino Beta IDE. In this case, the compilations are with SELECT turned off (which is the new default).

  • Duemilanove, Uno : 1,064 bytes (51%) of dynamic memory, leaving 984 bytes for local variables
  • Leonardo : 1,028 bytes (40%) of dynamic memory, leaving 1,532 bytes for local variables
  • Mega 2560 : 1,550 bytes (18%) of dynamic memory, leaving 6,642 bytes for local variables
  • Yun : 1,028 bytes (40%) of dynamic memory, leaving 1,532 bytes for local variables

As you can see, using the older Uno-style boards are going to limit your ability to write complex sketches with many variables, logic, etc. However, a Leonardo or even a Yun board may be a better choice especially if you run out of memory on the Uno-style board. Of course, the Mega is the best choice if your sketch is going to be very complex or if you need to store a lot of values in memory or use other, large libraries.

Conclusion


I hope this new version solves many of your problems with memory. I've tried to shrink it down as much as I can without radical changes. I believe the majority of the connection failures and freezes will be solved with this new version. Please feel free to provide feedback on this blog or (better) on the Oracle Connector/Arduino forum.

http://forums.mysql.com/list.php?175

8 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi Chuck!

    You have done a simply great job! I need your help, please... I'm realising a domotic system as a school project and I would like to periodically insert (for example every hours) some sensor values like temperature or luminosity) into a db provided by my school. I'm trying to make it work on an Arduino Yùn but I'm experiencing some problems. Could you please help me, maybe via mail or something else, in order to get it work? Please, I'm about to go crazy... Really need your support pls contact me via my e-mail and I will explain the problem D: Thanks!

    ReplyDelete
    Replies
    1. Hi. There are a lot of examples you can look at. My other blogs explain how to to queries and there are examples in the connector library folder. Please look through those and see if they answer your questions. You should be able to get started with that information. You will have to provide me your email address as I cannot see it on the blog space.

      Delete
  3. Hi Chuck, my email is gabrielepiccione@live.it thanks for support. I'm waiting for the mail. Bye!

    ReplyDelete
  4. Hey Chuck,
    Sorry for my bad english I am a french student.
    I have a problem for a project, I use a Arduino Uno + a Arduino WiFi Shield Adafruit PN532 NFC/RFID Shield.

    Adafruit PN532 NFC/RFID Shield : https://www.adafruit.com/products/789

    I use the new librarie 1.0.2 Beta It is work ! but when I use the shield Adafruit PN532 NFC/RFID and i connect the wifi shield at my MysqlDataBASE The program don't work and the connexion does not.

    I use the Adafruit PN532 NFC/RFID Shield In SPI.
    I think it is the probleme but I dont no know how to solve this.
    my email is simon35135@gmail.com

    ReplyDelete
    Replies
    1. Hello. You should begin with the simple test to connect to your MySQL server - use only the basic connection script as shown in the connector source files. Make sure this works first.

      Then, and only then, add your other shields.

      If you cannot get the basic connection to work, try using a mysql client tool to connect to your server using the same network pathways. This will ensure you have the right hostname, IP, user, and password (and permissions).

      See my other blogs about solving connection problems.

      Delete
  5. Hello Chuck
    Can I use the Sql query EXIST?
    If yes how to use the result as a boolean.
    (true or false)

    For example for my project I have the name of a person, I need to know if that person is present in the database.

    Thank you for your reply.

    ReplyDelete
    Replies
    1. The "IF EXISTS" option applies to objects on the server. For example, you preface commands to execute if a table, database, etc. exists with the IF EXISTS clause like this:

      DROP TABLE IF EXISTS mydb.tbl1;

      To test to see if a row exists in the table, you must use a SELECT with an appropriate WHERE clause like this:

      mysql> select 1 from employees.employees where emp_no = 12201;
      +---+
      | 1 |
      +---+
      | 1 |
      +---+
      1 row in set (0.00 sec)

      mysql> select 1 from employees.employees where emp_no = -1;
      Empty set (0.00 sec)

      This will return either an empty result set (if not found), or a 1 if the row is found.

      What you need to do then is to figure out the SELECT and WHERE clause and execute that. Then, use the example code to retrieve the result set. See the do_query() method example for hints on how to write this. Hint: you may also need to use sprintf() to replace the variables in the WHERE clause with the values you want to use.

      Delete