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.