Import doesn't support 2 procedures/functions in a row #103
Description
Hi,
I am using MySqlBackup.ImportFromFile to mount a DB backup.
Everything works perfectly as long as there is not 2 function defined in a row.
Here is an exemple:
-- phpMyAdmin SQL Dump
-- version 4.9.7
-- https://www.phpmyadmin.net/
--
-- Hôte : localhost
-- Généré le : lun. 15 mai 2023 à 11:26
-- Version du serveur : 10.3.32-MariaDB
-- Version de PHP : 7.4.30
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!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 utf8mb4 */;
--
-- Base de données : `DBGestionCCEch`
--
DELIMITER $$
--
-- Procédures
--
CREATE DEFINER=`root`@`%` PROCEDURE `DbPruning` () BEGIN
DECLARE hasError tinyint(1) DEFAULT 0;
DECLARE errState INT(11);
DECLARE errNo INT(11);
DECLARE errText VARCHAR(500);
DECLARE limitDate DATETIME;
-- Gestion d'erreur
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 errState = RETURNED_SQLSTATE, errNo = MYSQL_ERRNO, errText = MESSAGE_TEXT;
INSERT INTO TLog (`Source`, Horodatage, Info)
VALUES ('StoredProc DbPruning', NOW(), CONCAT('Erreur lors du nettoyage : ', errNo, ' (', errState, '): ', errText));
SET hasError = 1;
END;
-- Get the limit date of deletion
SET limitDate = (SELECT NOW() - INTERVAL CONVERT(Valeur, INTEGER) DAY FROM TParametres WHERE Nom = 'DataConservation' LIMIT 1);
-- Clean TLog
DELETE FROM TLog
WHERE Horodatage < NOW() - INTERVAL 90 DAY;
-- Log du succès du nettoyage
IF hasError = 0 THEN
INSERT INTO TLog (`Source`, Horodatage, Info)
VALUES ('StoredProc DbPruning', NOW(), 'Nettoyage terminé avec succès.');
END IF;
END$$
DELIMITER ;
DELIMITER $$
--
-- Fonctions
--
CREATE DEFINER=`root`@`%` FUNCTION `InsertRecipient` (`Name` VARCHAR(255), `FirstName` VARCHAR(255), `Address1` VARCHAR(255), `Address2` VARCHAR(255), `ZipCode` VARCHAR(6), `Location` VARCHAR(50)) RETURNS BIGINT(20) MODIFIES SQL DATA
BEGIN
DECLARE insertedId bigint(20);
SET insertedId = (SELECT MIN(Numero) FROM TClients) - 1;
INSERT INTO TClients (
Numero,
TypeContact,
Nom,
Prenom,
Adresse_1,
Adresse_2,
CP,
Lieu)
VALUES (
insertedId,
2,
Name,
FirstName,
Address1,
Address2,
zipCode,
Location);
RETURN insertedId;
END$$
DELIMITER ;
If first function is alone, it works. If second function is alone, it also works. But not when both function are together.
Note that if the following line are inserted in between them, it also works:
DELIMITER ;
DELIMITER $$
Unfortunately, I can't really modify the files as it is generated automatically.
Do you have any idea how to fix this?
Best regards
UPDATE:
After some test, I have found that adding a ; after each END$$ works as well. As a workaround, that's what I do.