Question About Posible Bug

[Razvan Goga]

Hi,

I use a MySql database which implements stored procedures, user functions and views.

One of the views calls one of the functions in the select statement.

I found that, on using the “Back-up Database as Sql Dump”, the script generated has the function DDL statements after the view DDL.

The problem is that on restore the view will not get created because it references an inexistent function.

I tryed versions 6.07 and 6.12 Comunity Editions and they behave the same.

Is there any way to change the scripting order ? (eg : tables, functions, sp, views)

Thanks in advance,

Razvan

[peterlaursen]

There is no way to change the order.

We will very soon procide a better GUI for exports so that you will be able to 'pick' individual objects. With this you will be able to divide the dump into two.

If you provide a complete structure dump of that database we can look deeper into it!

[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

[peterlaursen]

we will study this!

[peterlaursen]

If you create a view like

“create view myview as select myfunction(..) as mycol”

then the script will not restore because the function is not created vhen the view should be created. I only see the solution to have 2 dumps so that the function gets created in 1st dump and the view in 2nd.

That will be possible from SQLyog 6.2

[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

[peterlaursen]

Maybe you have a point! We will check details and discuss that by beginning of next week!

(However I also think that SQLyog and the 'mysqldump' program does the same!)

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

So maybe the SP's should be at the very beginning!

We will also have to consider Triggers and Events (that will also be supported in 6.2). I think Events are similar to SP's (it is a 'stored program' actually). But I do not think a Trigger will create if the table it is defined on does not exists.

I am also not sure if different MySQL versions are 100% consistent in this respect! We will check all that stuff of coruse!

[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.

[Author]

Posts