php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #73260 Multi SQL queries with CREATE TRIGGER inbetween adds flawed ; to trigger code
Submitted: 2016-10-06 19:27 UTC Modified: 2021-02-14 04:22 UTC
Votes:2
Avg. Score:4.0 ± 1.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: php_net at milania dot de Assigned: cmb (profile)
Status: No Feedback Package: MySQL related
PHP Version: 7.0.11 OS: Win10x64
Private report: No CVE-ID: None
 [2016-10-06 19:27 UTC] php_net at milania dot de
Description:
------------
Calling sql query containing multiple statements with a CREATE TRIGGER statement inbetween results in an additional (flawed) ; to be added to the trigger code.

The semicolon will only be added, if an additional statement follows after the CREATE TRIGGER statement (like 'SELECT * FROM Student;' in the example below). If the CREATE TRIGGER statement is the last, no semicolon is added (what is ok).

The error happens with both, PDO (mysqlnd 5.0.12-dev - 20150407) and mysqli (mysqlnd 5.0.12-dev - 20150407) drivers.

The error does not happen when the queries are executed from phpMyAdmin or from the mysql console (mysql -u root -h localhost test < triggerTest.sql).

Tested with 10.1.16-MariaDB and 5.5.46-0 MySQL.

If you wonder what the problem with the ; is about: it breaks mysqldump resulting in syntax errors.

Test script:
---------------
$sql = "DROP TRIGGER IF EXISTS TriggerStudent;
DROP TABLE IF EXISTS Student;

CREATE TABLE `test`.`Student`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `idInc` INT NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TRIGGER `TriggerStudent` BEFORE INSERT ON `Student`
FOR EACH ROW
SET NEW.`idInc` = NEW.id + 1;

SELECT *
FROM Student;";
$db = new PDO("mysql:host=localhost;dbname=test;charset=UTF8", "root", "");
$erg = $db->exec($sql);

//$mysqli = new mysqli("localhost", "root", "", "test");    // Same problem
//$mysqli->multi_query($sql);

Expected result:
----------------
SET NEW.`idInc` = NEW.id + 1

in the Statement column of SHOW TRIGGERS.

Actual result:
--------------
SET NEW.`idInc` = NEW.id + 1;

in the Statement column of SHOW TRIGGERS.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-02-03 15:50 UTC] cmb@php.net
-Status: Open +Status: Feedback -Assigned To: +Assigned To: cmb
 [2021-02-03 15:50 UTC] cmb@php.net
Is this still an issue with any of the actively supported PHP
versions[1]?  If so, would encapsulating the body of the trigger
in BEGIN … END help?

[1] <https://www.php.net/supported-versions.php>
 [2021-02-14 04:22 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 14 11:01:27 2024 UTC