Unknown INTERNAL_ERROR trying to remove Google Cloud SQL postgres database

Question

I have a Google SQL instance created and some databases. I need to delete one database but, for some reason, currently I get an unknown error.

Here is the debug output:

$ gcloud sql databases delete myDatabase -i myInstance --verbosity debug

DEBUG: Running [gcloud.sql.databases.delete] with arguments: [--
instance: "myInstance", --verbosity: "debug", DATABASE: "myDatabase"]
The database will be deleted. Any data stored in the database will be
destroyed. You cannot undo this action.

Do you want to continue (Y/n)?

Deleting Cloud SQL database...failed.
DEBUG: (gcloud.sql.databases.delete) INTERNAL_ERROR
Traceback (most recent call last):
  File "~/google-cloud-sdk/lib/googlecloudsdk/calliope/cli.py", line 797, in Execute
    resources = calliope_command.Run(cli=self, args=args)
  File "~/google-cloud-sdk/lib/googlecloudsdk/calliope/backend.py", line 757, in Run
    resources = command_instance.Run(args)
  File "~/google-cloud-sdk/lib/surface/sql/databases/delete.py", line 84, in Run
    'Deleting Cloud SQL database')
  File "~/google-cloud-sdk/lib/googlecloudsdk/api_lib/sql/operations.py", line 81, in WaitForOperation
    sleep_ms=_BaseOperations._INITIAL_SLEEP_MS)
  File "~/google-cloud-sdk/lib/googlecloudsdk/core/util/retry.py", line 226, in RetryOnResult
    if not should_retry(result, state):
  File "~/google-cloud-sdk/lib/googlecloudsdk/api_lib/sql/operations.py", line 65, in ShouldRetryFunc
    raise result
OperationError: INTERNAL_ERROR
ERROR: (gcloud.sql.databases.delete) INTERNAL_ERROR

In Google doc there isn't any entry about it. Does anyone has this error?

Any help will be greatly appreciated.

Thanks!

The only way for deleting the database that works for me is connecting to the instance a run a "DROP DATABASE". However, from gcloud or web console it doesn't work. — CJ. Mateos

neilH neilH · Accepted Answer · 2018-02-22T15:28:23

My hunch is that the database still has open connections which are preventing you from deleting it.

I think this issue is more common with postgres databases than MySQL databases because with postgres there is more likely to be lagging connections (this is a postgres issue rather than GCP issue).

To test this, connect to the shell of the Cloud SQL instance and run the following command in order to prevent any future connections to the database:

REVOKE CONNECT ON DATABASE DATABASE_NAME FROM public;

Then, connect to the database you would like to delete, and terminate all connections to this database apart from your current one by issuing the following command:

SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();

Exit the postgres shell.

I would also suggest disabling automatic backups. If an automatic backup is taking place it can produce an exception if you simultaneously try to delete the database.

Now try the gcloud sql databases delete command again.

Unknown INTERNAL_ERROR trying to remove Google Cloud SQL postgres database

3 Answers