The annual MySQL Conference & Expo will be held in this year on April 20-23 in Santa Clara, California with a double twist.

Not one, but *two* FREE additional MySQL Conferences are running at the same time, in the same hotel. If you on the west coast you can effectively get a free conference with many MySQL experts speaking at them. I am speaking at all three on three different topics.

The first announcement was the 2009 MySQL Camp organized by Sheeri K. Cabral – The She-BA in line with the O’Reilly approach of having a smaller un-conference within a conference such as with Web 2.0 NY last year.

However the big news was the Percona Performance Conference which was to be held in an adjacent location at the same time, is available now within the same hotel, the Hyatt Regency. In some regards, the Schedule is a better lineup then the official conference.

Percona is a major player in providing services to the MySQL eco-system, the failure to include Percona speakers such as Baron Schwartz was seen as a bizarre move. As a member of the MySQL Conference review committee, Percona submitted a number of presentations, and a number received high scores, more then sufficient in comparison to others accepted.

The MySQL eco-system is at a very fragile junction point, there are numerous independent versions, patches and forks now available, and the recent decision by Sun Microsystems, the trademark owner of the ‘mysql’ term to request Google to enforce trademark usage within Google Ad Words did not seem a productive move for a open source produce. Read more at
Hurting the little guy and MySQL Banned In Google Adword Campaigns. Combined with recent news that MySQL, may now become part of IBM, greater uncertainty may prevail.

These conferences will be unique opportunity to see and hear hopefully more details of what’s happening in the MySQL World.

While working with Monty Widenius on a bug I reported in MariaDB I was surprised to not get an IRC response for a few minutes. When committed Monty can identify, create a workaround, and patch a problem in code before you have time to read all the responses he also types. See my Monty’s Monument “Passion is a timeless wisdom” comment. One thing I forgot to say in that entry was, while 9pm in New york, it was at last 3am-5am Monty time.

We all love memorable quotes, so here is one.

“sorry, machine died; First time in years”

[9:40pm] montywi: then just try compiling mysqld.o, no need to wait for everything else...
....
[9:58pm] rbradfor: make is clean.
[9:59pm] montywi: sorry, machine died;  First time in years
[10:00pm] rbradfor: laughs out load, a priceless monty quote, love it.
...

I have raised this specific topic 3 times this week alone, twice in a MySQL setting.

The fundamental philosophy of testing is NOT to verify features of your product that work, it is to BREAK your system.

One such discussion this week was with a service provider that deployed a new system into an existing ecosystem. The release has been delayed due to development issue, and credibility with customers is now being further damaged because the system is reaching physical hardware limitations after just one month.

With this was described to me, my simple response was. You did not test you system to stress the system to breaking point. To know the limit of your capacity ahead of time is a proactive analysis, not a reactive one.

It’s not that complicated to do, easier in early stage before you have a 50-100-1000 server total environment, but it’s a best practice not see often enough.

Correctly managing your MySQL database objects such as schemas, tables, indexes, base data etc, is critical to the success of a 24×7 online website. I rarely encounter a robust working solution as part of my consulting so I would like to share my experience in identifying the best practices you should be adopting whether your an existing organization or just an individual with a simple website.

Much of the following concept actually pre dates my involvement in MySQL (since 1999), so this is not just applicable for a MySQL RDBMS. For the purposes of this discussion I’d like to focus on the theory successfully used with clients.

Under version control I have the following directory structure:

NOTE If your first observation was “Arrh, Version Control?”, you are in more trouble then you want to be right from day one. You need Version Control such as svn, cvs, bzr, git etc for any website no matter how small.

/database
  /scripts
  /sql
    /schema
    /patch
    /revert
    /admin
  /data
  ....

The /database is a top level directory, and for software packaging for all database related operations, you simply include all contents from /database.

At it’s core, every database object change for configuration management will be addressed in three (3) files.

• A schema file
• A patch file
• A revert file

In fact, you can add version control rules for example to ensure if you add a patch file, a corresponding revert and schema file is also specified.

For a “current” working environment, there are two paths for database object management.

