MySQL Bugs: #20186: spurious syntax error when using "source"

Bug #20186	spurious syntax error when using "source"
Submitted:	1 Jun 2006 7:58	Modified:	13 Aug 2008 12:03
Reporter:	Mark Johnson (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	1.2.12	OS:	Linux (Linux 2.6.12; slackware 10.2)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
When using the source command in the mysql command-line client, I get a syntax error when creating a table.  However, when I copy and paste that same command from my script file to the command-line tool directly, it works perfectly.

How to repeat:
Here's a copy of my session:
mj@enterprise:~/issues/mysql/new$ /usr/local/mysql/bin/mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21-standard

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

mysql> drop database Test;
Query OK, 2 rows affected (0.03 sec)

mysql> create database Test;
Query OK, 1 row affected (0.00 sec)

mysql> use Test;
Database changed
mysql> source tables.sql;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

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 8
mysql> drop database Test;
Query OK, 2 rows affected (0.02 sec)

mysql> create database Test;
Query OK, 1 row affected (0.00 sec)

mysql> use Test;
Database changed
mysql> CREATE TABLE IF NOT EXISTS `Test`.`Person` (
    ->         id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
    ->         FirstName VARCHAR(50) NOT NULL ,
    ->         LastName VARCHAR(50) NOT NULL ,
    ->         Phone VARCHAR(25) NULL ,
    ->         Email VARCHAR(129) NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> quit
Bye

Here's themj@enterprise:~/issues/mysql/new$ cat tables.sql
DELIMITER $$

CREATE TABLE IF NOT EXISTS `Test`.`Person` (
        id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
        FirstName VARCHAR(50) NOT NULL ,
        LastName VARCHAR(50) NOT NULL ,
        Phone VARCHAR(25) NULL ,
        Email VARCHAR(129) NULL
) ENGINE=InnoDB$$
CREATE TABLE IF NOT EXISTS `Test`.`Ratings` (
        RecipeID INT NOT NULL ,
        PersonID INT NOT NULL ,
        Rating INT NOT NULL ,
        Date DATETIME NOT NULL
) ENGINE=InnoDB$$

 CREATE  INDEX Index1 ON `Test`.`Ratings` (RecipeID, PersonID) $$

ALTER TABLE `Test`.`Ratings` ADD
        CONSTRAINT `FK_Ratings_Person` FOREIGN KEY
        (
                PersonID
        ) REFERENCES `Test`.`Person` (
                id
        )
        )$$

DELIMITER ;

Note that the syntax error is being reported as being at line 8.
However, the actual syntax error is an extra ')' at the END of the file.

Thanks for your bug report.  I was able to reporduce this bug under FC5 w/mysql 5.0.23bk

mysql> use Test;
Database changed
mysql> source t.sql;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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 8
mysql>

I got the same problem when trying to create a Stored Procedure from a file.

Note the strange characters added before changing the delimiter on my very first line:

'∩╗┐DELIMITER $$

code follows

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.1.11-beta-log |
+-----------------+
1 row in set (0.00 sec)

mysql> delimiter $$
mysql> drop procedure if exists HelloWorld$$
Query OK, 0 rows affected (0.01 sec)

mysql> create procedure HelloWorld()
    -> BEGIN
    -> SELECT 'Hello World';
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call HelloWorld();
+-------------+
| Hello World |
+-------------+
| Hello World |
+-------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> DROP PROCEDURE HelloWorld;
Query OK, 0 rows affected (0.00 sec)

mysql> call HelloWorld();
ERROR 1305 (42000): PROCEDURE vida.HelloWorld does not exist
mysql> SOURCE HelloWorld2.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 '∩╗┐DE
LIMITER $$

DROP PROCEDURE IF EXISTS HelloWorld$$
CREATE PROCEDURE Hello' 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$$

DELIMITER' at line 1

When can we expect a solution, or at least a workaround?

wolfie

Actually, I just discovered that the problem really lies within "MySQL Query Browser." Apparently the script editor is adding the spurious characters at the begining of the file. I tried editing and re-saving the file with other editors:
Notepad, Notepad++, and Wordpad.  Wordpad was the only one that got rid of the extra characters (by doing a dummy update on the file and re-saving)

So, I guess the workaround for now is "NOT" to use the Query Browser; at least until someone fixes it.

wolfie

Shouldn't this bug be connected to a Query Browser version rather than a server version? Is there a version available without this bug, old or new? Any plans to correct it?

This is a bug in Query Browser. It should not start script file with "strange" characters (EF BB BF in hex).

The original problem reported by Mark Johnson and the problem reported by Wolfgang Sanyer generate the same error message, but they appear to be caused by two different issues.  Mark's issue seems to be that the line numbering where the syntax error occurs is misleading; however, it is correct relative to the beginning of the last statement in the script.  I added 3 comment lines to the ALTER TABLE statement and the syntax error really does occur at line 11 (8+3) in the ALTER TABLE statement.  Wolfgang's issue results from unparsable syntax inserted in the script file.  I was not able to reproduce the errant characters.  I am running the tools on Linux 2.6; perhaps this is occurring on Windows?

Resolving Wolfgang's problem would not solve the error line numbering problem and vice versa.  So these appear to break down to 2 problems.  I hope I have analyzed this correctly.

I thought I'd try to come up with a bug fix for this one because it seems like one of the less risky problems and I am just starting out at this.

a duplicate of bug#29323.
Fixed in 5.0 and up.