Thanks again rick.
As i didn't want to spent even 5 minutes fixing lot of tables. Here is something that maybe you or others can use, a python script that will fix an entire database using a sequence like we tried. It's not very well coded but worked for me.
Requirements:
Python 2.5.* > (?)
MySQLdb: Mysql for Python (http://sourceforge.net/projects/mysql-python/)
Usage:
|| Fix tables script ||
Options:
-u - Database User
-p - Password
-c - Host to connect
-d - Database to connect
-f - Change sequence separated by commas (ex: -f blob,utf8,latin1)
----------------------------
-h - This help screen
Warning:
1. This won't touch any PRIMARY index
2. Some index values/orders can be affected.
You may prefer to use this with a backup and export fixed data instead use it directly over the original database
Example:
python fixtables.py -u root -p 12345 -c localhost -d mydatabase -f blob,utf8,latin1,blob,cp1256,utf8
Script:
#!/usr/bin/python
#-*- coding: utf8 *-
# WARNING: GPL+BEER VARIATION LICENSED IF YOU AREN'T RICK JAMES (THAT MADE THIS POSSIBLE) AND YOU USE, MODIFY OR SHARE THIS YOU OWE ME A BEER!
#
# Mail:
felipeariasf@gmail.com
#
# Ugly style, no comments and very fast coded: No support, guarantee, future versions... use this under your own risk.
import sys
import MySQLdb
import getopt, sys
def start(base,fix):
keys={}
queryhnd=base.query("show tables")
result=base.store_result()
for i in xrange( result.num_rows()):
array=result.fetch_row()[0][0]
queryhnd2=base.query("show full columns from "+array)
result2=base.store_result()
for i in xrange(result2.num_rows()):
array2=result2.fetch_row()[0]
if array2[1].find("text")>-1 or array2[1].find("varchar")>-1:
queryhnd3=base.query("show index from "+array)
result3=base.store_result()
arrayindex=[]
c=0
for i in xrange(result3.num_rows()):
array3=result3.fetch_row()[0]
if array3[4]==array2[0]:
if array3[10]=="BTREE" and array3[2]!="PRIMARY":
base.query("drop index "+array3[2]+" on "+array)
arrayindex=array3
break
else: c+=1
if arrayindex!=[] or c==result3.num_rows():
try:
for query in fix.split(","):
if query.find("blob")>-1:
base.query("alter table "+array+" modify "+array2[0]+" "+query+";")
else:
base.query("alter table "+array+" modify "+array2[0]+" "+array2[1]+" charset "+query+";")
print "Column repaired: "+array2[0]+" on "+array
except:
print "Error with "+array+" column "+array2[0]
if arrayindex!=[]:
base.query("CREATE INDEX "+arrayindex[2]+" USING BTREE ON "+array+" ("+arrayindex[4]+");")
def usage():
print """
|| Fix tables script ||
Options:
-u - Database User
-p - Password
-c - Host to connect
-d - Database to connect
-f - Change sequence separated by commas (ex: -f blob,utf8,latin1)
----------------------------
-h - This help screen
Warning:
1. This won't touch any PRIMARY index
2. Some index values can be affected.
You may preffer to use this with a backup and export fixed data instead use it directly to the original database
"""
def main():
global base
try:
opts, args = getopt.getopt(sys.argv[1:], "hu:p:c:d:f:v", ["help"])
except getopt.GetoptError, err:
print str(err)
usage()
sys.exit(2)
user = None
password = None
host = None
database = None
fix = None
verbose = False
for o, a in opts:
if o == "-v":
verbose = True
elif o in ("-h", "--help"):
usage()
sys.exit()
elif o in ("-u", "--user"):
user = a
print user
elif o in ("-p", "--password"):
password = a
elif o in ("-c", "--conhost"):
host = a
elif o in ("-d", "--database"):
database = a
elif o in ("-f", "--fix"):
fix = a
else:
assert False, "unhandled option"
if user!=None and host!=None and database !=None and fix!=None and password!=None:
base=MySQLdb.connect(host=host,user=user,passwd=password, db=database)
base.set_character_set("utf8")
start(base,fix)
else: usage()
if __name__ == "__main__":
main()