Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

it:linux:mysqldump_fehler_automatisch_beheben [2015/12/24 15:26]
it:linux:mysqldump_fehler_automatisch_beheben [2015/12/24 15:26] (aktuell)
Zeile 1: Zeile 1:
 +====== Mysqldump-Fehler automatisch beheben ======
 +===== Anzeichen =====
  
 +Aus unerfindlichen Gruenden ist mysqldump der Meinung, dass in meiner Datenbank viel kaputt ist, und will keinen Dump mehr machen.
 +
 +Man versucht <code bash>​mysqldump -uroot -p --all-databases -ralldb.sql</​code>​ und bekommt 3 unterschiedliche Fehlermeldungen:​
 +<​file>​Output:​ mysqldump: Got error: 1017: Can't find file: '<​filename>'​ (errno: 2) when using LOCK TABLES</​file>​
 +<​file>​Output:​ mysqldump: Got error: 1102: Incorrect database name '<​databasename>'​ when selecting the database</​file>​
 +<​file>​Output:​ mysqldump: Got error: 1146: Table '<​databasename>​.<​tablename>'​ doesn'​t exist when using LOCK TABLES</​file>​
 +
 +===== Loesung =====
 +Folgendes Pythonscript behandelt die Fehler wie folgt:
 +  * 1146: *.frm Datei (passende!) suchen und loeschen
 +  * 1102: Datenbank Unterverzeichniss loeschen
 +  * 1017: Tabelle per SQL Befehl DROP TABLE loeschen
 +
 +<code python>
 +import os
 +import re
 +import subprocess
 +import fnmatch
 +import shutil
 +
 +def invalidDatabase(output):​
 +  matches = re.findall(r"​Incorrect database name '​([a-zA-Z0-9_\.]*)'",​ output)
 +  if len(matches)==0:​
 +    return
 +  for root,​dirnames,​filenames in os.walk('/​var/​lib/​mysql/'​):​
 +    for dirname in fnmatch.filter(dirnames,​ matches[0]):​
 +      print "​deleting ", root+dirname
 +      shutil.rmtree(root+dirname)
 +
 +def invalidTable(output):​
 +  matches = re.findall(r"​Table '​([a-zA-Z0-9_-]*)[\.]*([a-zA-Z0-9_]*)'",​ output)
 +  dirname = ''​
 +  if len(matches) == 0:
 +    return
 +  if len(matches[0]) == 1:
 +    tablename = matches[0]
 +  if len(matches[0]) == 2:
 +    dirname = matches[0][0]
 +    tablename = matches[0][1]
 +  print "Found table",​ matches, '​dirname',​ dirname, '​tablename',​ tablename
 +
 +  for root,​dirnames,​filenames in os.walk('/​var/​lib/​mysql/'​):​
 +    for filename in fnmatch.filter(filenames,​ tablename + '​.frm'​):​
 +      if dirname != '':​
 +        if dirname == root.split('/'​)[-1]:​
 +          targetFile = root + '/'​ + filename
 +          print "Will delete",​ targetFile
 +          os.remove(targetFile)
 +      else:
 +        targetFile = root + '/'​ + filename
 +        print "Will delete",​ targetFile
 +        os.remove(targetFile)
 +
 +def cantFindFile(output):​
 +  matches = re.findall(r"​Can'​t find file: '​([a-zA-Z0-9_]*)'",​ output)
 +  if len(matches) == 0:
 +    return
 +  tablename = matches[0]
 +  for root,​dirnames,​filenames in os.walk('/​var/​lib/​mysql/'​):​
 +    for filename in fnmatch.filter(filenames,​ tablename + '​.frm'​):​
 +      dirname = root.split('/'​)[-1]
 +      print "​Dropping table",​ tablename, "​in",​ dirname
 +      os.system('​mysql -uadmin -p`cat /​etc/​psa/​.psa.shadow` '​+dirname+'​ -e "DROP TABLE ' + tablename + ';"'​)
 +
 +while True:
 +  cmd = ['​mysqldump',​ '​-uadmin',​ '​-p<​password>',​ '​-ralldb.sql',​ '​--all-databases'​]
 +  p = subprocess.Popen(cmd,​ stderr=subprocess.PIPE)
 +  p.wait()
 +  output = p.stderr.read()
 +  print "​Output:",​ output
 +  invalidTable(output)
 +  invalidDatabase(output)
 +  cantFindFile(output)
 +
 +</​code>​