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

Bug #19959 MDB2 trigger problem on multiple table references on mysql
Submitted: 2013-05-27 16:44 UTC
From: gzuki Assigned:
Status: Open Package: MDB2 (version 2.5.0b5)
PHP Version: Irrelevant OS: Ubuntu 12.04 LTS
Roadmaps: (Not assigned)    
Subscription  
Comments Add Comment Add patch


Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know! Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem : 30 - 2 = ?

 
 [2013-05-27 16:44 UTC] gzuki (Mario Gzuk)
Description: ------------ Hi, I have multiple tables which referes to one main tables "id". So I use: 'fk_fusioninventory_antivirus_hardware_id'=>array( 'primary' => 0, 'unique' => 0, 'foreign' => 1, 'check' => 0, 'fields' => array ( 'hardware_id' => array( 'position' => 1 ) ), 'references' => array( 'table' => 'fusioninventory_hardware', 'fields' => array( 'id' => array( 'position' => 1 ) ) ), 'deferrable' => 0, 'initiallydeferred' => 0, 'onupdate' => 'CASCADE', 'ondelete' => 'CASCADE' ) on each table. For the first table which will be created and which depends on the main table, there will be created an trigger which says that if the fusioninventory_hardware.id is changed please update also this first table. As mysql doesnt support more than one trigger for the same action, the second table which has the same foreign key - there will be no trigger created. The (damn) effect is that only the first table is updated if I change the id of the main table. If I delete the 2 triggers (update/delete) so that there is no more trigger on the main table, all works fine. All tables which has the foreign key will be updated. I have seen that you catch the correct error in the driver: $expected_errmsg = 'This MySQL version doesn\'t support multiple triggers with the same action time and event for one table'; So the trigger should not be created if this mysql version doesnt support multiple trigger, because of the first trigger is created and breaks the foreign key behaviour. If you want to reproduce that, the table definitions are located here: http://uranos.svn.sourceforge.net/viewvc/uranos/www/modules/fusioninventory/tables.php I use the latest version of MDB2: MDB2 2.5.0b5 beta MDB2_Driver_mysql 1.5.0b4 beta MDB2_Driver_mysqli 1.5.0b4 beta mysqld Ver 5.5.29-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 ((Ubuntu)) How can I prevent that the mgCreateConstraint creates this triggers? Thank you Test script: --------------- http://uranos.svn.sourceforge.net/viewvc/uranos/www/modules/fusioninventory/tables.php

Comments