MySQL Bugs: #6385: SQL Studio slow response

Bug #6385	SQL Studio slow response
Submitted:	2 Nov 2004 14:35	Modified:	18 Nov 2004 13:16
Reporter:	David Radcliffe	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MaxDB	Severity:	S1 (Critical)
Version:	7.5	OS:	W2000
Assigned to:	Ulf Wendel	CPU Architecture:	Any

Description:
When attempting to execute SQL which CREATEs a DBPROC, it takes a very long time (up to 15 minutes) to complete the execution, and appears to 'hang' during this time, showing an hourglass-cursor. The proc I'm trying to add is quite short.

This happens when errors are detected, and the new DBPROC fails to compile OK.
This is the only scenario I have been able to test, as I'm new to MaxDB/SQL Studio and am porting Procs from Oracle, and trying (without much success) to get them into a MaxDB database.

This process is proving difficult due the lack of response from the program.

How to repeat:
Attempt to create a DBPROC containing an error.

Hello Mr. Radcliffe
(hello David?)!

Thank you for your feedback on our product. I am very proud to hear that you consider moving from Oracle to MaxDB. 

Is it really an arbitrary dbproc with an error that causes the hangs? I could not reproduce it with a short dummy procedure. Can you give me instructions on how to repeat you problems?

Best regards,
Ulf Wendel

Ulf,

Thanks for getting back so quick. I beleive that attempting to create a dbproc is the culprit, *however* I'm not even convinced it is really trying to create a dbproc.

If I execute the SQL below, it actually returns a table of data - suggesting that it is executing each line independantly. I read somewhere that a 'delimiter //' should be issued, but placing this line before the 'create dbproc' line just gives another error... 
As you can see, I haven't got very far with my porting from Oracle (hence most of the proc missing, and what is there being in a comment block) - is there a white paper about this? I'm sure it's something *lots* of users will want to do... ;-)

CREATE dbprocedure fill_addresses (OUT result VARCHAR ) AS

/**********************************************/
/* procedure name : FillAddresses                                     */
/**********************************************/

BEGIN

select i.* from invoice_reference i;

/*

declare cursor non_payers for 
  select i.*
  from invoice_reference i
;

var
first_row NUMBER;

try -- begin
  result := 'OK';
catch
  result := 'FAIL';
*/

END;

Hello David, 

I need a hand: how do I reproduce slow responses of the SQL Studio to faulty procedures, how can I verify your observation?

Sorry, I do not know of a tool that helps you porting procedures from Oracle to MaxDB. 

Please remember, that this forum is for bugs only. I can not support you on bugs.mysql.com. Please consider asking general question on one of our great community based mailing lists (see http://lists.mysql.com/) or purchasing commercial support (e.g. via our online shop https://order.mysql.com/). 

Thanks,
Ulf

Ulf, 

I think that this issue can be closed. 

The slow response was caused by MaxDB executing the SELECT statement, instead of adding it to the stored proc I was trying to create.

I still cannot create a stored proc, but there is no slow response issue here.

Thanks

Ok, thank you!

David, have you considered subscribing to our community mailing list? Many of the core developers read it. I really recommend it to everybody working with MaxDB. You can subscribe to it on http://lists.mysql.com/#maxdb .

Good luck porting!
Ulf Wendel