Using a custom delimiter got error in MySQL?

Question

Modifed.

DROP FUNCTION IF EXISTS PersonName;
DELIMITER |;

CREATE FUNCTION PersonName( personID SMALLINT )
RETURNS CHAR(20)
BEGIN
  DECLARE pname CHAR(20) DEFAULT '';
  SELECT name INTO pname FROM family WHERE ID=personID;
  RETURN pname;
END;
|
DELIMITER ;

whats wrong with this code? i get following error with it.

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 102 STR: |; SQL: DROP FUNCTION IF EXISTS PersonName;# MySQL returned an empty result set (i.e. zero rows).

DELIMITER |; DELIMITER |; DELIMITER |; DELIMITER |; DELIMITER |; DELIMITER |; DELIMITER |;

SQL query:

DELIMITER |;

MySQL said: Documentation #1064 - 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 |' at line 1

Dystopia

basit, a tip: you must escape # (hash) symbol like this \#1064, otherwise the text you post is formated as a Header. – Haroun

Answer 1

I would remove the semicolon after END.

    ...
END
|
DELIMITER ;

---

Re your comment, you can't use the current delimiter when declaring a new delimiter. That sounds confusing, but consider if you do this:

DELIMITER |;

Now MySQL would think the delimiter is "|;" (two characters, a pipe and a semicolon). If you think about it, DELIMITER must be treated in a special way by the MySQL client. It's the only statement that can't be followed by the current delimiter.

So when setting the delimiter to pipe, do this:

DELIMITER |

When setting it back to semicolon, do this:

DELIMITER ;

---

FWIW, I ran the following with no error on my local test database on MySQL 5.0.75:

DROP FUNCTION IF EXISTS PersonName;
DELIMITER |

CREATE FUNCTION PersonName( personID SMALLINT )
RETURNS CHAR(20)
BEGIN
  DECLARE pname CHAR(20) DEFAULT '';
  SELECT name INTO pname FROM family WHERE ID=personID;
  RETURN pname;
END
|
DELIMITER ;

Cattleman

now getting #1064 - 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 |' at line 1 – Dystopia

i have 5.0.67-community-nt and it dont work on it.. dunno why.. i have tried on online server too, which is 5.0.81-community, but it gives me user access denied error on drop table.. the first line. – Dystopia

thank you for the help :).. im gonna try some new functions to see the result :) – Dystopia

Answer 2

Try this:

DROP FUNCTION IF EXISTS PersonName;
DELIMITER |

CREATE FUNCTION PersonName( personID SMALLINT )
RETURNS CHAR(20)
BEGIN
  DECLARE pname CHAR(20) DEFAULT '';
  SELECT name INTO pname FROM family WHERE ID=personID;
  RETURN pname;
END;
|
DELIMITER ; /* <-- add a space between DELIMITER and the semicolon */

Xeric

now i get #1064 - 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 |' at line 1 – Dystopia

OK. Let's try what mikej suggests. – Xeric

Answer 3

Try this if you are using phpMyAdmin:

http://dotnetfish.blogspot.com/2009/07/1064-you-have-error-in-your-sql-syntax.html

Agenesis

Answer 4

I was have problems with the delimiter. I was using Navicat, then I rolled over to MySql workbench and the problem is solved. Workbench inserts the delimiter into code...

Fifty

Yep, just had the same issue. Pasted into the console window and the query worked fine. – Ingridingrim

Answer 5

Best solution is, which I tried after getting the above error. The code should be like

Delimiter //
Create function or procedure
Write your function or procedure here...
End (without semicolon)
//
Delimiter ; (semicolon with space)

Sordid

Answer 6

In your last line where you're restoring the delimiter to semicolon you need a space between DELIMITER and ; i.e.

DELIMITER ;

Wardrobe

Answer 7

I recently stumbled upon integrating MySQL Stored Procedure with Liquibase Scripts using Spring Boot Project.

