A place for users and developers of the Xataface to discuss and receive support.
by silma » Mon Sep 13, 2010 7:01 am
Hello, I need your help once again... sorry for the inconvenience. My problem is not very easy to explain, I'll try to be clear. I'v got an application which is working (very well) with Xataface version 1.1.5r2 984. I try to run it with the new version ; I have not changed my application, nor the database : only the Xataface directory. I've got some trouble with the action "new_related_record" in a many to many relationship. Each time I try to insert a new related record, I've got an error : - Code: Select all
Fatal error: [pear_error: message="11" code=0 mode=return level=notice prefix="" info="Erreur en effectuant la requête 'INSERT INTO `compteur` (`Nom`,`Type`,`EnergieID`,`Estenergie`) VALUES ('TESTTEST','CONSO','1','1')' sur la ligne '1244' du fichier 'C:\wamp\www\xataface\Dataface\IO.php': 1452: Cannot add or update a child row: a foreign key constraint fails (`enerconso2`.`compteur`, CONSTRAINT `compteur_ibfk_1` FOREIGN KEY (`MouvrageID`) REFERENCES `mouvrage` (`MouvrageID`) ON DELETE CASCADE ON UPDATE CASCADE)"]On line 1247 of file C:\wamp\www\xataface\Dataface\IO.php in function printStackTrace() On line 1344 of file C:\wamp\www\xataface\Dataface\IO.php in function performSQL(array(INSERT INTO `compteur` (`Nom`,`Type`,`EnergieID`,`Estenergie`) VALUES ('TESTTEST','CONSO','1',) On line 628 of file C:\wamp\www\xataface\Dataface\ShortRelatedRecordForm.php in function addRelatedRecord(Dataface_RelatedRecord Object,1) On line of file in function save(array(1,,,BASE1,6,array(52,BASE1),vehicule,Compteurs,new in C:\wamp\www\xataface\Dataface\IO.php on line 1247
Obviously it lacks the "MouvrageID", but the field was filled in the form ! I use a function to fill it, and it displays well. (I tried to fill the form manually : I got the same error) Here's my tables - Code: Select all
CREATE TABLE `compteur` ( `CompteurID` int(11) NOT NULL AUTO_INCREMENT, `Nom` varchar(45) DEFAULT NULL, `Reference` varchar(45) DEFAULT NULL, `EnergieID` int(11) DEFAULT '1', `Estenergie` tinyint(1) DEFAULT NULL, `MouvrageID` int(11) NOT NULL, (...) PRIMARY KEY (`CompteurID`,`BaseID`), KEY `Compteurs_belong_to_Energie` (`EnergieID`), KEY `indexNom` (`Nom`), KEY `MouvrageID` (`MouvrageID`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
CREATE TABLE `compteurvehicules` ( `VehiculeID` int(11) NOT NULL DEFAULT '0', `CompteurID` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`VehiculeID`,`CompteurID`,`BaseID`), KEY `CompteurVehicule_belong_to_Vehicule` (`VehiculeID`), KEY `CompteurVehicule_belong_to_Compteur` (`CompteurID`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `vehicule` ( `VehiculeID` int(11) NOT NULL AUTO_INCREMENT, `CategorieID` int(11) DEFAULT NULL, `MouvrageID` int(11) NOT NULL, `Nom` varchar(45) NOT NULL, `Marque` varchar(45) DEFAULT NULL, `Modele` varchar(45) DEFAULT NULL, (...) PRIMARY KEY (`VehiculeID`,`BaseID`), KEY `vehicules_belong_to_Categorie` (`CategorieID`), KEY `MouvrageID` (`MouvrageID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
The file tables\vehicule\relationships.ini ; - Code: Select all
[Compteurs] compteur.CompteurID=compteurvehicules.CompteurID compteurvehicules.VehiculeID="$VehiculeID"
The fonction I use in tables\compteur\compteur.php to get MouvrageID - Code: Select all
function mouvrageID__default(){ $app =& Dataface_Application::getInstance(); $enregistrement =& $app->getRecord(); $mouvrageid = '' ;
if ( $enregistrement ) $mouvrageid = $enregistrement->val("MouvrageID");
return $mouvrageid;
}
The trouble occurs when i call : - Code: Select all
/index.php?-action=new_related_record&-table=vehicule&-relationship=Compteurs&VehiculeID=52&-cursor=0&-skip=0&-limit=30&-mode=list
With the same code, everything is working well with version 1.1.5r2 984. What could prevent Xataface to take the form into account ? Thank you in advance for your help, and sorry for my English.
Last edited by silma on Thu Apr 21, 2011 1:31 am, edited 1 time in total.
-
silma
-
- Posts: 87
- Joined: Tue Apr 28, 2009 11:47 pm
by shannah » Fri Sep 17, 2010 12:22 pm
I think it may be failing because of the order in which the insert statements are being performed. If it inserts into the join table before the destination table, it gives us this foreign key constraint error. It may have worked in 1.1.5 by random chance as the statements are not executed in any particular order right now..
I'll need to look into it a little, but I think it should be a relatively easy fix.. I should have the fix in SVN by later tonight.
-Steve
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by silma » Mon Sep 20, 2010 1:38 am
Steve,
Thanks a lot, i'll check the repository and try this.
-
silma
-
- Posts: 87
- Joined: Tue Apr 28, 2009 11:47 pm
by shannah » Mon Sep 20, 2010 10:25 am
Still haven't had a chance to make this fix...
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by shannah » Mon Sep 20, 2010 10:54 am
OK.. It should be there. Please give it a try. As I wasn't having this issue to begin with it is hard for me to test if this gets it working, but I think this should do it.
-Steve
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by silma » Thu Sep 23, 2010 9:28 am
Hello Steve,
I may not have the time to do all the tests before next week, but i'll tell you as soon as i do it.
Thanks again,
Silma
-
silma
-
- Posts: 87
- Joined: Tue Apr 28, 2009 11:47 pm
by silma » Tue Sep 28, 2010 2:06 am
Hello, I tried the SVN version (downloaded yesterday), but unfortunately, I still have the error, under the same conditions. A few line numbers given in the error are different, however. - Code: Select all
Fatal error: [pear_error: message="11" code=0 mode=return level=notice prefix="" info="Error performing query 'INSERT INTO `compteur` (`Nom`,`Type`,`EnergieID`,`Estenergie`) VALUES ('test','CONSO','1','1')' on line '1258' of file 'C:\wamp\www\xataface\Dataface\IO.php': 1452: Cannot add or update a child row: a foreign key constraint fails (`enerconso2`.`compteur`, CONSTRAINT `compteur_ibfk_1` FOREIGN KEY (`MouvrageID`) REFERENCES `mouvrage` (`MouvrageID`) ON DELETE CASCADE ON UPDATE CASCADE)"]On line 1261 of file C:\wamp\www\xataface\Dataface\IO.php in function printStackTrace() On line 1358 of file C:\wamp\www\xataface\Dataface\IO.php in function performSQL(array(INSERT INTO `compteur` (`Nom`,`Type`,`EnergieID`,`Estenergie`) VALUES ('test','CONSO','1','1')) On line 628 of file C:\wamp\www\xataface\Dataface\ShortRelatedRecordForm.php in function addRelatedRecord(Dataface_RelatedRecord Object,1) On line of file in function save(array(1,,,BASE1,6,array(533,BASE1),batiment,Compteurs,new_related_record,0,0, in C:\wamp\www\xataface\Dataface\IO.php on line 1261
My apologies for the time it takes you !
-
silma
-
- Posts: 87
- Joined: Tue Apr 28, 2009 11:47 pm
by shannah » Wed Oct 06, 2010 10:18 am
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by silma » Wed Oct 20, 2010 7:43 am
Hello Steve,
Excuse me, I have not read the forum last week. I will try this as quickly as possible, and I'll let you know.
Thanks again !
-
silma
-
- Posts: 87
- Joined: Tue Apr 28, 2009 11:47 pm
by silma » Thu Oct 21, 2010 12:32 am
Hello Steve, I've updated my xataface files with the svn version. (1936,1937,1942) I'm afraid I still have the same error : - Code: Select all
Fatal error: [pear_error: message="11" code=0 mode=return level=notice prefix="" info="Error performing query 'INSERT INTO `compteur` (`Nom`,`Type`,`EnergieID`,`Estenergie`) VALUES ('azerty','CONSO','1','1')' on line '1258' of file 'C:\wamp\www\xataface\Dataface\IO.php': 1452: Cannot add or update a child row: a foreign key constraint fails (`enerconso2`.`compteur`, CONSTRAINT `compteur_ibfk_1` FOREIGN KEY (`MouvrageID`) REFERENCES `mouvrage` (`MouvrageID`) ON DELETE CASCADE ON UPDATE CASCADE)"]On line 1261 of file C:\wamp\www\xataface\Dataface\IO.php in function printStackTrace() On line 1358 of file C:\wamp\www\xataface\Dataface\IO.php in function performSQL(array(INSERT INTO `compteur` (`Nom`,`Type`,`EnergieID`,`Estenergie`) VALUES ('azerty','CONSO','1','1) On line 628 of file C:\wamp\www\xataface\Dataface\ShortRelatedRecordForm.php in function addRelatedRecord(Dataface_RelatedRecord Object,1) On line of file in function save(array(1,,,BASE1,6,array(2,BASE1),vehicule,Compteurs,new_related_record,0,0, in C:\wamp\www\xataface\Dataface\IO.php on line 1261
I'm surprised to be the only one experiencing the problem: there may be something I'm doing wrong with the relationships declaration ? Thanks again for your help and patience, Silma
-
silma
-
- Posts: 87
- Joined: Tue Apr 28, 2009 11:47 pm
by shannah » Tue Apr 19, 2011 10:35 am
In trying to reproduce the error I have tried to create a database with the schema you specified. But the schema you specify is missing some column definitions.
e.g. in the Compteur table you have a primary key specified as: PRIMARY KEY (`CompteurID`,`BaseID`)
Where BaseID is not in the definition you provided. Further, CompteurID is an auto increment field. Hence this should be sufficient as a primary key. Why the compound primary key here?
-Steve
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by silma » Wed Apr 20, 2011 1:45 am
Hello Steve, Here's the concerned tables : - Code: Select all
CREATE TABLE `base` ( `BaseID` char(36) NOT NULL, `BaseURL` varchar(70) NOT NULL, PRIMARY KEY (`BaseID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `compteur` ( `CompteurID` int(11) NOT NULL AUTO_INCREMENT, `Nom` varchar(45) DEFAULT NULL, `Reference` varchar(45) DEFAULT NULL, `EnergieID` int(11) DEFAULT '1', `FournisseurID` int(11) DEFAULT NULL, `Localisation` varchar(250) DEFAULT NULL, `Nomprestataire` varchar(45) DEFAULT NULL, `Seuil` decimal(11,2) DEFAULT NULL, `Commentaire` text, `Caracteristique` varchar(45) DEFAULT NULL, `Objectif` decimal(11,2) DEFAULT NULL, `Estenergie` tinyint(1) DEFAULT NULL, `Clos` tinyint(1) DEFAULT '0', `BaseID` char(36) NOT NULL DEFAULT 'BASE1', `MouvrageID` int(11) NOT NULL, `Reference2` varchar(45) DEFAULT NULL, `Type` enum('CONSO','CONSOEAU','CONSOLIEPROD','MP','PROD','PRODEAU','FABRICATION') NOT NULL DEFAULT 'CONSO', `CompteurprodID` int(11) DEFAULT NULL, PRIMARY KEY (`CompteurID`,`BaseID`), KEY `Compteurs_belong_to_Energie` (`EnergieID`), KEY `indexNom` (`Nom`), KEY `belong_to_Base` (`BaseID`), KEY `MouvrageID` (`MouvrageID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5737 ;
CREATE TABLE `compteurvehicules` ( `VehiculeID` int(11) NOT NULL DEFAULT '0', `CompteurID` int(11) NOT NULL DEFAULT '0', `BaseID` char(36) NOT NULL DEFAULT 'BASE1', `Pourcentage` int(11) DEFAULT '100', PRIMARY KEY (`VehiculeID`,`CompteurID`,`BaseID`), KEY `CompteurVehicule_belong_to_Vehicule` (`VehiculeID`), KEY `CompteurVehicule_belong_to_Compteur` (`CompteurID`), KEY `belong_to_Base` (`BaseID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `mouvrage` ( `MouvrageID` int(11) NOT NULL AUTO_INCREMENT, `CategorieID` int(11) DEFAULT NULL, `BureauetudeID` int(11) DEFAULT NULL, `Logo` varchar(90) DEFAULT NULL, `Commentaire` text, `Societe` varchar(45) DEFAULT NULL, `Nomcontact` varchar(45) DEFAULT NULL, `Prenomcontact` varchar(45) DEFAULT NULL, `Tel` varchar(45) DEFAULT NULL, `Mail` varchar(45) DEFAULT NULL, `Adresse1` varchar(45) DEFAULT NULL, `Adresse2` varchar(45) DEFAULT NULL, `Adresse3` varchar(45) DEFAULT NULL, `Codepostal` int(11) DEFAULT NULL, `Ville` varchar(45) DEFAULT NULL, `Pays` varchar(45) DEFAULT NULL, `Site` varchar(45) DEFAULT NULL, `BaseID` char(36) NOT NULL DEFAULT 'BASE1', `StationdjuID` int(11) DEFAULT NULL, `StationmeteoID` int(11) DEFAULT NULL, PRIMARY KEY (`MouvrageID`,`BaseID`), KEY `indexste` (`Societe`), KEY `indexNom` (`Nomcontact`), KEY `Mouvrages_belong_to_Fournisseurs` (`BureauetudeID`), KEY `Mouvrages_belongto_Categorie` (`CategorieID`), KEY `belong_to_Base` (`BaseID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=124 ;
CREATE TABLE `vehicule` ( `VehiculeID` int(11) NOT NULL AUTO_INCREMENT, `CategorieID` int(11) DEFAULT NULL, `MouvrageID` int(11) NOT NULL, `Nom` varchar(45) NOT NULL, `Anneeconstruction` int(4) DEFAULT NULL, `Marque` varchar(45) DEFAULT NULL, `Modele` varchar(45) DEFAULT NULL, `Carburant` varchar(45) DEFAULT NULL, `Puissance` varchar(45) DEFAULT NULL, `Conso` varchar(45) DEFAULT NULL, `Commentaire` text, `Nomcontact` varchar(45) DEFAULT NULL, `Prenomcontact` varchar(45) DEFAULT NULL, `tel` varchar(45) DEFAULT NULL, `mail` varchar(45) DEFAULT NULL, `BaseID` char(36) NOT NULL DEFAULT 'BASE1', PRIMARY KEY (`VehiculeID`,`BaseID`), KEY `vehicules_belong_to_Categorie` (`CategorieID`), KEY `belong_to_Base` (`BaseID`), KEY `MouvrageID` (`MouvrageID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=106 ;
ALTER TABLE `compteur` ADD CONSTRAINT `Compteurs_belong_to_Energies` FOREIGN KEY (`EnergieID`) REFERENCES `energie` (`EnergieID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `compteur_ibfk_1` FOREIGN KEY (`BaseID`) REFERENCES `base` (`BaseID`) ON UPDATE CASCADE, ADD CONSTRAINT `compteur_ibfk_2` FOREIGN KEY (`MouvrageID`) REFERENCES `mouvrage` (`MouvrageID`) ON UPDATE CASCADE;
ALTER TABLE `compteurvehicules` ADD CONSTRAINT `compteurvehicules_ibfk_1` FOREIGN KEY (`VehiculeID`) REFERENCES `vehicule` (`VehiculeID`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `compteurvehicules_ibfk_2` FOREIGN KEY (`CompteurID`) REFERENCES `compteur` (`CompteurID`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `compteurvehicules_ibfk_3` FOREIGN KEY (`BaseID`) REFERENCES `base` (`BaseID`) ON UPDATE CASCADE;
ALTER TABLE `mouvrage` ADD CONSTRAINT `Mouvrages_bt_Fournisseurss` FOREIGN KEY (`BureauetudeID`) REFERENCES `bureauetude` (`BureauetudeID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `mouvrage_ibfk_1` FOREIGN KEY (`BaseID`) REFERENCES `base` (`BaseID`) ON UPDATE CASCADE;
ALTER TABLE `vehicule` ADD CONSTRAINT `vehicule_ibfk_2` FOREIGN KEY (`MouvrageID`) REFERENCES `mouvrage` (`MouvrageID`) ON UPDATE CASCADE, ADD CONSTRAINT `vehicule_ibfk_3` FOREIGN KEY (`BaseID`) REFERENCES `base` (`BaseID`) ON UPDATE CASCADE;
The Base ID primary Key is there because I sometimes have to merge different database : each database has is own BaseID code. Thanks again for your help !
-
silma
-
- Posts: 87
- Joined: Tue Apr 28, 2009 11:47 pm
by shannah » Wed Apr 20, 2011 10:15 am
I just set up a test instance with your database and relationships, on the latest Xataface (1.3), and I do get these errors when you enter a value for MouvrageID that is not in the database. However this is correct since you have a foreign key set up to ensure that this value must be there.
If I enter a valid MouvrageID on the form, then it saves correctly.
-
shannah
-
- Posts: 4457
- Joined: Wed Dec 31, 1969 5:00 pm
by silma » Thu Apr 21, 2011 1:17 am
Hello Steve,
I finally found what was going wrong, thanks to your experiment.
As i was now sure that nor Xataface, nor the database were in cause, I concentrated on the application.
I emptied my files one by one, untill i found the one that was involved.
I found that the error was due to the function __sql__ of the tables_compteurvehicules class. I'm not really sure, but i think the error was due to the "Role" left join in it : it seems that the application tried to insert more fields there are in the table compteurvehicules.
As i only needed this class to make valuelist, i commented the function__sql__ and gave the permission to admin only.
It seems to works.
Thanks a lot ! (And sorry for my english... )
Silma
-
silma
-
- Posts: 87
- Joined: Tue Apr 28, 2009 11:47 pm
Return to Xataface Users
Who is online
Users browsing this forum: No registered users and 3 guests
|