upgrade chado 1.2 to 1.3

[bradfordcondon]

@mpoelchau reported an error when enabling the eutils module- this is because it expects a Chado 1.3 table.

I5k is on chado 1.2.

The Tripal automatic upgrade gives us this error:

alling: tripal_chado_install_chado(Upgrade Chado v1.2 to v1.3)
Checking for existing v1.3 tables in v1.2 and fixing bigints...
Incorporating additional changes...
Loading sites/all/modules/contrib/tripal/tripal_chado/chado_schema/default_schema-1.2-1.3-diff.sql...
[site http://default] [TRIPAL ERROR] [TRIPAL_CHADO] FAILED. Line  1853, 0SQLSTATE[42809]: Wrong object type: 7 ERROR:  "all_feature_names" is not a viewHINT:  Use DROP TABLE to remove a table.:DROP VIEW IF EXISTS all_feature_names CASCADE;

Unfortunately, this is a pain. Fortunately, I've been through it before and its all documented in the issue here on our HWG site

Also relevant: issue in tripal core

[bradfordcondon]

Highlights:

The views need to be dropped first (as the above error suggests).

This script generates a script that will drop all the views

set search_path=public,so,frange,genetic_code;
select 'drop view "' || viewname || '" cascade;' 
from pg_views where schemaname = 'so';

select 'drop view "' || viewname || '" cascade;' 
from pg_views where schemaname = 'chado';

If you save the above script to a file, you can run it via drush like so:

drush sql-query --file=dump_mview_script.sql

(dont forget to add teh searchpath backs to the top of the script: set search_path=public,so,frange,genetic_code;)

looks like we had to additioanlly drop these views manually-

drop view public.orgnasim;
drop view public.organism;
drop view public.cvterm;
drop view public.feature_ssr_mview;

however those views were created by us and might not be relevant for i5k

The other major problem was deadlock issues, which we resolved by breaking up the migration script into chunks and running piecemeal. hopefully we wont have to do that here?

[bradfordcondon]

after generating the drop script as instructed above -

Calling: tripal_chado_install_chado(Upgrade Chado v1.2 to v1.3)
Checking for existing v1.3 tables in v1.2 and fixing bigints...
Incorporating additional changes...
Loading sites/all/modules/contrib/tripal/tripal_chado/chado_schema/default_schema-1.2-1.3-diff.sql...
[site http://default] [TRIPAL ERROR] [TRIPAL_CHADO] FAILED. Line  1853, 0SQLSTATE[42809]: Wrong object type: 7 ERROR:  "all_feature_names" is not a viewHINT:  Use DROP TABLE to remove a table.:DROP VIEW IF EXISTS all_feature_names CASCADE;

[bradfordcondon]

I manually deleted the all_feature_names table. Looking at the Chado definition it really appears to be a view, not a table... so... I dunno. I'm going to delete it and move on. I created a core issue, maybe someone will inform me that i shouldnt drop it.

DROP TABLE IF EXISTS all_feature_names CASCADE;

This brings us to the next error-

Calling: tripal_chado_install_chado(Upgrade Chado v1.2 to v1.3)
Checking for existing v1.3 tables in v1.2 and fixing bigints...
Incorporating additional changes...
Loading sites/all/modules/contrib/tripal/tripal_chado/chado_schema/default_schema-1.2-1.3-diff.sql...
[site http://default] [TRIPAL ERROR] [TRIPAL_CHADO] FAILED. Line  2374, 0SQLSTATE[0A000]: Feature not supported: 7 ERROR:  cannot alter type of a column used by a view or ruleDETAIL:  rule _RETURN on view so.genotype depends on column "feature_id":ALTER TABLE feature     ALTER feature_id TYPE  bigint,    ALTER dbxref_id TYPE bigint,    ALTER organism_id TYPE bigint,    ALTER seqlen TYPE bigint,    ALTER type_id TYPE bigint;

So, we have additional views that werent dropped. why not?

answer: because we only dropped so views in our script, not chado or public views.

additional chado views we should/could drop-

drop view "gffatts" cascade;
drop view "common_ancestor_cvterm" cascade;
drop view "common_descendant_cvterm" cascade;
drop view "cv_cvterm_count" cascade;
drop view "cv_cvterm_count_with_obs" cascade;
drop view "cv_leaf" cascade;
drop view "cv_link_count" cascade;
drop view "cv_path_count" cascade;
drop view "cv_root" cascade;
drop view "db_dbxref_count" cascade;
drop view "dfeatureloc" cascade;
drop view "f_type" cascade;
drop view "f_loc" cascade;
drop view "feature_contains" cascade;
drop view "feature_disjoint" cascade;
drop view "feature_difference" cascade;
drop view "feature_distance" cascade;
drop view "feature_intersection" cascade;
drop view "feature_meets" cascade;
drop view "feature_meets_on_same_strand" cascade;
drop view "feature_union" cascade;
drop view "featureset_meets" cascade;
drop view "fnr_type" cascade;
drop view "intronloc_view" cascade;
drop view "fp_key" cascade;
drop view "gff3atts" cascade;
drop view "gff3view" cascade;
drop view "intron_combined_view" cascade;
drop view "stats_paths_to_root" cascade;
drop view "type_feature_count" cascade;

After dropping these views at all, we're delivered to our next error:

Disk full: 7 ERROR:  could not extend file "base/40960/269560.3": No space left on deviceHINT:  Check free disk space.:ALTER TABLE feature     ALTER feature_id TYPE  bigint,    ALTER dbxref_id TYPE bigint,    ALTER organism_id TYPE bigint,    ALTER seqlen TYPE bigint,    ALTER type_id TYPE bigint;

This one also appeared for HWG.

[bradfordcondon]

looks like to resolve the disk space issue, we disabled transactions on HWG

I think you ALSO need a healthy amount of free disk space> . I'm repartitioning my docker to be 300GB from 60GB and I'll try again.

Calling: tripal_chado_install_chado(Upgrade Chado v1.2 to v1.3)
Checking for existing v1.3 tables in v1.2 and fixing bigints...
Incorporating additional changes...
Loading sites/all/modules/contrib/tripal/tripal_chado/chado_schema/default_schema-1.2-1.3-diff.sql...
Upgrade from v1.2 to v1.3 Successful!

Yay!

So, you just need to comment out the transaction on lines 221/244 of tripal_chado.install.inc prior to running and/or free up lots of disk space.

[mpoelchau]

@bradfordcondon can you write up a step-by-step guide for me based on your observations in this thread? I could re-capitulate all your steps, but it seems like there was a lot of trial and error here. I'd like to prioritize this (maybe even do this today).

[bradfordcondon]

OK sure! I'm in the middle of setting up a single repository that will run all of the gists etc/unite all the upgrade issues. I'll put that on hold and do that for you instead.

[mpoelchau]

thanks!

[bradfordcondon]

Tripal has a GUI to run the Chado 1.2 to 1.3 migration. However, it doesn't run smoothly on many sites. There are three main reasons for this:

• Your site has views Tripal didn't expect
• Your site has some views as tables instead
• The database transaction requires more disk space than your site has free

executive summary

• preparation of database by dropping views (see shell script below)
  • Create drop script
  • run drop script
• Submit upgrade job at /admin/tripal/storage/chado/install
• If size error: resize disk if possible
• If still size error: uncomment transaction in install file

touch generate_drop_file.sql
# copy the drop script in the "dropping views section" into said file
drush sql-query --file=generate_drop_file.sql --result-file=drop_script.sql
drush sql-query --file=drop_script.sql
drush sql-query "DROP TABLE IF EXISTS chado.all_feature_names CASCADE;"

details

dropping views

The first thing to do is create a drop script. The below SQL will output an SQL script that will drop all the chado and so (sequence ontology) views from your site (something the migration script attempts to do at the start.)

set search_path=public,so,frange,genetic_code;
select 'drop view "' || viewname || '" cascade;'
from pg_views where schemaname = 'so';

select 'drop view "' || viewname || '" cascade;'
from pg_views where schemaname = 'chado';

We can use drush to generate the drop query like so: drush sql-query --file generate_drop_file.sql result-file drop_script.sql where generate_drop_file is our input drop script above.

Once you've generated the drop script, you should run it. All together it will look like this:

drush sql-query --file=generate_drop_file.sql --result-file=drop_script.sql
drush sql-query --file=drop_script.sql

dropping tables that SHOULD be views

Oddly enough the chado.all_feature_names table is SUPPOSED to be a view according to the Chado documentation. We therefore need to drop it, otherwise the script will complain when it tries to drop the view!

set search_path=chado;
 DROP TABLE IF EXISTS all_feature_names CASCADE;

Disabling the transaction

You are now ready to try running the migration! It's located at/admin/tripal/storage/chado/install. You should see 1.2 as the currently installed version- select "upgrade existing Chado v1.2 to v1.3 (no data is lost)". You will see lots of warnings/notices informing you about what this ugpgrade will. Notably, you should plan on redefining any custom materialized views after running the migration to hold big integers instead of integers.

If you run the migration and get the bellow error, you have run out of space!

Disk full: 7 ERROR:  could not extend file "base/40960/269560.3": No space left on deviceHINT:  Check free disk space.:ALTER TABLE feature     ALTER feature_id TYPE  bigint,    ALTER dbxref_id TYPE bigint,    ALTER organism_id TYPE bigint,    ALTER seqlen TYPE bigint,    ALTER type_id TYPE bigint;

You have two strategies to overcome this:

1) Increase your disk space 2) Disable the transaction in the code

If 1) doesnt work or isn't possible, just comment out the transaction in the tripal_chado.install.inc file (tripal/tripal_chado/includes/tripal_chado.install.inc), which is currently on lines 221/244.

