Limitations

This page presents the limitations of using the connector; what is capable of, do's and dont's, and general advice on hardware.

Microcontrollers by their nature have limited memory and limited processing power. Despite that, you can achieve quite a lot with the Arduino platform. However, there are real limits of what can be done within the limited memory and processing power of the Arduino platform with respect to memory intensive or processor intensive applications such as network clients.

Thus, it may come as no surprise that the Connector/Arduino library has a number of limitations that place bounds on what is possible with such limited hardware. The following lists the major limitations of the library for you to consider when building you own MySQL client sketches. While some of these may be mitigated by using a "bigger" Arduino, you should consider these the upper bounds for what is possible on the more popular Arduino boards.

• Query strings (the SQL statements) must fit into memory. The connector uses an internal buffer for building data packets to send to the server. However, the library supports the use of PROGMEM strings.
• Similarly, the combined length of a row returned from the server (data size in bytes) must fit into memory. Attempting to read a row from a table with 250 columns of integers isn't going to work. Keep your results sets as narrow (fewest columns) as possible and use a WHERE clause to limit the number of rows returned.
• Result sets from SELECT (for example) queries must be read starting with fetching the columns then the complete rows one row at a time and one field at a time. Failure to do so will result in mysterious packet errors (because you didn't read all of the data).
• Server error responses are processed immediately if and only if the #debug directive is defined. If so, the connector prints the error code and message to the serial monitor otherwise, the errors may be suppressed.
• The connector is written to support the current and recent releases of MySQL from Oracle Corporation. While there are variants maintained by other vendors, they may have some modifications that introduce subtle incompatibilities. If you encounter strange errors or issues using the connector with your MySQL server, ensure you are using the server binaries distributed by Oracle.
• For newer versions of MySQL that use the newest security plugins, you must use the mysql_native_password plugin for any user you want to use with the connector. The connector does not support the latest authentication plugins.
• The connector is written to adhere to the Arduino Ethernet (and compatible WiFi) class libraries. If you plan to use a shield that comes with its own library, be sure to check that it is 100% compatible (has same classes and methods). Otherwise, you will not be able to use the connector with that shield without modification.

Hardware Requirements

The connector requires an Arduino or Arduino clone with at least 32k of memory. If you are using an older Arduino like the Duemilanove, be sure you have the version that uses the ATmega328p processor.

If your sketch is more than a few lines long, you are using a lot of libraries, have a lot of sensors attached, or want to complex queries, you should consider using one of the larger (as in memory, not physical size) boards such as the Mega or Due. We will see why this is so in a later section.

The connector also requires the Arduino Ethernet shield or equivalent. This is because the library references the Ethernet library written for the Ethernet shield. If you have some other form of Ethernet shield, or the Ethernet shield you are using uses a different library, you will have to make a slight modification to the library to use it.

Finally, the connector is written specifically for the Arduino Ethernet and WiFi shields or modules that are compatible with the Ethernet class included with the Arduino IDE. If you have another shield or module that requires an additional library, it is likely it will not work with this connector. Only those shields that use the Arduino-supplied Ethernet class will work.

A Note About Memory

The connector is implemented as an Arduino library. While the protocol is lightweight, the library does consume some memory. In fact, the library requires about 20k of flash memory to load. Thus, it requires the ATmega328 or similar processor with 32k of flash memory.

That may seem like there isn’t a lot of space for programming your sensor node but as it turns out you really don’t need that much for most sensors. If you do, you can always step up to a new Arduino with more memory. For example, the latest Arduino, the Due, has 512k of memory for program code. Based on that, a mere 20k is an insignificant amount of overhead.

However, memory limitations can easily be reached when you use additional libraries. Each library you load will consume memory thereby reducing the available memory for dynamic variables. The connector must allocate memory to store the query being sent (as a static string) as well as the results returned (dynamic memory). Thus, if you have several queries you want to send, each one of those will require space and if you return rows from a query, each row requires space. A combination of these along can cause a moderately complex sketch on an Uno to run out of space.

You can do a lot to mitigate this problem. You can use a board with more memory, reduce the number of variables, reduce the size of the rows returned (by specifying a list of columns instead of SELECT *), and limit the use of libraries and unnecessary code. But the most important task you can do is to check your sketch for memory leaks.

Memory leaks will cause your sketch to lockup when it runs out of memory. See the FAQ section below for more details. As a rule, I suggest leaving at least 800 bytes of memory available for dynamic variables. You can see this when you compile your sketch as shown below.

Sketch uses 20,654 bytes (64%) of program storage space. Maximum is 32,256 bytes.
Global variables use 1,186 bytes (57%) of dynamic memory, leaving 862 bytes for local variables. Maximum is 2,048 bytes.

Here we see the sketch leaves only 862 bytes for local variables. This should be sufficient for most small sketches that simply write data to the database.

However, consider what would happen if you wanted to retrieve a row from the database that was 400 bytes in length. The connector would need to allocate memory for that row and therefore leave about 450 bytes left. Considering the Arduino uses this memory (e.g. the stack), this isn’t enough memory to permit the sketch to run properly. The end result is the Arduino will likely hang.

Consider also a case where you fail to release the dynamic memory allocated. In this case, consider a case where we want to retrieve a very small amount of data – say about 40 bytes. The sketch will likely run fine for some time depending on how often we retrieve the data but each time it does, 40 more bytes will be allocated and not returned thus after about 10 or so queries, the Arduino will run out of memory and freeze.

A Note About Networking Hardware

Your networking hardware should be the usual and normal devices typically found in a home or small office. That is, you should have some sort of router or access point that permits you to connect your Ethernet or WiFi shield to your network.

For example, a typical wireless access port or cable modem will have additional Ethernet ports that you can use to connect an Ethernet shield using a normal Ethernet (CAT5 or similar) cable. Do not use a crossover cable unless you know what one is and how to use it.

Similarly, if using a WiFi shield, your WiFi router should permit connections with the security protocols supported (see https://www.arduino.cc/en/Main/ArduinoWiFiShield for more details).

Furthermore, your MySQL server and Arduino must reside on network segments that are reachable. Ideally, they should be on the same subnet but that isn’t a hard requirement.

If you are not certain of your network configuration or you are attempting to build a solution in a laboratory at a university, college, or at work, you should seek out the local IT support to help you configure your hardware on the network. This section presents the limitations of using the connector; what is capable of, do's and dont's, and general advice on hardware.