How to copy MySQL function?

Question

I have tried to use the export option in phpMyAdmin routines panel to copy functions from one database to another, with no success.

The export option supplies me with the following:

CREATE DEFINER=`root`@`localhost` FUNCTION `JSON_FIELD_NUM`(`col_name` TEXT CHARSET utf8, `data` TEXT CHARSET utf8) RETURNS text CHARSET utf8
    NO SQL
BEGIN
   RETURN 
   CONCAT('"',col_name,'":',
       IF(ISNULL(data),0,data)
   );
END

I get this error when I run that in another database:

#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 '' at line 7

I tried adding DELIMITER $$ at the top and $$ after END, but still no joy.

eggyal · Accepted Answer · 2013-01-18 22:47:51Z

4

You must set your client's statement delimiter to a string other than ; in order that it doesn't think the semicolon which ends the CONCAT() expression also terminates the CREATE FUNCTION statement.

In the MySQL command line tool, you can use the DELIMITER command. In phpMyAdmin, you will need to use the Delimiter text box before clicking Go.

edited Jan 18, 2013 at 22:47

answered Jan 18, 2013 at 22:42

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

1 Comment

Mahks Over a year ago

Delimiter text box ... so obvious once you know its there, Thanks

RolandoMySQLDBA · Accepted Answer · 2013-01-18 22:57:32Z

1

There is a far more concise way to do this:

MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-data"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-create-info"
mysqldump ${MYSQLDUMP_OPTIONS} > StoredProcedures.sql
less StoredProcedures.sql

This will dump only Stored Procedures.

Give it a Try !!!

edited Jan 18, 2013 at 22:57

answered Jan 18, 2013 at 22:52

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

3 Comments

eggyal Over a year ago

Surely this will produce the same CREATE FUNCTION statement as that with which the OP is already having problems?

RolandoMySQLDBA Over a year ago

@eggyal The DELIMITER statements are generated within the mysqldump

eggyal Over a year ago

And the OP says he's already tried using DELIMITER. The problem is that he's not using the MySQL command line tool.

Collectives™ on Stack Overflow

How to copy MySQL function?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related