Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database.

When I tried to create a routine from console (without prior issuing use dbname) I am getting an error:

ERROR 1046 (3D000): No database selected

We have tons of identical databases (data is different) and the goal is to create some triggers for some tablenames. But we want to run only one routine so we don't have to create those routines for every database (since they are identical, routines would work the same for each database).

share|improve this question
    
Are you sure that you want to create triggers in mysql? read this it an article off mine dba.stackexchange.com/questions/48797/… –  Raymond Nijland Sep 27 '13 at 20:36

1 Answer 1

up vote 7 down vote accepted

There is no way to define stored procedures or stored functions (or events) that are global.

One approach is to create a shared common schema and then qualify the calls to the functions and procedures with the name of that schema (CALL shared.the_procedure();).

This is something I do with my collection of custom date/time calculation functions (e.g., SELECT date_time.next_quarter_start_after(NOW())), and with the ever-so-handy common_schema framework, which, of course, lives in `common_schema`.

If you take that approach, you have to remember then when a routine is running, the "current" database automatically changes, and the return value of the DATABASE() function returns the name of the schema under which the routine was defined, not your session's current database. It changes back when the routine exits, so if used in a trigger, it doesn't break anything surrounding it but you don't have a way of knowing from inside the routine what the current database was, if you needed to know.

share|improve this answer
    
+1 Indeed stored procedures or stored functions (or events) are always defined within an database –  Raymond Nijland Sep 27 '13 at 20:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.