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: -
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:
Status: Open Package: MySQL related
PHP Version: 7.0.11 OS: Win10x64
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: php_net at milania dot de
New email:
PHP Version: OS:

 

 [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

Add a Patch

Pull Requests

Add a Pull Request

 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Wed Oct 23 00:01:31 2019 UTC