Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can a stored procedure work with two different databases? How about two servers?

Tags:

I am wondering if MySQL's stored procedures can work with two different databases on the same machine? How about if they are on different servers?

like image 216
Pentium10 Avatar asked Oct 25 '10 13:10

Pentium10


People also ask

How will you compare stored procedures on two databases in SQL Server?

Compare Two Database and find differences First, open the SQL Server Database project with visual studio, right-click on it, and choose compare schema as depicted. Then, we will select the source and target databases and provide a connection to those.

Can a stored procedure access another database?

You can work around this in SQL Server by creating a stored procedure that accesses data in another database and signing the procedure with a certificate that exists in both databases. This gives users access to the database resources used by the procedure without granting them database access or permissions.

Can one server have multiple databases?

There are three types of consolidation: Host multiple databases on a single SQL Server instance. Host multiple SQL Server instances on a single machine. Host multiple virtual SQL Server machines on a single physical machine.

Can a stored procedure run in parallel?

Even if you execute two stored procedures exactly at the same time, It is not guaranteed that they will go parallel. Parallelism of executions is dependent on other factors like Query Cost, MXDOP(Maximum Degree of Parallelism), Threshold for Parallelism etc.


1 Answers

If we're talking about two databases on the same server: yes, a stored procedure can access another database. You have to make sure that the user under whose privileges the procedure is being run has the necessary privileges on each database.

For example, suppose you have two databases on the same server, mydb1 and mydb2, and that each contains a table named messages with the same structure. Suppose you want to add a stored procedure to mydb2 that empties the messages table in mydb2 and copies the contents of the messages table in mydb1. You could do this:

CREATE PROCEDURE `SynchroniseMessages` ()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

DELETE FROM `mydb2`.`messages`;

INSERT INTO
    `mydb2`.`messages`
    SELECT * FROM `mydb1`.`messages`;

END

See how I've fully qualified the tables with the databases to which they belong. In fact you could argue that I'm being over-zealous here, because we specified that this stored procedure will belong in mydb2. I don't need to add the mydb2. qualifier. If the stored procedure were in the mydb1 database, I would need those qualifiers, but conversely I wouldn't need the mydb1. where it appears.

In order to be able to run this procedure (possibly in order to be able to define it?), I'd need to make sure my user has DELETE and INSERT privileges on mydb2, and also SELECT privileges on mydb1.

Databases on different servers sounds rather more complicated.

like image 108
Hammerite Avatar answered Sep 18 '22 14:09

Hammerite