def run_on_wiki(self, host, wiki, cursor, wiki_settings):
'''
run all queries for a specific wiki, after filling in the
query template; this assumes a db cursor is passed in
'''
print("wiki:", wiki)
queries = self.fillin_query_template(wiki_settings)
self.dbinfo.do_use_wiki(wiki, cursor)
if self.args['dryrun']:
for query in queries:
self.log.info("would run %s", qutils.prettyprint_query(query))
return
for query in queries:
self.log.info("running:")
self.log.info(qutils.prettyprint_query(query))
# be nice to the servers
time.sleep(0.05)
try:
cursor.execute(query.encode('utf-8'))
result = cursor.fetchall()
except MySQLdb.Error as ex:
raise MySQLdb.Error(
"exception running query on host "
"{host}, wiki {wiki} ({errno}:{message})".format(
host=host, wiki=wiki, errno=ex.args[0], message=ex.args[1]))
print(qutils.prettyprint_query(query))
print(qutils.prettyprint_rows(result, cursor.description))
def check_if_mysqlthr_exists(self, host, thread_id):
'''
return True if exists, not if not, and None if we
couldn't get result
'''
if self.args['dryrun']:
cursor = None
else:
cursor, _unused = self.dbinfo.get_cursor(host)
query = 'SHOW PROCESSLIST;'
if self.args['dryrun']:
self.log.info("would run %s", qutils.prettyprint_query(query))
return False
self.log.info("running:")
self.log.info(qutils.prettyprint_query(query))
try:
cursor.execute(query.encode('utf-8'))
result = cursor.fetchall()
except MySQLdb.Error as ex:
self.log.warning(
"exception looking for thread id on host %s (%s:%s)", host,
ex.args[0], ex.args[1])
return None
self.log.info("show processlist:")
self.log.info(qutils.prettyprint_rows(result, cursor.description))
for row in result:
if row[0] == thread_id:
return True
return False
def explain(self, wiki, cursor, thread_id):
'''
show explain for a given thread id, given an
initialized db cursor
'''
explain_query = 'SHOW EXPLAIN FOR ' + thread_id + ';'
if self.args['dryrun']:
self.log.info("would run %s",
qutils.prettyprint_query(explain_query))
return None, None
self.log.info("running:")
self.log.info(qutils.prettyprint_query(explain_query))
try:
cursor.execute(explain_query.encode('utf-8'))
description = cursor.description
explain_result = cursor.fetchall()
except MySQLdb.Error as ex:
if ex.args[0] == 1933 or ex.args[0] == 1094:
# 1933:Target is not running an EXPLAINable command, i.e. query is already complete
# 1094:Unknown thread id, i.e. query is already complete
explain_result = None
description = None
else:
raise MySQLdb.Error("exception explaining query on wiki "
"{wiki} ({errno}:{message})".format(
wiki=wiki,
errno=ex.args[0],
message=ex.args[1])) from None
return explain_result, description
def start_query(self, wiki, cursor, query):
'''
runs the passed query via the specified cursor, in a separate
thread, so we can do other things while it's running
(for loose values of 'while')
'''
if self.args['dryrun']:
self.log.info("would run %s", qutils.prettyprint_query(query))
return None
self.log.info("running:")
self.log.info(qutils.prettyprint_query(query))
thr = threading.Thread(target=async_query,
args=(wiki, cursor, query, self.log))
thr.start()
return thr
def explain_and_kill(self, host, wiki, thread_id, query):
'''
given the thread id of the thread running
our query, show explain it, then shoot
the query
'''
if self.args['dryrun']:
cursor = None
else:
cursor, _unused = self.dbinfo.get_cursor(host)
explain_result, description = self.explain(wiki, cursor, thread_id)
self.kill(wiki, cursor, thread_id)
qutils.print_and_log(self.log, "*** QUERY:")
qutils.print_and_log(self.log, qutils.prettyprint_query(query))
qutils.print_and_log(self.log, "*** SHOW EXPLAIN RESULTS:")
qutils.print_and_log(
self.log, qutils.prettyprint_rows(explain_result, description))
if cursor is not None:
cursor.close()
# additional insurance.
result = self.check_if_mysqlthr_exists(host, thread_id)
self.log.info("check if query still running: %s", result)
if result is None or result:
# we had a problem checking, or the thread is still there
# and presumably the kill failed
self.log.error(
"quitting while we're behind; run the following on host %s",
host)
self.log.error("echo 'kill {thread_id}' | mysql --skip-ssl")
raise MySQLdb.Error(
"query thread {id} still running".format(id=thread_id))
def kill(self, wiki, cursor, thread_id):
'''
given a db cursor and a thread id, attempt to kill
the thread and deal with errors
'''
kill_query = 'KILL ' + thread_id
if self.args['dryrun']:
self.log.info("would run %s", qutils.prettyprint_query(kill_query))
return
try:
cursor.execute(kill_query.encode('utf-8'))
kill_result = cursor.fetchall()
self.log.info("result from kill: %s", kill_result)
except MySQLdb.Error as ex:
# 1094:Unknown thread id: <thread_id>
if ex.args[0] != 1094:
raise MySQLdb.Error(("exception killing query on wiki "
"{wiki} ({errno}:{message})".format(
wiki=wiki,
errno=ex.args[0],
message=ex.args[1]))) from None
def do_use_wiki(self, wiki, cursor, lost_conn_ok=False):
'''
does a simple 'USE wikidbname'. That is all.
returns True on success, False for dryrun, None for lost conn,
raises exception on any other error
'''
usequery = 'USE ' + wiki + ';'
if self.args['dryrun']:
self.log.info("would run %s", qutils.prettyprint_query(usequery))
return False
self.log.info("running %s", usequery)
try:
cursor.execute(usequery.encode('utf-8'))
result = cursor.fetchall()
except MySQLdb.Error as ex:
if lost_conn_ok and ex.args[0] == 1049:
# this host no longer serves this wikidb, but caller wishes to handle it
return None
if result is not None:
self.log.error("returned from fetchall: %s", result)
raise MySQLdb.Error(
"exception for use {wiki} ({errno}:{message})".format(
wiki=wiki, errno=ex.args[0], message=ex.args[1])) from None
return True