Is something wrong with this MySQL query?

Question

I'm writing stored procedures for the first time, and it's not working. The trouble is I can't see a reason for it not to work.

I'm running it through phpmyadmin 2.8.2.4. My MySQL version is 5.1. Here is the first part of the query:

create procedure under_user_type (in original_post int, out user_type int, out user_id longtext)
begin
    if exists (
        select *
            from wp_postmeta as pm
            where pm.post_id = original_post
            and pm.meta_key = '_tdomf_original_poster_id'
    ) then
        set user_type = 0;
        select pm.meta_value
            into user_id
            from wp_postmeta as pm
            where pm.post_id = original_post
            and pm.meta_key = '_tdomf_original_poster_id';

    elseif exists ( ...

I get the error:
#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 '' at line 9

Line 9 corresponds to that first select statement in the if exists ( ... ) then portion.

update:

I get the same error if I use:

create procedure under_user_type (in original_post int, out user_type int, out user_id longtext)
begin
    if 1=1 then begin
        set user_type = 0;
        select pm.meta_value
            into user_id
            from wp_postmeta as pm
            where pm.post_id = original_post
            and pm.meta_key = '_tdomf_original_poster_id';
        end;

Update Again:

Running the examples on this MySQL documentation page also gives me the "check your syntax near ''" error. I tried removing all tabs from the query but that did nothing.

Update a third time:

I can run this query:

create procedure blah()
begin
end;

but not this query:

create procedure blah2()
begin
   if 1=1 then
   begin
   end;
   end if;
end;

as I get the same error.

Answer 1

I don't think MySQL can handle "if exists". You could try

declare testMe integer default (select ID from ... where ...);

if testMe is not NULL then
...

Answer 2

The following works for me:

create procedure blah2()
begin
  declare s int;
  if exists(select 1) then
    set s=1;
  end if;
end;

I think your problem (in the very first example, before you try doing all of your experimentation) is that you didn't declare the variable user_type, so MySQL prints out a (very generic) error when it encounters a variable name that it's never seen before on line 9.

(As for all of your other examples, you should not do if ... then begin ... end; The proper syntax is if ... then ... elseif ... else ... end if)

Answer 3

The error is on line 9, when it encounters the first semi-colon.

You need to set the DELIMITER to something other than semi-colon if you're going to use semi-colons inside the body of your procedure.

Here's how to do that for your simplest example:

DELIMITER $$

create procedure blah2()
begin
   if 1=1 then
   begin
   end;
   end if;
end $$

DELIMITER ;

Answer 4

The problem is that PHPMyAdmin must be trying to split up the queries itself every time it sees a semicolon. This is what the MySQL CLI does, which is why it's necessary to change the delimiter when creating a stored procedure on the command line. However, my version of PHPMyAdmin (2.8.2.4) doesn't allow me to change the delimiter, so I just wound up with a bunch of unhelpful error messages.

I wound up writing a script on the remote server with a textarea and submit button that would pass the contents of the textarea to mysqli::multi_query. This function lets mysql handle the delimiters rather than trying to split it up itself, and so creating the procedure worked in this respect.

Another, easier route would be to use MySQL Workbench to connect to the database, possibly through ssh if the database only allows connections from localhost.