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.