• 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
    • mysql> alter table bbmri_old.OntologyTerm change term name varchar(255) ;
    • mysql> alter table bbmri_old.OntologyTerm add column __Type enum('OntologyTerm','Species') ;
    • mysql> 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 .

mysql> select * from biobank where id=1;

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

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

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

| Agnes   |       34 | core | 800  | 2010-11-25 | see PLoS ONE 3(10): e3583. doi:10.1371/journal.pone.0003583 |  1 | NULL | NULL          | NULL              | NULL        | NULL    | NULL      | NULL          | NULL       | NULL         | NULL     | NULL   | NULL      |

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

1 row in set (0.00 sec)

mysql> select * from biobank where id=2;

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

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

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

| AGORA   |      126 | core | 5,000 | 2010-11-25 | 1600 trio's |  2 | NULL | NULL          | NULL              | NULL        | NULL    | NULL      | NULL          | NULL       | NULL         | NULL     | NULL   | NULL      |

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

1 row in set (0.00 sec)

mysql> update  biobank  set ="OntologyTerm";

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '="OntologyTerm"' at line 1

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

| name              | varchar(255)                   | YES  |     | NULL    |       |

| Investigation     | varchar(255)                   | YES  |     | NULL    |       |

| ontologyReference | varchar(255)                   | YES  |     | NULL    |       |

| Individuals       | varchar(255)                   | YES  |     | NULL    |       |

| Species           | varchar(255)                   | YES  |     | NULL    |       |

| PanelType         | varchar(255)                   | YES  |     | NULL    |       |

| FounderPanels     | varchar(255)                   | YES  |     | NULL    |       |

| Institutes        | varchar(255)                   | YES  |     | NULL    |       |

| Publications      | varchar(255)                   | YES  |     | NULL    |       |

| Contacts          | varchar(255)                   | YES  |     | NULL    |       |

| Topics            | varchar(255)                   | YES  |     | NULL    |       |

| Materials         | varchar(255)                   | YES  |     | NULL    |       |

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

19 rows in set (0.00 sec)