Comments - Two days in, and I still can't find the cause of this 1064 error.

7 years, 11 months ago Paul Dineen

Thanks, Brian. I appreciate your taking the time to report those problems.

I'm still having trouble, but I don't want to post my code with those fixes (and further changes) for others to do the work for me. Instead, I now post a boiled-down case that shows the 1064 error in a simple case, where I haven't been able to see what the error is. Specifically, if I simply add begin/end to the procedure body then it switches to giving the error. It's probably something simple, but I just don't see it. Thanks again.

delimiter //

-- simple query to test with
--
select 1, 2 from dual //

-- ********************************************************
-- precedure without "begin" and "end" compiles and runs ok
-- ********************************************************
--
create procedure do_leafs_debug()
select 3, 4 from dual;
//


call do_leafs_debug() //  -- runs fine
drop procedure do_leafs_debug //

-- ******************************************
-- just adding "begin" and "end" gives error:
-- ******************************************
--
create procedure do_leafs_debug()
begin
select 5, 6 from dual;
end
//


-- also error with ";" after "end":
--
create procedure do_leafs_debug()
begin
select 7, 8 from dual;
end;
//


delimiter ;
 
7 years, 10 months ago Ian Gilfillan

The sample code you provided works fine (see below). What client are you using? If you try create the procedure from the command line mysql client and it works, then the problem likely sits with the other client.

mysql [localhost] {msandbox} (test) > delimiter //
mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > -- simple query to test with
mysql [localhost] {msandbox} (test) > --
mysql [localhost] {msandbox} (test) > select 1, 2 from dual //
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > -- ********************************************************
mysql [localhost] {msandbox} (test) > -- precedure without "begin" and "end" compiles and runs ok
mysql [localhost] {msandbox} (test) > -- ********************************************************
mysql [localhost] {msandbox} (test) > --
mysql [localhost] {msandbox} (test) > create procedure do_leafs_debug()
    -> select 3, 4 from dual;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > call do_leafs_debug() //  -- runs fine
+---+---+
| 3 | 4 |
+---+---+
| 3 | 4 |
+---+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > drop procedure do_leafs_debug //
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > -- ******************************************
mysql [localhost] {msandbox} (test) > -- just adding "begin" and "end" gives error:
mysql [localhost] {msandbox} (test) > -- ******************************************
mysql [localhost] {msandbox} (test) > --
mysql [localhost] {msandbox} (test) > create procedure do_leafs_debug()
    -> begin
    -> select 5, 6 from dual;
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > -- also error with ";" after "end":
mysql [localhost] {msandbox} (test) > --
mysql [localhost] {msandbox} (test) > create procedure do_leafs_debug()
    -> begin
    -> select 7, 8 from dual;
    -> end;
    -> //
ERROR 1304 (42000): PROCEDURE do_leafs_debug already exists
mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > delimiter ;
mysql [localhost] {msandbox} (test) > 
 
7 years, 10 months ago Paul Dineen

Thanks for checking this and for responding.

I'm using the HeidiSQL 9.3.0.4984 that came as part of the MariaDB 10.0.28 download.

I have not used the comand line, and would sooner do what I need to with a non-procedure succession of stand-alone queries (and some intermediary tables) in HeidiSQL (or some comparable client) than use the command line. In my limited DB experience, I've used only Oracle SQL Developer and TOAD for Oracle. Do you have any recommendations for free or low-cost HeidiSQL alternatives for MariaDB? I'll google about that, but value first-person recommendations.

Thanks again!

 
7 years, 10 months ago Ian Gilfillan

You can take a look at some alternatives at Graphical and Enhanced Clients. If this is a HeidiSQL issue, please report it as a bug - it seems a strangely trivial issue, so perhaps there's something else being missed that others can pick up when they try recreate it.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.