In MySQL, how can I have a stored procedure query the tables from the calling database instead of the database where the stored procedure is defined

Question

For the sake of simplification, let's say I have 2 databases with data, db_data_1 and db_data_2 which have the same set of tables and I have a 3rd database where my stored procedures are defined, say db_sp. Let's say my stored procedure is called my_sp

When I try to call db_sp.my_sp from either db_data_1 or db_data_2, I get an error saying that the tables referenced in db_sp.my_sp don't exist.

How can I have db_sp.my_sp query the tables in the calling database vs the database where my_sp is defined (namely db_sp)

Thanks.

Answer 1

You must qualify the table names in your query with the database name in the stored procedure. SELECT col FROM db_data_1.tbl instead of SELECT col FROM tbl, for example.

The documentation says this:

USE statements within stored routines are not permitted. When a routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. References to objects in databases other than the routine default database should be qualified with the appropriate database name.

Why is this so? It seems like a big pain in the xxx neck.

A big use of stored code is the hiding of data from unprivileged users. You can GRANT MySQL users access to stored procedures without granting access to the underlying tables. This restriction ties the tables to the procedures.

A user who has privileges only in the test database shouldn't be able to do this sort of thing.

USE production;
CALL test.get_all_user_private_data();

And, if you're USEing one database and you run stored code that's in a second database, it gets the data from that second database.

Your solution is to consider your stored code (procedures, functions) to be part of the schema definition for each database. They go along with your other data definition operations like CREATE TABLE. Don't try to put them in their own "code library" database, but put them in each database where they're needed.