Pivotal Knowledge Base


How to Check the service_instance Entries in the mysql_broker Database


  • Pivotal Cloud Foundry all versions
  • Internal MySQL for Elastic Runtime 


The purpose of this article is to describe the procedure you can use to manually check if the mysql_broker database is full. The main cause of this is service_instances not being correctly removed from the database. This is normally due to the incorrect command being used when removing a service instance. To avoid this you should use the following command:

cf delete-service SERVICE_INSTANCE [-f]

The article will also show you how to manually delete them if required. 


The error is caused by a build of entries in the mysql_broker database. You will be unable to create a new service instance and will receive an error like the one below.

error code: 10001 service broker error: service capacity has been reached
error code: 270003 service broker error: url is already taken


In order to see the entries in the mysql_broker database you can manually access it with the following procedure:

  1. Bosh login using the director login details
  2. Target the correct bosh deployment 
  3. Bosh ssh into the mysql/0 vm and issue sudo -i
  4. cd /var/vcap/jobs/mysql/config/ and issue ls
  5. cat mylogin.cnf (You need the ip_address for the next step)
  6. mysql -h <ip_address_in_mylogin> -u <username> -p<password>
  7. show databases;
  8. use mysql_broker;
  9. show tables;
  10. select * from service_instances (This will show the service instances that already exist. You can try and create a new one using the cf create-service command, to see if it gets added to the database) 

If you find that the mysql_broker database is full you can manually delete the stale entries using the following procedure: 

  1. begin; (This will begin a transaction and allow you to undo a command if you need to. If you don't do this and make a mistake you cannot undo it.)
  2. delete from service_instances where db_name = "<db_name>"; (This will delete the entry from the database. You can include multiple entries at once.)
    ** delete from service_instance; (This will remove all of the service instances at once. Only use this if you are sure you want to delete all of the entries.)**
  3. select * from service_instance; (double check the instances have been removed)
    ** rollback; (At this point if you find you have made a mistake then you can issue this command and the deleted data will be retrieved.)**
  4. commit; (Only issue this command once you are certain you have removed the correct data as this completes the transaction.)



Powered by Zendesk