Currently, the way that SqlYog 6.12 (and earlier) auto generates scripts for the “Alter stored procedure” command is extremely dangerous. It tends to result in stored procedures accidentally being deleted, when developers are copying them from one database schema to another. This is because the “drop” clause contains a fully qualified object name ([DB NAME].[STORED PROC NAME]), yet the create clause does not. Consider the following sequence of events:
1. Open SQLyog and select a database from the drop-down menu at the top – for example we'll call this “mydb_live”
1. Right click on a stored procedure in the database (we'll call it sp_BusinessCriticalStuff), select “alter stored procedure”
2. Once the script has been generated by SQLYog, select a different database from the drop-down menu – for example “mydb_test”
3. Now run the script.
4. Guess what . . . our test database now has a beautiful copy of sp_BusinessCriticalStuff, but it's been deleted from the live database. Not good!
All that is needed is for the auto-generated script to be changed from something like this:
Code:
DELIMITER $$
DROP PROCEDURE IF EXISTS `mydb_live`.`sp_BusinessCriticalStuff`$$
GRANT execute ON `moderation`.`md_REPMOD_getFromQueue` to …;
(…)
This is needed because when procedure/function is deleted all grants to it are also deleted.
3. There is a need of checkbox in Preferences which would do that triggers will be also visible with “Column” and “Indexes” in tables' information. That option was in earlier versions and in the recent one is lacking of it.
4. Is there a possibility to change database connections type to persistance? We many times receive “MySQL server has gone away” error when trying to alter procedure/function. Maybe when connection is established there should be automatic query to MySQL server in form like:
This ALTER … was never considered a sync tool. Structure Sync will let you sync without touching thedatabase ..
You have experienced this as 'extemely dangerous'. The other way around (not specifying the database) may be equally dangerous – if you by mistake hit a database in the Object Browser the statements will execute where you hit. Other users may NOT be aware of that changing the database context changes where it executes!
You should understand the meaning of the SQL you are executing, basically!
We populate the TEMPLATE from the returns of 'show create procedure'. The server returns like that.
Maybe we can implement by querying from Information_Schema ..
Anyway we use database identifier with DROP and not with CREATE stmt. Looks like a mistake – or at least an inconsequence.
This ALTER … was never considered a sync tool. Structure Sync will let you sync without touching thedatabase ..
That's understood – and structure sync is a great tool when we need to syncronise an entire database and make sure it's identical to another. However, frequently it's useful just to quickly apply an update for a single stored procedure – for example when rolling out a bug fix or a patch. In this case, structure sync is overkill.
Quote:
You have experienced this as 'extemely dangerous'. The other way around (not specifying the database) may be equally dangerous – if you by mistake hit a database in the Object Browser the statements will execute where you hit. Other users may NOT be aware of that changing the database context changes where it executes!
I take your point – but the main problem with the script as is, is that it's inconsistent. The database name should either a) be specified for both drop and create statements, or 😎 it should not be specified for either. I would hold that the inconsistency actually leads to more confusion, not less.
Quote:
Anyway we use database identifier with DROP and not with CREATE stmt. Looks like a mistake – or at least an inconsequence.
Yes indeed, just what I was getting at above. Fingers crossed that information_Schema might return something more useful!
Simon Woolf
Memberpeterlaursen
Participant
