On Wed, 28 Jun 2006, allank wrote: > Hi Richard and Damian, > > I get server error (code 500) if I select to filter of any of the fields > of the mid__timepoint__main or the mid__post_infection__dm tables.\ > I have selected "Naive" to create a basic configuration which I then > exported, but the error remains.
is this a different error now to the one you were talking about earlier (SQL FAILED etc) or the same. IF you look in the biomart-plib/BioMart/Dataset/TableSet.pm you will see a couple of lines like #warn ($sql). Uncomment those to get the SQL being generated in the server logs. If you send me that and which filts and atts you chose may help me debug. Also maybe save your XML to file and send it to me so I can see if I can spot anything wrong cheers Damian > > Below is my table schema for biomart > > CREATE TABLE mid__sample__main > ( > sample_id_key INTEGER NOT NULL, > study_id VARCHAR(20), > location VARCHAR(20), > participant_id INTEGER, > PRIMARY KEY (sample_id_key) > ) TYPE=MyISAM; > > > CREATE TABLE mid__timepoint__main > ( > timepoint_id_key INTEGER NOT NULL, > sample_id_key INTEGER NOT NULL, > > study_id VARCHAR(20), > location VARCHAR(20), > participant_id INTEGER, > > phase INTEGER, > visit INTEGER, > interim_visit INTEGER, > year INTEGER, > month INTEGER, > day INTEGER, > PRIMARY KEY (timepoint_id_key), > FOREIGN KEY (sample_id_key) REFERENCES mid__sample__main(sample_id_key) > ) TYPE=MyISAM; > > CREATE TABLE mid__period_post_infection__dm > ( > timepoint_id_key INTEGER NOT NULL, > days_post_infection INTEGER, > weeks_post_infection INTEGER, > infection_year INTEGER, > infection_month INTEGER, > infection_day INTEGER, > PRIMARY KEY (timepoint_id_key), > FOREIGN KEY(timepoint_id_key)REFERENCES > mid__timepoint__main(timepoint_id_key) > )TYPE=MyISAM; > > > > >On Wed, 28 Jun 2006, Richard Holland wrote: > > > > > > > >>I might be wrong here (someone please correct me if I am) but you can > >>only have multiple main tables if there is a 1:M relation between them - > >>i.e. every B includes all the fields of A plus some extra fields, some > >>of which make B unique when looked at in combination with the fields > >>inherited from A. Dimension tables that refer to A must include all the > >>key columns for A, and those that refer to B must refer to all the key > >>columns for B (which by definition will include all the key columns that > >>B inherited from A). > >> > >>Could you check that this is the case? > >> > >> > > > >what Richard is saying is correct. From the error message it seems your > >second main table is prob missing the key from the first main table (ie) > >the second main needs all the cols from the first one including the key. > > > >cheers > >damian > > > > > > > > > >>cheers, > >>Richard > >> > >>On Wed, 2006-06-28 at 11:46 +0200, allank wrote: > >> > >> > >>>Hi all, > >>>I am unable to query from a biomart schema having two tables and (at the > >>>moment) one dimension table, I get a " SQL FAILED - LIKELY XML > >>>MISCONFIGURATION ERROR ..." error at the result display stage on the > >>>biomart web application. > >>>I have setup the biomart table structure in accordance with the multiple > >>>main table requirements, in Martj I "validate all" and I get no errors, > >>>when using the web application, I am able to go smoothly through the > >>>filter process and the field-section-for-output stages the pane at the > >>>right indicates I have 32 entries at the output level, when I attempt to > >>>proceed to the output display page, I get the message " SQL FAILED - > >>>LIKELY XML MISCONFIGURATION ERRORUnknown column 'main.timepoint_id_key' > >>>in 'where clause'". > >>>Could the error have been caused from using a key field (in my case > >>>timepoint_id_key) for fields from the dimension table that is not found > >>>in all the main tables. If this is so, how can I manage the situation > >>>where I have a main table (dataset__A__main) having a one to many > >>>relationship with one dimension table (dataset__C__dm) and other main > >>>table (dataset__B__main) which will have one to many relationship with > >>>several dimension tables. > >>> > >>>Allan. > >>> > >>> > >>> > >>-- > >>Richard Holland (BioMart Team) > >>EMBL-EBI > >>Wellcome Trust Genome Campus > >>Hinxton > >>Cambridge CB10 1SD > >>UNITED KINGDOM > >>Tel: +44-(0)1223-494416 > >> > >> > >> > >> > > > > > > > >
