How about a little simpler one I use where I select into from a table on one server into a
table on another server. A kind of selective replication without having all the hassle of
setting up replication. In MS SQL I can use select * from server.dbo.database.table into
server2.dbo.database.table I would like to do that in MySQL too.
-----Original Message-----
From: Adam Majer [mailto:adamm@stripped]
Sent: Thursday, July 24, 2003 3:45 PM
To: Adam Hooper
Cc: Tom Achtenberg; 'mysqlcc@stripped'
Subject: Re: Select between two servers
On Wed, Jul 23, 2003 at 07:34:18AM -0400, Adam Hooper wrote:
> I can give one example off the top of my head, no thought required:
> Imagine you have two companies running with the same accounting
> software, each with their own DB server. To compare accounting
> information between them, it would be super to be able to SELECT from
> both servers at once. MSSQL Server has this feature, and we use it quite
> a bit at work (We'd even use it to join our MySQL website databases, but
> some ODBC weirdness forces us to use OPENQUERY() or something).
>
> As an aside: notice that in MySQL it is possible to select from two
> different databases on the same server, i.e., SELECT * FROM db1.table1
> t1 INNER JOIN db2.table2 t2 ON t1.id = t2.id ORDER BY t1.id.... If
> you're going to be mirroring a database on your local machine, it might
> be useful to keep your databases separate.
True, there are instances where you would want to have different databases
and join b/w them. I use that all the time.
But I still don't understand *how* you can join resuls from different
databases. That would require you to send a command to one server,
that server automagically connects to another, runs a sub-query to get
some results specific to "the other" server, and finally run a query on
itself and join the results. Lastly, the data gets returned to the
user....
*BUT* my objections would be:
1. a server should not become a client and have to deal with all the
errors associated with it (socket allocation denied, connection lost, etc...
to "the other" server)
2. You can easily do a join yourself
a) select stuff from one database - order by join variable(s)
b) select stuff from another database - order by same join variable(s)
c) combine the results
3. If you cannot write a script that does that (Perl, PHP, etc..), then you can
still copy the entire table from the foreign database to local and then
query.
4. Security problems. For example, you use SSL to connect to database 1 but
database 2 doesn't support SSL -- does database open a non-secure
connection to 2 or fail?
5. Major security problems. Let's say user CANNOT access database 2 because
he was banned or is not allowed (based on IP). But if he goes though
1 he can get access to the database - even bypass firewalls if not set
correctly (or flaws in MySQL server). Or you can use one SQL server to
infect another with a worm like MS SQL :) No thanks! If databases cannot
see each other, the work like that cannot exist.
I don't know. This just seems to be a logistic nightmare. And it would just
bloat MySQL _a_lot_.
- Adam
> Adam Hooper
> adamh@stripped
>
> Adam Majer wrote:
> >On Tue, Jul 22, 2003 at 09:50:46AM -0700, Tom Achtenberg wrote:
> >
> >>I have a local MySQL installed and MyODBC connections to two other MySQL
> >>databases. In the command center I can execute sql commands on any one
> >>individual database. What I need to do is select from table on two
> >>different servers. I have not been able to find the syntax for the from
> >>in a select that specifies the server the table is in. Can anyone help?
> >
> >
> >You cannot do this - you cannot automagically split up a SELECT query
> >and the recombine the results.
> >
> >I do not belive that MySQL 5 will even have this "feature".
> >IMHO, it only makes sense to add it to client software.
> >
> >Futhermore, any way I can think of looking at it, I do not see
> >much benefit of an automagic query you describe.
> >
> >- Adam
> >
> >
--
MySQL Control Center Mailing List
For list archives: http://lists.mysql.com/mysqlcc
To unsubscribe: http://lists.mysql.com/mysqlcc?unsub=1
