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.

Saturday, April 6, 2013

Introducing MySQL Connector/Arduino

Have you ever wanted to use a local database server to store data from your Arduino projects? Would you like to be able to send queries directly to a MySQL database from your Arduino sketch? Well, now you can!

The MySQL Connector/Arduino is a new technology made for the Arduino permitting you to connect your Arduino project to a MySQL server via an Ethernet shield without using an intermediate computer or a web-based service. 

Having direct access to a database server means you can store data acquired from your project as well as check values stored in tables on the server and keep the network local to your facility including having a network that isn't connected to the internet or any other network.

Example Code


The Connector/Arduino is an Arduino library that encapsulates everything you need to communicate with a MySQL server. It's also very easy to use. The following shows a simple sketch to connect to a MySQL server and insert a row of data at startup.

/**
* Example: Hello, MySQL!
*
* This code module demonstrates how to create a simple 
* database-enabled sketch.
*/
#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "mysql.h"


/* Setup for Ethernet Library */
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10, 0, 1, 23);

/* Setup for the Connector/Arduino */
Connector my_conn; // The Connector/Arduino reference

char user[] = "root";
char password[] = "secret";
char INSERT_SQL[] = 
 "INSERT INTO test_arduino.hello VALUES ('Hello, MySQL!', NULL)";

void setup() {
  Ethernet.begin(mac_addr);
  Serial.begin(115200);
  delay(1000);
  Serial.println("Connecting...");
  if (my_conn.mysql_connect(server_addr, 3306, user, password))
  {
    delay(500);
     /* Write Hello, World to MySQL table test_arduino.hello */
     my_conn.cmd_query(INSERT_SQL);
     Serial.println("Query Success!");
  }
  else
    Serial.println("Connection failed.");
}

void loop() {
}


As you can see, the library adds very few methods for communicating with a MySQL server.

What Can It Do?


The Connector/Arduino library allows you to issue queries to the database server in much the same manner as you would through the MySQL client application. You can insert, delete, and update data, call functions, create objects, etc. Issuing SELECT queries are also possible but they incur a bit more thought concerning memory management.

