Code Bus

The Q4M queue backend storage engine for MySQL 5.1 doesn't seem to have been gaining much interest recently, probably because many don't consider it stable enough to move into a production environment because of the combined risk of upgrading to MySQL 5.1 and adding a new possibly experimental backend storage engine to it.

For this reason I've had to devise a way to add this to an existing system, using known and well tested methods while completely isolating it from the stable MySQL instance.

The Federated table engine introduced in MySQL 5.0.3 provides the first starting blocks; however it does have some well known caveats. Select performance is incredibly slow due to the "stupidity" of the engine, however update, insert and delete performance is quite good as it literally sends the command unmodified to the server on the other side.

The FederatedX storage engine by fixes some of the data query issues, but again - it's alpha/beta quality and probably not advisable to run it on a production server unless you have balls of steel.

Using the Q4M queue is out of the scope of this article, more information can be found at the Q4M Tutorial page.

CREATE TABLE q_order_process (
  order_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  invoice_id INT UNSIGNED NOT NULL
) ENGINE=queue;

Creating the Federated table

The next step is to link the production database to the queue server via a federated table:

CREATE TABLE q_order_process (
  order_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  invoice_id INT UNSIGNED NOT NULL
)
ENGINE=FEDERATED
CONNECTION='mysql://user@queue_server:9306/order_queue/q_order_process';

Creating the trigger

And for a final example, we use a trigger on an `orders` table to pass it onto the queue for processing later on.

CREATE TRIGGER tr_orders BEFORE INSERT ON orders
  FOR EACH ROW BEGIN
    INSERT INTO q_order_process ( order_id,customer_id,invoice_id ) VALUES( NEW.id, NEW.customer_id, NEW.invoice_id );
  END;