Querying other database from mySQL

Hi,

We're using a mySQL database as a replica of another (Sybase) DB for
reporting purposes. The Sybase is part of a real-time mission critical
system - hence the separate database where people can run their queries
on.
Currently a separate application is copying the information we're
interested in from Sybase to mySQL in an overnight batch-process. We
want to extend this to loading every 15 minutes and are now
investigating the possible solutions.

I am considering to create a scheduled task in mySQL, where mySQL
itself reaches out to the Sybase ASE server and fetches the data. That
seems to make the setup much easier and eliminates the need for an
intermediate application.

Does anyone know if it is possible to create a "pass-through" query in
a mySQL stored procedure (or scheduled task)? I searched for this but
could not find anything, unfortunately.

Thanks a lot for your input - all replies welcome!

Otto.

Re: Querying other database from mySQL

>We're using a mySQL database as a replica of another (Sybase) DB for
>reporting purposes. The Sybase is part of a real-time mission critical
>system - hence the separate database where people can run their queries
>on.
>Currently a separate application is copying the information we're
>interested in from Sybase to mySQL in an overnight batch-process. We
>want to extend this to loading every 15 minutes and are now
>investigating the possible solutions.

MySQL has the Federated table type which allows a table on another
server to appear as a table on this server. Unfortunately, it
has restrictions: the other server has to run *MySQL*, and
no transactions, and it's not very fast (is likely to select
the entire table).

May I presume here that the data from the Sybase server is considered
read-only from the point of view of MySQL? That is, the only thing
updating that data is doing it on the Sybase server, and the MySQL
copy just gets overwritten each time.

It may be possible to do a differential update. Each record on the
Sybase server has a last-modified timestamp field. The client
copies records that have been modified recently, then updates its
record of the last time the update was done. Deletions need to
either not happen or the records stay around marked deleted long
enough to be seen by the copy program.

There usually needs to be a little slop in the timing here (better
to copy a record twice than miss a change). Transactions can be
your enemy here: if it is possible for a modified record to be
committed well after its new last-modified date, this method may
require so much slop in the timing compared to your copy cycle it's
not worth it. Example: you copy every 15 minutes, but the billing
cycle (slow, and does one enormous commit) can modify a record at
12:00 but when it's committed at 16:00 it still has the 12:00
last-modified date. You either have to copy records modified up
to 4 hours ago, or this method isn't suitable.

>I am considering to create a scheduled task in mySQL, where mySQL
>itself reaches out to the Sybase ASE server and fetches the data. That
>seems to make the setup much easier and eliminates the need for an
>intermediate application.

Don't expect a MySQL server to have a Sybase client in it.
I'd recommend a cron job that is a MySQL client and Sybase client.
Perhaps you could use something similar to mysqldump on Sybase,
and the mysql command-line client to load the data onto MySQL.

>Does anyone know if it is possible to create a "pass-through" query in
>a mySQL stored procedure (or scheduled task)? I searched for this but
>could not find anything, unfortunately.

Federated tables only work on other *MySQL* servers, as far as I know.

Gordon L. Burditt

Re: Querying other database from mySQL

Gordon,

Thanks a lot for your very complete answer!

I'll go for the timestamp option; the Sybase system mainly stores
events, so there is no concern about transactions or updates on already
copied records.

Otto.