• An upgrade path
• A new version creation.

An upgrade path which is the normal production operation, takes an existing database schema and ‘patches’ this to a new revision. As the name suggests, for each ‘patch’ file a corresponding ‘revert’ file can be used to revert the upgrade. For testing and development environments, a current version of the full schema can always be created without using the upgrade path simply by creating the schema with the current schema file.

For the purposes of understanding how this would work in a real environment, I’ll use the Sakila test database and I’ll step through a few examples.

Seeding your configuration management

Because we already have an existing schema, the first step is to seed our new configuration management with the existing schema information.

This would actually involve some duplication, however this will become more apparent in future examples.

We will be creating the following three (3) files:

• /database/sql/schema/schema.sakila.sql
• /database/sql/patch/patch.20090303.01.sql
• /database/sql/revert/revert.20090303.01.sql

/database/sql/schema/schema.sakila.sql
This will be a copy of the sakila-db/sakila-schema.sql. You will need to edit this file to remove the following lines.

DROP SCHEMA IF EXISTS sakila;
CREATE SCHEMA sakila;
USE sakila;

All configuration files must not contain any schema definitions. This will be discussed in more detail at a later time.

/database/sql/schema/patch.20090303.01.sql
This will be a copy of the above file.

/database/sql/schema/revert.20090303.01.sql

DROP PROCEDURE IF EXISTS rewards_report ;
DROP FUNCTION IF EXISTS get_customer_balance;
DROP PROCEDURE IF EXISTS film_in_stock;
DROP PROCEDURE IF EXISTS film_not_in_stock;
DROP FUNCTION IF EXISTS inventory_held_by_customer;
DROP FUNCTION IF EXISTS inventory_in_stock;
DROP VIEW IF EXISTS customer_list;
DROP VIEW IF EXISTS film_list;
DROP VIEW IF EXISTS nicer_but_slower_film_list;
DROP VIEW IF EXISTS staff_list;
DROP VIEW IF EXISTS sales_by_store;
DROP VIEW IF EXISTS sales_by_film_category;
DROP VIEW IF EXISTS actor_info;
DROP TABLE IF EXISTS actor;
DROP TABLE IF EXISTS address;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS country;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS film;
DROP TABLE IF EXISTS film_actor;
DROP TABLE IF EXISTS film_category;
DROP TABLE IF EXISTS film_text;
DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS language;
DROP TABLE IF EXISTS payment;
DROP TABLE IF EXISTS rental;
DROP TABLE IF EXISTS staff;
DROP TABLE IF EXISTS store;

For the purposes of this theory, I will discuss schema creation and management at a later time. For this example, we assume the ‘sakila’ schema has been created and is empty.

The ‘two’ paths

The default path is to apply the patch file to the appropriate schema. In this case, by using the patch file, this would create the current ‘sakila’ schema.

If this fails for example, you should automatically apply the revert script which should restore your environment to it’s original state, in this case an empty schema.

If you wanted to create a new test environment for example, (following creation of the schema), you could simply apply the schema file.

Let’s perform another iteration, to see the full working process.

Adding new objects

Let’s say we wanted to keep additional information such as famous quotes an actor has made. We want to create a new table ‘actor_quote’.

For this we would first create a patch and revert script to manage this new object.
/database/sql/patch/patch.20090303.02.sql

