def get_gtid_to_skip(session=None):
"""
Get the gtid that is breaking replication.
This function returns the GTID breaking replication
Args:
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
import mysqlsh
shell = mysqlsh.globals.shell
if session is None:
session = shell.get_session()
if session is None:
print("No session specified. Either pass a session object to this "
"function or connect the shell to a database")
return
stmt = """select channel_name, replace(replace(regexp_substr(last_error_message, "transaction '.*'"), "transaction ",""), "'", "") `gtid_to_skip`
from performance_schema.replication_applier_status_by_coordinator"""
run_and_show(stmt, "table", session)
return
def get_persisted_variables(format="table",session=None):
"""
Prints all variables that have been persisted.
Args:
format (string): One of table, tabbed, vertical, json, ndjson, json/raw,
json/array, json/pretty. Default is table.
In table format, the value column is truncated to 50 characters.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
if format == "table":
stmt = """SELECT t1.VARIABLE_NAME "Variable Name", concat(set_user, "@", set_host) `Changed by`,
set_time `Time`, substring(VARIABLE_VALUE,1, 50) `Value`, VARIABLE_SOURCE `Source`
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE = 'PERSISTED';"""
else:
stmt = """SELECT t1.VARIABLE_NAME "Variable Name", concat(set_user, "@", set_host) `Changed by`,
set_time `Time`, VARIABLE_VALUE `Value`, VARIABLE_SOURCE `Source`
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE = 'PERSISTED';"""
run_and_show(stmt, format, session)
def get_queries_most_rows_affected(limit=1, schema=None, session=None):
"""
Prints the statements affecting the most rows.
This function list the all the statements affecting most rows.
Args:
limit (integer): The amount of query to return (default: 1).
schema (string): The name of the schema to use. This is optional.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
filter = ""
if schema is not None:
filter += "where db = '%s'" % schema
stmt = """SELECT db, rows_affected, rows_affected_avg, query_sample_text
FROM sys.statement_analysis as sa
JOIN performance_schema.events_statements_summary_by_digest as ed
ON ed.digest=sa.digest %s
ORDER BY rows_affected_avg DESC, rows_affected DESC LIMIT %s
""" % (filter, limit)
run_and_show(stmt, 'vertical')
def get_queries_updating_same_pk(limit=1, schema=None, session=None):
"""
Prints the statements updating mostly the same PK.
This finction list all the statements updatings mostly the same PK and
therefore having to wait more. This is used to detect hotspots.
Args:
limit (integer): The amount of query to return (default: 1).
schema (string): The name of the schema to use. This is optional.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
filter = ""
if schema is not None:
filter += "where (current_schema = '%s' or object_schema = '%s')" % (
schema, schema)
stmt = """SELECT current_schema, rows_examined, sql_text,
(
SELECT count(*)
FROM performance_schema.events_statements_history_long AS t2
WHERE t2.digest_text=t1.digest_text
) AS `count`
FROM performance_schema.events_statements_history_long AS t1
WHERE rows_affected > 1 %s
ORDER BY timer_wait DESC LIMIT %s
""" % (filter, limit)
run_and_show(stmt, 'vertical')
def get_queries_ft_scan(limit=1, select=False, schema=None, session=None):
"""
Prints the queries performing full table scans"
This function list the all the queries performing Full Table Scans. If the
limit is 1 you can also see all the details about the query.
Args:
limit (integer): The amount of query to return (default: 1).
select (bool): Returns only SELECT queries.
schema (string): The name of the schema to use. This is optional.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
import mysqlsh
shell = mysqlsh.globals.shell
if session is None:
session = shell.get_session()
if session is None:
print("No session specified. Either pass a session object to this "
"function or connect the shell to a database")
return
filter = ""
if select:
filter += "AND query_sample_text like '%select%'"
if schema is not None:
filter += "AND schema_name = '%s'" % schema
stmt = """SELECT schema_name,
sum_rows_examined, (sum_rows_examined/exec_count) avg_rows_call,
sys.format_time(total_latency) tot_lat,
exec_count, sys.format_time(total_latency/exec_count) latency_per_call,
t2.first_seen, t2.last_seen, query_sample_text
FROM sys.x$statements_with_full_table_scans AS t1
JOIN performance_schema.events_statements_summary_by_digest AS t2
ON t2.digest=t1.digest
WHERE schema_name NOT in ('performance_schema', 'sys') %s
ORDER BY (total_latency/exec_count) desc
LIMIT %d""" % (filter, limit)
run_and_show(stmt,'vertical')
if limit == 1:
result = session.run_sql(stmt)
row = result.fetch_one()
if row:
original_query = row[8]
_get_full_details(shell, session, original_query, row[0])
def _get_full_details(shell, session, original_query, schema):
old_schema=None
if session.get_current_schema() is None:
old_schema=None
session.set_current_schema(schema)
elif session.get_current_schema().get_name() != schema:
old_schema=session.get_current_schema().get_name()
session.set_current_schema(schema)
answer = shell.prompt('Do you want to have EXPLAIN output? (y/N) ', {'defaultValue':'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN %s""" % original_query
run_and_show(stmt,'vertical')
answer = shell.prompt('Do you want to have EXPLAIN in JSON format output? (y/N) ', {'defaultValue':'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN FORMAT=json %s""" % original_query
run_and_show(stmt,'vertical')
answer = shell.prompt('Do you want to have EXPLAIN in TREE format output? (y/N) ', {'defaultValue':'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN format=tree %s""" % original_query
run_and_show(stmt,'vertical')
answer = shell.prompt('Do you want to have EXPLAIN ANALYZE output? (y/N) ', {'defaultValue':'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN ANALYZE %s""" % original_query
run_and_show(stmt,'vertical')
if old_schema:
session.set_current_schema(old_schema)
return
def get_noninnodb_tables(session=None):
"""
Prints all tables not using InnoDB.
This function list all tables not using InnoDB Storage Engine.
Args:
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
stmt = """SELECT table_schema, table_name, engine, table_rows,
sys.format_bytes(index_length+data_length) AS 'SIZE'
FROM information_schema.tables
WHERE engine != 'innodb'
AND table_schema NOT IN
('information_schema', 'mysql', 'performance_schema');"""
run_and_show(stmt, "table", session)
def get_vatiable_info(variable_name, format="table", session=None):
"""
Prints all variables that have been persisted.
Args:
variable_name (string): The variable which you want to display the info.
format (string): One of table, tabbed, vertical, json, ndjson, json/raw,
json/array, json/pretty. Default is table.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
stmt = """SELECT t1.*, VARIABLE_VALUE
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_NAME LIKE '{}'""".format(variable_name)
run_and_show(stmt, format, session)
def get_amount_ddl(session=None):
"""
Prints a summary of the amount of DDL statements performed since server start.
This function list all the amount of DDL statements performed on the MySQL
server since its start.
Args:
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
stmt = """SELECT event_name, count_star, sum_errors
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE event_name REGEXP '.*sql/(create|drop|alter).*'
AND event_name NOT REGEXP '.*user'
AND count_star > 0;"""
run_and_show(stmt, "table", session)
def get_cascading_fk(session=None):
"""
Prints all foreign keys with cascading constraints.
This function list all InnoDB tables having foreign keys with cascading foreign key constraints.
Args:
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
stmt = """SELECT CONCAT(t1.table_name, '.', column_name) AS 'foreign key',
CONCAT(t1.referenced_table_name, '.', referenced_column_name) AS 'references',
t1.constraint_name AS 'constraint name', UPDATE_RULE, DELETE_RULE
FROM information_schema.key_column_usage as t1
JOIN information_schema.REFERENTIAL_CONSTRAINTS as t2
WHERE t2.CONSTRAINT_NAME = t1.constraint_name
AND t1.referenced_table_name IS NOT NULL
AND (DELETE_RULE = "CASCADE" OR UPDATE_RULE = "CASCADE");"""
run_and_show(stmt, "table", session)
def get_innodb_with_nopk(session=None):
"""
Prints all InnoDB tables not having a Primary Key or a non NULL unique key.
This function list all InnoDB tables not having a valid cluster index key.
Args:
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
stmt = """SELECT tables.table_schema , tables.table_name , tables.engine,
table_rows, sys.format_bytes(index_length+data_length) AS 'SIZE'
FROM information_schema.tables
LEFT JOIN (
SELECT table_schema , table_name
FROM information_schema.statistics
GROUP BY table_schema, table_name, index_name HAVING
SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks
ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name
WHERE puks.table_name is null
AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";"""
run_and_show(stmt, "table", session)
def _get_full_details(shell, session, original_query, schema):
max_text_size = _get_max_text_size(session)
current_query_size = len(original_query)
if current_query_size >= int(int(max_text_size)*.99):
print("\nThe returned query might be not complete, you should increase 'performance_schema_max_sql_text_length'. Actually it is set to {}".format(max_text_size))
answer = shell.prompt('Do you want to CONTINUE (not recommended)? (y/N) ', {'defaultValue':'n'})
if answer.lower() == 'n':
return
old_schema=None
if shell.parse_uri(session.get_uri())['scheme'] != "mysqlx":
print("\nFor more details, please use a MySQL X connection.")
return
if session.get_current_schema() is None:
old_schema=None
session.set_current_schema(schema)
elif session.get_current_schema().get_name() != schema:
old_schema=session.get_current_schema().get_name()
session.set_current_schema(schema)
answer = shell.prompt('Do you want to have EXPLAIN output? (y/N) ', {'defaultValue':'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN %s""" % original_query
run_and_show(stmt,'vertical')
answer = shell.prompt('Do you want to have EXPLAIN in JSON format output? (y/N) ', {'defaultValue':'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN FORMAT=json %s""" % original_query
run_and_show(stmt,'vertical')
answer = shell.prompt('Do you want to have EXPLAIN in TREE format output? (y/N) ', {'defaultValue':'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN format=tree %s""" % original_query
run_and_show(stmt,'vertical')
answer = shell.prompt('Do you want to have EXPLAIN ANALYZE output? (y/N) ', {'defaultValue':'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN ANALYZE %s""" % original_query
run_and_show(stmt,'vertical')
if old_schema:
session.set_current_schema(old_schema)
return
def _get_full_details(shell, session, original_query, schema):
old_schema = None
if shell.parse_uri(session.get_uri())['scheme'] != "mysqlx":
print("\nFor more details, please use a MySQL X connection.")
return
if session.get_current_schema() is None:
old_schema = None
session.set_current_schema(schema)
elif session.get_current_schema().get_name() != schema:
old_schema = session.get_current_schema().get_name()
session.set_current_schema(schema)
answer = shell.prompt('Do you want to have EXPLAIN output? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN %s""" % original_query
run_and_show(stmt, 'vertical')
answer = shell.prompt(
'Do you want to have EXPLAIN in JSON format output? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN FORMAT=json %s""" % original_query
run_and_show(stmt, 'vertical')
answer = shell.prompt(
'Do you want to have EXPLAIN in TREE format output? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN format=tree %s""" % original_query
run_and_show(stmt, 'vertical')
answer = shell.prompt('Do you want to have EXPLAIN ANALYZE output? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN ANALYZE %s""" % original_query
run_and_show(stmt, 'vertical')
if old_schema:
session.set_current_schema(old_schema)
return
def get(limit=5, session=None):
"""
Get the profile of a statement
This function configure back Perfomance Schema Instrumentation
to disable profiling for the current connected user.
Args:
limit (integer): The amount of events showed to retrieve info. Default 5.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
import mysqlsh
shell = mysqlsh.globals.shell
if session is None:
session = shell.get_session()
if session is None:
print("No session specified. Either pass a session object to this "
"function or connect the shell to a database")
return
if setup_actors == []:
print(
"Profiling was not started, run profiling.start() before getting any result !"
)
return
if monitored_thread is None:
where_str = "@@pseudo_thread_id"
else:
where_str = monitored_thread
# Get the list of Statements
stmt = """SELECT event_id, SQL_TEXT, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration,
t1.thread_id,
DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME='UPTIME') - TIMER_END*10e-13 second) AS `end_time`
FROM performance_schema.events_statements_history_long t1
JOIN performance_schema.threads t2
ON t2.thread_id=t1.thread_id
WHERE t2.processlist_id={}
ORDER BY event_id DESC LIMIT {}""".format(where_str, limit)
result = session.run_sql(stmt)
rows = result.fetch_all()
if len(rows) > 1:
print("Last 5 events from the proccess list id: {}".format(where_str))
print("-----------------------------------------" +
"-" * len(str(where_str)))
print(
"\033[33m---Events before profiling was started are in orange---\033[0m"
)
tab_element = {}
for row in rows:
if (datetime.strptime(str(row[4]), "%Y-%m-%d %H:%M:%S.%f") >
monitored_time):
print("\033[92m{}\033[0m : {}".format(row[0], row[1]))
else:
print("\033[33m{}\033[0m : {}".format(row[0], row[1]))
tab_element[row[0]] = {
"sql": row[1],
"duration": row[2],
"thread": row[3]
}
answer = shell.prompt("""Which event do you want to profile ? """)
if answer.isdigit():
if int(answer) not in tab_element.keys():
print("ERROR: element id {} not present in that list !".format(
answer))
return
else:
print("ERROR: [{}] is not the id of an event in the list !".format(
answer))
return
print("\nProfiling of:")
print("-------------")
print(tab_element[int(answer)]['sql'])
print("\033[33mduration: {}\033[0m".format(
tab_element[int(answer)]['duration']))
stmt = """SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM performance_schema.events_stages_history_long
WHERE NESTING_EVENT_ID={} and THREAD_ID={}""".format(
answer, tab_element[int(answer)]['thread'])
run_and_show(stmt)
print("Don't forget to stop the profiling when done.")
return
def get_autoinc_fill(percentage=50, schema=None, session=None):
"""
Prints information about auto_increment fill up.
Args:
percentage (integer): Only shows the tables where auto increments
values are filled to at least % (default: 50).
schema (string): The name of the schema to use. This is optional.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
where_filter = ""
having_filter = ""
import mysqlsh
shell = mysqlsh.globals.shell
if session is None:
session = shell.get_session()
if session is None:
print("No session specified. Either pass a session object to this "
"function or connect the shell to a database")
return
stmt = "select @@information_schema_stats_expiry;"
result = session.run_sql(stmt)
stats = result.fetch_all()
if len(stats) > 0:
for stat in stats:
if int(stat[0]) > 0:
print(
"Warning: information_schema_stats_expiry is set to {0}.".format(*stat))
if shell.options.interactive:
answer = shell.prompt(
"""Do you want to change it ? (y/N) """, {'defaultValue': 'n'})
if answer.lower() == 'y':
stmt = """SET information_schema_stats_expiry=0"""
result = session.run_sql(stmt)
else:
print(
"Changing information_schema_stats_expiry to 0 for this session only")
stmt = """SET information_schema_stats_expiry=0"""
result = session.run_sql(stmt)
if percentage > 0:
having_filter = "HAVING CAST(AUTO_INCREMENT_RATIO AS SIGNED INTEGER) >= {}".format(
percentage)
if schema:
where_filter = "{} AND TABLE_SCHEMA='{}'".format(where_filter, schema)
stmt = """SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
COLUMN_TYPE,
IF(
LOCATE('unsigned', COLUMN_TYPE) > 0,
1,
0
) AS IS_UNSIGNED,
(
CASE DATA_TYPE
WHEN 'tinyint' THEN 255
WHEN 'smallint' THEN 65535
WHEN 'mediumint' THEN 16777215
WHEN 'int' THEN 4294967295
WHEN 'bigint' THEN 18446744073709551615
END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
) AS MAX_VALUE,
AUTO_INCREMENT, CONCAT(ROUND(
AUTO_INCREMENT / (
CASE DATA_TYPE
WHEN 'tinyint' THEN 255
WHEN 'smallint' THEN 65535
WHEN 'mediumint' THEN 16777215
WHEN 'int' THEN 4294967295
WHEN 'bigint' THEN 18446744073709551615
END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
)*100), '%') AS AUTO_INCREMENT_RATIO
FROM
INFORMATION_SCHEMA.COLUMNS
INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema')
AND EXTRA='auto_increment' {}
{}
ORDER BY CAST(AUTO_INCREMENT_RATIO AS SIGNED INTEGER)
""".format(where_filter, having_filter)
run_and_show(stmt)
return
def workload_info(schema=None, table=None, session=None):
"""
Prints the workload ratio between reads and writes with some information
per schema and tables.
This function provides an overview of the workload. Is my application
read or write intensive ? Use this function to get the answer.
Args:
schema (string): The name of the schema to check (default: None). If none is specified,
all schemas are listed.
table (string): The name of a specific table present in the schema defined (default: None).
If none is specified, only the schema is listed. You can use '*' to show them all.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
# Get hold of the global shell object
import mysqlsh
shell = mysqlsh.globals.shell
if session is None:
session = shell.get_session()
if session is None:
print("No session specified. Either pass a session object to this "
"function or connect the shell to a database")
return
# Get first the total server workload
stmt = """SELECT SUM(count_read) `tot reads`,
CONCAT(ROUND((SUM(count_read)/SUM(count_star))*100, 2),"%") `reads`,
SUM(count_write) `tot writes`,
CONCAT(ROUND((SUM(count_write)/sum(count_star))*100, 2),"%") `writes`
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_star > 0 ;"""
result = session.run_sql(stmt)
row = result.fetch_one()
print("MySQL Workload of the server: {} reads and {} writes".format(
row[1], row[3]))
tot_reads = row[0]
tot_writes = row[2]
table_str = ""
table_grp = ""
extra = ""
if schema is None:
where_str = ""
else:
where_str = "AND object_schema='{}'".format(schema)
if table is not None:
stmt = """SELECT object_schema, {}
SUM(count_read) `tot reads`,
CONCAT(round((SUM(count_read)/SUM(count_star))*100, 2),"%") `reads`,
SUM(count_write) `tot writes`,
CONCAT(round((SUM(count_write)/SUM(count_star))*100, 2),"%") `writes`
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_star > 0 {} GROUP BY object_schema{}""".format(
table_str, where_str, table_grp)
result = session.run_sql(stmt)
row = result.fetch_one()
print(
" Workload for schema {}: {} reads and {} writes".format(
schema, row[2], row[4]))
schema_reads = row[1]
schema_writes = row[3]
extra = """CONCAT(ROUND((SUM(count_read)/{})*100, 2),"%") `ratio to schema reads`,
CONCAT(ROUND((SUM(count_write)/{})*100, 2),"%") `ratio to schema writes`,""".format(
schema_reads, schema_writes)
table_str = "object_name,"
table_grp = ", object_name"
if table == '*':
where_str = "AND object_schema='{}'".format(schema)
else:
where_str = "AND object_schema='{}' AND object_name='{}'".format(
schema, table)
stmt = """SELECT object_schema, {}
CONCAT(ROUND((SUM(count_read)/SUM(count_star))*100, 2),"%") `reads`,
CONCAT(ROUND((SUM(count_write)/SUM(count_star))*100, 2),"%") `writes`,
{}
CONCAT(ROUND((SUM(count_read)/{})*100, 2),"%") `ratio to total reads`,
CONCAT(ROUND((SUM(count_write)/{})*100, 2),"%") `ratio to total writes`
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_star > 0 {} GROUP BY object_schema{}""".format(
table_str, extra, tot_reads, tot_writes, where_str, table_grp)
run_and_show(stmt)
return
def show_error_log(limit=10,
type="all",
subsystem="all",
format="table",
session=None):
"""
Display the Errog Log lines.
Args:
limit (integer): The amount of lines to display. 0 means no limit. Default: 10.
type (string): The type of error entries. Valid values are 'all', 'system', 'error', 'warning' and 'note'.
Default is 'all'.
subsystem (string): Filter the entries to only get this specific subsystem. Default is 'all'.
format (string): One of table, tabbed, vertical, json, ndjson, json/raw,
json/array, json/pretty or flat.
Flat is like an error log with colors.
Default is table.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
import mysqlsh
shell = mysqlsh.globals.shell
if session is None:
session = shell.get_session()
if session is None:
print("No session specified. Either pass a session object to this "
"function or connect the shell to a database")
return
if limit < 0:
print("ERROR: the limit should be a postive value !")
return
if limit == 0:
limit_str = ""
else:
limit_str = "LIMIT {}".format(limit)
if type.lower() not in ['all', 'system', 'error', 'warning', 'note']:
print(
"ERROR: '{}' is not a valid type ! It should be 'all', 'system', 'error', 'warning' or 'note'."
.format(type.lower()))
return
if type.lower() == 'all':
type_str = ''
else:
type_str = 'PRIO = "{}" '.format(type.lower())
if subsystem.lower() == 'all':
subsystem_str = ''
else:
if type_str == '':
subsystem_str = 'SUBSYSTEM = "{}" '.format(subsystem.lower())
else:
subsystem_str = 'and SUBSYSTEM = "{}" '.format(subsystem.lower())
if subsystem_str != '' or type_str != '':
where_str = 'WHERE'
else:
where_str = ''
stmt = """(SELECT * FROM performance_schema.error_log {} {} {}
ORDER BY LOGGED DESC {}) ORDER BY LOGGED""".format(
where_str, type_str, subsystem_str, limit_str)
if format != 'flat':
run_and_show(stmt, format, session)
else:
class fg:
error = '\033[31m'
note = '\033[32m'
warning = '\033[33m'
system = '\033[34m'
result = session.run_sql(stmt)
rows = result.fetch_all()
for row in rows:
color = '\033[0m'
if row[2] == 'System':
color = fg.system
if row[2] == 'Error':
color = fg.error
if row[2] == 'Warning':
color = fg.warning
if row[2] == 'Note':
color = fg.note
print("{}{} {} [{}] [{}] [{}] {}".format(color, row[0], row[1],
row[2], row[3], row[4],
row[5]))
return
def get_error_log_by_time(start="1 hour ago",
limit=0,
type="all",
subsystem="all",
format="table",
session=None):
"""
Get Errog Log from a specific time
Args:
start (string): Start time from when you retrieve errors
limit (integer): The amount of lines to display. 0 means no limit. Default: 0.
type (string): The type of error entries. Valid values are 'all', 'system', 'error', 'warning' and 'note'.
Default is 'all'.
subsystem (string): Filter the entries to only get this specific subsystem. Default is 'all'.
format (string): One of table, tabbed, vertical, json, ndjson, json/raw,
json/array, json/pretty or flat.
Flat is like an error log with colors.
Default is table.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
dateparser_spec = util.find_spec("dateparser")
found_dateparser = dateparser_spec is not None
if found_dateparser:
import dateparser
else:
print(
"ERROR: could not import module 'dateparer' which is needed for this method, check if it's installed (Python {}.{}.{})"
.format(sys.version_info[0], sys.version_info[1],
sys.version_info[2]))
return
import mysqlsh
shell = mysqlsh.globals.shell
if session is None:
session = shell.get_session()
if session is None:
print("No session specified. Either pass a session object to this "
"function or connect the shell to a database")
return
if limit < 0:
print("ERROR: the limit should be a postive value !")
return
if limit == 0:
limit_str = ""
else:
limit_str = "LIMIT {}".format(limit)
if type.lower() not in ['all', 'system', 'error', 'warning', 'note']:
print(
"ERROR: '{}' is not a valid type ! It should be 'all', 'system', 'error', 'warning' or 'note'."
.format(type.lower()))
return
if type.lower() == 'all':
type_str = ''
else:
type_str = 'PRIO = "{}" '.format(type.lower())
if subsystem.lower() == 'all':
subsystem_str = ''
else:
if type_str == '':
subsystem_str = 'SUBSYSTEM = "{}" '.format(subsystem.lower())
else:
subsystem_str = 'and SUBSYSTEM = "{}" '.format(subsystem.lower())
start_time = dateparser.parse(start)
if start_time == None:
print(
"ERROR: impossible to parse [{}] and transform it into a valid datetime!"
.format(start))
return
if (datetime.datetime.now() - start_time).days >= 1 and not (
("hours" in start or "h " in start) and "ago" in start):
start_time = datetime.datetime.strptime(
start_time.strftime("%Y-%m-%d"), "%Y-%m-%d")
if start.lower() == "today":
start_time = start_time.strftime("%Y-%m-%d")
where_str = 'WHERE LOGGED >= "{}" '.format(str(start_time))
stmt = """(SELECT * FROM performance_schema.error_log {} {} {}
ORDER BY LOGGED {}) ORDER BY LOGGED""".format(
where_str, type_str, subsystem_str, limit_str)
log_stmt = """SELECT LOGGED
FROM performance_schema.error_log"""
result = session.run_sql(log_stmt)
row = result.fetch_one()
first_entry = row[0]
print("GETTING LOGS FROM {}:".format(str(start_time)))
#log_start = dateparser.parse("{} seconds ago".format(first_entry))
log_start = datetime.datetime.strptime(str(first_entry),
"%Y-%m-%d %H:%M:%S.%f")
if start.lower() != "today":
if log_start > start_time:
print(
"\033[34mWarning: the first log entry available is from {}\033[0m"
.format(first_entry))
if format != 'flat':
run_and_show(stmt, format, session)
else:
class fg:
error = '\033[31m'
note = '\033[32m'
warning = '\033[33m'
system = '\033[34m'
result = session.run_sql(stmt)
rows = result.fetch_all()
for row in rows:
color = '\033[0m'
if row[2] == 'System':
color = fg.system
if row[2] == 'Error':
color = fg.error
if row[2] == 'Warning':
color = fg.warning
if row[2] == 'Note':
color = fg.note
print("{}{} {} [{}] [{}] [{}] {}".format(color, row[0], row[1],
row[2], row[3], row[4],
row[5]))
return
def status(extended=False, format="table", session=None):
"""
Get the replication status information
This function prints the status of replication channels.
Args:
extended (bool): Use extended view. Default is False.
format (string): One of table, tabbed, vertical, json, ndjson, json/raw,
json/array, json/pretty or flat.
Flat is like an error log with colors.
Default is table.
session (object): The optional session object used to query the
database. If omitted the MySQL Shell's current session will be used.
"""
import mysqlsh
shell = mysqlsh.globals.shell
if session is None:
session = shell.get_session()
if session is None:
print("No session specified. Either pass a session object to this "
"function or connect the shell to a database")
return
if extended:
stmt = """SELECT
conn_status.channel_name as channel_name,
conn_status.service_state as IO_thread,
applier_status.service_state as SQL_thread,
conn_status.LAST_QUEUED_TRANSACTION as last_queued_transaction,
applier_status.LAST_APPLIED_TRANSACTION as last_applied_transaction,
if(LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP = 0, 0,
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) 'rep delay (sec)',
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP -
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP 'transport time',
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP -
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP 'time RL',
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP 'apply time',
if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = "","0" ,
abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,
timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) `lag_in_sec`
FROM
performance_schema.replication_connection_status AS conn_status
JOIN performance_schema.replication_applier_status_by_worker AS applier_status
ON applier_status.channel_name = conn_status.channel_name"""
else:
stmt = """SELECT
conn_status.channel_name as channel_name,
conn_status.service_state as IO_thread,
applier_status.service_state as SQL_thread,
if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = "","0" ,
abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,
timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) `lag_in_sec`
FROM
performance_schema.replication_connection_status AS conn_status
JOIN performance_schema.replication_applier_status_by_worker AS applier_status
ON applier_status.channel_name = conn_status.channel_name
order by 4 desc limit 1"""
run_and_show(stmt, format, session)
return
def get_full_detail(original_query, session, dbok):
shell = mysqlsh.globals.shell
stmt = """EXPLAIN FORMAT=json %s""" % original_query
try:
result = session.run_sql(stmt)
except mysqlsh.DBError as err:
print("Aborting: {}".format(err))
return
row = result.fetch_one()
qep = row[0]
qep_json = json.loads(qep)
print("The cost of the query is {}".format(
qep_json["query_block"]["cost_info"]["query_cost"]))
answer = shell.prompt('Do you want to have EXPLAIN output? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN %s""" % original_query
run_and_show(stmt, 'vertical')
answer = shell.prompt(
'Do you want to have EXPLAIN in JSON format output? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
print(qep)
stmt = """EXPLAIN format=tree %s""" % original_query
result = session.run_sql(stmt)
row = result.fetch_one()
qep_tree = row[0]
answer = shell.prompt(
'Do you want to have EXPLAIN in TREE format output? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
print(qep_tree)
answer = shell.prompt('Do you want to have EXPLAIN ANALYZE output? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
stmt = """EXPLAIN ANALYZE %s""" % original_query
result = session.run_sql(stmt)
row = result.fetch_one()
print(row[0])
dbok, got_one = _load_qep(original_query, qep, session, dbok)
if dbok and got_one:
answer = shell.prompt(
'Do you want to compare with a previous QEP? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
all_ids = _load_qep_all(original_query, session)
i = 0
if len(all_ids) > 0:
fmt = "| {0:>3s} | {1:20s} | {2:>11s} | {3:>7s} |"
header = fmt.format("Num", "Timestamp", "Query Cost",
"Version")
bar = "+" + "-" * 5 + "+" + "-" * 22 + "+" + "-" * 13 + "+" + "-" * 9 + "+"
print(bar)
print(header)
print(bar)
for rec in all_ids:
i += 1
print(
fmt.format(str(i), str(rec[1]), str(rec[2]),
str(rec[3])))
print(bar)
while True:
answer = shell.prompt(
'With which previous QEP do you want to compare? (1) ',
{'defaultValue': '1'})
if answer.isdigit():
if int(answer) > 0 and int(answer) <= len(all_ids):
qep2, qep2_tree = _get_qep_by_id(
all_ids[int(answer) - 1][0], session)
print(
"\033[33mCURRENT:\n--------\n{}\033[0m".format(
qep_tree))
print("\033[36mPREVIOUS:\n---------\n{}\033[0m".
format(qep2_tree))
print()
break
answer = shell.prompt('Do you want to save the QEP? (y/N) ',
{'defaultValue': 'n'})
if answer.lower() == 'y':
_save_qep(original_query, qep, qep_tree, session, dbok)
return dbok
