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.
