mysql delimiter error

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

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 ;

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 */

Answer 3

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)

Answer 4

Try this if you are using phpMyAdmin:

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

Answer 5

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...

Answer 6

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

DELIMITER ;

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.

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

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.

Answer 10

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