MySQL, phpmyadmin, how to import user defined functions

Question

Using MySQL, How do I import a user defined database function from one db server to another db server using phpmyadmin?

A list of user defined functions can be fetched using this SQL:

select * from information_schema.routines;

Here is My function I want to move to a different database server:

CREATE DEFINER=`XXX`@`%` FUNCTION `BDayDiff`( d1 DATE, d2 DATE ) RETURNS int(11)
   DETERMINISTIC
BEGIN
 DECLARE dow1, dow2, days, wknddays INT;
 SET dow1 = DAYOFWEEK(d1);
 SET dow2 = DAYOFWEEK(d2);
 SET days = DATEDIFF(d2,d1);
 SET wknddays = 2 * FLOOR( days / 7 ) +
                IF( dow1 = 1 AND dow2 > 1, 1,                              
                    IF( dow1 = 7 AND dow2 = 1, 1,              
                        IF( dow1 > 1 AND dow1 > dow2, 2,      
                            IF( dow1 < 7 AND dow2 = 7, 1, 0 )  
                          )
                      )
                  );
 RETURN FLOOR(days - wkndDays);
END

But I get an error:

Error

SQL query:

CREATE DEFINER = `XXX`@`%` FUNCTION `BDayDiff` (
d1 DATE,
d2 DATE
) RETURNS INT( 11 ) DETERMINISTIC BEGIN DECLARE dow1,
dow2,
days,
wknddays INT;

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

score 6 · Answer 1 · answered Jun 13 '12 at 06:41

Try this by adding $, it works for me.

DELIMITER $

CREATE DEFINER=`XXX`@`%` FUNCTION `BDayDiff`( d1 DATE, d2 DATE ) RETURNS int(11)
   DETERMINISTIC
BEGIN
 DECLARE dow1, dow2, days, wknddays INT;
 SET dow1 = DAYOFWEEK(d1);
 SET dow2 = DAYOFWEEK(d2);
 SET days = DATEDIFF(d2,d1);
 SET wknddays = 2 * FLOOR( days / 7 ) +
                IF( dow1 = 1 AND dow2 > 1, 1,                              
                    IF( dow1 = 7 AND dow2 = 1, 1,              
                        IF( dow1 > 1 AND dow1 > dow2, 2,      
                            IF( dow1 < 7 AND dow2 = 7, 1, 0 )  
                          )
                      )
                  );
 RETURN FLOOR(days - wkndDays);
END$

score 2 · Accepted Answer · answered Jun 12 '12 at 14:11

2

Hope this is possible in phpmyadmin:

Execute SHOW CREATE FUNCTION
You will see CREATE FUNCTION statement, execute it against another MySQL server
Repeat this steps for each function.

Note, that functions may have security options (DEFINER, SQL SECURITY), read about these options in the documentation (CREATE PROCEDURE and CREATE FUNCTION Syntax); if you want to use them, check that specified users are created on another server.

SHOW CREATE FUNCTION Syntax.

answered Jun 12 '12 at 14:11

Devart

119,203
23
166
186

thanks Devart, I have created query to create function but somehow it is throwing error. I have updated the question above with error. any idea? – Developer Jun 12 '12 at 15:26
It can be a DELIMITER problem on old phpmyadmin versions - http://stackoverflow.com/questions/2846516/how-to-write-a-stored-procedure-using-phpmyadmin-and-how-to-use-it-through-php – Devart Jun 13 '12 at 05:49
You also may try to create the procedure using mysql extensions for PHP. – Devart Jun 13 '12 at 05:50

score 0 · Answer 3 · answered Apr 13 '14 at 10:53

If you are using phpmyadmin interface to execute the query for function then you should follow the steps as below: 

DELIMITER $$

CREATE FUNCTION `BDayDiff`( d1 DATE, d2 DATE ) RETURNS int(11)
    DETERMINISTIC
BEGIN
 DECLARE dow1, dow2, days, wknddays INT;
 SET dow1 = DAYOFWEEK(d1);
 SET dow2 = DAYOFWEEK(d2);
 SET days = DATEDIFF(d2,d1);
 SET wknddays = 2 * FLOOR( days / 7 ) +
                IF( dow1 = 1 AND dow2 > 1, 1,                              
                    IF( dow1 = 7 AND dow2 = 1, 1,              
                        IF( dow1 > 1 AND dow1 > dow2, 2,      
                            IF( dow1 < 7 AND dow2 = 7, 1, 0 )  
                          )
                      )
                  );
 RETURN FLOOR(days - wkndDays);
END $$

DELIMITER ;

Bahriddin Abdiev · Answer 4 · 2015-09-03T11:00:12.363

In order to export User-defined functions:

In PhpMyAdmin open your DB. After that click on "Export" button. In opening window it should be folowing checkboxes in (if it's not shown select "Custom - display all possible options"): 1) Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT; 2) Add CREATE PROCEDURE / FUNCTION / EVENT; Check and export it.

Just import it to your DB and it works fine!

P.S. You can see those functions in Information Schema DB's Routines table but you can't import it directly because you haven't permission even if you are root user.

score 0 · Answer 5 · answered Jul 31 '19 at 09:24

0

If you have exported it from PHPMyAdmin, you need to include at the head of code with

    DELIMITER $
CREATE DEFINER=`root`@`localhost` FUNCTION

DELIMITER ;

this could resolve your issue.

answered Jul 31 '19 at 09:24

Pranav Bhatt

715
4
8

MySQL, phpmyadmin, how to import user defined functions

5 Answers5