MySQL Bugs: #17124: error in function definition causes parser to get confused

Bug #17124	error in function definition causes parser to get confused
Submitted:	4 Feb 2006 21:11	Modified:	8 May 2009 19:30
Reporter:	Matt Borack	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.18-nt	OS:	Linux (Linux, Windows XP Pro)
Assigned to:		CPU Architecture:	Any

Description:

Using Source in MySQL Command Line Client to create procedures/functions/views from a text file will report the file has an error even though the error has been fixed. 

Note: I am able to duplicate this error repeatedly and with little effort. 

Note: I've cold booted the machine and still get the error. 

Note: I'm getting this error on two different machines. Both machines displayed this problem as soon as I installed MySQL.

Note: I'm getting this error using Notepad, WordPad and DOS Edit.

How to repeat:

Use MySQL Command Line Client and login as root.

Switch to the test database using "use test;"

Execute a good version (see below) of the script (I used "source c:/1/c.txt").

The good copy of the script is as follows:

use test;

delimiter //

drop function if exists test;
create function test(p_id bigint)
returns varchar(10)
begin	
	declare return_value varchar(10);

	set return_value = 'a';

	return return_value;
end;//

delimiter ;

Now, change "set return_value = 'a';" to "return_value = 'a';". This will cause the script to fail.

Execute this bad version of the script.

Your should get an error message that points to or near the error.

Now, fix the script by reversing the change made to the "return_value = ..." line.

Execute this fixed version of the script and now you'll get an error message that says "ERROR 1064 (42000): ... syntax to use near '; create function test(p_id bigint) returns varchar(10) begin declare retu' at line 1"

At this point, no matter what changes you make to the file, it will give the above error message. The only way to fix this is to close then reopen the MySQL Command Line Client. If you then run the script after closing/reopening, there will be no error message.

Suggested fix:

Thank you for a bug report. Verified just as described with 5.0.19-BK on Linux:

openxs@suse:~/dbs/5.0> cat /tmp/c.txt
use test;

delimiter //

drop function if exists test;
create function test(p_id bigint)
returns varchar(10)
begin
        declare return_value varchar(10);

        set return_value = 'a';

        return return_value;
end;//

delimiter ;
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source /tmp/c.txt
Database changed
Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

At that moment I had changed the source in a separate session.

mysql> source /tmp/c.txt
Database changed
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 '= 'a'
;
        return return_value;
end' at line 5
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

At that moment I had changed the source back to correct one in a separate session.

mysql> source /tmp/c.txt
Database changed
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 ';
create function test(p_id bigint)
returns varchar(10)
begin
        declare return_v' at line 1
mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source /tmp/c.txt
Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

So, this should be explained.

Changed category to a more appropriate one.

This doesn't appear to be a problem with the mysql client or its source command, the server's SQL parser appears to be getting stuck in an invalid state when there is an error in a function definition.

mysql> delimiter //
mysql> drop function if exists test;
    -> create function test(p_id bigint)
    -> returns varchar(10)
    -> begin
    ->         return "hi";
    -> end;//
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> drop function if exists test;
    -> create function test(p_id bigint)
    -> returns varchar(10)
    -> begin
    ->         "hi";
    -> end;//
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 '"hi";
end' at line 4
mysql> drop function if exists test;
    -> create function test(p_id bigint)
    -> returns varchar(10)
    -> begin
    ->         return "hi";
    -> end;//
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 ';
create function test(p_id bigint)
returns varchar(10)
begin
        return "hi' at line 1
mysql> select version()//
+---------------+
| version()     |
+---------------+
| 5.1.26-rc-log | 
+---------------+
1 row in set (0.34 sec)

I can no longer reproduce this problem with MySQL 5.1.34. This was probably another manifestation of Bug #26030, which was fixed in 5.0.68 and 5.1.28.