[Razvan Goga]

peterlaursen wrote on Nov 24 2007, 12:12 PM:

Actually a stored procedure/function will restore/create even if they are referencing non-existing Tables and Views.

I mostly worked with 5.0.37 and 5.0.41 versions of MySql so i can't be sure about the others, but i found that :

1. stored procedures / functions are only checked if they are corect from a syntax point of view when they are created

eg : every line ends with “;”

the SQL statements are corect, the language

the language specifics are folowed (the parameter definition is prm1 int(11) not int(11) prm1

the objects it references are not checked, so you can write a sp with a totaly bogus SQL statement in it, if it complies with the things above it will get created.

2. views are checked for both syntax and object definition when they are created

IMHO the aproach MySQL took with sp/functions is a little sttrange. I have worked with both Oracle and Ms SQL Server before and both signaled to me

that the sp/ function referenced unexsiting objects, but MySQL creates the sp/function successfully, the only errors coming upon execution.

[Razvan Goga]

peterlaursen wrote on Nov 23 2007, 11:27 AM:

That will be possible from SQLyog 6.2

Am I to understand that in SQLyog 6.2 the dump objects order is to be changed ? ( ie tables, sp, functions, views ) ?

I have resorted to doing my back-ups with the MySQL Administrator tool that is ofered by MySQL. It does make the dump with views last.

I don't want to use 2 separate dumps… It's to much overhead for such a small detail…

You realy should fix this. I tryed most of the free MySQL front-ends (toad, ems, mysql query browser, heidi….) and yours is by far the most proficient both in speed and capabilities…

Razvan

[Razvan Goga]

Hi Peter,

I can't give you real structure dump but i made an example for you.

It has one table, one function and one view that uses the function. Please see the atachment for it.

So :

1. if you copy /paste the script into a query window and hit “execute all queries” thetable and function will be created , but the view will not .

2. if i use the “Restore database from sql dump” only the table will be created ( i think the backup process quits at the first exception )…

Razvan

[Author]

Posts