Skip to content

Instantly share code, notes, and snippets.

@mousavian
Created June 17, 2015 02:17
Show Gist options
  • Save mousavian/d68bcd903207366c1bfd to your computer and use it in GitHub Desktop.
Save mousavian/d68bcd903207366c1bfd to your computer and use it in GitHub Desktop.
A tool to cleanup openstack nova database from all deleted instances
#run this to keep the output:
#$ cleaner.py > cleaner.log
import mysql.connector
uuids = []
class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):
def _row_to_python(self, rowdata, desc=None):
row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc)
if row:
return dict(zip(self.column_names, row))
return None
cnx = mysql.connector.connect(host="", user="", passwd="", db="nova")
cur1 = cnx.cursor(cursor_class=MySQLCursorDict)
cur2 = cnx.cursor(cursor_class=MySQLCursorDict)
cur3 = cnx.cursor(cursor_class=MySQLCursorDict)
cur4 = cnx.cursor(cursor_class=MySQLCursorDict)
cur5 = cnx.cursor(cursor_class=MySQLCursorDict)
cur = cnx.cursor(cursor_class=MySQLCursorDict)
cur.execute("SELECT * FROM `instances` WHERE `deleted_at` IS NOT NULL")
for row in cur.fetchall():
uuids.append( row['uuid']);
cur5.execute("SET FOREIGN_KEY_CHECKS = 0;")
for uuid in uuids:
cur.execute("show tables");
for table_name in cur.fetchall():
if (table_name['Tables_in_nova'] != 'services'):
cur2.execute("SELECT column_name FROM information_schema.columns WHERE TABLE_SCHEMA='nova' and table_name='%s';" % table_name['Tables_in_nova'])
for column_name in cur2.fetchall():
cur3.execute("SELECT * from `%s` WHERE `%s` like '%s';" % (table_name['Tables_in_nova'], column_name['column_name'], uuid))
lastq = cur3.fetchall()
if(len(lastq) > 0):
print "%s => %s => %s" % (table_name['Tables_in_nova'], column_name['column_name'], uuid)
#print "DELETE FROM `%s` WHERE `%s` like '%s';" % (table_name['Tables_in_nova'], column_name['column_name'], uuid)
cur4.execute("DELETE FROM `%s` WHERE `%s` like '%s';" % (table_name['Tables_in_nova'], column_name['column_name'], uuid))
cnx.commit()
print uuid
print "___________________________________"
cur5.execute("SET FOREIGN_KEY_CHECKS = 1;")
cnx.close()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment