MySQL 5.1.39 SQL syntax error

Question

I have made an upgrade for my MySQL Server to 5.1.39 and now when I run SQL scripts (which had worked previously) - it throws error. I have checked syntax many times and I couldn't find any incompatible code parts. Please suggest any solution for this problem.

Error message

Mysql::Error: 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 clean_dymmy_table (dummy_name VARCHAR(255)) RETURNS V' at line 3:

SQL code:

 /*DELIMITER //*/
 DROP FUNCTION IF EXISTS clean_dymmy_table;
 CREATE FUNCTION clean_dymmy_table (dummy_name VARCHAR(255)) RETURNS VARCHAR(255)
 DETERMINISTIC
 BEGIN
    DECLARE temp_val VARCHAR(255);
    SET temp_val = dummy_name;

    -- Test
    SET temp_val = REPLACE(temp_val, 'Tmp ', '');
    SET temp_val = REPLACE(temp_val, ' TmP', '');
    SET temp_val = REPLACE(temp_val, 'TMP ', '');
    SET temp_val = REPLACE(temp_val, ' TMP', '');
    SET temp_val = REPLACE(temp_val, ' tmp', '');

    RETURN dummy_name;
 END/*//*/

Do you reckon this has anything to do with it? bugs.mysql.com/bug.php?id=46429 — pinkgothic
– pinkgothic, Commented Jun 28, 2011 at 11:46

cwallenpoole · Accepted Answer · 2011-06-28 10:40:03Z

1

Not sure why you removed the DELIMITER part, but when I add that back in, it runs fine:

DELIMITER // -- you have to change what MySQL expects between commands
DROP FUNCTION IF EXISTS clean_dymmy_table // -- tell it a new command's coming
CREATE FUNCTION clean_dymmy_table (dummy_name VARCHAR(255)) 
                                   RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    -- now this can be parsed as part of the current command.
    DECLARE temp_val VARCHAR(255); 
    SET temp_val = dummy_name;

    -- Test
    RETURN dummy_name;
END
// -- Now you're done with that command.
-- go back to semi-colons, because otherwise life is too zany for me.
DELIMITER ;

(This was in 5.1.54... but I don't think that should matter)

answered Jun 28, 2011 at 10:40

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

thesis Over a year ago

I have changed code to this:

DELIMITER // DROP FUNCTION IF EXISTS clean_dymmy_table // CREATE FUNCTION clean_dymmy_table (dummy_name VARCHAR(255))  RETURNS VARCHAR(255) DETERMINISTIC BEGIN     DECLARE temp_val VARCHAR(255);     SET temp_val = dummy_name;      RETURN temp_val; END // DELIMITER ;

But now it throws same error

Mysql::Error: 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 'DELIMITER // DROP FUNCTION IF EXISTS clean_dymmy_table // CREATE FUNCTION c' at line 1

cwallenpoole Over a year ago

What happens if you literally copy and paste the above into something which will evaluate a script? (In MySQL browser this will be under scripts, it won't be a regular query)

Community · Accepted Answer · 2017-05-23 10:33:17Z

0

A hunch: There seems to be a MySQL Bug in the 5.1.x line pertaining to DELIMITER that may be biting you here:

#46429 use DELIMITER command in MySql.Data.MySqlClient.MySqlScript

Though that heavily depends on how you're calling it, given cwallenpoole's response, your symptom does suggest it may well be that bug, given your MySQL version. Is upgrading a possibility for you?

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Jun 28, 2011 at 11:59

pinkgothic

6,2173 gold badges53 silver badges74 bronze badges

2 Comments

thesis Over a year ago

Better way is not to upgrade. Do you have any suggestion, how can I refactor SQL code to get rid of DELIMITER statement in the code?

pinkgothic Over a year ago

I've never worked with SQL procedures, unfortunately. But if I understand the bug correctly, you should be able to use DELIMITER by calling the code from the mysql client. Check out the comment by Valeriy Kravchuk in that link.

Collectives™ on Stack Overflow

MySQL 5.1.39 SQL syntax error

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related