Python MySQLdb 模块，Warning() 实例源码
我们从Python开源项目中，提取了以下29个代码示例，用于说明如何使用MySQLdb.Warning()。
def insert_pagelength(self, source_article_id, screen_positions_1920_1080, zip_file_path):
data={}
data['source_article_id'] = source_article_id
if screen_positions_1920_1080 is not None:
data['page_length_1920_1080'] = screen_positions_1920_1080
else:
data['page_length_1920_1080'] = None
#print data
sql = "INSERT INTO page_length (id, page_length_1920_1080) VALUES" \
"(%(source_article_id)s, %(page_length_1920_1080)s);"
try:
self.cursor.execute(sql, data)
except (MySQLdb.Error, MySQLdb.Warning), e:
print ('FAIL: Data caused warning or error "%s" for source_article_id: "%s"', data, source_article_id)
print 'FAIL: EXCEPTION:', e
print zip_file_path
def insert_pagelength(self, source_article_id, screen_positions_1920_1080, zip_file_path):
data={}
data['source_article_id'] = source_article_id
if screen_positions_1920_1080 is not None:
data['page_length_1920_1080'] = screen_positions_1920_1080
else:
data['page_length_1920_1080'] = None
#print data
sql = "INSERT INTO redirects_candidates_page_length (id, page_length_1920_1080) VALUES" \
"(%(source_article_id)s, %(page_length_1920_1080)s);"
try:
self.cursor.execute(sql, data)
except (MySQLdb.Error, MySQLdb.Warning), e:
print ('FAIL: Data caused warning or error "%s" for source_article_id: "%s"', data, source_article_id)
print 'FAIL: EXCEPTION:', e
print zip_file_path
def ChangeMaster(self,host,port):
'''????'''
repluser,replpassword,ssl_ca,ssl_cert,ssl_key = GetConf().GetReplAcount()
try:
sql = 'reset slave all;'
print self.host
try:
self.mysql_cur.execute(sql)
except:
self.mysql_cur.execute('stop slave')
self.mysql_cur.execute(sql)
change_sql = 'change master to master_host="%s",master_port=%s,master_user="%s",master_password="%s",master_auto_position=1 for channel "default"' % (host,int(port),repluser,replpassword)
self.mysql_cur.execute(change_sql)
return True
except MySQLdb.Warning,e:
start_sql = 'start slave'
self.mysql_cur.execute(start_sql)
self.mysql_cur.execute('set global read_only=1;')
logging.warning('Change master to %s state : Warning' % host)
logging.warning(traceback.format_exc())
return True
except MySQLdb.Error,e:
logging.error('Change master to %s state : Error' % host)
logging.error(traceback.format_exc())
return False
def query_database(self, query):
"""
Perform a database query
Args:
query (str): The SQL query
Returns:
list: Mysql Rows
"""
try:
self.cursor.execute(query)
return self.cursor.fetchall()
except MySQLdb.Error as err:
# print("Failed executing query: {}".format(err))
return 0
except MySQLdb.Warning as wrn:
return 0
def __execute_query(self, sql):
with warnings.catch_warnings():
warnings.simplefilter('error', MySQLdb.Warning)
try:
self.__cur.execute(sql)
return True
except MySQLdb.Error, e:
print "An Error occured running query. %s" %e
#print sql;
return False
except MySQLdb.Warning, e:
print "An Warning occured running query. %s" %e
return True
except MySQLdb.ProgrammingError, e:
print "A ProgrammingError occured running query. %s" %e
exit(1)
return False
def ConnecttoDB(self, params):
try:
conn = \
MySQLdb.connect(host = params[0], \
user = params[1], \
passwd = params[2], \
db = params[3], \
port = params[4], \
charset = params[5], \
cursorclass = MySQLdb.cursors.DictCursor)
return conn
except MySQLdb.Warning, w:
print "Warning:%s" % str(w)
return None
except MySQLdb.Error, e:
print "Error:%s" % str(e)
return None
def queryDB(self):
sql = """
show variables like 'max_connections';
"""
try:
cursor = self.conn.cursor()
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
print row
except MySQLdb.Warning, w:
print "Warning:%s" % str(w)
self.close()
except MySQLdb.Error, e:
print "Error:%s" % str(e)
self.close()
def insert_into_non_ip(sourceName,db):
cursor = db.cursor()
print '---------------------------------------------------------------------------------'
print "STEP 5 : insert into non_ip_camera table"
sql = "INSERT IGNORE INTO non_ip_camera (camera_id, snapshot_url) SELECT id, camera_key FROM camera WHERE source ="+"'"+sourceName+"'"
print sql
print '---------------------------------------------------------------------------------'
isSuccess =True
try:
print "inserting into non_ip_camera table"
cursor.execute(sql)
num_record = cursor.rowcount
print "%d row affected"%(int(num_record))
except MySQLdb.Error, e:
print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
isSuccess = False
except MySQLdb.Warning,e:
print "MYSQL Warning : %s"%e
#print "MySQL Warning[%d]: %s"% (e.args[0], e.args[1])
isSuccess = False
return isSuccess
def update_camera_id(db):
print '---------------------------------------------------------------------------------'
print "STEP 6 : update camera IDs in info_source, camera table"
cursor= db.cursor()
sql = "UPDATE info_source, camera SET camera_id = id WHERE camera_key = snapshot_url"
print sql
print '---------------------------------------------------------------------------------'
isSuccess =True
try:
print "Updating IDs.."
cursor.execute(sql)
num_record = cursor.rowcount
print "%d row affected"%(int(num_record))
except MySQLdb.Error, e:
print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
isSuccess = False
except MySQLdb.Warning,e:
print "MYSQL Warning : %s"%e
isSuccess = False
return isSuccess
def __init__(self, db):
warnings.filterwarnings('error', category=MySQLdb.Warning)
try:
self.name = db
host = configuration.get('mysql_host')
port = configuration.get('mysql_port')
if str(port).startswith('0.0.0.0:'):
# Thanks Docker :/
port = int(port[8:])
user = configuration.get('mysql_user')
passwd = configuration.get('mysql_passwd')
self.connection = MySQLdb.connect(host=host, port=port, user=user, passwd=passwd, use_unicode=True, charset='utf8', autocommit=True)
self.cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
self.execute('SET NAMES utf8mb4')
try:
self.execute("USE {db}".format(db=db))
except DatabaseException:
print('Creating database {db}'.format(db=db))
self.execute('CREATE DATABASE {db} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'.format(db=db))
self.execute('USE {db}'.format(db=db))
except MySQLdb.Error as e:
raise DatabaseException('Failed to initialize database in `{location}`'.format(location=db)) from e
def execute(self, sql, args=None):
sql = sql.replace('COLLATE NOCASE', '') # Needed for case insensitivity in SQLite which is default in MySQL.
if args is None:
args = []
if args:
# eww
sql = sql.replace('?', '%s')
try:
p = perf.start()
self.cursor.execute(sql, args)
perf.check(p, 'slow_query', (sql, args), 'mysql')
return self.cursor.fetchall()
except MySQLdb.Warning as e:
if e.args[0] == 1050 or e.args[0] == 1051:
pass # we don't care if a CREATE IF NOT EXISTS raises an "already exists" warning or DROP TABLE IF NOT EXISTS raises an "unknown table" warning.
elif e.args[0] == 1062:
pass # We don't care if an INSERT IGNORE INTO didn't do anything.
else:
raise
except MySQLdb.Error as e:
raise DatabaseException('Failed to execute `{sql}` with `{args}` because of `{e}`'.format(sql=sql, args=args, e=e)) from e
def execute(self, sql, args=None):
"""
Execute the given sql against current open connection
without caring about the result output
"""
# Turning MySQLdb.Warning into exception, so that we can catch it
# and maintain the same log output format
with warnings.catch_warnings():
warnings.filterwarnings('error', category=MySQLdb.Warning)
try:
cursor = self.conn.cursor()
cursor.execute("%s %s" % (self.query_header, sql), args)
except Warning as db_warning:
log.warning(
"MySQL warning: {}, when executing sql: {}, args: {}"
.format(db_warning, sql, args))
return cursor.rowcount
def create_db(instance, db):
""" Create a database if it does not already exist
Args:
instance - a hostAddr object
db - the name of the to be created
"""
conn = connect_mysql(instance)
cursor = conn.cursor()
sql = ('CREATE DATABASE IF NOT EXISTS ' '`{db}`;'.format(db=db))
log.info(sql)
# We don't care if the db already exists and this was a no-op
warnings.filterwarnings('ignore', category=MySQLdb.Warning)
cursor.execute(sql)
warnings.resetwarnings()
def f_print_log_error(conn,perfor_or_infor,save_as):
title = "Log file Statistics"
style = {1: 'start & shutdown:,l'}
rows =[]
WarnLog = 0
ErrLog = 0
query = "SELECT variable_value FROM " + perfor_or_infor + ".global_variables where variable_name ='log_error'"
filename = f_get_query_value(conn, query)
if os.path.exists(filename):
with open(filename, 'r') as f:
for line in f:
if ('ready for connections' in line or 'Shutdown completed' in line):
rows.append([line])
if ('Warning' in line):
WarnLog += 1
if ('error' in line):
ErrLog += 1
else:
rows.append([filename + " not exists"])
rows.append(['Warning & Error Statistics:'])
rows.append([filename + ' contains ' + str(WarnLog) + ' warning(s).'])
rows.append([filename + ' contains ' + str(ErrLog) + ' error(s).'])
f_print_table(rows, title, style,save_as)
def insert_link(self, source_article_id, target_article_name, data, position, zip_file_path):
target_article_id = self.db_build_view._resolve_title(target_article_name.split('-----##$$$##-----')[0].replace('_', ' '))
if target_article_id is not None:
data['source_article_id'] = source_article_id
data['target_article_id'] = target_article_id
if position is not None:
data['target_x_coord_1920_1080'] = position[0]
data['target_y_coord_1920_1080'] = position[1]
if data['target_y_coord_1920_1080'] < 0:
data['target_y_coord_1920_1080'] = 0
else:
data['target_x_coord_1920_1080'] = None
data['target_y_coord_1920_1080'] = None
#print data
sql = "INSERT INTO links (source_article_id, target_article_id," \
"target_position_in_text, target_position_in_text_only, target_position_in_section, " \
"target_position_in_section_in_text_only, section_name," \
" section_number, target_position_in_table, table_number, table_css_class, table_css_style," \
" target_x_coord_1920_1080, target_y_coord_1920_1080) VALUES" \
"(%(source_article_id)s, %(target_article_id)s, %(target_position_in_text)s," \
"%(target_position_in_text_only)s, %(target_position_in_section)s, %(target_position_in_section_in_text_only)s, " \
"%(section_name)s, %(section_number)s, %(target_position_in_table)s, %(table_number)s, " \
"%(table_css_class)s, %(table_css_style)s," \
"%(target_x_coord_1920_1080)s, %(target_y_coord_1920_1080)s);"
try:
self.cursor.execute(sql, data)
#logging.info('DB Insert Success for target article id: "%s" ' % target_article_id)
except (MySQLdb.Error, MySQLdb.Warning), e:
#print sql
print ('FAIL: Data caused warning or error "%s" for target_article_id: "%s" in for source_article_id: "%s"', data, target_article_id, source_article_id)
print 'FAIL: EXCEPTION:', e
print zip_file_path
#print('DB Insert Error target article id: "%s" ' % target_article_id)
return target_article_id
def insert_link(self, source_article_id, target_article_name, data, position, zip_file_path):
target_article_id = self.db_build_view._resolve_title(target_article_name.split('-----##$$$##-----')[0].replace('_', ' '))
data['target_article_name'] = target_article_name.split('-----##$$$##-----')[0].replace('_', ' ')
data['source_article_id'] = source_article_id
data['target_article_id'] = target_article_id
if position is not None:
data['target_x_coord_1920_1080'] = position[0]
data['target_y_coord_1920_1080'] = position[1]
if data['target_y_coord_1920_1080'] < 0:
data['target_y_coord_1920_1080'] = 0
else:
data['target_x_coord_1920_1080'] = None
data['target_y_coord_1920_1080'] = None
#print data
sql = "INSERT INTO redirects_candidates (source_article_id, target_article_id, target_article_name," \
"target_position_in_text, target_position_in_text_only, target_position_in_section, " \
"target_position_in_section_in_text_only, section_name," \
" section_number, target_position_in_table, table_number, table_css_class, table_css_style," \
" target_x_coord_1920_1080, target_y_coord_1920_1080) VALUES" \
"(%(source_article_id)s, %(target_article_id)s, %(target_article_name)s, %(target_position_in_text)s," \
"%(target_position_in_text_only)s, %(target_position_in_section)s, %(target_position_in_section_in_text_only)s, " \
"%(section_name)s, %(section_number)s, %(target_position_in_table)s, %(table_number)s, " \
"%(table_css_class)s, %(table_css_style)s," \
"%(target_x_coord_1920_1080)s, %(target_y_coord_1920_1080)s);"
try:
self.cursor.execute(sql, data)
#logging.info('DB Insert Success for target article id: "%s" ' % target_article_id)
except (MySQLdb.Error, MySQLdb.Warning), e:
#print sql
print ('FAIL: Data caused warning or error "%s" for target_article_id: "%s" in for source_article_id: "%s"', data, target_article_id, source_article_id)
print 'FAIL: EXCEPTION:', e
print zip_file_path
#print('DB Insert Error target article id: "%s" ' % target_article_id)
return target_article_id
def __init__(self,host="127.0.0.1",user="root",
passwd="",dbname="mysql",port=3306):
self.conn = None
self.cursor = None
self.host = host
self.user = user
self.passwd = passwd
self.dbname = dbname
self.port = port
warnings.filterwarnings('ignore',category=MySQLdb.Warning)
def Change(self, region, host_content):
repluser, replpassword, ssl_ca, ssl_cert, ssl_key = GetConf().GetReplAcount(rg=True)
master_host, master_port = host_content['host'], int(host_content['port'])
if host_content['ssl']:
sql = 'change master to master_host="%s",master_port=%d,master_user="%s",master_password="%s",master_ssl=1,' \
'master_ssl_ca="%s",' \
'master_ssl_cert="%s",master_ssl_key="%s",master_auto_position=1 for channel "%s" ' % (
master_host, master_port,
repluser, replpassword, ssl_ca,
ssl_cert, ssl_key, region)
else:
sql = 'change master to master_host="%s",master_port=%d,master_user="%s",master_password="%s",' \
'master_auto_position=1 for channel "%s" ' % (master_host, master_port,
repluser, replpassword, region)
with closing(self.conn.cursor()) as cur:
try:
cur.execute('stop slave for channel "%s"' % region)
cur.execute('reset slave for channel "%s"' % region)
except:
pass
try:
cur.execute(sql)
self.__set_group_region(region,host_content)
except MySQLdb.Warning,e:
logging.error(traceback.format_exc())
cur.execute('start slave;')
self.__set_group_region(region, host_content)
except MySQLdb.Error,e:
logging.error(traceback.format_exc())
logging.error('addition task for %s failed,master to %s in region %s ! ! !' % (self.host, host_content['host'], region))
return False
return True
def ResetMaster(self,groupname):
try:
'''????binlog????'''
master_log_file,read_master_log_pos,master_host = self.CheckPos(get_host=True)
if master_host:
readbinlog_status = str([groupname,master_log_file,read_master_log_pos])
from zk_handle.zkHandler import zkHander
from contextlib import closing
with closing(zkHander()) as zkhander:
zkhander.SetReadBinlog(master_host,readbinlog_status)
''''''
#self.mysql_cur.execute('set global read_only=0;')
self.mysql_cur.execute('stop slave')
self.mysql_cur.execute('reset slave all;')
except MySQLdb.Warning,e:
logging.warning(traceback.format_exc())
except MySQLdb.Error,e:
logging.warning(traceback.format_exc())
"""????,????????????????zk????????"""
import AdditionTask
addition = AdditionTask.Addition(self.host)
addition_master = addition.GetRepl()
if addition_master:
exe_addition = AdditionTask.ExecuteAdditionTask(self.host,self.port)
for region in addition_master:
exe_addition.Change(region,addition_master[region])
""""""
def initialize(self):
"""Initialize SQL database in the correct state"""
for name, ddl in self.DROP.iteritems():
try:
print "Drop table {}:".format(name),
self.cursor.execute(ddl)
except MySQLdb.Error as err:
print err
except MySQLdb.Warning as wrn:
pass
finally:
print 'OK'
for name, ddl in self.TABLES.iteritems():
try:
print "Creating table {}:".format(name),
self.cursor.execute(ddl)
except MySQLdb.Error as err:
print err
except MySQLdb.Warning as wrn:
pass
finally:
print 'OK'
for name, ddl in self.INSERT.iteritems():
try:
print "Inserting into table {}:".format(name),
self.cursor.execute(ddl)
except MySQLdb.Error as err:
print err
except MySQLdb.Warning as wrn:
pass
finally:
print 'OK'
def insertDB(self, sql, params):
try:
print params
cursor = self.conn.cursor()
print cursor.execute(sql, params)
except MySQLdb.Warning, w:
print "Warning:%s" % str(w)
print traceback.print_exc()
pass
except MySQLdb.Error, e:
print "Error:%s" % str(e)
print traceback.print_exc()
pass
def insert_into_camera(sourceName,cameraType,db):
print '---------------------------------------------------------------------------------'
print "STEP 4 : Load camera table from info_source where source = '%s'"%sourceName
print "Is this camera traffic camera? (y/n) if it is traffic camera, is_safe will be 1"
print "otherwise, 0"
print '---------------------------------------------------------------------------------'
isSuccess = True
cursor = db.cursor()
yn = raw_input()
if yn == 'y' or 'Y':
sql = "INSERT IGNORE INTO camera (camera_key, encrypted_camera_key, type, source, latitude, longitude, country, state, city, resolution_width, resolution_height, frame_rate, is_video, is_active, is_safe, is_high_load, is_analysis_restricted, utc_offset, timezone_id, timezone_name, reference_logo, reference_url,multiple_cameras,is_located,weather_wind_speed,weather_temperature_faren,weather_humidity,weather_code) SELECT snapshot_url, null, %s, source, latitude, longitude, country, state, city, null, null, null, 0, 1, 1, 0, 0, null, null, null, null, null, null, 1, null, null, null, null FROM info_source WHERE source=%s"%(("'"+cameraType+"'","'"+sourceName+"'"))
else:
sql = "INSERT IGNORE INTO camera (camera_key, encrypted_camera_key, type, source, latitude, longitude, country, state, city, resolution_width, resolution_height, frame_rate, is_video, is_active, is_safe, is_high_load, is_analysis_restricted, utc_offset, timezone_id, timezone_name, reference_logo, reference_url,multiple_cameras,is_located,weather_wind_speed,weather_temperature_faren,weather_humidity,weather_code) SELECT snapshot_url, null, %s, source, latitude, longitude, country, state, city, null, null, null, 0, 1, 0, 0, 0, null, null, null, null, null, null, 1, null, null, null, null FROM info_source WHERE source=%s"%(("'"+cameraType+"'","'"+sourceName+"'"))
try:
print "Inserting into camera table from info_source"
cursor.execute(sql)
cursor.execute("select * from info_source where source = "+"'"+sourceName+"'")
num_record=cursor.rowcount
cursor.execute("select * from camera where source = "+"'"+sourceName+"'")
num_table = cursor.rowcount
print "%d recorded in camera out of %d from info_source"%(int(num_table),int(num_record))
except MySQLdb.Error, e:
print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
isSuccess = False
except MySQLdb.Warning,e:
print "MYSQL Warning : %s"%e
isSuccess = False
return isSuccess
def _executeSQL(self, cur, sql):
try:
cur.execute(sql)
except MySQLdb.Warning:
if not self.setting('IgnoreSQLWarnings', False):
raise
def stop_replication(instance, thread_type=REPLICATION_THREAD_ALL):
""" Stop replication, if running
Args:
instance - A hostAddr object
thread - Which thread to stop. Options are in REPLICATION_THREAD_TYPES.
"""
if thread_type not in REPLICATION_THREAD_TYPES:
raise Exception('Invalid input for arg thread: {thread}'
''.format(thread=thread_type))
conn = connect_mysql(instance)
cursor = conn.cursor()
ss = get_slave_status(instance)
if (ss['Slave_IO_Running'] != 'No' and ss['Slave_SQL_Running'] != 'No' and
thread_type == REPLICATION_THREAD_ALL):
cmd = 'STOP SLAVE'
elif ss['Slave_IO_Running'] != 'No' and thread_type != REPLICATION_THREAD_SQL:
cmd = 'STOP SLAVE IO_THREAD'
elif ss['Slave_SQL_Running'] != 'No' and thread_type != REPLICATION_THREAD_IO:
cmd = 'STOP SLAVE SQL_THREAD'
else:
log.info('Replication already stopped')
return
warnings.filterwarnings('ignore', category=MySQLdb.Warning)
log.info(cmd)
cursor.execute(cmd)
warnings.resetwarnings()
def start_replication(instance, thread_type=REPLICATION_THREAD_ALL):
""" Start replication, if not running
Args:
instance - A hostAddr object
thread - Which thread to start. Options are in REPLICATION_THREAD_TYPES.
"""
if thread_type not in REPLICATION_THREAD_TYPES:
raise Exception('Invalid input for arg thread: {thread}'
''.format(thread=thread_type))
conn = connect_mysql(instance)
cursor = conn.cursor()
ss = get_slave_status(instance)
if (ss['Slave_IO_Running'] != 'Yes' and
ss['Slave_SQL_Running'] != 'Yes' and
thread_type == REPLICATION_THREAD_ALL):
cmd = 'START SLAVE'
elif ss['Slave_IO_Running'] != 'Yes' and thread_type != REPLICATION_THREAD_SQL:
cmd = 'START SLAVE IO_THREAD'
elif ss['Slave_SQL_Running'] != 'Yes' and thread_type != REPLICATION_THREAD_IO:
cmd = 'START SLAVE SQL_THREAD'
else:
log.info('Replication already running')
return
warnings.filterwarnings('ignore', category=MySQLdb.Warning)
log.info(cmd)
cursor.execute(cmd)
warnings.resetwarnings()
time.sleep(1)
def run(self):
print "normal POS firmware running..."
with open("/home/ubuntu/payment-server/pos_firmware.py", "r") as firmware:
m = hashlib.md5(firmware.read())
old_checksum = m.digest()
# keep opening nc listening port to receive firmware update
#with open("/home/ubuntu/payment-server/pos_firmware.py", "w+") as firmware:
# if subprocess.Popen(["nc", "-l", "-p", str(self.port)], stdout=firmware, stderr=subprocess.PIPE) == 0:
# print "received firmware update, exiting..."
# exit(0)
while True:
skip = 0
with open("/home/ubuntu/payment-server/pos_firmware.py", "r") as firmware:
m = hashlib.md5(firmware.read())
new_checksum = m.digest()
if new_checksum != old_checksum:
print "detected firmware update, restarting..."
exit(0)
transac_id = ''.join(random.choice(string.digits) for _ in range(8))
datetime = time.strftime('%Y-%m-%d %H:%M:%S')
content = ''.join(random.choice(string.ascii_letters) for _ in range(8))
amount = random.uniform(1, 10000)
credit_card_no = ''.join(random.choice(string.digits) for _ in range(16))
rd_credit_card_no = NormalPOS.redact_info(credit_card_no)
sql = "INSERT INTO transactions(transac_id, \
datetime, content, amount, credit_card_no) \
VALUES ('%s', '%s', '%s', '%.2f', '%s')" % \
(transac_id, datetime, content, amount, rd_credit_card_no)
try:
# Execute the SQL command
self.cursor.execute(sql)
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
skip = 1
if skip == 0:
try:
# Commit your changes in the database
self.db.commit()
print "one record inserted."
except Exception as e:
traceback.print_exc(e)
# Rollback in case there is any error
self.db.rollback()
# assume interval is drawn from exp distribution
interval = random.expovariate(0.1)
# insert one record every interval seconds
time.sleep(interval)
def load_file_to_temp_table(fileName,tableName,db):
print "STEP 2 : Load your file into temp table you created"
print "ALL warning will be raised as error except for duplicates in the file "
print "table will be dropped if loading failed"
print '---------------------------------------------------------------------------------'
print "Loading your file to %s"%tableName
print '---------------------------------------------------------------------------------'
isSuccess = True
cursor = db.cursor()
#set warning to error
warnings.simplefilter("error",MySQLdb.Warning)
fields_in_table = desc_table(db,tableName)
fields_in_file = list_field(fileName)
sql = 'insert ignore into '+tableName+'('
i =0
field_type=[]
for member in fields_in_file:
if i == len(fields_in_file)-1:
sql = sql+member+') VALUES ('
else:
sql = sql+member+','
i = i+1
field_type.append([item[1] for item in fields_in_table if member in item[0] and exact_match(member,item[0])][0])
f = open(fileName,'rb')
skipfirstLine = True
for line in f:
if(skipfirstLine):
skipfirstLine =False
continue
members = line.replace('\n','').split('#')
i=0
cmd = sql
for member in members:
types = field_type[i]
if 'char' in types:
if i == len(members)-1:
cmd = cmd +"'"+member+"'"+')'
else:
cmd = cmd +"'"+member+"'"+','
else:
if i == len(members)-1:
cmd = cmd +member+')'
else:
cmd = cmd +member+','
i = i+1
try:
cursor.execute(cmd)
except MySQLdb.Error, e:
print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
isSuccess = False
except MySQLdb.Warning,e:
print "MYSQL Warning : %s"%e
isSuccess = False
f.close()
return isSuccess
def swap_master_and_slave(instance, dry_run):
""" Swap a master and slave in zk. Warning: this does not sanity checks
and does nothing more than update zk. YOU HAVE BEEN WARNED!
Args:
instance - An instance in the replica set. This function will figure
everything else out.
dry_run - If set, do not modify configuration.
"""
zk_local = MysqlZookeeper()
kazoo_client = get_kazoo_client()
if not kazoo_client:
raise Exception('Could not get a zk connection')
log.info('Instance is {inst}'.format(inst=instance))
(replica_set, version) = zk_local.get_replica_set_from_instance(instance)
log.info('Detected replica_set as '
'{replica_set}'.format(replica_set=replica_set))
(zk_node, parsed_data,
version) = get_zk_node_for_replica_set(kazoo_client, replica_set)
log.info('Replica set {replica_set} is held in zk_node '
'{zk_node}'.format(
zk_node=zk_node, replica_set=replica_set))
log.info('Existing config:')
log.info(pprint.pformat(remove_auth(parsed_data[replica_set])))
new_data = copy.deepcopy(parsed_data)
new_data[replica_set][REPLICA_ROLE_MASTER] = \
parsed_data[replica_set][REPLICA_ROLE_SLAVE]
new_data[replica_set][REPLICA_ROLE_SLAVE] = \
parsed_data[replica_set][REPLICA_ROLE_MASTER]
log.info('New config:')
log.info(pprint.pformat(remove_auth(new_data[replica_set])))
if new_data == parsed_data:
raise Exception('No change would be made to zk, '
'will not write new config')
elif dry_run:
log.info('dry_run is set, therefore not modifying zk')
else:
log.info('Pushing new configuration for '
'{replica_set}:'.format(replica_set=replica_set))
kazoo_client.set(zk_node, simplejson.dumps(new_data), version)
def change_master(slave_hostaddr,
master_hostaddr,
master_log_file,
master_log_pos,
no_start=False):
""" Setup MySQL replication on new replica
Args:
slave_hostaddr - hostaddr object for the new replica
hostaddr - A hostaddr object for the master db
master_log_file - Replication log file to begin streaming
master_log_pos - Position in master_log_file
no_start - Don't run START SLAVE after CHANGE MASTER
"""
conn = connect_mysql(slave_hostaddr)
cursor = conn.cursor()
set_global_variable(slave_hostaddr, 'read_only', True)
reset_slave(slave_hostaddr)
master_user, master_password = get_mysql_user_for_role('replication')
parameters = {
'master_user': master_user,
'master_password': master_password,
'master_host': master_hostaddr.hostname,
'master_port': master_hostaddr.port,
'master_log_file': master_log_file,
'master_log_pos': master_log_pos
}
sql = ''.join(("CHANGE MASTER TO "
"MASTER_USER=%(master_user)s, "
"MASTER_PASSWORD=%(master_password)s, "
"MASTER_HOST=%(master_host)s, "
"MASTER_PORT=%(master_port)s, "
"MASTER_LOG_FILE=%(master_log_file)s, "
"MASTER_LOG_POS=%(master_log_pos)s "))
warnings.filterwarnings('ignore', category=MySQLdb.Warning)
cursor.execute(sql, parameters)
warnings.resetwarnings()
log.info(cursor._executed)
if not no_start:
start_replication(slave_hostaddr)
# Replication reporting is wonky for the first second
time.sleep(1)
# Avoid race conditions for zk update monitor
assert_replication_sanity(slave_hostaddr,
set([CHECK_SQL_THREAD, CHECK_IO_THREAD]))