[SA 7.1.166 Pro] - (not) creating MySQL stored procedure

[SA 7.1.166 Pro] - (not) creating MySQL stored procedure

I run into a weird and very annoying issue I cannot solve. The strange thing it is that it worked before, this one has came out of blue.

I cannot create stored procedures in SQL Editor. No matter what I try I always get the error message, even the simplest create query below:

	Code:
	DELIMITER $$$ USE volan $$$ -- Drop procedure usp_dammit if it exists DROP PROCEDURE IF EXISTS `usp_dammit` $$$ -- Create procedure usp_dammit CREATE PROCEDURE `usp_dammit` ( a_int INT; ) BEGIN SELECT a_int; END; $$$ --- Procedure ertekesites.usp_dammit created DELIMITER ;

The error message is:

Quote:

Executing selected queries... Please wait or close this tab to cancel execution.
Scanning for batch delimiters... please wait.
0 rows affected.
0 rows affected.
Code 1064, State 42000, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a_int INT; BEGIN SELECT a_int; END' at line 3
The command(s) completed with errors (10.50 ms).

And this is just plain wrong. I guess the issue is related to SQL Assistant, because the query is 'flawless' :) It runs in every other editor I tried that does not involve SA. It runs in Toad without any glitches but it fails in Notepad++ as well.

EDIT: aaaand having this pain in the back for slightly longer than two days it suddenly vanished, I cannot reproduce it anymore. I don't understand...

Are you sure this syntax

	Code:
	( a_int INT; )

is valid? According to MySQL documentation http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html, it's not.

The error message is coming from MySQL, it clearly refers to that part and it doesn't like it.

No, I'm sure it wasn't that. You're right, the code posted would not work but that's the result of incorrectly editing the copypasted code. I tried it with live stored procedures, nasty ones with several hundred lines of codes at first, that I made a day before in SQL Editor. The procedures were created when the script was executed in Toad and were not created in SQL Editor. I tried to narrow down which part of the code was the one that made the code fail in SQL Editor but I failed at that. All the codes worked in Toad and neither of them worked in SQL Editor (or in Notepad++ either). I stripped down the code to a stored procedure having no parameters at all and a simple 'select 1 as what' in it after posting the issue here, alas to no avail. I guess something went awry that day because I cannot reproduce that effect anymore. No reboot since then, to make things even more bizarre.

What kind of error message do you get if you remove the ending ; symbol?

When you remove ; from the parameters list, what kind of error MySQL returns in that case?

	Code:
	( a_int INT )

None, of course, and none were expected. I guess it had to be the connection that made things break. Though I still wonder what difference it made when connecting from Toad. Next time I see that again (I hope I won't) I'm going to capture it.

It might be be ODBC vs ADO.NET or something.

Is still think that ( a_int INT; ) is an invalid syntax and should not compile with any connection type unless the editor parses it incorrectly and removes ";" before sending that code to the database server

	gemisigo wrote:
	...You're right, the code posted would not work but that's the result of incorrectly editing the copypasted code...

Hmm. I guess you missed this sentence 5 posts above. When I said I was sure it wasn't that I did not mean it wasn't the reason the procedures weren't created. The code, as you noticed, is of invalid syntax, of course. I edited the code after pasting it into the browser. I removed parts of it, both from parameter list and from body to make it simple. I usually do that by selecting text and pressing delete or enter. I probably pressed ; instead of enter. They are next to each other on my keyboard.
The original code that failed to execute was of proper syntax. It was created and saved in SQL Editor several days before and ran without any problems, just the way it does now. Nothing changed in it.

Sorry I missed the reference to copy&paste edit.

in SA you can try 3 different connection types for MySQl, MySQLlib.dll, ODBC, and ADO.NET, each uses different type of MySQL client code with its own quirks and behavior which isn't always consistent.

MySQLlib.dll and ODBC were the ones that I failed with. Haven't tried ADO.NET.

UPDATE: and here it comes again, this time I captured it. Watch it here. On the right side is an instance of SQL Editor with the script that fails. To the left there is a Toad editor with exactly the same, copy-pasted code. The small difference you can see on the screen (at SELECT ... statement) is Toad replacing tabs with spaces). The script runs without any errors here. SQL Editor forwarded the following error message:

	Quote:
	Code 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN SELECT 1 AS a; END' at line 5

