[Solved] 1.2.5 upgrade

A place for users and developers of the Xataface to discuss and receive support.

[Solved] 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby shannah » Wed Oct 06, 2010 10:18 am

shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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

Re: 1.2.5 upgrade

Postby 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 1 guest

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved