Unable to Run Query in MySQL syntax error unexpected

Question

I'm running Workbench 5.2.47.

I have a long procedure I wrote with basic data checking. If a record did not exist in the database, the record would be inserted.

The procedure saved with no problems, but MySQL 5.5 throws an error when I try running it.

It is long, and has a lot of company sensitive data in it, or I would post it here.

I am trying to debug the procedure by executing small chunks of the code, but I can't seem to get Workbench to allow anything I try.

MySQL shows how to create a stored procedure in 5.1.5 Working with Stored Procedures.

Let me show you something very basic I am trying to write:

DELIMITER $$
DROP PROCEDURE IF EXISTS my_test;

CREATE PROCEDURE my_test()
BEGIN
    SELECT * FROM Employees;
END $$

DELIMITER ;

With that, Workbench gives me the error, "syntax error, unexpected CREATE, expecting $end".

I don't understand that, but I need to get something done, so I am moving on.

I make a simpler query:

SET @Count=(SELECT Count(*) FROM tbl_object_users WHERE username='jp2code');
IF (@Count < 1) THEN
    INSERT INTO tbl_object_users (username, date_time) VALUES ('jp2code', NOW());
END IF;

Again, I get an error, this time on my IF statement.

Next, I go into PhpMyAdmin to try running something from there using its database:

SET @Count=Count(id) FROM `tbl_object_users` WHERE `username`='jp2code';

It, too, tells me I have an error in my SQL syntax.

I did download and install the newest Workbench 6, but it did not solve the problem - and I did not like the interface, so I uninstalled it and went back to Workbench 5.2.

What is going on? SQL isn't that hard, so what is with these hurdles?

Answer 1

Problem with this:

DELIMITER $$
DROP PROCEDURE IF EXISTS my_test;
CREATE PROCEDURE my_test() ...

is that MySQL isn't seeing the semicolon at the end of the DROP PROCEDURE statement line as the end of the statement. This is because the preceding line told MySQL that the statement terminator was something other than a semicolon. You told MySQL that statements were going to be terminated with two dollar signs. So MySQL is reading the DROP PROCEDURE line, looking for the statement terminator. And the whole blob it reads is NOT a valid MySQL statement, it generates a syntax error.

The fix: either move the DROP PROCEDURE line before the DELIMITER $$ line; or terminate the DROP PROCEDURE statement with the specified delimiter rather than a semicolon.

---

The second problem you report is a syntax error. That's occurring because MySQL doesn't recognize IF as the beginning of a valid SQL statement.

The IF statement is valid only within the context of a MySQL stored program (for example, within a CREATE PROCEDURE statement.)

The fix: Use an IF statement only within the context of a MySQL stored program.

---

The third problem you report is also a syntax error. That's occurring because you don't have a valid syntax for a SET statement; MySQL syntax for SET statement to assign a value to user variable is:

SET @uservar = expr 

MySQL is expecting an expression after the equals sign. MySQL is not expecting a SQL statement.

To assign a value to a user variable as the result from a SELECT statement, do the assignment within the SELECT statement, for example:

SELECT @Count := Count(id) FROM `tbl_object_users` WHERE `username`='jp2code'

Note that the assignment operator inside the SELECT statement is := (colon equals), not just =.

Answer 2

try this

DELIMITER $$

DROP PROCEDURE IF EXISTS my_test$$

CREATE PROCEDURE my_test()
BEGIN
    SELECT * FROM `customer_to_pay`;
END $$

DELIMITER ;