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