Re: Problem with arabic charset
Posted by: Felipe Arias
Date: February 12, 2011 08:03PM

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()

Options: ReplyQuote


Subject
Views
Written By
Posted
4590
January 25, 2011 07:17PM
1556
January 27, 2011 02:57PM
1677
January 29, 2011 11:57PM
2576
February 08, 2011 11:08PM
2356
February 10, 2011 10:05PM
1462
February 10, 2011 10:09PM
1442
February 10, 2011 10:41PM
Re: Problem with arabic charset
2414
February 12, 2011 08:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.