Database export is missing body of Procedures and Triggers

[M393]

Describe the bug

When exporting a database structure Procedures and Triggers are not correctly exported.

To Reproduce

Steps to reproduce the behavior:

1. Go to Export
2. • select structure
3. • select Add CREATE PROCEDURE / FUNCTION / EVENT statement
4. Check exported sql document

The exported procedures are incomplete:

--
-- Database: `areadata`
--
CREATE DATABASE IF NOT EXISTS `areadata` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `areadata`;

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`xx`@`%` PROCEDURE `FindAreaIDs` (IN `x` INT(10), IN `y` INT(10))  DETERMINISTIC READS SQL DATA proc_label:BEGIN
    DECLARE point_1 POINT$$

CREATE DEFINER=`xx`@`%` PROCEDURE `SetAreaIDs` ()  MODIFIES SQL DATA BEGIN

DECLARE xCoord, yCoord INT DEFAULT NULL$$

DELIMITER ;

-- ...

--
-- Triggers `Info`
--
DELIMITER $$
CREATE TRIGGER `Info_AFTER_UPDATE` AFTER UPDATE ON `Info` FOR EACH ROW BEGIN
DECLARE d DATEDELIMITER ;

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

Server configuration

• Operating system: Ubuntu 22.04
• Web server: Apache 2.4.52-1ubuntu4.4
• Database version: 5.7.38
• PHP version: 8.1.2
• phpMyAdmin version: 5.2.2-dev+20230611.e6bbb848a3

It worked with version 5.2.1.

[williamdes]

Hi @M393
Thank you so much for this detailed report !
Can you post the correct output you have on 5.2.1 so we can compare and fix ?

[M393]

The problem seems stem from one of the composer dependencies. If I installl the dependencies from 5.2.1 it works, upgrade to the latest versions and it breaks:

Here's a complete example export:

-- phpMyAdmin SQL Dump
-- version 5.2.2-dev
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Jun 13, 2023 at 07:57 AM
-- Server version: 5.7.38-log
-- PHP Version: 8.1.13

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 */;

--
-- Database: `x`
--

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost` FUNCTION `random_name` (`len` TINYINT UNSIGNED) RETURNS VARCHAR(256) CHARSET latin1  BEGIN
	DECLARE chars VARCHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE name VARCHAR(255) DEFAULT '';
	WHILE LENGTH(name) < len DO
		SET name = CONCAT(name, SUBSTRING(chars, FLOOR(rand() * LENGTH(chars)) + 1, 1));
    END WHILE;
    
    RETURN name;
END$$

DELIMITER ;

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

--
-- Table structure for table `xx`
--

CREATE TABLE `xx` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Triggers `xx`
--
DELIMITER $$
CREATE TRIGGER `xx_BEFORE_INSERT` BEFORE INSERT ON `xx` FOR EACH ROW BEGIN
	IF NEW.name = '' THEN
		SET NEW.name = random_name(10);
	END IF;
END
$$
DELIMITER ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `xx`
--
ALTER TABLE `xx`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `xx`
--
ALTER TABLE `xx`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

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

And here is the broken export with current dependencies

-- phpMyAdmin SQL Dump
-- version 5.2.2-dev
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Jun 13, 2023 at 08:06 AM
-- Server version: 5.7.38-log
-- PHP Version: 8.1.13

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 */;

--
-- Database: `x`
--

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost` FUNCTION `random_name` (`len` TINYINT UNSIGNED) RETURNS VARCHAR(256) CHARSET latin1  BEGIN
	DECLARE chars VARCHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'$$

DELIMITER ;

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

--
-- Table structure for table `xx`
--

CREATE TABLE `xx` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Triggers `xx`
--
DELIMITER $$
CREATE TRIGGER `xx_BEFORE_UPDATE` BEFORE INSERT ON `xx` FOR EACH ROW BEGIN
	IF NEW.name = '' THEN
		SET NEW.name = random_name(10)DELIMITER ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `xx`
--
ALTER TABLE `xx`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `xx`
--
ALTER TABLE `xx`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

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

[M393]

The first bad commit is phpmyadmin/sql-parser@0ebfa9c

[williamdes]

Thank you for the bisect, that will definitely help

[williamdes]

I pushed 38a4328

This commit should fix this issue

Sorry for the very long delay in fixing this bug

[williamdes]

fc98aff will also be needed

And a54e97f depending on your PHP version, but with 8.1 it should not

[M393]

Thank you for working on this.

I applied these fixes to my installed snapshot. Procedures are exported fine, but it still seems to be missing the closing delimiter ($$) at the end of exported triggers.

[williamdes]

I updated the phpMyAdmin snapshot, when I finish my day I will try to fix the triggers too

Could you update your previous message: "And here is the broken export with current dependencies" ?

Thank you so much for replying quickly !

[M393]

Here is the export, note the missing $$ at the end of the trigger.

Export with b2a9856

-- phpMyAdmin SQL Dump
-- version 5.2.2-dev
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Nov 16, 2023 at 04:43 PM
-- Server version: 5.7.42-log
-- PHP Version: 8.1.25

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 */;

--
-- Database: `x`
--

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost` FUNCTION `random_name` (`len` TINYINT UNSIGNED) RETURNS VARCHAR(256) CHARSET latin1  BEGIN
	DECLARE chars VARCHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE name VARCHAR(255) DEFAULT '';
	WHILE LENGTH(name) < len DO
		SET name = CONCAT(name, SUBSTRING(chars, FLOOR(rand() * LENGTH(chars)) + 1, 1));
    END WHILE;
    
    RETURN name;
END$$

DELIMITER ;

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

--
-- Table structure for table `xx`
--

CREATE TABLE `xx` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Triggers `xx`
--
DELIMITER $$
CREATE TRIGGER `xx_BEFORE_INSERT` BEFORE INSERT ON `xx` FOR EACH ROW BEGIN
	IF NEW.name = '' THEN
		SET NEW.name = random_name(10);
	END IF;
END
DELIMITER ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `xx`
--
ALTER TABLE `xx`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `xx`
--
ALTER TABLE `xx`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

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

[M393]

@MauricioFauth This is not yet completely resolved. Please reopen.

[williamdes]

Done
This is automatic

[williamdes]

Fixed by #19090
Thank you @MoonE

Guess who did a drop database in prod and the export of the day did not import because of this bug..
Sorry for taking so much time to fix this