[Solved] 1.2.5 upgrade

Archived from the Xataface Users forum.

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.

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

silma — Mon Sep 20, 2010 1:38 am

Steve,

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

shannah — Mon Sep 20, 2010 10:25 am

Still haven’t had a chance to make this fix…

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

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 — 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 !

shannah — Wed Oct 06, 2010 10:18 am

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

-Steve

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 — 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

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

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 !

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.

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

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

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;

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 !

shannah — Wed Apr 20, 2011 10:15 am

If I enter a valid MouvrageID on the form, then it saves correctly.

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.

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