distal-attribute
distal-attribute
distal-attribute
distal-attribute

SELECT on 2 instances

User, date Message
Written by giordano2Money, Euro
7 months ago
Category: Running SQL scripts
12 posts since Sun, 03 Mar 13
I would like to create a table from database db_x (server X) into database db_y (server Y):


CREATE TABLE db_y.new
SELECT u.*, v.*
FROM db_x.u u
INNER JOIN db_x.v v = u.id = v.id
WHERE b < 1
;



If both databases are on the same server this wouldn't be any problem. My problem is that the databases are in different Server. It reports:
"SQL Error (1049): Unknown database 'db_y'".
Is there a way to solve this problem?
Thanks for help.
Written by kalvaro
7 months ago
591 posts since Thu, 29 Nov 07
This has nothing to do with HeidiSQL. As far as I know, only some major DBMS like Oracle support linked servers (and it needs specific server-side configuration anyway). MySQL doesn't.
Written by giordano2Money, Euro
7 months ago
12 posts since Sun, 03 Mar 13
Thanks for the response. I thought that if it is possible to copy with HeidiSQL from one instance to another:
http://www.heidisql.com/forum.php?t=12301
it should be possible also submitting queries over instances. Obviously I'm wrong.
Written by imre
7 months ago
12 posts since Thu, 30 Dec 10
create table in 1 instance and then export to another. Or export query results directly
Written by kalvaro
7 months ago
591 posts since Thu, 29 Nov 07
Exporting is a clearly defined feature that's used through a GUI. HeidiSQL composes its own SQL queries; it reads from source server and writes to target (which doesn't even need to be a MySQL server at all). Both operations happen consecutively, not at the same time.

What you're asking for is to launch a single query against two different servers. Parsing and executing SQL is entirely the server's responsibility—that was precisely a key feature of SQL when it was proposed some decades ago: you no longer had to say how to retrieve data as in past database systems, on what data you want.

Having a client-side engine that's able to execute arbitrary SQL against different servers would require to:

1. Extend the syntax of the SQL language so you can qualify identifiers with the server name (and servers don't really have names, you'd have to provide either the full credentials or HeidiSQL's stored connection name).

2. Implement a 100% reliable full-fledged SQL parser (current HeidiSQL parser is based on regular expressions thus doesn't always work as expected).

3. Possibly retrieve complete all the rows from the involved tables before being able to filter out unwanted ones (thus making everything terribly slow).

In other words, replicate the functionality of MySQL Server without the benefit of fast local storage.

Said that, the precise query you need can easily replaced with two queries as giordano2 and imre explain.
Written by jfalchMoney, Euro
7 months ago
385 posts since Sat, 17 Oct 09
It is possible in principle to redirect queries to a different mysql server using the FEDERATED storage engine; however, it has several restrictions, and I have never seen an instance of it in practical use. Experimentation would be required.
Written by kalvaro
7 months ago
591 posts since Thu, 29 Nov 07
You're right. I always forget that there's MySQL beyond MyISAM and InnoDB. Sorry if I mislead someone.
Written by giordano2Money, Euro
7 months ago
12 posts since Sun, 03 Mar 13
Thanks for this info. There is also CONNECT in MariaDB:
https://mariadb.com/kb/en/federated-storage-engine/
.
 

Please login to leave a reply, or register at first.