Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is an odd question but is it possible to manipulate a Drizzle database through MySQL stored procedures? The reason I need to do this is that I'm migrating my MySQL database (which contains a lot of stored procedures) to Drizzle (which doesn't support stored procedures). This was essentially one of my 'grand' short term ideas for this.

Any ideas for doing this, or some other ones?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Noting that you didn't ask whether it was a good idea, but only whether it is possible :) I would venture to say it seems like it should be possible, since Drizzle speaks the MySQL protocol over TCP -- as evidenced by the fact that you can use the "mysql" CLI client and other MySQL client libraries to connect to a drizzle server.

The "federated" storage engine in MySQL allows your "local" server to connect to a "remote" MySQL (Drizzle?) server using the standard MySQL C-client library (aha!), and remotely access one or more tables on the remote server as if they were tables on the local server... the "local" server essentially spoofs a client connection to the remote server... one connection per table, from what I've observed.

CREATE TABLE (
-- column definitions on the local server 
-- should be identical to the remote server
-- although the engine is more forgiving than
-- the documentation will lead you to believe 
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user:pass@remote_server:port/schema/table';

-- this is one of two ways to configure the actual connection

I believe this will work because the remote server doesn't know anything unusual is going on -- it thinks it's being accessed by a client, issuing ordinary INSERT/DELETE/SELECT/UPDATE statements, and queries on the local server interact with the federated tables just as they would with any other table.

If the "local" server were MySQL with your stored procedures, and federated tables configured to connect to the "remote" server -- the Drizzle server -- where your data lives, this should accomplish what you want, if I understand the question correctly. I use "local" and "remote" to describe their roles, of course, not their placement. For this application, they would most likely be on the same machine, or a very fast LAN.

Your application would connect to the MySQL server to call the stored procedures, which would then manipulate data on the Drizzle server via the federated connections.

How efficient will it be? Not nearly as efficient as having the stored procedures and data on the same DBMS.

Is it a good idea? I'm not too sure about that. But in principle, it's probably worth a shot.

The federated engine has gotten some bad press, but at least in MySQL 5.5, I find the criticism to be unmerited. I have numerous federated connections from 5.5 → 5.5 and 5.5 → 5.1 in my production systems.

There are a number of caveats that go along with the federated engine, such as no support for transactions, and queries involving federated tables aren't cached on the local server (there's no way to invalidate them, after all). They don't directly support foreign keys, but the foreign keys are still enforced by the remote server, which will throw an error that propagates back to you locally if you violate them (though it may not be the same MySQL error or SQLSTATE).

You also want be sure you define at least the same indexes on the "local" server as exist on the "remote" server -- and sometimes it may even make sense to declare indexes on columns that aren't really indexed on the far-end -- because if the optimizer decides a full table scan is needed, it actually requests the entire contents of the remote table over the wire from the far-end... usually not what you want. The indexes don't actually "exist" locally or take up any space on the local server -- but the query sent to the far end includes a WHERE clause to get only the appropriate rows, only if the optimizer realizes this is a possibility. (This fact does not seem quite as strange when you remember that federated is implemented as a storage engine and the core of MySQL server interacts with it as though it were indeed a storage engine.)

This document is several years old but provides some very good background on the federated storage engine.

Final note: yes, the federated engine is one of the things that got stripped out of Drizzle, but that's fine -- we only need it on the MySQL side for this application.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.