Xataface Forums

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.

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

Steve,

Thanks a lot, i'll check the repository and try this.

Still haven't had a chance to make this fix...

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

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

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 !

OK... give it another shot.
http://weblite.ca/svn/dataface/core/tru ... ace/IO.php

-Steve

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 !

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

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

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 !

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.

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