List:General Discussion« Previous MessageNext Message »
From:Mark Haney Date:December 27 2011 6:13pm
Subject:errors running WHILE loop
View as plain text  
I've had a DEVIL of a time with this problem and I'm sure it's something simple, but I
can't find it anywhere in the documentation or online what the problem is.  Let me
explain.

I cannot get any WHILE loop to work from either a CLI or a script on MySQL 5.1.41.  For
example, this is a simple script that sets a variable, and loops through 10 interations
and prints the SQL version each time.  It's a silly script, but this is what I'm getting:

set @v1 = 0; while @v1 < 10 do; use mysql; select VERSION(); set @v1 = @v1 + 1;
Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (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 'while @v1 <
10 do' at line 1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+---------------------+
| VERSION()           |
+---------------------+
| 5.1.41-3ubuntu12.10 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Here's where I'm stuck.  In some cases, I've seen where END WHILE is used, or a BEGIN/END
block inside the WHILE, and even outside the WHILE.  I'm at a complete loss as to WTF is
going on.  The documentation tells me little.  In fact the test example in the MySQL
reference manual online for 5.1 bombs as well:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    USE mysql;
    SELECT VERSION();
    SET v1 = v1 - 1;
  END WHILE;
END;

mysql> source ~/dowhile.sql
ERROR 1064 (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 '' at line 3
ERROR 1064 (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 'WHILE v1 >
0 DO
    USE mysql' at line 1
+---------------------+
| VERSION()           |
+---------------------+
| 5.1.41-3ubuntu12.10 |
+---------------------+
1 row in set (0.00 sec)

ERROR 1193 (HY000): Unknown system variable 'v1'
ERROR 1064 (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 'END WHILE' at
line 1
ERROR 1064 (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 'END' at line 1
mysql>

So, what the heck is going on here?  I'm at the end of my wits.



Thread
errors running WHILE loopMark Haney27 Dec
  • Re: errors running WHILE loophsv27 Dec
  • Re: errors running WHILE loopPeter Brawley28 Dec