Any ideas?

I have posted this as a bug ticket and asked for help with the issue. I will report on that as soon as I hear anything back.

Things are falling apart here :) I get random malfunctions every day. Right now, creating stored procedures works. Updating tables works as well (it didn't two days ago). However, inserting into tables does not. Syntactically correct INSERT statements like this one below

	Code:
	INSERT INTO ertekesites.altermek_tag_altermek ( id_altermek, id_altermek_tag ) SELECT a.id_altermekek ,9999 FROM ertekesites.altermekek AS a WHERE 1 = 1 AND a.id_kedvezmeny_csoportok = 9999;

fail to execute in SQL Editor and result in error message

	Quote:
	Code 1064, State 42000, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id_altermek, id_altermek_tag SELECT a.id_altermekek ,9999 FROM ertekesites.' at line 3

I was beginning to suspect SA (or SQL Editor) adds some random characters (or removes some) to the query that are not visible in the editor before sending the whole shebang to the server but then I let the connection through mysql-proxy and nothing unusual was seen in the query passed. I'm puzzled.

It surfaced again. The strange thing is that the following script is syntactically correct and runs without any glitches in Toad and the procedure is created:

	Code:
	-- Drop procedure usp_aoeu if it exists DROP PROCEDURE IF EXISTS `usp_aoeu`; DELIMITER $$$ -- Create procedure usp_aoeu CREATE PROCEDURE `usp_aoeu` ( ) BEGIN SELECT 1; END; $$$ --- Procedure volan.usp_aoeu created DELIMITER ;

But it throws an error in SQL Editor:

	Quote:
	Code 1064, State 42000, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN SELECT 1; END' at line 5

Altering it a little bit (removing ; at the end of END, and moving $$$ there) will still be a syntactically correct code:

	Code:
	-- Drop procedure usp_aoeu if it exists DROP PROCEDURE IF EXISTS `usp_aoeu`; DELIMITER $$$ -- Create procedure usp_aoeu CREATE PROCEDURE `usp_aoeu` ( ) BEGIN SELECT 1; END$$$ --- Procedure volan.usp_aoeu created DELIMITER ;

which executes without errors in both Toad and SQL Editor. The difference is that while it creates the stored procedure in Toad, it only drops an existing one in SQL Editor.

Any ideas?

EDIT: passing them through MySQL Proxy yields:

	Quote:
	we got a normal query: DROP PROCEDURE IF EXISTS `usp_aoeu` we got a normal query: SHOW WARNINGS we got a normal query: SELECT DATABASE() we got a normal query: CREATE PROCEDURE `usp_aoeu` ( ) BEGIN SELECT 1; END

for Toad
and a single

	Quote:
	we got a normal query: DROP PROCEDURE IF EXISTS `usp_aoeu`

for SQL Editor.

When pushing the original query through MySQL Proxy shows no changes for Toad but the captured data for SQL Editor is slightly different:

	Quote:
	we got a normal query: DROP PROCEDURE IF EXISTS `usp_aoeu` we got a normal query: CREATE PROCEDURE `usp_aoeu` BEGIN SELECT 1; END;

This indicates that the original statement fails because the paretheses seem to be lost somewhere between MySQL Proxy and SQL Editor. Also, having an argument this way:

	Code:
	CREATE PROCEDURE `usp_aoeu` ( a INT )

loses the closing parenthesis, but having:

	Code:
	CREATE PROCEDURE `usp_aoeu` ( a INT)

retains them both.
Is it possible that SQL Editor swallows one or both of the parentheses in case they aren't on the same line and there's nothing between them? If so, why does it happen? And why is it random (sometimes happening, sometimes not)?

EDIT2: I suppose the INSERT mentioned here failed for the same reason (missing closing parenthesis).

Thank you very much. This is very helpful. I added your messages to the ticket. Hope a root cause of this issue can be identified and fixed soon.