473,325 Members | 2,308 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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.

Jun 21 '06 #1
2 2135
>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
Jun 21 '06 #2
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.

Jun 22 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Michael Whittaker | last post by:
Hello! I have a problem with my php script. The script's task is to search an IP-Database with ranges as entries and find, in which range the entered IP is. OK, I've queried the MySQL-Results...
3
by: Marcus | last post by:
I have a column in a MySQL database called price and of type FLOAT(8,2). I then insert two rows into my table, one where price = 100.00 and one where price = 100.01, and then try to find these...
0
by: Jaime Teng | last post by:
Hi, I have a table: mysql> describe archivetable; +-----------+----------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra |...
6
by: Greg | last post by:
I am working on a project that will have about 500,000 records in an XML document. This document will need to be queried with XPath, and records will need to be updated. I was thinking about...
2
by: Usulnet | last post by:
Hi All, I was hoping someone has experienced this before, I'm having trouble googling this. I'm working with a poorly writtend database that has some fields named as such: MTIC_PROD_VEND I do...
4
by: monomaniac21 | last post by:
hi all im wondering is there a better db than mysql for perfoming queries to search textfields for the occurence of a string value? for example does oracle have greater functionality is this...
2
by: RajSharma | last post by:
Hi, I am facing a problem regarding querying thru a large table having millions of rows....... Its hanging in between while querying for all those rows Can anybody suggest me a query regarding :...
2
by: runway27 | last post by:
i am building a registration page where a user register for a username. i am able to insert this into mysql. the situation is every time data is inserted into example table1 in mysql in database1 for...
2
pezholio
by: pezholio | last post by:
Hi, I'm trying (and failing) to find a safe method that returns records from a database if they have quotes in them, for example, if I generate a query like this: SELECT * FROM foo WHERE bar...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.