Error in function creating query

Question

There is an error in the following function creating query:

CREATE FUNCTION `_increase_num` () RETURNS int(11)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
 RETURN IF(@counter, @counter:=@counter+1, @counter:=1);
END

1. Any idea how to fix?
2. Can I avoid using ; in the query above? The development framework doesn't allow multiple queries in one call and identifies this one as multiple queries.

EDIT1: The aim of the function to make each row unique, like an autoincreased primary key. But for some development reason I can't use autoincremented primary keys.

EDIT2: The error message is usual, that query failed. You have an error in your query.

EDIT3: I use the function like this way: SELECT _increase_num() as rownr, content FROM mytable

WORKING SOLUTION:

CREATE FUNCTION _increase_num() 
returns int 
 return IF(@counter, @counter:=@counter+1, @counter:=1)

• Lennart - Slava Ukraini: There are a couple of obvious problems: RETURNS int(11) RETURNS INT. It does not appear to read any SQL DATA, you can probably skip that one. Is the function really deterministic? It's probably a bad idea to update a global variable via a function, can't you pass it as an argument to the function?
• RDFozz: It can also be helpful to provide the actual error message you got (if any).
• klor: Thanks for your answer and your fixes! The aim of the function to make line unique, like an autoincreased primary key. But for some development reason I can't use autoincremented primary keys.
• klor: Edited my post.

Answer 1

Here's a version that does not give a syntax error:

delimiter !!
drop function _increase_num !! 
CREATE FUNCTION _increase_num() 
returns int 
begin 
 return IF(@counter, @counter:=@counter+1, @counter:=1); 
end !!
delimiter ;

A bit of warning though, whenever counter looses its value it will start from 1. Also, I would not put any money on how well this works when the function is called in parallel from several processes.

Edit: ";" has a special meaning in stored procedure language, it separates statements within the procedure. This is why you normally change the statement terminator to something else:

delimiter @

However, for this trivial procedure, we don't need the begin/end block. That is:

CREATE FUNCTION _increase_num() 
returns int 
 return IF(@counter, @counter:=@counter+1, @counter:=1);

will do.

• klor: Thanks for your answer! Can I avoid using ; sign? I described reason in EDIT1.
• klor: I got an error, that function doesn't exist to drop.
• Lennart - Slava Ukraini: well, don't drop it then ;-) I added some info
• klor: Great solutions! But the ; sign is still not eliminated completely. I must have to avoid it.
• Lennart - Slava Ukraini: Have you tried removing it?