Bug #35146 Error on restoring a Procedure or Function
Submitted: 7 Mar 2008 13:12 Modified: 23 Feb 2009 13:04
Reporter: Hool P Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.51a OS:Windows (Vista Bussiness)
Assigned to: Mike Lischke CPU Architecture:Any
Tags: restore

[7 Mar 2008 13:12] Hool P
Description:
I have look up the bugs database and it could be a similar problem to 34316, Althougth it sais it is not a bug, it still happens.

I am trying to restore a complete database with tables and Procedures/functions.
It restores the tables correctly, but does not the Procedures:

Warning: Do not know how to handle this statement at line 2050:
CREATE DEFINER=`root`@`localhost` PROCEDURE `clear_history`()
BEGIN
  DELETE FROM afluenciadia WHERE 1;
  DELETE FROM afluenciaperiodo WHERE 1;
  DELETE FROM estadisticasplazadia WHERE 1;
  DELETE FROM estadosplaza WHERE 1;
  DELETE FROM datalogger WHERE 1;
  DELETE FROM incidencia WHERE 1;
  DELETE FROM ocupaciondia WHERE 1;
  DELETE FROM ocupacionperiodo WHERE 1;
END $$
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

Warning: Do not know how to handle this statement at line 2073:
CREATE DEFINER=`root`@`localhost` PROCEDURE `guardarCalcularAfluenciaDia`(tipo INT, idPk INT, idPl INT, idZn INT, fecha Date)
BEGIN

  DECLARE hoy DATETIME DEFAULT '2007-09-20';
  SET hoy = fecha;
  SELECT MAX(ocupacion) max_aflu, MIN(ocupacion) min_aflu from afluenciaperiodo where idVia=idPk and fecha=hoy;

  SELECT SUM(entradas) sum_entr, SUM(salidas) sum_salid FROM afluenciaperiodo WHERE fecha=hoy GROUP BY idVia, fecha;

  SELECT hora max_hora FROM afluenciaperiodo WHERE ocupacion= max_aflu And fecha= hoy;

  SELECT hora min_hora FROM afluenciaperiodo WHERE ocupacion= min_aflu And fecha= hoy;

  INSERT INTO afluenciaperiodo(maximaAfluencia, horaMaximaAfluencia, minimaAfluencia, horaMinAfluencia, entradasSUMA, salidasSUMA) VALUES

    (max_aflu, max_hora, min_aflu, min_hora, sum_entr, sum_salid);

END $$
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

Warning: Do not know how to handle this statement at line 2096:
CREATE DEFINER=`root`@`localhost` PROCEDURE `guardarCalcularOcupacionDia`(idPk INT, idPl INT, idZn INT, fecha DATE)
BEGIN

  DECLARE hoy DATETIME DEFAULT '2007-09-20';
  SET hoy = fecha;
  SELECT SUM(ocupacion) sum_ocup, MAX(ocupacion) max_ocup, MIN(ocupacion) min_ocup from ocupacionperiodo where idParking=idPk and idPlanta=idPl and idZona=idZn and fecha=hoy;

  SELECT hora max_hora FROM ocupacionperiodo WHERE ocupacion= max_ocup And fecha= hoy;

  SELECT hora min_hora FROM ocupacionperiodo WHERE ocupacion= min_ocup And fecha= hoy;

  INSERT INTO ocupaciondia(maximaOcupacion, horaMaximaOcupacion, minimaOcupacion, horaMinOcupacion, ocupaciondiaSUMA) VALUES

    (max_Ocup, max_hora, min_ocup, min_hora, sum_ocup);

END $$
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

----------------------------------------------------------------------------

After restore operation is finished, the procedures are missing in the database.

Regards,
JPA

How to repeat:
Use backup file:

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.22-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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' */;

--
-- Create schema XXXX
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ XXXX;
USE XXXX;

--
-- Procedure `XXXX`.`clear_history`
--

