Cannot delete Terra workspace due to apparent MySQL error
I am attempting to delete a workspace that contains controlled access TOPMed data (hence me not adding support to the workspace) that is costing about 80 cents a months in storage costs. Unfortunately when I try to delete the workspace, Terra throws this error:
Cannot delete or update a parent row: a foreign key constraint fails (rawls.WORKSPACE_REQUESTER_PAYS, CONSTRAINT FK_WRP_WORKSPACE FOREIGN KEY (WORKSPACE_ID) REFERENCES WORKSPACE (id) ON DELETE NO ACTION ON UPDATE NO ACTION)
I am the one who made the workspace, and I am the only one with access to it, so it's not like I'm deleting a workspace I don't own. It has a Team Calcium authorization domain on it. The full exception that gets printed:
{ "causes": [], "exceptionClass": "com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException", "message": "Cannot delete or update a parent row: a foreign key constraint fails (`rawls`.`WORKSPACE_REQUESTER_PAYS`, CONSTRAINT `FK_WRP_WORKSPACE` FOREIGN KEY (`WORKSPACE_ID`) REFERENCES `WORKSPACE` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)", "source": "rawls", "stackTrace": [ { "className": "sun.reflect.NativeConstructorAccessorImpl", "fileName": "NativeConstructorAccessorImpl.java", "lineNumber": -2, "methodName": "newInstance0" }, { "className": "sun.reflect.NativeConstructorAccessorImpl", "fileName": "NativeConstructorAccessorImpl.java", "lineNumber": 62, "methodName": "newInstance" }, { "className": "sun.reflect.DelegatingConstructorAccessorImpl", "fileName": "DelegatingConstructorAccessorImpl.java", "lineNumber": 45, "methodName": "newInstance" }, { "className": "java.lang.reflect.Constructor", "fileName": "Constructor.java", "lineNumber": 423, "methodName": "newInstance" }, { "className": "com.mysql.jdbc.Util", "fileName": "Util.java", "lineNumber": 425, "methodName": "handleNewInstance" }, { "className": "com.mysql.jdbc.Util", "fileName": "Util.java", "lineNumber": 408, "methodName": "getInstance" }, { "className": "com.mysql.jdbc.SQLError", "fileName": "SQLError.java", "lineNumber": 935, "methodName": "createSQLException" }, { "className": "com.mysql.jdbc.MysqlIO", "fileName": "MysqlIO.java", "lineNumber": 3973, "methodName": "checkErrorPacket" }, { "className": "com.mysql.jdbc.MysqlIO", "fileName": "MysqlIO.java", "lineNumber": 3909, "methodName": "checkErrorPacket" }, { "className": "com.mysql.jdbc.MysqlIO", "fileName": "MysqlIO.java", "lineNumber": 2527, "methodName": "sendCommand" }, { "className": "com.mysql.jdbc.MysqlIO", "fileName": "MysqlIO.java", "lineNumber": 2680, "methodName": "sqlQueryDirect" }, { "className": "com.mysql.jdbc.ConnectionImpl", "fileName": "ConnectionImpl.java", "lineNumber": 2490, "methodName": "execSQL" }, { "className": "com.mysql.jdbc.PreparedStatement", "fileName": "PreparedStatement.java", "lineNumber": 1858, "methodName": "executeInternal" }, { "className": "com.mysql.jdbc.PreparedStatement", "fileName": "PreparedStatement.java", "lineNumber": 2079, "methodName": "executeUpdateInternal" }, { "className": "com.mysql.jdbc.PreparedStatement", "fileName": "PreparedStatement.java", "lineNumber": 2013, "methodName": "executeUpdateInternal" }, { "className": "com.mysql.jdbc.PreparedStatement", "fileName": "PreparedStatement.java", "lineNumber": 5104, "methodName": "executeLargeUpdate" }, { "className": "com.mysql.jdbc.PreparedStatement", "fileName": "PreparedStatement.java", "lineNumber": 1998, "methodName": "executeUpdate" }, { "className": "com.zaxxer.hikari.pool.ProxyPreparedStatement", "fileName": "ProxyPreparedStatement.java", "lineNumber": 61, "methodName": "executeUpdate" }, { "className": "com.zaxxer.hikari.pool.HikariProxyPreparedStatement", "fileName": "HikariProxyPreparedStatement.java", "lineNumber": -1, "methodName": "executeUpdate" }, { "className": "slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4", "fileName": "JdbcActionComponent.scala", "lineNumber": 269, "methodName": "$anonfun$run$3" }, { "className": "slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4", "fileName": "JdbcActionComponent.scala", "lineNumber": 267, "methodName": "$anonfun$run$3$adapted" }, { "className": "slick.jdbc.JdbcBackend$SessionDef", "fileName": "JdbcBackend.scala", "lineNumber": 425, "methodName": "withPreparedStatement" }, { "className": "slick.jdbc.JdbcBackend$SessionDef", "fileName": "JdbcBackend.scala", "lineNumber": 420, "methodName": "withPreparedStatement$" }, { "className": "slick.jdbc.JdbcBackend$BaseSession", "fileName": "JdbcBackend.scala", "lineNumber": 489, "methodName": "withPreparedStatement" }, { "className": "slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4", "fileName": "JdbcActionComponent.scala", "lineNumber": 267, "methodName": "run" }, { "className": "slick.jdbc.JdbcActionComponent$DeleteActionExtensionMethodsImpl$$anon$4", "fileName": "JdbcActionComponent.scala", "lineNumber": 266, "methodName": "run" }, { "className": "slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction", "fileName": "JdbcActionComponent.scala", "lineNumber": 28, "methodName": "run" }, { "className": "slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction", "fileName": "JdbcActionComponent.scala", "lineNumber": 25, "methodName": "run" }, { "className": "slick.basic.BasicBackend$DatabaseDef$$anon$3", "fileName": "BasicBackend.scala", "lineNumber": 276, "methodName": "liftedTree1$1" }, { "className": "slick.basic.BasicBackend$DatabaseDef$$anon$3", "fileName": "BasicBackend.scala", "lineNumber": 276, "methodName": "run" }, { "className": "java.util.concurrent.ThreadPoolExecutor", "fileName": "ThreadPoolExecutor.java", "lineNumber": 1149, "methodName": "runWorker" }, { "className": "java.util.concurrent.ThreadPoolExecutor$Worker", "fileName": "ThreadPoolExecutor.java", "lineNumber": 624, "methodName": "run" }, { "className": "java.lang.Thread", "fileName": "Thread.java", "lineNumber": 748, "methodName": "run" } ] }
Comments
11 comments
Hi Ash O'Farrell,
Thanks for writing in. Can you let us know your billing project and workspace name so we can take a closer look?
Thanks,
Samantha
The workspace is called "Undergrad Controlled Access Tests" and the billing project is anvil-stage-demo.
Hi Ash O'Farrell,
Sorry for the delayed response. This was identified as a bug on our end where deleting a workspace is not deleting some dependent rows as well. Our engineers were able to clear out the offending records so you should be able to successfully delete the workspace now.
Please let me know if you are still experiencing any issues.
Best,
Samantha
Unfortunately the bug has now been transferred to another workspace. This one is called merge-and-subsample, also under anvil-stage-demo, and also managed by the Team Calcium auth domain. Brian Hannafious may have a workflow of the same name and billing project; the one I am trying to delete, I am the owner of.
Hi Ash O'Farrell,
The other workspace has been fixed. You should now be able to delete it.
Please let me know if you continue to have issues.
Best,
Samantha
Unfortunately it seems this issue has returned in another controlled access workspace. It's the one tied to the google bucket fc-secure-aa0b24fa-7774-423d-9136-5525a953881d, which I used for developing the blood pressure GWAS template workspace.
Hi Ash O'Farrell,
This error actually indicates that there are service accounts associated with the workspace that have requester pays enabled. Can you try disabling requester pays for those service accounts and then deleting the workspace?
To do so, you can run this command in a notebook in the workspace you want to delete:
Our engineering team is currently looking into a fix to automatically disable requester pays for service accounts when someone submits a request to delete a workspace so you don't run into this in the future.
Best,
Samantha
Can you clarify what this means here? Does it have to do with billing projects or authorization domains? I'm not super familiar with service accounts. I have seen here in your documentation that they are part of how Terra interacts with GCP but beyond that I know nothing. Just want to know the implications of what I'm doing by performing the interaction you pasted here.
Hi Ash O'Farrell,
Every Terra user has one or more service accounts (one for each of their Billing Project), which are used to access data, including external Google buckets, as well as other Google Cloud Platform resources (VMs that power Cloud Environments and workflows). Requester pays gets automatically enabled on a user's service account in order for them to run certain workflows that use DRS and Martha to pull from requester pays buckets. There's currently some logic that disallows deletion of a workspaces if associated service accounts have requester pays enabled, which is why you receive the error. Running the command I shared will disable requester pays on your service account(s) and will allow you to then delete the workspace.
Best,
Samantha
Will disabling requester pays block me from running workflows involving DRS on this billing project? ie, is this something I have to keep toggling?
Hi Ash O'Farrell,
No, this happens automatically so you wouldn't have to toggle anything on/off.
Best,
Samantha
Please sign in to leave a comment.