Thursday, April 3, 2014

New! MySQL Utilities release-1.4.2-RC

The MySQL Utilities Team is pleased to announce the latest release candidate (RC) release of MySQL Utilities. This release includes a number of improvements for useabilty, stability, and a few enhancements. A complete list of all improvements can be found in our release_notes.

New Utilities!


We have also included two new utilities.
  • The mysqlrplsync utility was added, which checks data consistency between servers in a replicated setup. 
  • The mysqlrplms utility was added, which provides round-robin multi-source replication (a slave server continually cycles through multiple masters in order to store a consolidated data set).

How Can I Download MySQL Utilities?


You can download MySQL Utilities 1.4.2 from the following link using one of the pre-built installation repositories including a source download. Click on the Development Releases tab.

http://dev.mysql.com/downloads/tools/utilities/

Where is the Documentation?


You can find online documentation for MySQL Utilities version 1.4 at:

http://dev.mysql.com/doc/index-gui.html

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

Tuesday, February 4, 2014

Announcing MySQL Connector/Arduino 1.0.1 Beta

I've completed a new release of the Connector/Arduino. The new version supports a few refinements and a new feature.

  • New! disconnect() method - enables disconnect from server. Note: you must call mysql_connect() to reconnect.
  • Better error handling for dropped packets. No more random reboots when bad packet appears.
  • Library can recover from short-term loss of connectivity. Along with bad packets is a check to make sure what is received is valid making the connector ignore garbage packets associated with a dropped connection.
  • Detection of Out of Memory condition. Should there not be enough memory to allocate the buffer for the Connector, you will see an OOM error (enable the serial monitor to see the errors). This reduces random reboots when memory gets too low.
I made this release because a number of people were running into problems with noisy, tenuous, or just plain bad network connections. Also, some do not want to hold the connection open on the database server. This release addresses all of these issues.

Did you say disconnect?


Yes, that's right, you can now disconnect from the server should you want to write sketches that connect to the MySQL server for only a brief period then sleep, calculate the distance to Alpha-centuri, make coffee, etc. It is also helpful for those sketches that will update the database only once every few minutes, hours, or days permitting you to connect, run a query, then disconnect on the interval.

But wait...what about when the Ethernet shield goes wonky?


I have also devised a way to overcome the problem of the Ethernet shield controller going away. That is, if your Arduino looses connectivity for more than a few seconds (about 15-30), the Ethernet shield could wig out and fail to respond. The rest of your sketch will continue to run but calls to the Ethernet library will be ignored (how rude).

So...what to do? In short, we need to reboot the Ethernet shield. You could make a hardware-based connection to the reset button but some have reported problems with this solution. And it is a hard reset for the Arduino too - they are inseparable.

Rather than use hardware, I've devised a way to force the Arduino to reload its software. This won't fix any hardware issues like the reset button will but it will restore the Ethernet shield to proper operation.

Ok, I'm sold. How do I do it?


First, you need a variable and a define to set a threshold.

int num_fails;
#define MAX_FAILED_CONNECTS 5


Next, you need a method that will force the Arduino to reload. In this case, I use the tricky jump-to-zero address code which is sort of like a bootstrap (but not quite). Anyway, it works!

void soft_reset() {
  asm volatile("jmp 0");
}


To use this in your sketch, modify the loop() method (or where ever you put your MySQL connector code) as follows:

Note: this assumes your initial mysql_connect() call is in setup() like I originally intended. Modify the following accordingly if that is not the case.

void loop() {
  delay(1000);
  if (my_conn.is_connected()) {
    my_conn.cmd_query(QUERY_SQL); // <-- br="" goes="" here="" query="" your="">    delay(1000);
    num_fails = 0;
  } else {
    my_conn.disconnect();
    Serial.println("Connecting...");
    if (my_conn.mysql_connect(server_addr, 3306, user, password)) {
      delay(500);
      Serial.println("Success!");
      num_fails = 0;
    } else {
      num_fails++;
      Serial.println("Connect failed!");
      if (num_fails == MAX_FAILED_CONNECTS) {
        Serial.println("Ok, that's it. I'm outta here. Rebooting...");
        delay(2000);
        soft_reset();
      }
    }
  }
}


