Exporting Functions

PCHome's profile image PCHome posted 6 years ago in General Permalink

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

[expired user #1502]'s profile image [expired user #1502] posted 6 years ago Permalink

insufficient privileges?

PCHome's profile image PCHome posted 6 years ago Permalink

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

ansgar's profile image ansgar posted 6 years ago Permalink

Can you post the CREATE code for that function?

PCHome's profile image PCHome posted 6 years ago Permalink

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
ansgar's profile image ansgar posted 6 years ago Permalink

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.

1 attachment(s):
  • fnStripTags-export
PCHome's profile image PCHome posted 6 years ago Permalink

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!

ansgar's profile image ansgar posted 6 years ago Permalink

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?

PCHome's profile image PCHome posted 6 years ago Permalink

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.

ansgar's profile image ansgar posted 6 years ago Permalink
  • 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?
PCHome's profile image PCHome posted 6 years ago Permalink

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.

ansgar's profile image ansgar posted 6 years ago Permalink

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.

PCHome's profile image PCHome posted 6 years ago Permalink

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, '') /;

ansgar's profile image ansgar posted 6 years ago Permalink

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

PCHome's profile image PCHome posted 6 years ago Permalink

It shows:

Function    sql_mode    Create Function character_set_client    collation_connection    Database Collation

fnStripTags         utf8    utf8_general_ci latin1_swedish_ci
[expired user #1502]'s profile image [expired user #1502] posted 6 years ago Permalink

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

[expired user #1502]'s profile image [expired user #1502] posted 6 years ago Permalink

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@%

[expired user #1502]'s profile image [expired user #1502] posted 6 years ago Permalink

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.

PCHome's profile image PCHome posted 6 years ago Permalink

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.

ansgar's profile image ansgar posted 6 years ago Permalink

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.

[expired user #1502]'s profile image [expired user #1502] posted 6 years ago Permalink

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 &lt;name&gt;' 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 &lt;function name&gt;' returns not null 'Create Function' value when I run it from Heidi or from local mysql.exe

PCHome's profile image PCHome posted 6 years ago Permalink

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.

Please login to leave a reply, or register at first.