Home
**Under Construction and Subject to Change **
This wiki is the new face of the Connector/Arduino project. Here you will find (for those that abhor long technical documentation) how to get started quickly using the connector as well as several examples of using the connector with some of the more popular Arduino boards. Later, you can find more details about the connector and how it works.
For those of you new to this project, it is strongly recommended that you attempt the example sketches before attempting your complete project sketch. In fact, if you have problems, taking time to read through the troubleshooting section should provide you with answers to the most common problems. Pay particular attention to the limitations section.
Finally, please be sure to read all of the Wiki before opening a new issue for the connector. While the project page is monitored, it is not monitored daily so issues can go some time before being addressed. Plus, at this point, most of the common problems have already been solved. With that, enjoy!
- Quick Start
- Limitations
- Examples
- Troubleshooting
- Tips and Tricks
- Frequently Asked Questions
- Change Log
- API
- For More Information
To start using Connector/Arduino (hence connector or library), you must first have the Arduino IDE installed along with the correct drivers (if required) to access your Arduino board.
To install the connector, open the Arduino IDE and select Sketch | Include Library | Manager Libraries... and wait until the manger finishes loading. Then, type MySQL in the search box and click Install to install the latest version of the connector. The following shows an example of what you should see in the Library Manager.
You also need access to a MySQL server running on your local network or accessible from your local network (watch out for firewalls!). In addition, you need to create a user account on the MySQL server for your Arduino. Since most will be using a local MySQL server, it is fine to use an account that is not tied to a specific host. Savvy MySQL users will want to improve that if you plan to use a MySQL server on the Internet.
To create a user account that has access to the database(s) you want to use from your Arduino, issue the following commands. Substitute a user name and password of your choice. Also, be sure to list the databases that you want to grant access.
CREATE USER 'arduino_user'@'%' IDENTIFIED WITH mysql_native_password BY 'secret';
GRANT ALL ON <comma separated list of databases> TO 'arduino_user';
Notice the use of mysql_native_password in the IDENTIFIED WITH clause. This is for the latest versions of MySQL that support newer authentication protocols (the default has changed). If you are using a (much) older version, you may not need this clause.
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 wit 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
WHEREclause 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
#debugdirective 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_passwordplugin 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.
This section contains a list of suggestions for making better sketches with the connector. In some cases this is advice and in other cases it is suggested code or techniques. If your sketch will include more complex queries than those shown above, you should read this section for incorporation into your own sketches.
There are many example sketches included with the connector. You should run one or more of these to ensure you understand how the connector works before writing your own sketch. I recommend starting with the connect, basic_insert, and basic_select examples first. Get to know these and test them to ensure your MySQL server is setup correctly and your Arduino can connect to it. If you have trouble with these examples, do not blame the connector (at least not initially). Read the troubleshooting section below to solve one or more of the common problems and try your example again. Don’t forget to change the IP address, user name and password!
This one I feel is a given for writing code for microprocessors, but you may be surprised at the number of requests I’ve had for helping solve problems. The root cause or the significant factor for much of the users’ trouble stems around making the sketch far more complex than it needs to be.
This is especially true for those that write their entire solution before testing it. That is, they write hundreds of lines of code, get it to compile (sometimes not so much) then try to run it. In this case, the user has failed to realize all aspects of their solution should be tested in a step-wise fashion.
For example, write the sketch to do the minimalist steps needed to demonstrate (test) each part. For working with the MySQL database, start with a simple connection test then proceed to testing each and every query using dummy data or simulated values.
Likewise, working with sensors or other devices should be done in isolation so that you can eliminate major portions of the sketch for investigation should something go wrong.
If you adopt this philosophy, your sketches will be easier to write and you will have far more success than the “code it once and pray it works” philosophy.
Most sketches are written to connect once at startup. However, for complex solutions that collect or interact with the database, the connection is critical for longer running projects. It is often the case that networks can become unreliable. Indeed, there is nothing in the specification of the networking protocols or equipment to suggest it is always lossless. In fact, the network is design to be “mostly” reliable with some acceptable loss.
When loss occurs, it can sometimes cause errors in the connector when reading from the database or can cause the Ethernet shield to drop its connection. In extreme cases, it can cause the sketch to hang or loop out of control (depending on how the conditional statements are written).
To combat this, we can use a technique whereby we connect and close on each pass through the loop. This will work, but there is a more elegant solution that allows you to reconnect whenever the connection is dropped. The following demonstrates this concept.
void loop() {
delay(1000);
if (conn.connected()) {
// do something
} else {
conn.close();
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(500);
Serial.println("Successful reconnect!");
} else {
Serial.println("Cannot reconnect! Drat.");
}
}
}
Notice here we check the status of the connector and if it is not connected, we reconnect. This will save you from cases where the connection is dropped to network or database errors.
Closely related to the connect/close technique is a technique to reboot the Arduino should something bad happen. This can be really handy if you have a project that must work but is Ok if there are short data gaps. For example, if you are monitoring something and performing calculations it is possible your hardware could have periodic issues as well as logic errors or simple networking failures.
To overcome these situations, you can program the Arduino to reboot using the following code. Note that this shows this technique used with the connect/close option as they are complimentary. After all, if you cannot connect after N tries, a reboot cannot hurt and in most cases where it is a problem with memory or the Ethernet shield or related, it works.
void soft_reset() {
asm volatile("jmp 0");
}
void loop() {
delay(1000);
if (conn.connected()) {
// do something
num_fails = 0;
} else {
conn.close();
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(500);
Serial.println("Successful reconnect!");
num_fails++;
if (num_fails == MAX_FAILED_CONNECTS) {
Serial.println("Ok, that's it. I'm outta here. Rebooting...");
delay(2000);
soft_reset();
}
}
}
}
Notice here we use an assembler call to jump to position 0. This effectively reboots the Arduino microcode. Cool, eh? And you thought you’d have to slog out to the pig farm and press the wee little reboot button.
Another useful technique is monitoring or diagnosing memory problems by calculating how much memory is remaining. We do this with the following method.
int get_free_memory()
{
extern char __bss_end;
extern char *__brkval;
int free_memory;
if((int)__brkval == 0)
free_memory = ((int)&free_memory) - ((int)&__bss_end);
else
free_memory = ((int)&free_memory) - ((int)__brkval);
return free_memory;
}
You can use this method anywhere in the code. I like to use it with a print statement to print out the value calculated as follows.
Serial.print(" RAM: ");
Serial.println(get_free_memory());
Placing this code strategically in the sketch and watching the results in the serial monitor can help you spot memory leaks and situations where you run out of memory.
It is at this point that I would like to clarify one thing about using libraries such as the connector. This is advice for all who are learning how to program your Arduino. Be sure to do your homework and your own research before asking questions. So many times I get questions about the most basic things (well, basic to the experienced) that have nothing to do with the connector. For example, working with memory, variables, and strings seem to be stumbling blocks for new users.
In the end, you will get far more useful help from library authors and other experienced Arduinistas if you take some time to read a book, web page, or listen to a podcast before contacting the author for help or complain about a compiler error. A small amount of learning on your part will reap dividends when you can ask a specific question or seek help for a complex issue.
A case in point is this document. From my experience, this document is far more detailed than any other library available for the Arduino (with notable exceptions). Part of the motivation for writing this document was to consolidate the information about the connector and to ensure those new to using the connector had a sufficiently detailed tutorial. The following section completes the body of information about the connector by presenting the most common questions asked of users.
This section presents a short but proven practice for troubleshooting sketches that use the connector. Should you have a situation where your sketch fails or doesn't work when modified or moved to another network, deployed, etc., following this process can help isolate the problem.
- Verify the network. Try connecting another computer in place of the Arduino to ensure you can connect to the network and the database sever. Correct any network issues before moving on.
- Verify your user account. With the same computer, try logging into the database using the credentials in your sketch. Correct any issues with permissions and user accounts before moving on.
- Check permissions. If you restart your Arduino and still cannot connect, go back and verify your permissions again.
- Check network hardware. Make sure there are no firewalls, port scanners, etc. that are blocking access to the database server.
- Isolate your code. Once all connection problems are solved, check your code. Chances are you can comment out or remove most of the code to check only the bare minimum parts. I recommend breaking the code into sections and testing each until you encounter the section with the problem.
- Check your hardware. When all else fails, try another Arduino. I've seen cases where an Arduino breaks or has a short or some other failure where it can boot and run simple sketches but anything more than that it fails.
The following are a list of questions that have been asked numerous times on the forums. They address a lot of common pitfalls and explain a few new techniques not discussed above. Be sure to scan this list before making new inquiries on the forums. The following are listed in no particular order.
When a new version of the connector is released, a lot of testing is done to ensure the code works the same or better than the last release. However, sometimes new changes are introduced that can make older sketches not work as well. In the unlikely event this happens, you can always downgrade the connector in the Library Manager to a previous version. If that fixes the problem, check the change notes to see what has changed and whether you need to change your sketch. If nothing seems obvious, open an issue and report the problem so it can be fixed in the next release.
No. The connector was developed and tested to work with MySQL server from Oracle.
The connector only works with Ethernet shields and modules that support the Arduino Ethernet class. If your module requires a new Ethernet class that differs, it will not work with the connector.
The problem can be one of several things, but the most likely cause is running out of memory or dropping the network. Check your memory usage to ensure you have enough memory. You can switch to a large Arduino if your sketch outgrows your board. For network issues, you can use the connect/close or the reboot techniques above.
I'm getting “multiple definition of Connector::check_ok_packet() and similar compiler errors. What is wrong?
If you are seeing compiler errors about duplicate functions and similar, it is because you have the connector code in more than one place. That is, you have duplicated the code in your Libraries or sketch folder. On Windows machines, this is possible if you copy the archive to a temporary folder or unzip it in multiple locations. Be sure only one copy of the MySQL_* files exist in the Libraries folder.
I’m getting error: 'column_names' was not declared in this scope and similar compiler errors. What is wrong?
You must enable WITH_SELECT in MySQL_Cursor.h to enable the methods for processing result sets (SELECT queries).
Be sure to sprintf() and dtostrf() to format your query with variables. The code does not support variable substitution.
It is the port on which the MySQL server is listening. You can specify another port, but your MySQL server must be setup to listen on the port. 3306 is the default setting.
You should test your queries using the mysql client before attempting to run your sketch. Many times there are small syntax errors that you must fix before the query will work.
I purposefully disabled the code to process result sets to save a few bytes. That is, if your sketch (like most) are just inserting data, it does not need the extra code taking up valuable memory.
If you are getting a connection failed message (as written into your sketch), it is most likely your Arduino is not connected to the network properly or your user account and password is not correct or the user does not have permissions to connect. Use a second computer and the credentials from your sketch to check to see you can connect. Resolve any issues and retry your sketch.
The most likely scenario is you have not placed the connector in your Arduino Libraries folder or you have renamed it or you placed it in another folder. Be sure it is installed correctly and restart your IDE.
Yes, use one of the alternative set of parameters for the Ethernet class to setup the IP manually. See https://www.arduino.cc/en/Reference/EthernetBegin.
Yes, but it requires using the dns library as follows.
#include <Dns.h>
...
char hostname[] = "www.google.com”; // change to your server’s hostname/URL
...
IPAddress server_ip;
DNSClient dns;
dns.begin(Ethernet.dnsServerIP());
dns.getHostByName(hostname, server_ip);
Serial.println(server_ip)
Serial.println("Connecting...");
if (conn.connect(server_ip, 3306, user, password)) {
...
Yes, just make sure you have enough memory for the strings.
This error occurs when the connector receives the wrong packet header or an unexpected response from the server. It occurs most often when using select queries where there are additional rows that are not read. See the examples above to ensure you are processing the entire result set. You can also use a WHERE or LIMIT clause to help restrict the number of rows returned.
Check to make sure the baud rate of the serial monitor matches your sketch. Change one or the other to match and you should see valid characters.
You have one or more of the following incorrect:
- server address
- using static IP (try DHCP)
- the network connection isn't viable or behind a switch
- the user credentials do not work
Your best diagnostic is to use a second computer on the same Ethernet line with the same credentials (server address, user, password) and attempt to connect. If you can, then you may have a problem with your hardware.
You should use one of the examples that come with the Arduino IDE such as the Web Client sketch. Try this and if that works, you know your Ethernet shield is working. You can do the same for the WiFi shield. Once you verify the shield works, go back and check your MySQL server and test connecting to it from another computer until the credentials and permissions are correct.
The top causes are:
- the IP address of the server has changed
- there is a firewall blocking incoming connections on 3306
- the network port/router/switch doesn't work
- the user and host permissions are not correct (Cannot login)
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.
No. Only the Arduino Ethernet or WiFi shields.
Use a timestamp column in your table. This will be updated with the current time and date when the row is inserted.
Include the program memory space header then declare your string with the keyword as shown. Remember to use the optional second parameter in the query method when passing in these strings for queries.
#include <avr/pgmspace.h>
...
const PROGMEM char query[] = "SELECT name, population FROM world.city";
...
conn.execute(query, true);
Yes. There is an example on how to use the new WiFi Shield 101. See the File|Examples|MySQL Connector Arduino menu.
Yes and no. Yes, the connector will work with the new shield but you will need to make a minor change to the MySQL_Packet.h file. Open the MySQL_Packet.h file and change:
#include <Ethernet.h>
to:
#include <Ethernet2.h>
And no because you cannot use the new shield (currently) with the Arduino IDE from arduino.cc. You must download the arduino.org software, not the software from arduino.cc. Yes, there is a difference. I won't go into that here, but suffice to say there are differences. To download the IDE, go to http://www.arduino.org/software. You can run it along side another version, just make sure you install it in another location. Once installed, you can compile your sketch but first change the include directives to list the following.
#include <SPI.h> // <---- Add this include
#include <Ethernet2.h> // <---- Change to use the new library :)
This section is for those who want to know a bit more about how the connector works and what functions are available. If you plan to modify the connector for your own use or to improve some part under the license, this section should give you a place to get started.
You may submit questions and problems with using the connector (but not generic Arduino questions) as an issue on Github, but before asking your question, be sure you’ve read this document in its entirety before opening a new issue. Chances are, others have seen your problem and a solution already exists. I will also accept special requests emailed to me directly at [email protected], but I reserve the right to delay my response until time permits. Thus, do not expect an immediate answer (but sometimes I will respond within 24-72 hours).