Bug #73260 [Opn->Fbk]: Multi SQL queries with CREATE TRIGGER inbetween adds flawed ; to trigger code

Edit report at https://bugs.php.net/bug.php?id=73260&edit=1 ID: 73260 Updated by: cmb@php.net Reported by: php_net at milania dot de Summary: Multi SQL queries with CREATE TRIGGER inbetween adds flawed ; to trigger code -Status: Open +Status: Feedback Type: Bug Package: MySQL related Operating System: Win10x64 PHP Version: 7.0.11 -Assigned To: +Assigned To: cmb Block user comment: N Private report: N New Comment: 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> Previous Comments: ------------------------------------------------------------------------ [2016-10-06 19:27:13] 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. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=73260&edit=1