MySQL Bugs: #30465: Query Browser removes essential schema identification on stored proceduers

Bug #30465	Query Browser removes essential schema identification on stored proceduers
Submitted:	16 Aug 2007 21:26	Modified:	15 Oct 2009 11:41
Reporter:	Brad Peterson	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.2.12	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	CHECKED, stored procedure

Description:
When you create a stored procedure or stored function using the Query Browser, it removes essential schema information. This can lead to very problematic scenarios where editing a stored procedure causes the stored procedure to be completely dropped from one schema and possibly moved to another. Trying to fix the problem can be confusing and leads to other frustrating scenarios. An example of a very bad scenario is shown below in the 'How to repeat' steps.

How to repeat:
These steps have been double checked for accuracy. On their own, the steps don't feel like something real-world users would run into. But in our office, we end up doing these steps over the course of an hour or so (because we use multiple schemas), and so we do run into this issue all the time.

Perform the following tasks:

* Create a schema called test1

* Create another schema called test2

* Click File->Change default schema. Choose test1.

* Press F5 to refresh everything.

* Right-click on test1, and choose 'Create New Procedure / Function'

* Type "test_proc" for the name, and click 'Create PROCEDURE'

* A template for a procedure will be generated. It should contain the line "CREATE PROCEDURE `test1`.`test_proc` ()"

* Click 'Execute'. It will of course create the procedure.

* Exit the Query Browser app and restart it.

* Click 'File'->'Change Default Schema...'. Choose test2.

* Back on the right side, click on test1, test_proc, right-click, and choose Edit Procedure. (It will now contain the line:
"CREATE DEFINER=`brad`@`%` PROCEDURE `test_proc`()".
That's the problem, it removed the `test1`).

* Click 'Execute'.

The procedure has been dropped from database test1 and created over in database test2.

Suggested fix:
When the procedure is actually written, it should be written as:

CREATE DEFINER=`brad`@`%` PROCEDURE `test1`.`test_proc`()

instead of:

CREATE DEFINER=`brad`@`%` PROCEDURE `test_proc`().

That lack of schema identification can create crazy problems, because the procedure's DROP statement refers directly to the schema, but the CREATE statement does not. Without the schema info in the CREATE statement, the statement is executed on whatever is the current schema at that moment.

What's even more frustrating, is that if I manually specify `test1` in the CREATE statement, it's gone the next time I re-open the procedure for editing.

Thank you for the bug report.

We are coming across this as a major issue too, as we use schemas to isolate stored procedures and data from various users, but our application spans all the schemas. 

As we develop, we hit exactly the same problem - i.e. the default database has changed but we run the script to recreate a SP and it is created in the 'wrong' schema.

We would be very grateful for this bug being addresses.

Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. We won't add this feature request anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/