Bug #73260 [Fbk->NoF]: 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: php-bugs@lists.php.net Reported by: php_net at milania dot de Summary: Multi SQL queries with CREATE TRIGGER inbetween adds flawed ; to trigger code -Status: Feedback +Status: No Feedback Type: Bug Package: MySQL related Operating System: Win10x64 PHP Version: 7.0.11 Assigned To: cmb Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2021-02-03 15:50:26] 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> ------------------------------------------------------------------------ [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