There was once a big hooplah about the MySQL Storage Engine Architecture and how it was easy to just slot in some other method of storage instead of the provided ones. Over the years I’ve repeatedly mentioned how this

  [Read more...]

Recently, someone emailed me:

I have a requirement in MYSQL as follows:
we have a table EMP and we have to restrict the users not delete employees with DEPT_ID = 10. If user executes a DELETE statement without giving any WHERE condition all the rows should be deleted except those with DEPT_ID = 10.

We are trying to write a BEFORE DELETE trigger but we are not able to get this functionality.

I have seen your blog where you explained about Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggers. Will it helps me to get this functionality? Could you suggest if we have any other alternatives to do this as well?

Frankly, I usually refer people




  [Read more...]

Your searching for how to create a join across two databases on two different servers and it can’t be done directly.   select  d1.a, d2.b from db1@server1 join db2@server2 where db1.c = db2.c; does not work.

You learn about federated databases.  The federated storage engine allows accesses data in tables of remote databases.  Now how do you make it work?

1) Check if the federated storage engine is supported.  Federation is OFF by default!

mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| InnoDB     | YES    

  [Read more...]

The NoSQL/relational database debate has been going on for quite some time. MariaDB, like MySQL is relational. And if you read these series of blog posts, you’ll realise that if you use MySQL correctly, you can achieve quite a lot.

It all starts with Kellan Elliott-McCrea with his introductory post on Using, Abusing and Scaling MySQL at Flickr. Follow the entire series.

He starts of the series with Ticket Servers: Distributed Unique Primary Keys on the

  [Read more...]

You can now yum (RPM) or apt-get (DEB) MariaDB 5.1.39, courtesy of OurDelta and in close cooperation with Monty Program Ab. Simply follow the info on the CentOS, Debian or Ubuntu pages.

(note: give the mirrors some hours to sync up)

Quick overview

• See the Release Notes for relevant bugfixes.
• Drop-in replacement for stock MySQL 5.0 or 5.1 (If you’re coming from stock MySQL, see the MariaDB extras/differences overview).
• If you are upgrading from 5.0, please review the

  [Read more...]

I'm extremely happy to announce that the MariaDB project has integrated FederatedX into the main tree and has replaced the old, un-maintained Federated storage engine! This means that from now on, I will target any FederatedX changes and enhancements for MariaDB since it will be easier than having to try to make it run as a pluggable storage engine. This also means I will have another reason why I should keep this project moving forward. I will still provide the ability for anyone who wants to use it as a pluggable engine (not that difficult) so this can be loaded with MySQL as well.

Also changed: I changed to using the BSD license for FederatedX.

For Drizzle, I will need to rewrite a good part of the code base to use libdrizzle as well as the new storage engine interface, so they will be somewhat separate projects -- though I'll do my best to keep them the same single project.



  [Read more...]

I have been meaning to, for months, get working on the FederatedX storage engine. There are a lot of great features it has -- such as better support for transactions as well as some architectural changes, thanks to the work of Antony Curtis. It now is designed so that the connection is abstracted into a federated_io_ class. This allows you to be able to subclass different connection schemes/drivers. For instance, I have the code for Federated ODBC that I need to take the code from and port to a federated_io_odbc class.

FederatedX has been pulled into an older MariaDB repository (thanks Antony!) that I need to merge with the latest, as well as figure out how to get the test suite to load the plugable storage engine. I've tried to add 'INSTALL PLUGIN federated SONAME 'ha_federatedx.so'' to the test, but the server expects ha_federated.so to be in a particular file location. That needs to be solved.

  [Read more...]

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides can be downloaded as a

  [Read more...]

Followers of my blogs and talks know that I am partial to the Federated engine. Not much as a normal way of storing data, but more as an auxiliary device to create innovative schemes.
A few years ago I wrote an article, Federated: the missing manual where I listed most of the gotchas that you can meet when using the Federated engine. This article alone would be enough to discourage you from using Federated in production. If you attempt to use a Federated table like a normal one, your server will suffer, and possibly crash.

Add the sad fact that the Federated engine is






  [Read more...]

Yup, the presentation slides as well as the scripts are now available online on the conference website.

The stuff you will find in there:
Information_schema.pdfDiagram of the information schema
I_S_VC_Slides.pdfSlides for the UC presentation, "Grand Tour of the Information Schema"
I_S_INDEXES2.sqlscript, returns one row for each index (rollup of information_schema.STATISTICS)
I_S_REDUNDANT_INDEXES2.sqlscript, lists all redundant indexes. Redundancy rules:

• two indexes with the same columns, type and uniqueness are interchangeable. The one with the largest index name is listed as redundant


• if there is a unique index and a non unique index with the same columns and type exists, the non-unique one is considered redundant

  [Read more...]

The initial release of the FederatedX Pluggable Storage Engine for MySQL is now
available. I developed the Federated Storage Engine when I worked at MySQL,
and really saw a lot of potential with it. However, there were many other
projects that I had to give attention to, and many features and bug fixes that
users wanted didn't come into fruition because of the busy schedule. I left
MySQL a year ago to pursue an opportunity with Grazr what has kept me equally
busy. However, I have made a resolution this year to give some projects that I
have wanted to improve the attention that they need. I still see a lot of
potential with the "federated" concept.

Federated as it is isn't what many people expect it to be. IBM for instance,
has a federation as a very integral part of DB2. The Federated Storage Engine
is a proof-of-concept storage engine -- not to











  [Read more...]