Notice the counter num_fails is incremented any time the connection to the MySQL server fails and is reset when it succeeds. If num_fails reaches the value of MAX_FAILED_CONNECTS, the sketch will call the soft_reset() method and the Arduino will be reloaded (not the same as restarting or resetting - keep that in mind if you use components that require a true reset to initialize).

So now if your sketch runs happily for a time but looses its connection to the database server for a long period, it will reboot itself and therefore reestablish a connection - assuming the network or server is back up.

Note: this code is in the hello_mysql_reboot sketch in the examples folder.

Enjoy!

Thursday, December 19, 2013

New! MySQL Utilities release-1.3.6 GA

The MySQL Utilities Team is pleased to announce the latest GA release of MySQL Utilities. This release includes a number of improvements for usability, stability, and a few enhancements. We have also included a performance upgrade for exporting, importing, and copying databases.

Improvements


The following highlights a few of the more significant improvements.

* mysqldbexport, mysqldbimport, and mysqldbcopy have multiprocessing support that allows for much improved performance
* mysqlfrm can now generate a .frm file with storage engine substitution
* Mac OS X packages added!
* mysqlserverinfo now includes the log files (error, general, slow)
* mysqlprocgrep can now search and kill processes by id
* mysqlmetagrep can now search the body of routines with the new --body option
* all utilities report license type with --version and --help
* all utilities have the new --license option to view the license text
* the mysqluc now reports errors with clearer text and tags the message with the name of the utility that returned the error
* mysqlindexcheck now warns user if there is not enough information to calculate best/worst indexes
* rpm, debian, and msi packages will update/remove old versions automatically when installing a newer version
* the documentation is now a separate reference manual (see link below)

The following spotlight some of the more important enhancements.

Multiprocessing with mysqldbexport, mysqldbimport, and mysqldbcopy


The performance of the mysqldbcopy, mysqldbexport and mysqldbimport utilities has been significantly improved. Moreover, a new --multiprocess option was added to allow concurrent execution making the most of the available CPU resources (the number of CPU cores).

Note: the --thread option in mysqldbcopy was replaced by the --multiprocess option.

Multiprocessing is applied at different levels according to the operating system. The utilities mysqldbcopy and mysqldbexport allow multiprocessing at the table-level for non-Windows systems and at the database-level for Windows system. The mysqldbimport utility allows multiprocessing at the file-level independently from the OS.

Other more specific options were also added for performance reasons for the other utility. A new --output-file option was added to mysqldbexport to specify a file to store the generated output which allows faster output than sending messages to the terminal.

Two additional additional options are now available in mysqldbimport: 1) --autocommit to enable autocommit for each operation because now by default a single commit is performed at the end of importing each file which is much faster, and 2) --max-bulk-insert to adjust the maximum number of inserts in a bulk, following the improved bulk insert support that is now provided.

Create New .frm Files with New Storage Engine


The mysqlfrm utility allows you to use the --new-storage-engine and the new --frmdir option to provide a directory to store the new .frm files. This feature is useful for those who want to recover the CREATE statement from existing .frm files and change the storage engine without having to launch the server. Try it out!

Mac OS X Installer


Yes, we now have a Mac OS X package installer. If you install Utilities with this installer, you will need to either use Connector/Python version 1.1.4 or later (which has a Mac OS X installer too) or use the Connector/Python 1.0.8 or later source code package and install manually.

How Can I Download MySQL Utilities?


You can download MySQL Utilities 1.3.6 from the following link using one of the pre-built installation repositories including a source download.

http://dev.mysql.com/downloads/tools/utilities/

If you are a commercial customer, you can download MySQL Utilities from the following link:

https://edelivery.oracle.com/

MySQL Utilities is also available on Lauchpad as a source download at:

https://code.launchpad.net/mysql-utilities

Where is the Documentation?


You can find online documentation for MySQL Utilities version 1.3 at:

