- Added to Library Manager : yes, you can download and install the library from the Arduino IDE now. Just open the Library Manager and search for "MySQL".
- More Example Sketches : there are many more example sketches of how to use the new library from basic connections to complex queries and more!
- Redesigned Classes : the library has been redesigned with new classes making it much easier to use a wider variety of shields and modules. Now, you pass in the Client class for your shield and so long as it adheres to the Ethernet.Client primitive, you can use any library to initiate the connector. No more #defines!
- Better Memory Management : the new classes permit you to manage memory easier by allocating the MySQL_Query class dynamically leaving memory cleanup for the delete operation. No more free_* methods!
- It's on GitHub! : yes, the code is now on GitHub making it much easier to log and track issues. See GitHub Repo.
Notes on Usage
If you have been using the previous versions of MySQL Connector/Arduino (1.0.4 and prior), do not despair as the old version remains in Launchpad and will be be left there for some time. Best of all, the new version does not cause conflicts with any of your existing sketches. That is, your existing sketches will not be affected by installing the new library.
However, if you want to use the newest version in your existing sketches, you will have to change a few things. Please see the section entitled "Changes from Previous Versions" in the reference manual located in the extras folder of the library source code.
How do I get it?
As mentioned, you can simply open the Library manager in the Arduino IDE from the Sketch->Include Library->Manage Libraries menu. This opens the Library Manager. In the filter your search box, enter "MySQL" then choose the connector and click Install. In seconds, the new library is installed and ready for use. Cool, eh?
You can also check the Library Manager periodically for updates to this or any of the libraries you have installed.
Of course, if you want to download the source code directly, you can find it on GitHub at GitHub Repo.
How do I use the library?
There is extensive documentation on how to use the library in the reference manual located in the extras folder. The document is named MySQL_Connector_Arduino_Reference_Manual.pdf.
What if I have Questions?
You can post questions to this blog or if you find a defect, open an issue of GitHub. However, before you do either, please read the documentation - even if you've been using the older versions as much has changed!
Enjoy!
Sorry but is not visible , can be difference from genuino and arduino , europe and usa version
ReplyDeleteLinks fixed. Try again.
DeleteOh, wait. If you are using the IDE from Arduino.org instead of Arduino.cc, or an older version, it may not show. The Library Manager is part of the Arduino.cc IDE version 1.6.7 (or latest).
Deleteit showed after i've added manually
ReplyDeleteYes, you can always do it that way.
Deletealso please correct github link in this useful guide, a spot is on the end of link and if someone copy it can be confused
ReplyDeleteLinks fixed. Try again.
DeleteHi Chuch
ReplyDeletevoid Connector::disconnect()
{
if (is_connected())
{
client.flush();
client.stop();
print_message(DISCONNECTED, true);
}
}
in this function stop and flush functions are used. How do use such functions while developing code in C.
I am able to connect to MySQL database as well as execute query using other functions.
I am not sure what you are asking.
DeleteIf you are asking, "What happened to the disconnect() method?" then you simply call close() instead.
If you are asking how do you do the same calls elsewhere in your sketch, you can call the client directly now that the client is no longer hidden in the connector.
In any case, please read the documentation as it outlines the changes from the older versions in detail.
Hi chuck,
ReplyDeleteI want to use SSL support in this arduino connector.
What should I do in coding to enable SSL.
Have you done SSL implementation in current MySQL/Arduino connector?
Hi. No, currently, SSL is not implemented. I left it out because there has not been much interest and it will take more memory to include.
DeleteHowever, it is not difficult to add and I welcome you to create a request in Github to track the work. Perhaps I can get some time to include it in a future release.
If you want to implement it yourself, I recommend studying the MySQL reference manuals (internals) for how SSL works internally.
Hello Chuck.
ReplyDeleteCan I use the next queries:
SELECT CURDATE();
SELECT CURTIME();
Any example of them?
Thanks Chuck's
Hi. Check out the example sketch "query_results" and the reference manual for how to handle query results.
DeleteHint: while you are only retrieving one row, you need to write the loop so that the end of result packet can be read.
As to the first question, yes, both queries will work. You can also do this:
Deletemysql> select curdate() as date, curtime() as time;
+------------+----------+
| date | time |
+------------+----------+
| 2016-01-26 | 21:02:55 |
+------------+----------+
1 row in set (0.00 sec)
All in one query.
Hello, I would like to use ESP8266 module coupled to the Arduino ethernet shield in place of wifi?
ReplyDeleteHi. Yes, you can use the library with the ESP8266. However, it will depend on which ESP8266 you use. The requirement is the ESP8266 networking library must be compatible with the Ethernet.Client library.
DeleteDo you have a specific module in mind?
hi, I AM STUCK WITH THE SAME ISSUE. I have an ESP8266-01, what libraries should I include and what changes are to be there in the code? Please help, I need this urgently.
DeleteThere are many libraries available for use with the ESP8266, some of which are not compatible with the connector. However, if you google for ESP8266 network (WiFi) libraries, you'll find many. Look for ones that are modeled and compatible with the Arduino WiFi library. Specifically, one that implements the Client class with the same methods. For instance, https://github.com/bportaluri/WiFiEsp has worked in the past. Simply declare a Client instance and pass it to the constructor as shown in the example sketches.
DeleteThank you for this nice software.
ReplyDeleteIt works fine with wifi, however the arduino hangs at MySQL query after some time. I update readings with 1 minute interval. It hangs sometimes after some hours. Completely random. Suspect network issue. What to do with MySQL settings? Why does the library not produce an error? Can you trap this error?
Further note to previous message:
ReplyDeleteProgrammed as follows:
Opened MySQL connection at setup. Checks before each query if connection is still a-live if so query. Apparently the connections is always a-live as I do not see a the message that the connection is/was re-established.
Hi. I would need to see your entire sketch before I can make any concrete suggestions. However, the number one cause of lockups is running out of memory due to memory leaks. Second would be networking errors.
DeleteTo remedy the first, fix the memory leaks. ;) To remedy the second, well, you can't but you can treat the symptoms by closing the connection on each step through the loop. See the reference manual for an example of how to do this. There is also an example sketch to demonstrate the concept.
Thx, Certainly no memory issue. I use Arduino Due. Sometimes it hangs after five minutes (5 queries), sometimes after 3 hours. (180 queries). I do close after each query and open again before each query. I use the same program now with Ethernet. This board arrived just 6 hours ago. It works fine since then. It is definitely a network issue. A pity there is no error trapping for the network problem.
DeleteYes, there is much to desire in Arduino networking hardware and library support. There really isn't any (easy) way to detect when packets go missing - which I've observed is one of the possible causes (but cannot confirm). Another possibility is I think the WiFi just goes wonky occasionally.
DeleteThere is one other thing to treat the symptoms that I've developed; a soft reset logic that forces the Arduino to reboot if the network goes dark for a long time. See the example named "reboot" for more details. Note: YMMV.
All that said, if you have ideas or suggestions for detecting and/or improving error handling, please share! :)
Thx for quick answer. Yeah, I came to same conclusion. A reboot does not work, because the Arduino hangs at the query and will then does not see the reboot code any more. The only possibility would be to use a separate hardware timer on the reset.... ! May be somebody comes up with improved error handling.
DeleteHi
DeleteSame problem using ESP8266 (no Arduino). Fails randomly with no wdt. Always fails in cur_mem->execute(query); line. Instant fail at same line can be provoked by making table inaccessible. It would be nice if the thing timed out with error rather than locking the ESP8266.
Hi. I am sorry you're encountering a problem. I want to help, but I need more information.
DeleteBut first, some history and limitations of use.
The connector is designed to be as lightweight as possible due to the limited memory footprint of the small devices it used on (Arduino). As such, it does not nor can it support the full MySQL protocol with all of the nice timeouts and error analysis, etc. Indeed, implementing even part of this would make using the connector useless on all but the larger (memory) Arduino boards.
However, I am always open to ways to improve the connector and it sounds like you've found a case where some extra error handling code could be helpful.
It would help me greatly if I had a full accounting of the use case you're working with including a description of how to set it up as well as the versions of the hardware, MySQL, etc. that you're working with.
Also, a setup for the precise table access issue is needed. For example, what do you mean by "inaccessible" - permissions changed, table dropped, etc?
It may also be helpful to see your sketch to see how you are using the connector.
Finally, what does "wdt" mean?
Let's work together to make the connector better! :)
Hi
DeleteThanks for prompt reply and indeed for the software itself.
Late at night here so only the easy ones for now.
wdt - WatchDogTimer.
I haven't tried many ways of making a table inaccessible but certainly stopping MySQLd will do it. The way I first found out was by opening the table to edit structure using Libre Office base from a remote machine. Does it really matter why there is no response?
MySQL Workbench says MySQL is 5.5.44-0+deb7u1 compiled for debian-linux-gnu (i686). This MySQL server never fails for any other type of access I have - MySQL, LO base, php mysqli_connect, Python MySQLdb - either locally or remote, from PC or RasPi as may be.
As for the sketch - well the example select will do it.....with appropriate user, server, password, etc., changes of course. Failure is more or less random anything from almost immediate to maybe 2 hours, but (in my sketches) it is more likely to fail on a select than an insert, maybe 5:1.
I have been using the dynamic cursor (method 2 in select example I think) as static one seems not to compile on ESP8266 (01 or 12). Loads of memory and Serial.println(ESP.getFreeHeap(),DEC); shows it going down through loop() and then being reclaimed.
As I understand it (but please believe my ignorance is woeful) the ESP8266 wdt is reset by any delay() (or yield()) allowing the TCP/IP stack to be serviced.
I have not tried this stuff on an Arduino either using the ESP8266 as a WiFi adaptor or with another WiFi adaptor (as i don't have one) or ethernet shield (ditto I but will order one tomorrow). I can't do anything for the next 10 days or so. I will check this page as I am able.
Thank you for being so positive.
"the example select will do it" - fail I mean.
DeleteHi Chuck,
ReplyDeleteThanks for your efforts to produce this library. I have been working on modifying the code to allow for the use of the Adafruit CC300 and have got this working with minimal mods to your code. Do you want to include this?
Sorry CC3000
DeleteYes, can you send me a diff of the changes?
Deleted r c h a r l e s b e l l at g m a i l dot c o m
I won't promise to include it verbatim, but supporting the CC3000 is on my list of requirements. ;)
DeleteSo I'm going through this same thing, The issue is with the cc3000 libraries in from what I can tell two places:
Delete-Adafruit_CC3000_Client::connect
reverses the IP address as IPAddress doesn't return what the address as Integer in the same way the cc3000 library expects
-Adafruit_CC3000_Client::available
does not return byte received, but what it had buffered so far or just 1 if nothing is buffered but data is available)
Frankly the cc3000 library is pretty sloppy... I've hacked it working but nothing clean enough to really be a fix. If I get time I'll fix it properly and do a pull request on that library.
So I've noticed my "fix" works with my Due but not with my Uno - this is a Due project so I'm ok with that for now. I'll also note all I'm doing is inserting data.
DeleteYes, I've not been impressed with the cc3000 library. I never understood why they wrote it so differently from the Arduino libraries.
DeleteI invite you to share your changes with me so that I can use it as input to making the connector work with the cc3000 library. You can contact me at:
d r c h a r l e s b e l l at g m a i l dot c o m
Hi chuck.
ReplyDeleteI have error using your mysql-connector for arduino ethernet shield and xampp.
It not move further than this:
Libraries: MySQL_Packet.cpp
void MYSQL_Packet::send_authentication_packet(char *user, char *password){
}
Xampp uses folders for localhost example 192.168.100.100/arduino
How can I add this folder for your example?
I do not understand. MySQL servers use TCP/IP + port, no folders.
DeleteBe sure to read the troubleshooting section in the documentation (in extras folder). Also, test your board and shield with one of the Ethernet class example sketches to ensure you have compatible hardware.
Hi Chuck,
ReplyDeleteThank you for creating this awesome library! I have one question that I hop you can point me in the right direction with..
I'm using digital ocean (cloud server provider) with an Ubuntu + MySQL as the server to store/read my data from the Arduino (+Ethernet shield). My problem is that I can't get a connection to the server, I believe it's something to do with requiring SSH connection to the server together with the root username/password.
I apologize in advance for being really new to MySQL and your library. I'm using your example code for the reading of the example DB as well which I created on the MySQL server via SSH (terminal on mac), so I know the MySQL server is working fine. I've also connected to the server via sequel pro (Mac MySQL software) to verify the DB is working. In sequel pro, I had to connect using the SSH login method though, hence I'm guess it's nothing wrong with the Arduino code/library, but some setting I need to set on my sql server to allow "regular ip + user/pass" connections.
Any help you can provide me with would be awesome! Thank you very much in advance Chuck!
Regards,
Lewis
Hi. Do you mean, SSL? If your MySQL server requires SSL connections, then, no, you cannot use the connector. The connector does not support SSL connections. This is because it would consume nearly all of the program memory in order to write the SSL portions for the Arduino.
DeleteIf you could post the command(s) you use to connect to MySQL via the command line, I may be able to discern more about what you're trying to do and offer pointers.
Hi,
ReplyDeleteis it possible to use the Connector library with UNO connected to network through SLIP (SerialIP library)?
Tomaž
I have never tried SLIP. From what I understand of the mechanism, it should be possible. But again, you'll be blazing the trail alone. ;) Try it.
DeleteI highly recommend you get the connector working first. Don't try using SLIP and setting up the connector code together. Get the one working first.
Oh. How can I get connector working while I don't posses Ethernet shield? My UNO is connected through USB on laptop which connects to local MySQL server.
DeleteWhen I try to compile example mysql_reboot, this is what I get:
/tmp/arduino_modified_sketch_548558/hello_mysql_reboot.ino: In function 'void loop()':
hello_mysql_reboot:47: error: 'class Connector' has no member named 'show_results'
my_conn.show_results();
^
exit status 1
'class Connector' has no member named 'show_results'
Ah, yes, that could be a problem. ;)
DeleteYour compilation error is explained in the reference manual. Specifically, you must enable WITH_SELECT in mysql.h to enable the methods for processing result sets (SELECT queries).
However, I once again caution against jumping into such an example. Use the connect example sketch until you solve all of your connectivity issues. Otherwise, problems like this will mask the other issues and perhaps lengthen your development time (as well as frustration).
Chuck,
ReplyDeleteI would really like to use this library on a college project I am working on. I am trying to connect to a MySQL server using a WiFi_101 shield, but I also have an Ethernet shield. I have a MySQL database setup through my university's server and a backup database through an online service. I have tried connecting to both using their IP's, but no success. Does this library only work with local MySQL databases hosted on a PC? Sorry if my lingo is not correct. Would love any advice on connecting to a database as I need to get this rolling.
You can use the connector to connect to any MySQL server. So long as you can get there by IP address (and port), there is no firewall (locally or remotely) blocking said port, and you have proper permissions to connect to the server.
DeleteRead the reference manual to learn more about how to troubleshoot connections. Hint: try connecting with your PC through a command prompt using the credentials provided by your University IT staff. Engage them until that works. ;)
A hardware specialist at my university advised me that security measures with the database are pretty strict and I won't be able to connect to it via the arduino. To get around this I created a new database on a free service (freemysqlhosting.net) that provides enough memory for my project. I used the IP address and port that I found for the host on phpMyAdmin to attempt the connection along with the provided username and password that logs me in to phpMyAdmin.
DeleteI am unsure if I need to do anything with the permissions that I am now reading about in the troubleshooting. I assume my user login credentials give me full read/write access? Maybe this free service has security that won't allow a connection or the IP address I found via phpMyAdmin is incorrect?
If you know of any online hosting service that has worked with this library in the past that would be awesome to hear as I have never built my own mySQL server from scratch
I don't know of such, no, but MySQL is very easy to install! Go take a look at the docs online (dev.mysql.com). If you're working on Windows, there is an installer that allows you to install and configure everything in a few clicks of the mouse.
DeleteOther than that, I would recommend installing the MySQL client tools at the very least and use the mysql client to connect to the server.
e.g.
mysql -uXXXX -p -h NNN.NNN.NNN.NNN --port=NNNN
If you can do that, the connector should work with the same credentials.
Thanks, will try this and hopefully report back a success.
DeleteI created my own MySQL on my laptop and was able to successfully connect using the ethernet shield and use the examples.
DeleteI am running into trouble when trying to run a select statement from my own table. The value it should be reading (as tested on the server) is 0 stored as int. I have it defaulted to 0 in the program as well.
My changed code from the example is the following:
char query[] = "select actionType from capstone.actionbitlog where lockID=1 order by actionTime desc limit 1";
void loop() {
row_values *row = NULL;
int type = 0;
delay(1000);
Serial.println("1) Reading type from last actionbitlog");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(query);
// Fetch the columns (required) but we don't use them.
column_names *columns = cur_mem->get_columns();
// Read the row (we are only expecting the one)
do {
row = cur_mem->get_next_row();
if (row != NULL) {
type = int(row->values[0]);
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
// Show the result
Serial.print(" type = ");
Serial.println(type);
The result I receive on the serial monitor is:
1) Reading type from last actionbitlog
type = 1466
1) Reading type from last actionbitlog
type = 1389
1) Reading type from last actionbitlog
type = 1481
1) Reading type from last actionbitlog
type = 1466
1) Reading type from last actionbitlog
type = 1389
1) Reading type from last actionbitlog
type = 1481
1) Reading type from last actionbitlog
type = 1466
Do you have any idea why I might be receiving these strange values in a pattern like this? I can't seem to find any significance to these numbers.
Anything helps.
Thanks,
Tom
What does that select give you in the MySQL client?
DeleteAdd a print to print out the raw value inside the row loop.
DeleteUsing the "2) Demonstrating using a local, global cursor." method I am able to read the correct value each loop. I use the value to control an LED on the board (going to control servo motor next) When I change the value on the server it takes about 2 seconds to turn on/off the LED on the board. Hope this info helps I'm glad to be making some progress
DeleteOk, good. Can you perhaps list a few rows from that table so I can check the dynamic cursor code?
DeleteExamples of a few rows from the table would be
Delete(1,1,'2016-04-06 10:00:45',1), (1,0,'2016-04-06 10:01:30',1)
Is the Wifi101 example sketch up to date?
Yes, it is uptodate. I do not see anything wrong with your sketch. Can you post the CREATE TABLE statement for the capstone.actionbitlog table?
DeleteIf you want to conduct our conversation via email, you can reply to:
d r c h a r l e s b e l l at g m a i l dot c o m
This comment has been removed by the author.
ReplyDelete