SELECT on 2 instances

giordano2 posted 4 years ago in Running SQL scripts
I would like to create a table from database db_x (server X) into database db_y (server Y):
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.
kalvaro posted 4 years ago
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.
giordano2 posted 4 years ago
Thanks for the response. I thought that if it is possible to copy with HeidiSQL from one instance to another:
it should be possible also submitting queries over instances. Obviously I'm wrong.
imre posted 4 years ago
create table in 1 instance and then export to another. Or export query results directly
kalvaro posted 4 years ago
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.
jfalch posted 4 years ago
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.
kalvaro posted 4 years ago
You're right. I always forget that there's MySQL beyond MyISAM and InnoDB. Sorry if I mislead someone.
giordano2 posted 4 years ago
Thanks for this info. There is also CONNECT in MariaDB:

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