http://dev.mysql.com/doc/index-gui.html

Wednesday, October 23, 2013

Introducing MySQL Connector/Arduino 1.0.0 beta

There is a new release of the Connector/Arduino on Launchpad! See https://launchpad.net/mysql-arduino. The new version supports a number of refinements and a few new features. These include:
  • Improved support for processing result sets
  • Conditional compilation to omit result set handling features to save program space
  • Support for the Arduino WiFi shield
  • New version() method to check version of the connector
  • Simplified download (no more patching SHA1!)

So What is It?


If you have never heard of Connector/Arduino, it is simply a library designed to allow the Arduino platform to connect to and issue queries to a MySQL Database server.

Simply add an Ethernet shield to your Arduino and use the library to connect your Arduino to a MySQL database server. Yes, no more web-based hand waving or third party systems! Cool.

New Feature : Improved Support for Result Sets


In the previous version of the connector, there was a method named show_results() which demonstrated how to read result sets (rows returned from the server from a SHOW or SELECT query).

Unfortunately, this method was too obtuse to be of any use to all but the most devoted connector fan (you had to know the source code really well). Perhaps worse, you had to modify the library directly to use the methods demonstrated.

Why was it like that? Simply because I felt SELECT queries would be very rare and used by only a very small number of people. I was wrong. Live and learn, eh?

The good news is the new version has additional methods that can be called from outside the library making it much, much easier to get results from your database. Let's see how to do this.

Example: Getting a Lookup Value


I think the most popular request for supporting SELECT queries was to allow for an easy way to query the database for a lookup value. Since lookup queries are (or should be) designed to return exactly one row, we can simplify the code as follows.

Recall when the MySQL server returns a result set, the first thing returned is a list of the columns in the result set. Next are the rows. So we must process the columns first.

  // SELECT query for lookup value (1 row returned)
  // Here we get a value from the database and use it.
  long head_count = 0;
  my_conn.cmd_query(QUERY_POP);
  // We ignore the columns but we have to read them to get that data out of the queue
  my_conn.get_columns();
  // Now we read the rows.
  row_values *row = NULL;
  do {
    row = my_conn.get_next_row();
    // We use the first value returned in the row - population of NYC!
    if (row != NULL) {
      head_count = atol(row->values[0]);
    }
  } while (row != NULL);
  // We're done with the buffers so Ok to clear them (and save precious memory).
  my_conn.free_columns_buffer();
  my_conn.free_row_buffer();
  // Now, let's do something with the data.
  Serial.print("NYC pop = ");
  Serial.println(head_count);


In this example, I query the database for the population of New York City (nervemind the validity of that value), then use the value by printing it out. Notice the basic structure is still there - read columns then read rows but in this case we ignore the columns because we don't need that data. We still need the free_*_buffer() calls to free memory however. I explain these methods in the next example.

Example: Processing Result Sets


The next most popular request for supporting result queries was being able to loop through a result set and do something with the data. In this example, I create a method in my sketch to execute the query and process the results. Let's look at the code first.

/**
 * do_query - execute a query and display results
 *
 * This method demonstrates how to execute a query, get the column
 * names and print them, then read rows printing the values. It
 * is a mirror of the show_results() example in the connector class.
 *
 * You can use this method as a template for writing methods that
 * must iterate over rows from a SELECT and operate on the values read.
 *
 */
