Bug #34316 Restore failed on successfully backed up functions
Submitted: 5 Feb 2008 17:44 Modified: 25 Feb 2009 14:17
Reporter: mike avergun Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Administrator Severity:S1 (Critical)
Version:1.2.12 OS:Any (MS Windows, Mac OS X)
Assigned to: CPU Architecture:Any

[5 Feb 2008 17:44] mike avergun
Description:
Create backup of any database with function below (see repeat steps). On restore you would get this:

Error while executing this query: <<function body>>

The server has returned this error message:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
MySQL Error.

This very bad if you backup is in gigabytes - you waiting a long time just to see fail message. If backup succeeded then restore should too with out any alteration.

.

How to repeat:
1. Create function 
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_get_enum_string_value`(
tableName VARCHAR(64),
columnName VARCHAR(64),
numericValue INTEGER
) RETURNS varchar(64) CHARSET latin1
BEGIN
DECLARE enumStr VARCHAR(255);
DECLARE _start INTEGER;
DECLARE _end INTEGER;
DECLARE _pos INTEGER;
DECLARE _rotation INTEGER;
DECLARE retVal VARCHAR(64);

SET retVal = NULL;

set enumStr = (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA = 'soemon3' AND TABLE_NAME = tableName AND COLUMN_NAME = columnName));

IF enumStr > "" AND numericValue > 0 THEN

  set _start = LOCATE("(", enumStr) + 1;
  set _end = LOCATE(")", enumStr);
  set _pos = LOCATE(",", enumStr);
  IF _pos = 0 THEN
     set _pos = _end;
  END IF;

  SET _rotation = numericValue - 1;

  IF numericValue > 1 THEN
    findSubStr:
      LOOP
        set _start = _pos + 1;
        set _pos = LOCATE(",", enumStr, _start + 1);

        SET _rotation = _rotation - 1;
        IF _rotation > 0  THEN
          IF _pos > 0 THEN
            ITERATE findSubStr;
          ELSE
            LEAVE findSubStr;
          END IF;
        ELSE
          IF _pos = 0 THEN
            set _pos = _end;
          END IF;
        END IF;
      LEAVE findSubStr;
      END LOOP findSubStr;
  END IF;

  IF _start > 0 AND _end > 0 AND _pos > 0 THEN
    SET retVal =  (select TRIM(SUBSTRING(enumStr, _start, _pos - _start)));
  END IF;

ELSE
   
  set retVal = NULL;
END IF;

return retVal;
END $$

2. Execute backup
3. Execute restore
[5 Feb 2008 20:45] MySQL Verification Team
Thank you for the bug report. I wasn't able to repeat with the test case
you have provided. Which server version are you using and please provide
your my.ini file. Thanks in advance.
[5 Feb 2008 21:45] mike avergun
Server version is 5.0.45 CE. 
Most likly you were not able to duplicate because your server start up option have --log_bin_trust_function_creators=1 option set, my serve does not have this option, nor it is clear whether I can set it up in MySQL Administrator\Statup Variables page
[6 Feb 2008 10:58] Sveta Smirnova
Thank you for the report.

Verified as described.

Same dump can be restored with command line mysql client.
[6 Feb 2008 11:02] Sveta Smirnova
Bug #22235 was marked as duplicate of this one.
[10 Mar 2008 20:43] Valeriy Kravchuk
Bug #35146 was marked as a duplicate of this one.
[23 Feb 2009 13:02] Mike Lischke
I'm sorry. I don't understand. What exactly is the bug here?

What I see is that a function declaration does not contain a characteristics part. It is so backed up and when restored the server gives the documented error message that it would be necessary to have a characteristic given (or disable the error with the log_bin_trust_function_creators flag). What is it that MA does wrong here?
[24 Feb 2009 8:59] Sveta Smirnova
I believe problem is same backup file can be restored with mysql command line client and can not with MA which created it.

mysql command line client does not report any error.

Workaround in MA: check "Ignore SQL Errors"
[24 Feb 2009 9:02] Sveta Smirnova
Also mysqld I tested with started with binary logging turned off, so option log_bin_trust_function_creators should not affect it in any case.
[24 Feb 2009 9:47] Sveta Smirnova
Susanne, what is not clear for you?
[24 Feb 2009 9:51] Sveta Smirnova
How to repeat.

1. Start mysql command line client.
2. Create database bug34316
3. Create function described in the initial description.
4. Create dummy table (you could not backup database using MA without it)
5. Open MA
6. Choose backup, create new project
7. Add database bug34316 to backup project and all its objects
8. Create backup
9. Choose Restore
10. Get error
11. Open mysql command line client
12. Load same dump created by MA
13. No error

mysql> show variables like '%bin%';
+---------------------------------+------------+
| Variable_name                   | Value      |
+---------------------------------+------------+
...
| log_bin                         | OFF        | 
| log_bin_trust_function_creators | OFF        | 
| log_bin_trust_routine_creators  | OFF        | 
...
+---------------------------------+------------+
10 rows in set (0.00 sec)
[25 Feb 2009 10:27] Sveta Smirnova
Susanne,

thank you for pointing me to the problem in my dump.

mike, in my "how-to-repeat" problem was MySQL Administrator didn't add "DROP FUNCTION" if I didn't check "Add frop table" checkbox. Please send us dump which MySQL Administrator created in your case, so we can compare if you experience same problem or not.
[25 Feb 2009 14:17] Susanne Ebrecht
The problem was related to missing drop statement which was reported in another bug report. Unfortunately, I can't find the bug report again. But this problem was fixed in actual version.

Also I tested this again with actual version and all worked fine. So this is really fixed.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/