-
Notifications
You must be signed in to change notification settings - Fork 133
Hardware Guide
One of the most often asked questions about the connector is how to make it work with a specific set of hardware. Most of the questions center around how to make the connector work with a specific Arduino board, shield, networking module, etc. While it isn't practical to list each and every combination of hardware and how to modify the connector to make it work, there is a way you can determine if your hardware is capable of using the connector. This section reveals this mystery by providing advice in the following areas.
- Arduino Compatibility
- Modifying the Connector
- Selecting the Right MySQL Server
- Setting Expectations
Once you've read through the following sections, you should have a better idea whether you can use the connector with your chosen hardware and what (if any) changes are needed. Indeed, you can use these sections as an evaluation guide to help you set your hardware up for use with the connector. If you find any of these areas do not match your hardware, you can at least be prepared for any potential challenges if you choose non-Arduino or similar unorthodox hardware.
The first and most common hardware (and software) issue concerns Arduino compatibility. For the most part, any board that is offered by Arduino.cc or is built to 100% compatibility with said boards, should work well with the connector (assuming there is enough memory - see limitations).
However, the water is less clear if you are using hardware that is "Arduino friendly" or is touted as being usable by the Arduino IDE. For example, there are hardware boards out there that require their own hardware libraries (even some Arduino.cc boards do this), but often times these hardware libraries contain slightly different versions of the underlying supporting libraries that may or may not work correctly.
A case in point are the plethora of ESP-derivative modules that, primarily because they are inexpensive, are used by many people. Most of these boards are great and will provide you with an excellent Arduino experience, but some simply aren't compatible enough or have enough memory or processing power to work with a library like the MySQL Connector/Arduino. If you plan to use such a board, make certain you test it with example sketches and particularly with generic networking sketches before attempting to use it with the connector.
More importantly, if your hardware comes with its own library, you should ensure it works with the connector. The connector was written to work with any networking shield connected to an Arduino or Arduino compatible board with sufficient memory (see limitations) that also conforms to the Ethernet or WiFi libraries.
More specifically, if you are trying to use an Arduino-like device with a shield or module that is advertised as "Arduino friendly", be certain the hardware used the Arduino networking libraries. If either the device or the shield or module require special compilers, libraries, etc., it is unlikely the connector will work without modification.
That isn't to say the connector won't work with your hardware; only that you may have some work to do to make it work. For example, some shields and modules come with their own libraries that can and will work because they have the same classes and methods as the Arduino libraries. The trick to getting them to work is modifying the connector code to use the new library.
If your networking hardware uses its own library that is compatible (has the same classes and methods) as the Arduino networking libraries, you may be able to use it with your sketches. However, you must first download and install that library in your Arduino IDE. This is the most commonly misunderstood aspect of using the connector.
However, before trying to use that new library, you should open one of the example sketches provided by the library (you can find them under the menu for your board/module in the File | Examples section in the Arduino IDE). Once you verify your shield or module works with your network, you can then modify the connector to use it instead of Ethernet.h or WiFi.h.
To do so, open the MySQL_Packet.h file and find the following section of code. #ifdef ARDUINO_ARCH_ESP32 #include <Arduino.h> #elif ARDUINO_ARCH_ESP8266 #include <ESP8266WiFi.h> #else #include <WiFi.h> // #include <Ethernet.h> #endif
Notice this is a conditional compilation directive that is setup to automatically recognize the ESP32 and ESP8266 boards. How cool is that? If you are not using either of those, the directive includes the Ethernet.h library.
Note also the WiFi.h library is commented out. To use the WiFi.h library, you can comment out the Ethernet.h line and uncomment the WiFi.h line.
If you have a different library to use, comment out the Ethernet.h line and add a line to include your own library. For example, if your library was named strange_hardware_ethernet, you'd include the following (shown in context).
#else
// #include <WiFi.h>
// #include <Ethernet.h>
#include "strange_hardware_ethernet.h"
#endif
Once you've modified the connector for use with your shield or module, you can now test it with one of the sample sketches. Begin with the Hello, MySQL! sketch. If you have any problems compiling the sketch, be sure to double check that your new networking library is 100% compatible. If it is not, you may not be able to use that shield or module with the connector.
This was discussed in the limitations section, but it is important to include as part of the process of getting your hardware ready. Recall, the connector was written for use with an Oracle-provided MySQL server version 5.6 or later. While there are other variants of MySQL that may work, you should start by using an Oracle-provided release until you are certain your sketches work. Too many times users have attempted to write their entire sketch only to discover some minor incompatibility with their MySQL installation. Do yourself a big favor and start with the version that is known to work (Oracle's MySQL) then, if you're brave enough, you venture down the variant pathway.
In addition, if you are using MySQL 8.0, be sure to use the mysql_native_password authentication plugin for all of the user accounts you create. The connector does not conform to the newer SHA1-based authentication plugins.
Finally, a word about expectations. The connector is a bare bones MySQL client. It is not a full featured, do everything MySQL client. It was written to give basic query capability to Arduino projects. As such, you must set your expectations accordingly.
For example, you cannot expect to write a sketch that saves a thousand samples per second, a sketch that queries a table, retrieves all of the rows, and performs calculations on the data. It just isn't meant for that and your Arduino isn't likely to have the capability for such.
Thus, you should consider using the connector for collecting data and possibly querying a few rows at a time if needed for reference. Here is a list of some of the things you should be able to achieve, which should give you an idea of what is possible.
- Save data to a table once every N seconds
- Update a row in a table with data read from sensors or other sources
- Lookup a specific row in a reference table to make a foreign key link
- Return a few rows of data for a web-page lookup
While that list is by no means exhaustive, the following is a list of things that are impractical at best and not possible for most Arduino projects.
- Saving multiple sensor values multiple times per second (too fast for the hardware)
- Performing administrative SQL commands
- Returning more than a few rows saving the data in memory
- Reading a large file of data and forming batch inserts/updates
- Using blob or large text columns (such as storing images in a table)
- Returning "wide" rows that contain more data than the Arduino has memory to store
- Intensive manipulation of the data prior to storing it
If you find yourself wanting to do some of these things, there are some things you can do that may make it possible to perform some of these actions. The following tips should help you set your expectations if you must perform some of these more intensive operations.
- Reduce the "width" of data retrieved - return only those columns you need and nothing more.
- Reduce the number of rows retrieved - issue multiple queries if you need to retrieve more than a few rows at a time.
- Store the data in a raw form and use triggers or functions on the MySQL server to massage data (hint: use a TIMESTAMP column to save datetime)
- Avoid any and all DDL and administrative commands - use MySQL Shell on your PC to do those operations
Now that you know more about what is and isn't possible with the connector, you can use this as a guide when building your Arduino projects. With a little care and planning, you can make very sophisticated data collection solutions and store your data in MySQL. Finally, the best advice is to start small, add one feature at a time, and stay within what is practical for the Arduino platform. Do that, and all of your Arduino projects will be enjoyable and successful.
It is possible the hardware (board) libraries for various Arduino boards and clones can change over time. In some cases, this can lead to minor compilation errors. If you encounter compilation errors in the connector code, make sure you have the latest code from Github and check the FAQ for more details on solutions for specific issues.