ERROR: (gcloud.sql.instances.patch) HTTPError 409

Google Cloud Database
1

We are trying to delete one of our cloud sql instance however everytime we try we get the error below.

ERROR: (gcloud.sql.instances.patch) HTTPError 409: The instance or operation is not in an appropriate state to handle the request.

We tried researching and found this link https://serverfault.com/questions/944326/google-cloud-sql-instance-is-under-maintenance-mode-for-an-exceedingly-long-ti

We don’t have a support plan currently active but we are looking if we can resolve it before escalating to google support. Thank you

2

Cloud SQL instances are occasionally placed in maintenance mode by Google automatically for updates or other necessary tasks. During maintenance, instances cannot be deleted.

Another possibility is that the instance is in a state that prevents it from being deleted, such as the running state.

To check the state of your Cloud SQL instance, you can use the following command:

gcloud sql instances describe <instance_name>

This command will display the state of the instance, as well as any other relevant information.

If the instance is under maintenance, you will need to wait until maintenance is complete before you can delete it. If the instance is in a state that prevents it from being deleted, you will need to take steps to change the state of the instance before you can delete it.

Here are some additional things you can try to resolve the issue:

  • Wait for at least 10-15 minutes and try again.
  • Check the Cloud SQL API documentation for more information about the error message you are receiving.
3

@ms4446

Here is the output

# gcloud sql instances describe <instance_name>
# output
availableMaintenanceVersions:
- MYSQL_8_0_31.R20230624.00_05
backendType: SECOND_GEN
connectionName: masked:us-central1:croc-prod-repl-slave-2
createTime: '2023-07-19T09:22:15.473Z'
databaseInstalledVersion: MYSQL_8_0_31
databaseVersion: MYSQL_8_0
etag: 2d70d53aebe47f8e6fc30237e1a3aeec69c2f6a076950586a1a100d950949e61
gceZone: us-central1-f
instanceType: READ_REPLICA_INSTANCE
ipAddresses:
- ipAddress: masked
type: PRIMARY
- ipAddress: masked
type: OUTGOING
- ipAddress: masked
type: PRIVATE
kind: sql#instance
maintenanceVersion: MYSQL_8_0_31.R20230624.00_00
masterInstanceName: masked:croc-prod-repl-slave-2-master
name: croc-prod-repl-slave-2
project: cloud-platform-us-prd
region: us-central1
replicaConfiguration:
failoverTarget: false
kind: sql#replicaConfiguration
selfLink: https://sqladmin.googleapis.com/sql/v1beta4/projects/masked/instances/croc-prod-repl-slave-2
serverCaCert:
cert: |-
-----BEGIN CERTIFICATE-----
masked
-----END CERTIFICATE-----
certSerialNumber: '0'
commonName: C=US,O=Google\, Inc,CN=Google Cloud SQL Server CA,dnQualifier=masked
createTime: '2023-07-19T09:22:08.602Z'
expirationTime: '2033-07-16T09:23:08.602Z'
instance: croc-prod-repl-slave-2
kind: sql#sslCert
sha1Fingerprint: masked
serviceAccountEmailAddress: masked@gcp-sa-cloud-sql.iam.gserviceaccount.com
settings:
activationPolicy: ALWAYS
availabilityType: ZONAL
backupConfiguration:
backupRetentionSettings:
retainedBackups: 7
retentionUnit: COUNT
enabled: false
kind: sql#backupConfiguration
startTime: 03:00
transactionLogRetentionDays: 7
connectorEnforcement: NOT_REQUIRED
crashSafeReplicationEnabled: true
dataDiskSizeGb: '100'
dataDiskType: PD_SSD
databaseFlags:
- name: key_buffer_size
value: '16384'
- name: bulk_insert_buffer_size
value: '65536'
- name: max_allowed_packet
value: '2097152'
- name: tmp_table_size
value: '16384'
- name: max_heap_table_size
value: '16384'
- name: thread_cache_size
value: '100'
- name: concurrent_insert
value: NEVER
deletionProtectionEnabled: false
edition: ENTERPRISE
insightsConfig: {}
ipConfiguration:
authorizedNetworks:
- kind: sql#aclEntry
name: new-bastion
value: masked
enablePrivatePathForGoogleCloudServices: false
ipv4Enabled: true
privateNetwork: projects/masked/global/networks/masked
kind: sql#settings
locationPreference:
kind: sql#locationPreference
zone: us-central1-a
pricingPlan: PER_USE
replicationType: SYNCHRONOUS
settingsVersion: '215'
storageAutoResize: true
storageAutoResizeLimit: '0'
tier: db-custom-1-3840
userLabels:
goog-dms-instance: 'true'
state: MAINTENANCE

We tried connecting to this instance a couple of time but had the same error message. The issue started last August 21 of this year and this instance was always in Maintenance status.

4

Based on the output of the gcloud sql instances describe command, the instance croc-prod-repl-slave-2 is currently in the MAINTENANCE state. This means that it is undergoing maintenance and cannot be accessed.

The reasons for maintenance can vary, including:

  • Updating to a newer version of MySQL.
  • Patching for security vulnerabilities.
  • Reconfigurations.

Maintenance typically completes within a few hours, but the duration can vary depending on the nature of the maintenance. If it extends beyond the expected duration, you can consider reaching out to Google Cloud support.

In the meantime, you can attempt to connect to the master instance of the replication group, croc-prod-repl-slave-2-master. As the primary instance in the replication group, it should be accessible:

gcloud sql instances connect croc-prod-repl-slave-2-master

Once connected to the master instance, you can perform necessary operations. However, be cautious and ensure you have recent backups before making significant changes, as these will be replicated to the replicas once they are back online.

After the maintenance on the replica instance croc-prod-repl-slave-2 is complete, you should regain access.