CREATE TABLE actor_quote (
  quote_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_id SMALLINT UNSIGNED NOT NULL,
  quote   VARCHAR(200) NOT NULL,
  PRIMARY KEY  (quote_id),
  KEY idx_fk_actor_id (actor_id),
  CONSTRAINT fk_actor_quote_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

/database/sql/revert/revert.20090303.02.sql

DROP TABLE IF EXISTS actor_quote;

/database/sql/schema/schema.sakila.sql
The contents of the patch file should be appended to this file.

The ‘two’ paths

If we look at the two paths again.

The normal production operation, by using the patch file, would create the new database object.

If this fails for example, you should automatically apply the revert script which should restore your environment to it’s original state, in this case drop the table if it exists. In this simplest example,

If you wanted to create a new test environment for example, (following creation of the schema), you could simply apply the schema file.

Review

We have only touched on the entire process of configuration management for database objects. The implementation of this practice includes meta data and controlling scripts that manage the order of execution, recording operations performed successfully or unsuccessfully for example.

About Standards

Within this overview a number of standards are in place. These include:

• SQL scripts do not contain any CREATE/DROP DATABASE|SCHEMA commands
• SQL scripts do not contain any schema/database specific references. This is important for being able to easily test and verify operations. In our above examples, the default Sakila DB contains such information and would be edited appropriately.
• For Patch and Revert files a chronological date format for naming is used, e.g. YYYYMMDD.XX, where XX is a sequential number for multiple patch/revert scripts for any given day.
• All SQL statements must be terminated with ‘;’. This is important for the management processes and automated scripts that take these fundamental schema/patch/revert scripts as source information.
• Where possible, try to make revert scripts, support either a successful or failed patch process. For example, adding IF EXISTS to a DROP TABLE statements supports both cases.
• It is reasonably obvious to have schema, patch and revert directories as a naming standard, but file name also include this as a prefix. This is performed as a double check, if a file is seen in isolation it’s type can be determined regardless of directory location. Also for logging, only filenames are used.

More Information

Configuration Management in MySQL is one of the topics discussed in the “MySQL Essentials” training course. You can find more information regarding this and other training offerings including an upcoming schedule at 42SQL Education.

Did anybody notice that http://planetmysql.org now redirects to http://planet.mysql.com?

Curious to know the reason why, perhaps an official MySQL person can give us some details.
Also it’s a 302 redirect, not a 301 redirect, interesting?

 wget http://planetmysql.org
--2009-02-26 14:40:09--  http://planetmysql.org/
Resolving planetmysql.org... 213.136.52.29
Connecting to planetmysql.org|213.136.52.29|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: http://www.planetmysql.org/ [following]
--2009-02-26 14:40:10--  http://www.planetmysql.org/
Resolving www.planetmysql.org... 213.136.52.29
Connecting to www.planetmysql.org|213.136.52.29|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://planet.mysql.com/ [following]
--2009-02-26 14:40:10--  http://planet.mysql.com/
Resolving planet.mysql.com... 213.136.52.29
Connecting to planet.mysql.com|213.136.52.29|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]

MySQL by default is vary lax with data validation. Silent conversions is a concept that is not a common practice in other databases. In MySQL, instead of throwing an error, a warning was thrown and many applications simply did not handle warnings. With the introduction of sql_mode=STRICT_ALL_TABLES (or TRADITIONAL), in MySQL 5, a better level of validation now exists.

My understanding was that Warnings are now thrown as Errors, therefore eliminating the need to do a SHOW WARNINGS to confirm any problems after every query (this is a performance overhead on a high volume system due to the round trip latency).

However I found an instance where MySQL in STRICT Mode still throws warnings, leading to the question, are there any other areas, and does the earlier statement “Warnings are now thrown as Errors” hold true.

Here is my seeding process to showing the problem.

mysql> create table i(i tinyint, unique key( i));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into i values(999);
Query OK, 1 rows affected (0.00 sec)

Using default settings, attempting to INSERT a duplicate row throws an error, using INSERT IGNORE does not.

mysql> insert into i values(999);
ERROR 1062 (23000): Duplicate entry '127' for key 'i'
mysql> insert ignore into i values(999);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'i' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

When using a Strict Mode, a recommendation for all new systems, it is generally accepted that warnings are translated into errors, which implies your could should never have to consider checking for warnings.

mysql> truncate table i;
mysql> set sql_mode=strict_all_tables;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into i values(999);
ERROR 1264 (22003): Out of range value for column 'i' at row 1
mysql> insert ignore into i values(999);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'i' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode=traditional;
Query OK, 0 rows affected (0.00 sec)

mysql> insert ignore into i values(9990);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'i' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

I should caveat this post also with using caution with INSERT IGNORE. This should only be used if you never care about errors which I would never consider as a best practice design approach.