{"action":"created","issue":{"url":"https://api.github.com/repos/genome/gms/issues/181","labels_url":"https://api.github.com/repos/genome/gms/issues/181/labels{/name}","comments_url":"https://api.github.com/repos/genome/gms/issues/181/comments","events_url":"https://api.github.com/repos/genome/gms/issues/181/events","html_url":"https://github.com/genome/gms/issues/181","id":62167822,"number":181,"title":"ERROR:  insert or update on table \"instance\"","user":{"login":"gatoravi","id":764948,"avatar_url":"https://avatars.githubusercontent.com/u/764948?v=3","gravatar_id":"","url":"https://api.github.com/users/gatoravi","html_url":"https://github.com/gatoravi","followers_url":"https://api.github.com/users/gatoravi/followers","following_url":"https://api.github.com/users/gatoravi/following{/other_user}","gists_url":"https://api.github.com/users/gatoravi/gists{/gist_id}","starred_url":"https://api.github.com/users/gatoravi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gatoravi/subscriptions","organizations_url":"https://api.github.com/users/gatoravi/orgs","repos_url":"https://api.github.com/users/gatoravi/repos","events_url":"https://api.github.com/users/gatoravi/events{/privacy}","received_events_url":"https://api.github.com/users/gatoravi/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/genome/gms/labels/bug","name":"bug","color":"fc2929"},{"url":"https://api.github.com/repos/genome/gms/labels/testing%20merge","name":"testing merge","color":"fbca04"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":13,"created_at":"2015-03-16T18:17:43Z","updated_at":"2015-03-19T22:30:58Z","closed_at":null,"body":"The Pindel step in the somatic-variation workflow dies with this error,\r\n```\r\n    451 2015-03-16 17:29:40+0000 ip-172-31-19-8: DBD::Pg::st execute failed: ERROR:  insert or update on table \"instance\" violates foreign key constraint \"instance_peer_instance_id_fkey\"\r\n    452 2015-03-16 17:29:40+0000 ip-172-31-19-8: DETAIL:  Key (peer_instance_id)=(cb0287f34d074a78877863ccb749dedc) is not present in table \"instance\". at /opt/gms/RGTBV26/sw/ur/lib/UR/DBI.§\r\n    453 2015-03-16 17:29:40+0000 ip-172-31-19-8: 2015/03/16 17:29:40 Workflow::DataSource::InstanceSchema id(Workflow::DataSource::InstanceSchema): Workflow::DataSource::InstanceSchema: Err§\r\n    454 2015-03-16 17:29:40+0000 ip-172-31-19-8:  INSERT INTO workflow.instance (workflow_plan_id,workflow_instance_id,peer_instance_id,parent_instance_id,parent_execution_id,parallel_index§\r\n    455 2015-03-16 17:29:40+0000 ip-172-31-19-8: PARAMS: 'e09177aaa6784d5884ea498e4d1a6558', 'fe9cf90e320d44048342597d075a7b75', 'cb0287f34d074a78877863ccb749dedc', (undef), (undef), '14', §\r\n    456 2015-03-16 17:29:40+0000 ip-172-31-19-8:  024c6f37ca0f44d89ea714ec3a515c65^@^@^@^Rprevious_result_id^D^C^@^@^@^B^D^Q&Genome::Model::Build::SomaticVariation^C^@^@^@^P\r\n```\r\n\r\n\r\nThis is a violation of the constraint - \"instance_peer_instance_id_fkey\".\r\n\r\nNote - This constraint is absent in the latest schema dump, this might just be due to TGI having moved on from workflow to flow?"},"comment":{"url":"https://api.github.com/repos/genome/gms/issues/comments/83787042","html_url":"https://github.com/genome/gms/issues/181#issuecomment-83787042","issue_url":"https://api.github.com/repos/genome/gms/issues/181","id":83787042,"user":{"login":"gatoravi","id":764948,"avatar_url":"https://avatars.githubusercontent.com/u/764948?v=3","gravatar_id":"","url":"https://api.github.com/users/gatoravi","html_url":"https://github.com/gatoravi","followers_url":"https://api.github.com/users/gatoravi/followers","following_url":"https://api.github.com/users/gatoravi/following{/other_user}","gists_url":"https://api.github.com/users/gatoravi/gists{/gist_id}","starred_url":"https://api.github.com/users/gatoravi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gatoravi/subscriptions","organizations_url":"https://api.github.com/users/gatoravi/orgs","repos_url":"https://api.github.com/users/gatoravi/repos","events_url":"https://api.github.com/users/gatoravi/events{/privacy}","received_events_url":"https://api.github.com/users/gatoravi/received_events","type":"User","site_admin":false},"created_at":"2015-03-19T22:30:58Z","updated_at":"2015-03-19T22:30:58Z","body":"Some helpful info from Tony to help us debug this,\r\nThe short answer is that the MetaDB is used at commit time to deduce what the database schema looks like so it can construct the SQL needed to save all your changes.  When UR needs to load objects from the MetaDB, it looks at the date of the sqlite3 database file and the dump file.  If the dump is newer, then it repopulates the database file from the dump.\r\n\r\nThe class definition tells UR what database table to save to using the table_name attribute.  When it's time to save, UR looks up a UR::DataSource::RDBMS::Table object (backed by the dd_table table in the MetaDB) that goes with that class's table.  If it finds one, then it believes the MetaDB is the truth about that class's table, columns and constraints.\r\n\r\nIf the Table object is not found, then UR asks the database what that table's columns and constraints look like, then creates a UR::DataSource::RDBMS::Table object (and associated Column and Constraint objects) that exist only during the life of the program - they're not saved to the MetaDB.\r\n\r\nUR then uses these MetaDB objects to determine what columns the table has in order to write the SQL for saving.  It uses the FK constraint objects to determine the order to save to to satisfy the constraints.\r\n\r\nWhat I think is going on is there's a constraint in the DB to ensure the peer_instance_id column in the workflow_instance table also appears in that same table with the same workflow_instance_id.  There's a record for the workflow_instance table in the MetaDB, so UR believes the MetaDB has the complete truth about the workflow_instance table.\r\n\r\nWhen it looks up the FK constraints in the MetaDB, it's not finding all the info it needs to satisfy the constraint in the DB; probably because it's looking up table_name \"workflow.workflow_instance\" when it's in the metaDB as \"workflow_instance\".  You could verify this by running it with the env var UR_DBI_MONITOR_SQL=1 and look for a SELECT statement looking up something from the dd_table table filtered by table_name.\r\n\r\nOne thing that doesn't make sense is that if it could find the Table MetaDB object, why can't it also find the FKConstraint objects?\r\n\r\n> Is the dump file writeable by the builds as well?\r\n\r\nThe MetaDB is just another database, like all the other databases UR can talk to.  In practice, changes to the MetaDB objects only happens with you run \"ur update classes-from-db\".  When saving objects from other namespaces like Genome, it only loads or __define__s MetaDB objects, never changing them or creating them, so the MetaDB (and its dump) don't change.\r\n"}}