Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 2.5.0b5

Bug #13928 Invalid triggers created for 'ON UPDATE' in mysqli and mysql drivers
Submitted: 2008-05-18 13:55 UTC
From: ethnar Assigned: quipo
Status: Closed Package: MDB2 (version 2.5.0b1)
PHP Version: 5.2.1 OS: Windows XP SP2
Roadmaps: (Not assigned)    
Subscription  


 [2008-05-18 13:55 UTC] ethnar (Arkadiusz Bisaga)
Description: ------------ When creating foreign key constraint using either mysql or mysqli drivers, two triggers are created. However, the one responsible for 'ON UPDATE' creates an issue: it's impossible to execute UPDATE query even if you are not changing foreign key field (assuming another record in another table references to this one). Test script: --------------- $mdb2->createTable('foo',array('id'=>array('type'=>'integer'),'data'=>array( 'type'=>'text'))); $mdb2->createConstraint('foo', 'foo_primary',array('primary'=>true,'fields'=>array('id'=>array()))); $mdb2->createTable('bar',array('id'=>array('type'=>'integer'))); $mdb2->createConstraint('bar', 'bar_foreign', array('foreign'=>true,'fields'=>array('id'=>array()),'references'=>array('table'=>'foo', 'fields'=>array('id'=>array())))); $mdb2->execParam('INSERT INTO foo VALUES (?,?)', array(1,'Testing..')); $mdb2->execParam('INSERT INTO bar VALUES (?)', array(1)); $result = $mdb2->execParam('UPDATE foo SET data=? WHERE id=?', array('Passed!',1)); if ($result===1) print('OK'); else $result->getUserInfo(); Expected result: ---------------- This code should work all fine and return OK as a result. Actual result: -------------- What I receive is "_doQuery: [Error message: Could not execute statement] [Last executed query: EXECUTE mdb2_statement_mysql_3d7863f7f73bd31f768b855340c2ea062e77307fe USING @0] [Native code: 1305] [Native message: PROCEDURE e2.update_ON_TABLE_bar_VIOLATES_FOREIGN_KEY_CONSTRAINT does not exist]" Looking at the trigger it seems it's not appropriate for ONUPDATE: BEGIN SET FOREIGN_KEY_CHECKS = 0; IF (SELECT bar.id AS id FROM bar WHERE id = OLD.id) IS NOT NULL THEN CALL update_ON_TABLE_bar_VIOLATES_FOREIGN_KEY_CONSTRAINT(); END IF; SET FOREIGN_KEY_CHECKS = 1; END Means each time anything is changed, it will be blocked if any other record references to this table.

Comments

 [2008-05-31 10:31 UTC] quipo (Lorenzo Alberton)
I've committed a fix to CVS. Please fetch the latest files and let me know if you still have issues. Thanks in advance for the feedback!