MySQL Bugs: #70278: Trigger creation using file with the query results in "Query was empty" error

Bug #70278	Trigger creation using file with the query results in "Query was empty" error
Submitted:	9 Sep 2013 13:08	Modified:	11 Oct 2013 8:27
Reporter:	Denis Simonet	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.32	OS:	Linux (Ubuntu 12.04 LTS)
Assigned to:		CPU Architecture:	Any

Description:
When creating a trigger using a file which contains the SQL query the error message "Query was empty" is shown. Despite the error the trigger was created, though. In the CLI there is no such error shown.

How to repeat:
╭─dev@symonitoringng-dev  ~/work ‹master› 
╰─% cat app/updates/13_AddCustomerTriggerAndRenameConstraintName.sql                                                                                                                                   9:18:04
# ---------------------------------------------------------------------- #
# Script generated with: DeZign for Databases V7.3.6                     #
# Target DBMS:           MySQL 5                                         #
# Project file:          SyMonitoring.dez                                #
# Project name:                                                          #
# Author:                                                                #
# Script type:           Alter database script                           #
# Created on:            2013-09-09 14:27                                #
# ---------------------------------------------------------------------- #

DELIMITER |
CREATE TRIGGER
  parentIdCheck
BEFORE UPDATE ON `Customer`
FOR EACH ROW BEGIN
  DECLARE nullCount INT;
  IF (new.`parentId` IS NULL) THEN
    SET nullCount = (SELECT COUNT(*) FROM `Customer` WHERE `parentId` IS NULL GROUP BY `parentId`);
    IF (nullCount > 0) THEN
      SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT = 'Only one parent id may be NULL';
    END IF;
  END IF;
END;
|;
╭─dev@symonitoringng-dev  ~/work ‹master› 
╰─% mysql -u symonitoring -p symonitoring < app/updates/13_AddCustomerTriggerAndRenameConstraintName.sql                                                                                         127 ↵ 9:07:08
Enter password: 
ERROR 1065 (42000) at line 12: Query was empty

Thank you for the bug report. Please provide the complete test case table on another hand you are using in the script the delimiter | then why you ended with |; (see ;), Thanks.

Oh, the ; is there out of habit - didn't notice it :). Thx. Removing it doesn't change the behaviour.

Sure, the table is:
CREATE TABLE IF NOT EXISTS `Customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `warnHoster` tinyint(1) NOT NULL,
  `wiki` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comment` text COLLATE utf8_unicode_ci,
  `passwordInformation` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `passwordMaintenance` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastAccess` date DEFAULT NULL,
  `ldapGroup` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `isEnabled` tinyint(1) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `customer_name` (`name`),
  KEY `Customer_Customer` (`parentId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=85 ;

ALTER TABLE `Customer`
  ADD CONSTRAINT `Customer_Customer` FOREIGN KEY (`parentId`) REFERENCES `Customer` (`id`);

Hello Denis,

Thank you for the report.
As Miguel pointed out, this is due to an extra ";" in the trigger definition.
Please try the updated trigger and let us know if you still have the issue. 

//  5.5.32/33

With the extra "|;" I could get:

[ushastry@cluster-repo mysql-5.5.32]$ more /tmp/trg.sql
DELIMITER |
CREATE TRIGGER
  parentIdCheck
BEFORE UPDATE ON `Customer`
FOR EACH ROW BEGIN
  DECLARE nullCount INT;
  IF (new.`parentId` IS NULL) THEN
    SET nullCount = (SELECT COUNT(*) FROM `Customer` WHERE `parentId` IS NULL GROUP BY `parentId`);
    IF (nullCount > 0) THEN
      SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT = 'Only one parent id may be NULL';
    END IF;
  END IF;
END;
|;

[root@cluster-repo mysql-5.5.32]# bin/mysql -u root -p test < /tmp/trg.sql
Enter password:
ERROR 1065 (42000) at line 2: Query was empty

// Without extra ";" it works without any errors
  
[ushastry@cluster-repo mysql-5.5.32]$ more /tmp/trg.sql
DELIMITER |
CREATE TRIGGER
  parentIdCheck
BEFORE UPDATE ON `Customer`
FOR EACH ROW BEGIN
  DECLARE nullCount INT;
  IF (new.`parentId` IS NULL) THEN
    SET nullCount = (SELECT COUNT(*) FROM `Customer` WHERE `parentId` IS NULL GROUP BY `parentId`);
    IF (nullCount > 0) THEN
      SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT = 'Only one parent id may be NULL';
    END IF;
  END IF;
END;|
delimiter ;

[root@cluster-repo mysql-5.5.32]# bin/mysql -u root -p test < /tmp/trg.sql
Enter password:

or from mysql> prompt

mysql> source /tmp/trg.sql
Query OK, 0 rows affected (0.04 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".