When issuing SELECT queries, the query strings must fit into memory and the largest row of a result set must also fit in memory. This is because result sets are read one row at a time and the class uses an internal buffer for building data packets to send to the server. The connector reads one packet-at-a-time and since the Arduino has a limited data size, the combined length of all fields must be less than available memory. It is suggested long strings be stored in program memory using PROGMEM (see cmd_query_P in the mysql.cpp file http://bazaar.launchpad.net/~chuck-bell/mysql-arduino/trunk/view/head:/mysql.cpp).

Most projects are those that need to store data and in that case the only memory requirements are those for the SQL statements. However, with careful planning, you can preserve memory by using parametrized queries.

Limitations


As you can imagine, a library that communicates with a MySQL server is larger than most libraries. Indeed, it consumes about 16-20k of program space. Fortunately, the latest Arduino boards have enough memory that only the most complex projects need be concerned. And in that case you can move to a larger Arduino board like the Arduino Mega.

Aside from memory, the following are some limitations you may want to consider when planning your sketches.

  • Query strings (the SQL statements) must fit into memory.
  • Result sets are read one row-at-a-time and one field-at-a-time.
  • The combined length of a row in a result set must fit into memory.
  • Server error responses are processed immediately with the error code and text written via Serial.print.

How To Get MySQL Connector/Arduino


You can download Connector/Arduino from LaunchPad (https://launchpad.net/mysql-arduino). The library is open source, licensed as GPLv2, and owned by Oracle Corporation. Thus, any modifications to the library that you intend to share must meet the GPLv2 license.

By popular demand, I have made available a zip file that contains the mysql_connector and patched sha1 code. Go to https://launchpad.net/mysql-arduino and download the zip file, extract it, and copy the mysql_connector and sha1 folders to your Arduino/Libraries folder.

For More Information


If you would like to get started using the library, please feel free to download it and checkout the example and the text files for specifics of installation and use.

However, if you'd like a full tutorial and learn more about using the Connector/Arduino in your project and to learn more about sensor networks, look for my book entitled Beginning Sensor Networks with Arduino and Raspberry Pi (Apress) due June 2013.


Note


The Connector/Arduino library was created to demonstrate the versatility of the MySQL client protocol and to provide a unique capability for the Arduino platform. While offered under the GPLv2 license, the library is not supported by Oracle.

Friday, April 5, 2013

MySQL Utilities: The New .frm Reader Utility

Have you ever wondered what was in those .frm files littered throughout your data directory? Better still, have you encountered a situation where your data is either missing (was deleted) or damaged and all you have is the .frm files but don't know the structure of the table? Well, wonder no more!

The MySQL Utilities Team is pleased to announce the newest utility - the .frm reader (mysqlfrm). This utility is designed to read .frm files and produce a facsimile of the CREATE statement for the table or view.

That's Impossible! How Can That Work?


It works by making a copy of the .frm file(s) and launching a new, read-only instance of your existing server. The server need not be running but you are required to provide an open port for the new instance with the --port option.

The utility will launch the cloned server without reading your configuration file (--no-defaults). The utility also makes some minor modifications to the cloned server instance configuration to allow reading of the .frm file without data. Yes, that's right - you don't need your data files to use this utility!

Using a new cloned instance and copying the .frm files means your original server is not altered in any way. The .frm reader also cleansup after itself by removing all temporary files and shutting down the cloned server.

Two Modes for Reading .frm Files


The .frm reader has two modes of operation. The default is intended for use in the normal process of discovering the CREATE statement in a .frm file. There is also a diagnostic mode for cases where the .frm file contains complex table settings or is damaged in some way.

Default Mode


The default mode, as described above, reads most .frm files and produces the CREATE statement for each. In this case, you need provide only the connection to the server via the --server option or the path to the server installation (for a downed server) with the --basedir option. You also need to provide a port with the --port option and, of course, a list of the .frm files you want to read or a list of directories to scan for .frm files.

If you do not want to (or cannot) use an existing server to clone, you can use the --diagnostic mode instead.

Diagnostic Mode


The diagnostic mode reads the .frm files byte-by-byte and makes a best effort to read the data in the file. We say best effort because there are many nuances to the .frm file that have been introduced over the years. Suffice to say that without the servers code to assist, deciphering the data is non-trivial.

We built the diagnostic mode as a feature to make it possible to get something useful from the files in the event the file is damaged or unreadable by the server or has complex table settings that the default mode cannot read or gives an error while reading. Thus, the CREATE statements produced in this mode may not be completely accurate and may be missing some parts. To get the most out of the diagnostic mode, provide a server connection to allow the reading of the character set information. This will improve the accuracy of column definitions.

While this may sound like the diagnostic mode isn't as useful, remember that it is designed to be a tool for diagnosing problems (hence the name) rather than a duplication of the server code. If you think about it in that light, the diagnostic mode is a very important tool that you may need in certain situations where no other tool will work.

While its accuracy is limited today, we plan to improve the feature in the future.

What's the Catch?


If you're thinking this is too good to be true, you're right - there are some limitations. Fortunately, these limitations are, er limited, for the default mode.

The default mode currently cannot handle storage engines marked as PARTITION and PERFORMANCE_SCHEMA. Also, some elements of the table structure are not stored in the .frm file so these are not included in the CREATE statement. Again, fortunately this is a short list that currently includes foreign keys and autoincrement values.

If you find the utility reporting it cannot read a .frm file in the default mode, try it again with the diagnostic mode.

Is That It?


The utility has a few features to help make it more versatile. For example, you can see the statistics for each file (dates, size, etc.) using the --show-stats option and you can substitute a new storage engine to be printed in the CREATE statement with the --new-storage-engine option (applies to all files read for each run of the utility).

There is also a debug mode that prints more information. When used with the diagnostic mode, and you can see the actual values read from the file.

Skip the Hype and Show Me How it Works!


Suppose you find yourself in a situation where your server has gone wonky in such a way as to make your data inaccessible. Suppose that you do have access to your .frm files but no record of the latest changes to the schema. Now suppose you have a backup of the raw data. How do you know if the .frm files in your existing server match those in the backup? Simple: just run the .frm reader on your existing server .frm files then compare the results to your backup.

But wait, what if the data directory is protected (your datadir is protected, isn't it)? Well, the .frm reader provides the --user option to allow you to launch the utility with elevated privileges to read the .frm files but execute the cloned server with a different user account.

Observe the command:

$ sudo env PYTHONPATH=$PYTHONPATH mysqlfrm --server=root:pass@localhost --port=3310 --user=mysql /usr/local/mysql/data/employees
In this command, we clone the server with the mysql user account and tell the .frm reader to read all of the .frm files for the employees database.

Here's the output.

# Source on localhost: ... connected.
# Starting the spawned server on port 3310 ... done.
# Reading .frm files
#
# Reading the departments.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/departments.frm:
#

CREATE TABLE `employees`.`departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the dept_emp.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/dept_emp.frm:
#

CREATE TABLE `employees`.`dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `emp_no` (`emp_no`),
  KEY `dept_no` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the dept_manager.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/dept_manager.frm:
#

CREATE TABLE `employees`.`dept_manager` (
  `dept_no` char(4) NOT NULL,
  `emp_no` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `emp_no` (`emp_no`),
  KEY `dept_no` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the employees.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/employees.frm:
#

CREATE TABLE `employees`.`employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the salaries.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/salaries.frm:
#

CREATE TABLE `employees`.`salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the titles.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/titles.frm:
#

CREATE TABLE `employees`.`titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the view1.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/view1.frm:
#

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `employees_temp`.`view1` AS select `employees`.`t1`.`a` AS `a` from `employees`.`t1`

#...done.


Is that cool or what? Notice it also reads .frm files for views. Very cool.

What About Diagnostic Mode?


Let's take one of the tables in the employees database and run it with the diagnostic mode. For fun, lets change the storage engine and show the file statistics. We specify the server (this is optional) so that if there are multiple byte character sets the diagnostic mode will correctly translate the field lengths. For example, if your table has a 3-byte character set and you do not provide a server connection, there is no way to know that a value of 30 read from the file is actually a field length of size 10 thus char(10) would appear char(30) without the server connection.

Observe the command and output:

$ sudo env PYTHONPATH=$PYTHONPATH mysqlfrm --server=root:pass@localhost --diagnostic --show-stats --new-storage-engine=MEMORY /usr/local/mysql/data/employees/titles.frm
# Source on localhost: ... connected.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /usr/local/mysql/data/employees/titles.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `employees`.`titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) COLLATE `latin1_swedish_ci` NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
PRIMARY KEY `PRIMARY` (`emp_no`,`title`,`from_date`),
KEY `emp_no` (`emp_no`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

# File Statistics:
#         Last Modified : Thu Jan 26 14:23:14 2012
#         Creation Time : Wed Jan  4 12:15:21 2012
#         Last Accessed : Fri Mar 22 15:18:33 2013
#                  Mode : 33200
#                  Size : 8672

# Table Statistics:
#                Engine : INNODB
#           frm Version : 10
#         MySQL Version : 5.1.50
#      frm File_Version : 5
#               IO_SIZE : 4096
#  Def Partition Engine : None

#...done.


Interesting, eh? If the disclaimer is scary, it is intended to be a reminder that the output may not be as accurate as the default mode. In this case, we see some very minor differences but none of which are showstoppers nor do they demure the usefulness of the output. Lastly, notice we did indeed change the storage engine.

Notice also the file stats show the modification and creation date as well as the original storage engine and version of the server when the .frm file was created.

Ok, I'm Hooked! Where Can I Get It?


The .frm reader utility is part of the new release-1.3.0 Alpha available as a separate download at http://dev.mysql.com/downloads/tools/utilities/. Simply choose the platform repository or source repository and download it.

We welcome your comments and hope that this utility will help expand your diagnosis and recovery toolkit.

Postlude 


Many thanks to Giuseppe Maxia for creating the surprisingly useful employees test database! You can download it from: https://launchpad.net/test-db/

Introducing MySQL Utilities release-1.3.0

The MySQL Utilities Team is pleased to announce a major advancement of MySQL Utilities. It is now available as a separate download!

That's right. If you want to use MySQL Utilities without installing MySQL Workbench, you can do that now.

The Utilities release-1.3.0 has been built for Windows Installer, RPM archive, and .tar/.zip. We have also made downloads for source only if you want to use Utilities to develop your own utilities or install the product in custom location. We plan to add other repositories in the future.

Is that it? Well, not quite. We have also included a new utility - the .frm Reader. See the blog, "New Utility: .frm Reader" for more information.

If you'd like to try out the new download, visit the MySQL Workbench download page:

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

Note: this is an alpha release and will follow the alpha-beta-GA development path. As such, it is not included in MySQL Workbench. However, it does include all of the improvements from the release-1.2.X tree.

For more information about switchover, failover, replication and MySQL high availability, download the "Guide to Building a Self-healing Replication Topology", which discusses how Global Transaction Identifiers and the MySQL replication utilities can be used for failover and switchover with slave promotion from:

http://www.mysql.com/why-mysql/white-papers/mysql-replication-high-availability/

Introducing MySQL Utilities release-1.2.1

The MySQL Utilities Team is pleased to announce our latest release, version 1.2.1. This release contains many quality improvements and enhancements to the HA and Replication utilities. The following lists some of the most significant improvements.
  • Improved transaction gathering algorithm for failover
    • Skips slaves that are already caught up
    • Ensures all transactions in the relay logs on the slaves are executed first
  • External scripts in mysqlfailover and mysqlrpladmin now receive the old and new master information
  • Improved demote master handling for switchover
  • Improved connection error handling
  • Quoting of tables and database names has been improved
  • Login-path feature now reads port and socket
The utilities team continues to focus on improving usability, making features easier to use, and improving overall quality of error handling.

You can try the new release by downloading it from LaunchPad at:

https://launchpad.net/mysql-utilities

For more information about switchover, failover, replication and MySQL high availability, download the "Guide to Building a Self-healing Replication Topology", which discusses how Global Transaction Identifiers and the MySQL replication utilities can be used for failover and switchover with slave promotion from:

http://www.mysql.com/why-mysql/white-papers/mysql-replication-high-availability/

Note:
this is an interim release. These features will be included in a future release of MySQL Workbench.