Before

$transaction = db_transaction();
try {
  if ($action == 'Install Chado v1.3') {
    tripal_chado_install_chado_1_3();
    chado_query($vsql, [':version' => '1.3']);
  }
  elseif ($action == 'Upgrade Chado v1.2 to v1.3') {
    tripal_chado_upgrade_chado_1_2_to_1_3();
    chado_query($vusql, [':version' => '1.3']);
  }
  elseif ($action == 'Install Chado v1.2') {
    tripal_chado_install_chado_1_2();
    chado_query($vsql, [':version' => '1.2']);
  }
  elseif ($action == 'Upgrade Chado v1.11 to v1.2') {
    tripal_chado_upgrade_chado_1_11_to_1_2();
    chado_query($vsql, [':version' => '1.2']);
  }
  elseif ($action == 'Install Chado v1.11') {
    tripal_chado_install_chado_1_11();
  }
} catch (Exception $e) {
  $transaction->rollback();
  tripal_chado_install_done();
  tripal_report_error('tripal_chado', TRIPAL_ERROR, $e->getMessage(), ['print' => TRUE]);
  return FALSE;
}

After

//$transaction = db_transaction();
try {
  if ($action == 'Install Chado v1.3') {
    tripal_chado_install_chado_1_3();
    chado_query($vsql, [':version' => '1.3']);
  }
  elseif ($action == 'Upgrade Chado v1.2 to v1.3') {
    tripal_chado_upgrade_chado_1_2_to_1_3();
    chado_query($vusql, [':version' => '1.3']);
  }
  elseif ($action == 'Install Chado v1.2') {
    tripal_chado_install_chado_1_2();
    chado_query($vsql, [':version' => '1.2']);
  }
  elseif ($action == 'Upgrade Chado v1.11 to v1.2') {
    tripal_chado_upgrade_chado_1_11_to_1_2();
    chado_query($vsql, [':version' => '1.2']);
  }
  elseif ($action == 'Install Chado v1.11') {
    tripal_chado_install_chado_1_11();
  }
} catch (Exception $e) {
//  $transaction->rollback();
  tripal_chado_install_done();
  tripal_report_error('tripal_chado', TRIPAL_ERROR, $e->getMessage(), ['print' => TRUE]);
  return FALSE;
}

[mpoelchau]

Note to my future self - the 'executive summary' steps worked. Will need to keep this open for when we set up the final copy of the database.

[bradfordcondon]

Note: this is part of the "T2-to-T3" migration repo. It does require some manual steps.

[mpoelchau]

@Ferrisx4 can you confirm the chado 1.2 to 1.3 upgrade is part of the current migration process, and if so, close this issue?

[Ferrisx4]

Dropping the views is part of the pre-migration scripts: generate_drop_file.sql.

These scripts get called before initiating the Chado 1.2 to 1.3 upgrade.