Bug #42401 The server has returned this error message:You have an error in your SQL syntax
Submitted: 28 Jan 2009 5:44 Modified: 2 Apr 2009 20:18
Reporter: Cozta Nayl Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S1 (Critical)
Version:5.1, 6.0 bzr OS:Any (MS Windows 2008 Server, Linux)
Assigned to: CPU Architecture:Any
Tags: In Param, Naming Error, regression, stored procedure
Triage: Triaged: D3 (Medium) / R5 (Severe) / E2 (Low)

[28 Jan 2009 5:44] Cozta Nayl
Description:
The server has returned this error message:

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 '_FileName varchar(200)'

when I tried to restore my database from MySql Version 5.0.

It doesn't like the In Param '_FileName'. When I change the name to _F or anything else, it works fine.

How to repeat:
I have the following stored procedure from MySql 5.0:

DROP PROCEDURE IF EXISTS `crud_bpaaccountfiletbl`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `crud_bpaaccountfiletbl`(
	_BpaAccountFileId int,
	_CoopId smallint,
	_OriginalFileName varchar(200),
	_FileTypeId smallint,
	_FileName varchar(200),
	_Description text,
	_Succeeded int,
	_Failed int,
	_FileStatusId smallint,
	_RegisterDate datetime,
	_ModifyDate datetime,
	_PrintDate datetime,
	_RegisterBy int,
	_ModifyBy int,
	_PrintBy int,
	Command varchar(10)
)
BEGIN
	IF (Command='INSERT') THEN
		INSERT INTO bpaaccountfiletbl (
			CoopId,
			OriginalFileName,
			FileTypeId,
			FileName,
			Description,
			Succeeded,
			Failed,
			FileStatusId,
			RegisterDate,
			ModifyDate,
			PrintDate,
			RegisterBy,
			ModifyBy,
			PrintBy
		) VALUES (
			_CoopId,
			_OriginalFileName,
			_FileTypeId,
			_FileName,
			_Description,
			_Succeeded,
			_Failed,
			_FileStatusId,
			_RegisterDate,
			_ModifyDate,
			_PrintDate,
			_RegisterBy,
			_ModifyBy,
			_PrintBy
		);
		SELECT LAST_INSERT_ID();
	ELSEIF (Command='SELECT') THEN
		SELECT * FROM bpaaccountfiletbl WHERE BpaAccountFileId=_BpaAccountFileId;
	ELSEIF (Command='UPDATE') THEN
		UPDATE bpaaccountfiletbl SET
			CoopId=_CoopId,
			OriginalFileName=_OriginalFileName,
			FileTypeId=_FileTypeId,
			FileName=_FileName,
			Description=_Description,
			Succeeded=_Succeeded,
			Failed=_Failed,
			FileStatusId=_FileStatusId,
			RegisterDate=_RegisterDate,
			ModifyDate=_ModifyDate,
			PrintDate=_PrintDate,
			RegisterBy=_RegisterBy,
			ModifyBy=_ModifyBy,
			PrintBy=_PrintBy
		WHERE BpaAccountFileId=_BpaAccountFileId;
	ELSEIF (Command='DELETE') THEN
		DELETE FROM bpaaccountfiletbl WHERE BpaAccountFileId=_BpaAccountFileId;
	END IF;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;
[28 Jan 2009 5:47] Cozta Nayl
I forgot to mention, Version 5.1 win x64 on Windows 2008 Server
[28 Jan 2009 5:55] Shane Bester
smaller testcase:

drop table if exists `t1`;
create table `t1`(_filename int);

quoting all params, fields in `backticks` works.
[28 Jan 2009 10:46] Sveta Smirnova
Thank you for the report.

Verified as described. Versions 4.1 and 5.0 are not affected.
[10 Mar 2009 4:37] Tony Bojangles
I just ran into this bug.  Any update on when it'll be fixed?
[2 Apr 2009 20:18] Paul Dubois
_FILENAME is treated as a reserved word by the parser. This has been noted at:
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html