1. Paste your MySQL Stored Procedure in GetCustomersWithCreditCardExpiry.sql File which is saved under liquibase directory.

   DROP PROCEDURE IF EXISTS GetCustomersWithCreditCardExpiry;
   #
   CREATE PROCEDURE GetCustomersWithCreditCardExpiry(IN customer_status VARCHAR(10), IN time_period INT(11))
   BEGIN
   SELECT cs.*
   FROM customer cs
     JOIN credit_card cc
          ON cc.id = cs.credit_card_id
   WHERE cs.status = customer_status
   AND cc.expiry_date < DATE_ADD(now(), INTERVAL time_period HOUR);
   END
   #
   

2. Add the following changeset in Liquibase Script with DELIMITER as #

   <changeSet id="1" author="ishaq" runOnChange="true">

   <sqlFile path="procedures/GetCustomersWithCreditCardExpiry.sql"

   relativeToChangelogFile="true"

   endDelimiter="#"

   splitStatements="true"/>

   </changeSet>

3. Consuming the Stored Procedure within the Spring Boot Java Application.

    @Query(value = "CALL GetCustomersWithCreditCardExpiry(:customer_status, :time_period);", nativeQuery = true)
    List<CustomerCreditCardRenewal> GetCustomersWithCreditCardExpiry(@Param("customer_status") String customer_status,
                                                                   @Param("time_period") Integer time_period);
   

4. Entity Class to map the results

   @Builder(toBuilder = true)
   @AllArgsConstructor
   @NoArgsConstructor
   @Getter
   @Data
   @Entity
   @NamedStoredProcedureQuery(name = "CustomerCreditCardRenewal.getCustomersWithCreditCardExpiry",
    procedureName = "GetCustomersWithCreditCardExpiry",
    parameters = {
            @StoredProcedureParameter(
                    mode = ParameterMode.IN,
                    name = "customer_status",
                    type = String.class
            ),
            @StoredProcedureParameter(
                    mode = ParameterMode.IN,
                    name = "time_period",
                    type = Integer.class
            )
    })
    public class CustomerCreditCardRenewal {
    //... members
    }
   

When the spring boot application runs the changeset in Liquibase script will be executed without any errors. By invoking the Repository Method using a Service Class the required results from the Stored Procedure will be populated.

Hope this will help someone.

Maryrose

I had the exact same issue. Thank you for the help and effort! – Frausto

Answer 8

You have to add delimiter $$ in the beginning and in the last of the mysql script you should to end by the delimiter

Labana

Answer 9

I'm going to throw this in the mix because it may help other people with this issue.

If you are using phpMyAdmin, below the SQL entry box there is a delimiter box where you can type the delimiter to use for the query. You can put the outside delimiter here if you want to and then you don't need the delimiter directives.

For example, if you put the delimiter $$ inside the box you could use the following format for your query.

CREATE FUNCTION
blah blah...
END 
$$

Some people may find this easier.

Collaborative

Answer 10

Running this Dbeaver gave this error... run this in mysql shell and you will not get any error..

Jennine

Answer 11

I got the same error when I created an event as shown below:

mysql> USE apple;
mysql> CREATE EVENT my_event
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2023-11-08 00:00:00'
    -> DO
    -> BEGIN
    -> SELECT * FROM person;
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 6

So, I changed the default delimiter ; to something like $$ when I created an event, then the error was solved. Then, after creating an event, I changed the delimiter $$ back to ; as shown below:

mysql> USE apple;
mysql> delimiter $$
    -> CREATE EVENT my_event
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS '2023-11-08 00:00:00'
    -> DO
    -> BEGIN
    -> SELECT * FROM person;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

And, I got the error below:

DELIMITER must be followed by a 'delimiter' character or string

When I ran the delimiter command with the default delimiter ';' not putting any spaces between delimiter and ; as shown below:

delimiter;

So, I put a space between delimiter and ; as shown below, then the error was solved:

      -- ↓ A space
delimiter ;

Verboten