DespoinaLog/2010/12/08 – Trac

wiki:DespoinaLog/2010/12/08

Context Navigation

When importing bbmri mysql table , table structures change --> exception .
- Try to export / import one table at a time : mysqldump5 -u root -p bbmri biobank >biobank.mysql / mysqldump5 -u root -p bbmri_old biobank >biobank.mysql / mysql5 -u root -p bbmri < biobank.mysql / mysql5 -u root -p bbmri < ontologyTerm.mysql

Inconsistency with OntologyTerm table

org.molgenis.framework.db.DatabaseException: Unknown column 'OntologyTerm.__Type' in 'where clause'
- Was this produced after the import ? (generate gcc, update db -> test if it is shown --> import -> test if it's there)
- So this is the PROBLEM for OntologyTerm :
- * mysql> describe OntologyTerm;
- +---------------+--------------------------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +---------------+--------------------------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | name | varchar(127) | NO | | NULL | |
- | __Type | enum('OntologyTerm','Species') | NO | | NULL | |
- | ontology | int(11) | YES | MUL | NULL | |
- | termAccession | varchar(255) | YES | | NULL | |
- | definition | varchar(255) | YES | | NULL | |
- | termPath | varchar(1024) | YES | | NULL | |
- +---------------+--------------------------------+------+-----+---------+----------------+
- 7 rows in set (0.04 sec)
- mysql> describe bbmri_old.OntologyTerm;
- +---------------+--------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +---------------+--------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | term | varchar(255) | NO | | NULL | |
- | ontology | int(11) | YES | MUL | NULL | |
- | termAccession | varchar(255) | YES | | NULL | |
- | definition | varchar(255) | YES | | NULL | |
- +---------------+--------------+------+-----+---------+----------------+
- 5 rows in set (0.01 sec)
- SOLUTION
  - ```
  alter table bbmri_old.OntologyTerm change term name varchar(255) ;
  alter table bbmri_old.OntologyTerm add column __Type enum('OntologyTerm','Species')  ;
  alter table bbmri_old.OntologyTerm add column termPath varchar(1024);
```
```
  mysqldump5 -u root -p bbmri_old ontologyTerm > ontologyTerm.mysql
  mysql5 -u root -p bbmri < ontologyTerm.mysql
```

STILL no data shown in molgenis forms :

mysql> update OntologyTerm set __Type="OntologyTerm"

problem solved !

Inconsistency with Biobanks table

mysql> describe biobanks;

ERROR 1146 (42S02): Table 'bbmri.biobanks' doesn't exist
mysql> describe biobank;
+-------------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------------------------+------+-----+---------+-------+
| Acronym | varchar(255) | YES | | NULL | |
| Category | int(11) | NO | MUL | NULL | |
| Type | enum('core','support','other') | NO | | NULL | |
| Size | varchar(255) | YES | | NULL | |
| LastUpdate | date | NO | | NULL | |
| Description | text | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
+-------------+--------------------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> describe bbmri_old.biobank;
+-------------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------------------------+------+-----+---------+-------+
| Acronym | varchar(255) | YES | | NULL | |
| Category | int(11) | NO | MUL | NULL | |
| Type | enum('core','support','other') | NO | | NULL | |
| Size | varchar(255) | YES | | NULL | |
| LastUpdate | date | NO | | NULL | |
| Description | text | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
+-------------+--------------------------------+------+-----+---------+-------+

But according to the model this is the complete list of fields :

id
name
Investigation
ontologyReference
Individuals
Species
PanelType?
FounderPanels?
Acronym
Institutes
Publications
Category
Type
Contacts
Topics
Materials
Size
LastUpdate?
Description

ALTER TABLE biobank ADD name varchar(255);

  ALTER TABLE biobank ADD Investigation varchar(255);

  ALTER TABLE biobank ADD ontologyReference varchar(255);

  ALTER TABLE biobank ADD Individuals varchar(255);

  ALTER TABLE biobank ADD Species varchar(255);

  ALTER TABLE biobank ADD !PanelType        varchar(255);

  ALTER TABLE biobank ADD !FounderPanels        varchar(255);

  ALTER TABLE biobank ADD Institutes varchar(255);

  ALTER TABLE biobank ADD Publications varchar(255);

  ALTER TABLE biobank ADD Contacts varchar(255);

  ALTER TABLE biobank ADD Topics varchar(255);

  ALTER TABLE biobank ADD Materials varchar(255);

after import from old table , molgenis forms still not showing data .Only record id=1 is shown .

mysql> select * from biobank where id=1;

+---------+----------+------+------+------------+-------------------------------------------------------------+----+------+---------------+-------------------+-------------+---------+-----------+---------------+------------+--------------+----------+--------+-----------+

1 row in set (0.00 sec)

mysql> select * from biobank where id=2;

+---------+----------+------+-------+------------+-------------+----+------+---------------+-------------------+-------------+---------+-----------+---------------+------------+--------------+----------+--------+-----------+

1 row in set (0.00 sec)

mysql> describe biobank;

+-------------------+--------------------------------+------+-----+---------+-------+

+-------------------+--------------------------------+------+-----+---------+-------+

| Category | int(11) | NO | MUL | NULL | |

| id | int(11) | NO | PRI | NULL | |

+-------------------+--------------------------------+------+-----+---------+-------+

19 rows in set (0.00 sec)

Last modified 13 years ago Last modified on 2010-12-09T10:37:08+01:00

Download in other formats:

Plain Text