/*
void do_query(const char *q) {
  column_names *c; // pointer to column values
  row_values *r;   // pointer to row values

  // First, execute query. If it returns a value pointer,
  // we have a result set to process. If not, we exit.
 

  if (!my_conn.cmd_query(q)) {
    return;
  }

  // Next, we read the column names and display them.
 
  // NOTICE: You must *always* read the column names even if
  //         you do not use them. This is so the connector can
  //         read the data out of the buffer. Row data follows the
  //         column data and thus must be read first.
 

  c = my_conn.get_columns();
  for (int i = 0; i < c->num_fields; i++) {
    Serial.print(c->fields[i]->name);
    if (i < c->num_fields - 1) {
      Serial.print(",");
    }
  }
  Serial.println();

  // Next, we use the get_next_row() iterator and read rows printing
  // the values returned until the get_next_row() returns NULL.
 

  int num_cols = c->num_fields;
  int rows = 0;
  do {
    r = my_conn.get_next_row();
    if (r) {
      rows++;
      for (int i = 0; i < num_cols; i++) {
        Serial.print(r->values[i]);
        if (i < num_cols - 1) {
          Serial.print(", ");
        }
      }
      Serial.println();
 

      // Note: we free the row read to free the memory allocated for it.
      // You should do this after you've processed the row.
 

      my_conn.free_row_buffer();
    }
  } while (r);
  Serial.print(rows);
  Serial.println(" rows in result.");

  // Finally, we are done so we free the column buffers
 

  my_conn.free_columns_buffer();
}


So what's going on here? Notice how the code is structured to execute the query and if there are results (cmd_query() does not return NULL), we read the column headers. Why? Because the server always sends the column data back first for every result set.

The return from the get_columns() method is a structure that contains an array of field structures. Here are the structures:

// Structure for retrieving a field (minimal implementation).
typedef struct {
  char *db;
  char *table;
  char *name;
} field_struct;

// Structure for storing result set metadata.
typedef struct {
  int num_fields;     // actual number of fields
  field_struct *fields[MAX_FIELDS];
} column_names;


Notice the column_names structure has a fields array. Use that array to get information about each field in the form of the field_struct (see above) structure. In that structure, you will be able to get the database name, table name, and column name. Notice in the example I simply print out the column name and a comma after each except the last column.

Next, we read the rows using a special iterator named get_next_row() which returns a pointer to a row structure that contains an array of the field values as follows:

// Structure for storing row data.
typedef struct {
  char *values[MAX_FIELDS];
} row_values;


In this case, while get_next_row() returns a valid pointer (not NULL indicating a row has been read), we access each field and print out the values.

You may be wondering what is MAX_FIELDS? Well, it is an easy way to make sure we limit our array to a maximum number of columns. This is defined in mysql.h and is set to 32. If you want to save a few bytes, you can change that value to something lower but beware: if you exceed that value, your code will wander off into la-la-land (via an unreferenced pointer). There is no end of array checking so tread lightly.

Notice also there are calls to free_row_buffer() and free_columns_buffer(). These are memory cleanup methods needed to free any memory allocated when reading columns and row values (hey - we got to put it somewhere!).

We call the free_row_buffer() after we are finished processing the row and the free_columns_buffer() at the end of the method. If you fail to add these to your own query handler method, you will run out of memory quickly.

Why is it a manual process? Well, like the MAX_FIELDS setting, I wanted to keep it simple and therefore save as much space as possible. Automatic garbage collection would have added a significant amount of code. Likewise array bound checking would have add a bit more.

You can use this method as a template to build your own custom query handler. For example, instead of printing the data to the serial monitor, you could display it in an LCD or perhaps use the information in another part of your sketch.


New Feature : Conditional Compilation


If you find you do not need the result set support, you can use conditional compilation to remove the methods and code from the connector. This can save you about 2k of program memory!

To do this, simply edit the mysql.h file and comment out this code:

//#define WITH_SELECT  // Comment out this for use without SELECT capability
                       // to save space.


This will tell the compiler to ignore key result set handling methods and code from the connector.

If you do this but find there are methods suddenly missing (via compilation errors), check your sketch to make sure you are not using show_results(), get_columns(), get_next_row(), and similar methods. This is because with the SELECT code turned off, these methods no longer exist in the compiled library. Uncomment the #define WITH_SELECT to add them back.

New Feature : Support for WiFi Shield


To use the WiFi shield, you need only make a few changes to your sketch and a minor change to the library.

Note: You will need to download the WiFi library and install it to use the WiFi shield. See http://arduino.cc/en/Main/ArduinoWiFiShield for more information.

First, add the #include for the WiFi library *before* the include for the connector (mysql.h).

#include <WiFi.h>  // Use this for WiFi
#include <mysql.h>


Next, setup your choice of WiFi connection options in your setup() method. While you're there, comment out the Ethernet.begin() call.

// WiFi card example
char ssid[] = "my_lonely_ssid";
char pass[] = "horse_no_name";

void setup() {
  Serial.begin(115200);
  while (!Serial); // wait for serial port to connect. Needed for Leonardo only

//  Ethernet.begin(mac_addr);

  // WiFi section
  int status = WiFi.begin(ssid, pass);
  // if you're not connected, stop here:
  if ( status != WL_CONNECTED) {
    Serial.println("Couldn't get a wifi connection");
    while(true);
  }
  // if you are connected, print out info about the connection:
  else {
    Serial.println("Connected to network");
    IPAddress ip = WiFi.localIP();
    Serial.print("My IP address is: ");
    Serial.println(ip);
  }
...


Lastly, you need to make one small change to the connector itself. Open the mysql.h file and uncomment these two lines:

#define WIFI       // Uncomment out this for use with the WiFi shield
#include <WiFi.h>  // Uncomment out this for use with the WiFi shield


This tells the connector to use the conditional compilation sections to turn on support for the WiFi shield.

New Feature : version() method


I've added a method to return the version of the connector as a string. If you don't have this method, you're using an old version of the connector. As more releases of the connector occur, this method will be key in diagnosing problems or checking for support of certain features.

(Somewhat) New Feature : Single File Download


This was actually added to the Launchpad site for the previous version of the connector (version 1.0.0 alpha). But I'm making it the default download method from now on. You can still get the code the old way (by using bzr to clone the tree) but the single file download makes it much easier.

Simply download the file, extract it, then place the two folders; mysql_connector and sha1 in your libraries folder then restart the IDE. Install done!

I hope you enjoy the new enhancements.

Tuesday, October 22, 2013

Announcing: New Forum for Connector/Arduino!

Due to the growing popularity of Connector/Arduino, the moderator of MySQL Forums has created a forum for us to meet up and discuss the connector. Yippie!

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

While the forum has been started very recently, I expect it will grow quickly as people discover the connector for the first time and experienced users find new and interesting ways to use it. I hope to moderate the new forum periodically to answer questions and respond to posts. See you there!

Note: you need an account to write to the forum. Click on "register" in the upper right hand corner of the forum page to create an account if you do not already have one.


Thursday, August 22, 2013

Announcing MySQL Utilities release-1.3.4 GA

The MySQL Utilities Team is pleased to announce the latest GA release of
MySQL Utilities. This release marks a milestone of concentrated effort to
expand the use of utilities in more diverse installations through improved
robustness, error handling, and quality.

Many Improvements


There are number such enhancements in this release. In this post we will
highlight a few of the more significant improvements.
  • (new utility) MySQL .frm Reader (mysqlfrm) - read .frm files and generate CREATE statements with or without a server connection.
  • (revised) improved documentation including a section on example administrative tasks - see http://dev.mysql.com/doc/workbench/en/mysql-utilities.html
  • MySQL Utilities is packaged for .msi, .rpl, .deb platforms and source .tar/.zip
  • You can run mysqlfailover as a daemon on POSIX systems
  • Improved accuracy redundant index checking algorithm for mysqlindexcheck
  • Improved accuracy for comparing databases with mysqldbcompare
  • The --exclude option for mysqldbcopy and mysqldbexport now accept database patterns
  • Improved quoting of database, table, index names in SQL generation
  • External script return code checking for mysqlfailover, mysqlrpladmin
  • Slave thread state included in verbosity output of mysqlrplshow
  • The mysqldbimport utility can now read raw CSV files with headers
...and that's just a few of the many improvements in this release. While many
of these improvements have been filtering into the 1.2.X release over the last
year, this release marks the first GA in that time frame.

How Can I Download MySQL Utilities?


You can download MySQL Utilities 1.3.4 from
http://dev.mysql.com/downloads/tools/utilities/ using one of the pre-built
installation repositories including a source download. MySQL Utilities is also
available on Lauchpad as a source download at
https://code.launchpad.net/mysql-utilities.