I just upgraded Rack 68, the main network server. We are now running MariaDB Version: 10.1.28 globally... We were running MySQL Version: 5.6 and using the Federated engine. It turns out that the Federated project was dropped and at one time that portion of the code was maintained by a few Oracle developers. This is no longer the case and now with MariaDB, since the federated engine project was dropped, there definitely needed to be something done about it. A wonderful idea came about and it was to keep the project alive and get some fresh souls working on it. I give you FederatedX which is taking the place of the old not very well maintained Federated engine and with fresh souls helping the engine progress. They have already done some new wonderful things...
What is the FederatedX storage engine?
The FederatedX Storage Engine is a storage engine that works with both MariaDB and MySQL. Where other storage engines are built as interfaces to lower-level file-based data stores, FederatedX uses libmysql to talk to the data source, the data source being a remote RDBMS. Currently, since FederatedX only uses libmysql, it can only talk to another MySQL RDBMS. The plan is, of course, to be able to use other RDBMS systems as a data source. There is an existing project Federated ODBC which was able to use PostgreSQL as a remote data source, and it is this type of functionality which will be brought to FederatedX in subsequent versions.
The history of FederatedX is derived from the History of Federated. Cisco needed a MySQL storage engine that would allow them to consolidate remote tables on some sort of routing device, being able to interact with these remote tables as if they were local to the device, but not actually on the device, since the routing device had only so much storage space. The first prototype of the Federated Storage Engine was developed by JD (need to check on this- Brian Aker can verify) using the HANDLER interface. Brian handed the code to Patrick Galbraith and explained how it needed to work, and with Brian and Monty's tutelage and Patrick had a working Federated Storage Engine with MySQL 5.0. Eventually, Federated was released to the public in the MySQL 5.0 release. There is every indication that the Federated engine existed in previous versions of MySQL due to the fact that Ernest Allen Buffington had previously used it with PHP-Nuke Titanium early on through discoveries of his own and with little or no documentation.
We were not quite ready to upgrade all the way to MariaDB 10.0.2 but when we did FederatedX had new support for assisted table discovery. So there is that and the fact that new exciting things are happening with the new engine project. It looks like the fresh new souls are working their buns off to make it awesome and are going the extra miles needed.
Internal workings of FederatedX
Normal database files are local and as such: You create a table called 'users', a file such as 'users.MYD' is created. A handler reads, inserts, deletes, updates data in this file. The data is stored in particular format, so to read, that data has to be parsed into fields, to write, fields have to be stored in this format to write to this data file.
With the FederatedX storage engine, there will be no local files for each table's data (such as .MYD). A foreign database will store the data that would normally be in this file. This will necessitate the use of the MySQL client API to read, delete, update, insert this data. The data will have to be retrieved via an SQL call "SELECT * FROM users ". Then, to read this data, it will have to be retrieved via mysql_fetch_row
one row at a time and then converted from the column in the select into the format that the handler expects.
The basic functionality of how FederatedX works
The user issues an SQL statement against the local federatedX table. This statement is parsed into an item tree
FederatedX uses the MySQL handler API to implement the various methods required for the storage engine.
It has access to the item tree for the SQL statement issued, as well as the Table object and each of its Field members.
With this information, FederatedX constructs an SQL statement
The constructed SQL statement is sent to the Foreign data source through libmysql using the MySQL client API
The Foreign database reads the SQL statement and sends the result back to the MySQL client API to the origin
If the original SQL statement has a result set from the Foreign data source, the FederatedX storage engine iterates
through the result set and converts each row and column to the internal handler format
If the original SQL statement only returns the number of rows returned (affected_rows), that number is added to the
table stats which results in the user seeing how many rows were affected.