DROP PROCEDURE IF EXISTS `clear_history`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `clear_history`()
BEGIN

  DELETE FROM afluenciadia WHERE 1;

  DELETE FROM afluenciaperiodo WHERE 1;

  DELETE FROM estadisticasplazadia WHERE 1;

  DELETE FROM estadosplaza WHERE 1;

  DELETE FROM datalogger WHERE 1;

  DELETE FROM incidencia WHERE 1;

  DELETE FROM ocupaciondia WHERE 1;

  DELETE FROM ocupacionperiodo WHERE 1;

END $$

DELIMITER ;

--
-- Procedure `XXXX`.`guardarCalcularAfluenciaDia`
--

DROP PROCEDURE IF EXISTS `guardarCalcularAfluenciaDia`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `guardarCalcularAfluenciaDia`(tipo INT, idPk INT, idPl INT, idZn INT, fecha Date)
BEGIN

  DECLARE hoy DATETIME DEFAULT '2007-09-20';

  SET hoy = fecha;

  SELECT MAX(ocupacion) max_aflu, MIN(ocupacion) min_aflu from afluenciaperiodo where idVia=idPk and fecha=hoy;

  SELECT SUM(entradas) sum_entr, SUM(salidas) sum_salid FROM afluenciaperiodo WHERE fecha=hoy GROUP BY idVia, fecha;

  SELECT hora max_hora FROM afluenciaperiodo WHERE ocupacion= max_aflu And fecha= hoy;

  SELECT hora min_hora FROM afluenciaperiodo WHERE ocupacion= min_aflu And fecha= hoy;

  INSERT INTO afluenciaperiodo(maximaAfluencia, horaMaximaAfluencia, minimaAfluencia, horaMinAfluencia, entradasSUMA, salidasSUMA) VALUES

    (max_aflu, max_hora, min_aflu, min_hora, sum_entr, sum_salid);

END $$

DELIMITER ;

--
-- Procedure `XXXX`.`guardarCalcularOcupacionDia`
--

DROP PROCEDURE IF EXISTS `guardarCalcularOcupacionDia`;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `guardarCalcularOcupacionDia`(idPk INT, idPl INT, idZn INT, fecha DATE)
BEGIN

  DECLARE hoy DATETIME DEFAULT '2007-09-20';

  SET hoy = fecha;

  SELECT SUM(ocupacion) sum_ocup, MAX(ocupacion) max_ocup, MIN(ocupacion) min_ocup from ocupacionperiodo where idParking=idPk and idPlanta=idPl and idZona=idZn and fecha=hoy;

  SELECT hora max_hora FROM ocupacionperiodo WHERE ocupacion= max_ocup And fecha= hoy;

  SELECT hora min_hora FROM ocupacionperiodo WHERE ocupacion= min_ocup And fecha= hoy;

  INSERT INTO ocupaciondia(maximaOcupacion, horaMaximaOcupacion, minimaOcupacion, horaMinOcupacion, ocupaciondiaSUMA) VALUES

    (max_Ocup, max_hora, min_ocup, min_hora, sum_ocup);

END $$

DELIMITER ;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Suggested fix:
It should create three new procedures in database:

clear_history()
guardarCalcularAfluenciaDia()
guardarCalcularOcupacionDia()

with no errors.
[8 Mar 2008 11:18] Valeriy Kravchuk
Bug #34316 is a verified bug in MySQL Administrator now. What tool do you use to restore?
[10 Mar 2008 9:20] Hool P
I am using MySQL Administrator v1.1.9. I should upgrade the version, shouldn´t I? or is still pending for fixing?

I can restore my procedures manually at the moment.

Yours sincerely,
Julio PA
[10 Mar 2008 20:42] Valeriy Kravchuk
Duplicate of Bug #34316.
[10 Mar 2008 20:45] Valeriy Kravchuk
Bug #34316 is still "Verified", that is not fixed. So, there is no version to upgrade to - even 1.2.12 is still affected.
[23 Feb 2009 13:04] Mike Lischke
This bug has nothing to do with Bug #34316. It is a parser bug in the admin library in the MA, which has been fixed quite some time ago.