* When importing bbmri mysql table , table structures change --> exception . * [http://www.abbeyworkshop.com/howto/lamp/MySQL_Export_Backup/index.html 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)