Exporting Functions

I saw other postings about this but nothing I tried seemed to work. When I export a function so that I can apply it to a different database, it is empty. What am I doing wrong?

-- Host: hostname.com -- Server version: 5.6.38 - MySQL Community Server (GPL) -- Server OS: Linux -- HeidiSQL Version: 9.5.0.5196

/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /; /!40101 SET NAMES utf8 /; /!50503 SET NAMES utf8mb4 /; /!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /; /!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;

-- Dumping structure for function pchome_packardsimperials.fnStripTags DROP FUNCTION IF EXISTS fnStripTags; DELIMITER // // DELIMITER ;

/!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') /; /!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) /; /!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;

insufficient privileges?

No, the account has full privileges and the same account can do it in phpmyadmin so that’s not it.

Can you post the CREATE code for that function?

The CREATE code looks like this and is what I expected to see when exporting it using HeidiSQL:

CREATE DEFINER=`root`@`localhost` FUNCTION `fnStripTags`( Dirty varchar(10000) ) RETURNS varchar(10000) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE iStart, iEnd, iLength int;
    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END

I just created that function on my local MariaDB 10.2, and exported it to file. Works as expected - the function code is there.

Attached a screeny with the export settings I used.

I see that you are exporting to a file while I was exporting to the clipboard and it is to the clipboard that it fails giving only the result that I posted originally. It also fails when sending directly to a different database so clearly there is a HeidiSQL bug of some sort that is causing the failure which really takes much extra time to have to export functions individually when recreating a complete database onto a new location. I don't do this often but when I do it would be great if it didn't throw errors and fail to export the functions and procedures along with the rest!

I just also did the same export to clipboard, and once again to another database. In all cases, the function was exported as expected. I can't help it - I'm unable to reproduce that.

You are not perhaps having ANSI SQL mode enabled?

Can’t say for sure but I am using the same mode as used for exporting tables so there should be no need to change.

• Do you have some startup script in your session settings, on the "Advanced" tab? (if yes, what's the contents?)
• Is that empty export reproducible for you?
• Did you uncheck "Tables(s): Create" on the export dialog?

No special settings and yes, it does it consistently every time. I tried both with table create checked and unchecked but generally I need it checked since I rarely need to export functions by themselves but rather as a dump if the entire database including tables.

Ok, one more question: when you export the function, there should be a SHOW CREATE FUNCTION ... query visible in the SQL log panel at the bottom. Is there some error message before or after that query? Please scroll up a bit to check.

The panel shows no errors soit apparently "thinks" it has succeeded and before you ask, it's not just this function that I selected as an example with the problem but rather all of them:

SELECT 'mydb' AS Database, 'fnStripTags' AS Table, -1 AS Rows, 0 AS Duration; /!40101 SET @OLD_LOCAL_SQL_MODE=@@SQL_MODE, SQL_MODE='' /; SHOW CREATE FUNCTION mydb.fnStripTags; /!40101 SET SQL_MODE=IFNULL(@OLD_LOCAL_SQL_MODE, '') /;

What does that SHOW CREATE FUNCTION mydb.fnStripTags return when you fire that in a query tab?

It shows:

Function    sql_mode    Create Function character_set_client    collation_connection    Database Collation

fnStripTags         utf8    utf8_general_ci latin1_swedish_ci

Have you tried to use mysqldump (with --routines option added)? Do you have the functions dumped in this case?

No, the account has full privileges and the same account can do it in phpmyadmin so that’s not it.

Are you sure that the account is the same? From phpmyadmin it might be user@locahlost, while from heidi -- user@%

It shows: Function sql_mode Create Function character_set_client collation_connection Database Collation fnStripTags utf8 utf8_general_ci latin1_swedish_ci

So there is no content for the functions returned? https://dev.mysql.com/doc/refman/5.7/en/show-create-procedure.html

If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL.

So I'd suggest you to double check your user's permissions.

Just to clarify, I am an IT professional so I do know which account is being used and it does have full permissions and I set both of them up myself to be the same. Also, I tried it on several different systems, two Windows 10 64bit and another Ubuntu Linux under Wine and all behave identically.

Well, the question is why is it empty on your side?

Pasted from the docs:

To use either statement, you must be the user named in the routine DEFINER clause or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL.

When I fire that SHOW CREATE FUNCTION fnStripTags, I have the whole function code in the "Create Function" column. Well, I am root@localhost and root@localhost is the definer of the function.

Sorry, I did not meant to offend you while I suggested to double check permissions...

Heidi simply issues SQL queries to get data. For getting function's code it issues 'SHOW CREATE FUNCTION <name>' query. You shown that in your case the 'Create Function' returned by that query is empty so the Heidi cannot get the function code and dump it.

I do not have such issue when I'm working with 2 different servers (one -- local MySQL on Win10 x64, another -- MariaDB on linux). In my case the functions are dumped and sql query 'SHOW CREATE FUNCTION <function name>' returns not null 'Create Function' value when I run it from Heidi or from local mysql.exe

No offense taken at all but I've been too busy to baby sit this issue right now. If I get back to it I'll let you know but all I can say at this time is that for me with identical permissions it does not work. I even tried root but these functions and procedures were not created under root so I didn't expect it to make any difference. Odd that I can dump tables without any issue with the same permissions too.