def clean(retention_days):
if float(retention_days) == 0:
slow_queries.truncate_slow_queries()
else:
start_time = int((time.time() - retention_days * 24 * 60 * 60) * 1000)
slow_queries.delete_slow_queries(start_time)
write_to_terminal('Success to delete redundant results.')
def setup_directory(confpath):
# Determine whether the directory is empty.
if os.path.exists(confpath) and len(os.listdir(confpath)) > 0:
raise SetupError("Given setup directory '%s' already exists." %
confpath)
utils.write_to_terminal(
"You are not in the interactive mode so you must modify configurations manually.\n"
"The file you need to modify is '%s'.\n"
"After configuring, you should continue to set up and initialize the directory with --initialize option, "
"e.g.,\n "
"'... service setup -c %s --initialize'" %
(os.path.join(confpath, constants.CONFILE_NAME), confpath),
color='yellow')
# Make the confpath directory and copy all files
# (basically all files are config files) from MISC directory.
shutil.copytree(src=constants.MISC_PATH, dst=confpath)
# output configurations
with open(file=os.path.join(confpath, constants.CONFILE_NAME),
mode='r+') as fp:
old = fp.readlines()
# Add header comments (including license and notice).
fp.seek(0)
fp.write(DBMIND_CONF_HEADER)
fp.writelines(old)
utils.write_to_terminal(
"Configure directory '%s' has been created successfully." % confpath,
color='green')
def show(query, start_time, end_time):
field_names = (
'slow_query_id', 'schema_name', 'db_name',
'query', 'start_at', 'duration_time',
'root_cause', 'suggestion'
)
output_table = PrettyTable()
output_table.field_names = field_names
result = slow_queries.select_slow_queries(field_names, query, start_time, end_time)
nb_rows = 0
for slow_query in result:
row = [getattr(slow_query, field) for field in field_names]
output_table.add_row(row)
nb_rows += 1
if nb_rows > 50:
write_to_terminal('The number of rows is greater than 50. '
'It seems too long to see.')
char = keep_inputting_until_correct('Do you want to dump to a file? [Y]es, [N]o.', ('Y', 'N'))
if char == 'Y':
dump_file_name = 'slow_queries_%s.txt' % int(time.time())
with open(dump_file_name, 'w+') as fp:
fp.write(str(output_table))
write_to_terminal('Dumped file is %s.' % os.path.realpath(dump_file_name))
elif char == 'N':
print(output_table)
print('(%d rows)' % nb_rows)
else:
print(output_table)
print('(%d rows)' % nb_rows)
def clean(retention_days):
if float(retention_days) == 0:
forecasting_metrics.truncate_forecasting_metrics()
else:
start_time = int(
(time.time() - float(retention_days) * 24 * 60 * 60) * 1000)
forecasting_metrics.delete_timeout_forecasting_metrics(start_time)
write_to_terminal('Success to delete redundant results.')
def _create_dynamic_config_schema_and_generate_keys():
utils.write_to_terminal(
'Starting to generate a dynamic config file...', color='green')
create_dynamic_config_schema()
s1_ = security.safe_random_string(16)
s2_ = security.safe_random_string(16)
dynamic_config_set('dbmind_config', 'cipher_s1', s1_)
dynamic_config_set('dbmind_config', 'cipher_s2', s2_)
return s1_, s2_
def set_config_parameter(confpath, section: str, option: str, value: str):
if not os.path.exists(confpath):
raise ConfigSettingError(
"Invalid directory '%s', please set up first." % confpath)
# Section is case sensitive.
if section.isupper():
with ConfigUpdater(os.path.join(confpath,
constants.CONFILE_NAME)) as config:
# If not found, raise NoSectionError or NoOptionError.
try:
old_value, comment = config.get(section, option)
except (NoSectionError, NoOptionError):
raise ConfigSettingError('Not found the parameter %s-%s.' %
(section, option))
valid, reason = check_config_validity(section, option, value)
if not valid:
raise ConfigSettingError('Incorrect value due to %s.' % reason)
# If user wants to change password, we should encrypt the plain-text password first.
if 'password' in option:
# dynamic_config_xxx searches file from current working directory.
os.chdir(confpath)
s1 = dynamic_config_get('dbmind_config', 'cipher_s1')
s2 = dynamic_config_get('dbmind_config', 'cipher_s2')
# Every time a new password is generated, update the IV.
iv = security.generate_an_iv()
dynamic_config_set('iv_table', '%s-%s' % (section, option), iv)
cipher = security.encrypt(s1, s2, iv, value)
value = ENCRYPTED_SIGNAL + cipher
config.set(section, option, value, comment)
elif section.islower():
# dynamic_config_xxx searches file from current working directory.
os.chdir(confpath)
try:
old_value = dynamic_config_get(section, option)
except ValueError:
raise ConfigSettingError('Not found the parameter %s-%s.' %
(section, option))
if not old_value:
raise ConfigSettingError('Not found the parameter %s-%s.' %
(section, option))
dynamic_config_set(section, option, value)
else:
# If run here, it seems that the format of section string is not correct.
raise ConfigSettingError(
'%s is an incorrect section. '
'Please take note that section string is case sensitive.' %
section)
write_to_terminal('Success to modify parameter %s-%s.' % (section, option),
color='green')
def run(self):
# Wipe off password of url for the process title.
try:
url = self.args.url
wiped_url = wipe_off_password(url)
with open('/proc/self/cmdline') as fp:
cmdline = fp.readline().replace('\x00', ' ')
wiped_cmdline = cmdline.replace(url, wiped_url)
set_proc_title(wiped_cmdline)
except FileNotFoundError:
# ignore
pass
set_logger(self.args.__dict__['log.filepath'],
self.args.__dict__['log.level'])
try:
service.config_collecting_params(
url=self.args.url,
parallel=self.args.parallel,
disable_cache=self.args.disable_cache,
constant_labels=self.args.constant_labels,
)
except ConnectionError:
write_to_terminal('Failed to connect to the url, exiting...',
color='red')
sys.exit(1)
if not self.args.disable_settings_metrics:
with open(os.path.join(YAML_DIR_PATH, PG_SETTINGS_YAML),
errors='ignore') as fp:
service.register_metrics(yaml.load(fp, Loader=yaml.FullLoader))
if not self.args.disable_statement_history_metrics:
with open(os.path.join(YAML_DIR_PATH, STATEMENTS_YAML),
errors='ignore') as fp:
service.register_metrics(yaml.load(fp, Loader=yaml.FullLoader),
force_connection_db='postgres')
with open(self.args.config, errors='ignore') as fp:
service.register_metrics(yaml.load(fp, Loader=yaml.FullLoader))
check_ssl_file_permission(self.args.ssl_keyfile,
self.args.ssl_certfile)
check_ssl_certificate_remaining_days(self.args.ssl_certfile)
controller.run(host=self.args.__dict__['web.listen_address'],
port=self.args.__dict__['web.listen_port'],
telemetry_path=self.args.__dict__['web.telemetry_path'],
ssl_keyfile=self.args.ssl_keyfile,
ssl_certfile=self.args.ssl_certfile,
ssl_keyfile_password=self.args.keyfile_password)
def check_config_validity(section, option, value, silent=False):
config_item = '%s-%s' % (section, option)
# exceptional cases:
if config_item in ('METADATABASE-port', 'METADATABASE-host'):
if value.strip() == '' or value == NULL_TYPE:
return True, None
# normal inspection process:
if 'port' in option:
valid_port = check_port_valid(value)
if not valid_port:
return False, 'Invalid port for %s: %s(1024-65535)' % (config_item,
value)
if 'host' in option:
valid_host = check_ip_valid(value)
if not valid_host:
return False, 'Invalid IP Address for %s: %s' % (config_item,
value)
if 'database' in option:
if value == NULL_TYPE or value.strip() == '':
return False, 'Unspecified database name'
if config_item in INTEGER_CONFIG:
if not str.isdigit(value) or int(value) <= 0:
return False, 'Invalid value for %s: %s' % (config_item, value)
options = CONFIG_OPTIONS.get(config_item)
if options and value not in options:
return False, 'Invalid choice for %s: %s' % (config_item, value)
if 'dbtype' in option and value == 'opengauss' and not silent:
write_to_terminal(
'WARN: default PostgreSQL connector (psycopg2-binary) does not support openGauss.\n'
'It would help if you compiled psycopg2 with openGauss manually or '
'created a connection user after setting the GUC password_encryption_type to 1.',
color='yellow')
if 'dbtype' in option and value == 'sqlite' and not silent:
write_to_terminal(
'NOTE: SQLite currently only supports local deployment, so you only need to provide '
'METADATABASE-database information. if you provide other information, DBMind will '
'ignore them.',
color='yellow')
# Add more checks here.
return True, None
def show(metric, host, start_time, end_time):
field_names = ('rowid', 'metric_name', 'host_ip', 'metric_time',
'metric_value')
output_table = PrettyTable()
output_table.field_names = field_names
result = forecasting_metrics.select_forecasting_metric(
metric_name=metric,
host_ip=host,
min_metric_time=start_time,
max_metric_time=end_time).all()
for row_ in result:
row = [getattr(row_, field) for field in field_names]
output_table.add_row(row)
nb_rows = len(result)
if nb_rows > 50:
write_to_terminal('The number of rows is greater than 50. '
'It seems too long to see.')
char = keep_inputting_until_correct(
'Do you want to dump to a file? [Y]es, [N]o.', ('Y', 'N'))
if char == 'Y':
dump_file_name = 'metric_forecast_%s.csv' % int(time.time())
with open(dump_file_name, 'w+') as fp:
csv_writer = csv.writer(fp)
for row_ in result:
row = [
str(getattr(row_, field)).strip()
for field in field_names
]
csv_writer.writerow(row)
write_to_terminal('Dumped file is %s.' %
os.path.realpath(dump_file_name))
elif char == 'N':
print(output_table)
print('(%d rows)' % nb_rows)
else:
print(output_table)
print('(%d rows)' % nb_rows)
def main(argv):
parser = argparse.ArgumentParser(description='Slow Query Diagnosis: Analyse the root cause of slow query')
parser.add_argument('action', choices=('show', 'clean'), help='choose a functionality to perform')
parser.add_argument('-c', '--conf', metavar='DIRECTORY', required=True,
help='set the directory of configuration files')
parser.add_argument('--query', metavar='SLOW_QUERY',
help='set a slow query you want to retrieve')
parser.add_argument('--start-time', type=check_positive_integer, metavar='TIMESTAMP_IN_MICROSECONDS',
help='set the start time of a slow SQL diagnosis result to be retrieved')
parser.add_argument('--end-time', type=check_positive_integer, metavar='TIMESTAMP_IN_MICROSECONDS',
help='set the end time of a slow SQL diagnosis result to be retrieved')
parser.add_argument('--retention-days', type=check_positive_float, metavar='DAYS', default=0,
help='clear historical diagnosis results and set '
'the maximum number of days to retain data')
args = parser.parse_args(argv)
if not os.path.exists(args.conf):
parser.exit(1, 'Not found the directory %s.' % args.conf)
if args.action == 'show':
if None in (args.query, args.start_time, args.end_time):
write_to_terminal('There may be a lot of results because you did not use all filter conditions.',
color='red')
inputted_char = keep_inputting_until_correct('Press [A] to agree, press [Q] to quit:', ('A', 'Q'))
if inputted_char == 'Q':
parser.exit(0, "Quitting due to user's instruction.")
elif args.action == 'clean':
if args.retention_days is None:
write_to_terminal('You did not specify retention days, so we will delete all historical results.',
color='red')
inputted_char = keep_inputting_until_correct('Press [A] to agree, press [Q] to quit:', ('A', 'Q'))
if inputted_char == 'Q':
parser.exit(0, "Quitting due to user's instruction.")
# Set the global_vars so that DAO can login the meta-database.
os.chdir(args.conf)
global_vars.configs = load_sys_configs(constants.CONFILE_NAME)
try:
if args.action == 'show':
show(args.query, args.start_time, args.end_time)
elif args.action == 'clean':
clean(args.retention_days)
except Exception as e:
write_to_terminal('An error occurred probably due to database operations, '
'please check database configurations. For details:\n'
+ str(e), color='red', level='error')
traceback.print_tb(e.__traceback__)
return 2
return args
def test_write_to_terminal():
utils.write_to_terminal(1111)
utils.write_to_terminal(111, level='error', color='red')
utils.write_to_terminal('hello world', color='yellow')
def initialize_and_check_config(confpath, interactive=False):
if not os.path.exists(confpath):
raise SetupError('Not found the directory %s.' % confpath)
confpath = os.path.realpath(confpath) # in case of dir changed.
os.chdir(confpath)
dbmind_conf_path = os.path.join(confpath, constants.CONFILE_NAME)
dynamic_config_path = os.path.join(confpath, constants.DYNAMIC_CONFIG)
def _create_dynamic_config_schema_and_generate_keys():
utils.write_to_terminal(
'Starting to generate a dynamic config file...', color='green')
create_dynamic_config_schema()
s1_ = security.safe_random_string(16)
s2_ = security.safe_random_string(16)
dynamic_config_set('dbmind_config', 'cipher_s1', s1_)
dynamic_config_set('dbmind_config', 'cipher_s2', s2_)
return s1_, s2_
if not os.path.exists(dynamic_config_path):
# If dynamic config file does not exist, create a new one.
s1, s2 = _create_dynamic_config_schema_and_generate_keys()
else:
# If exists, need not create a new dynamic config file
# and directly load hash key s1 and s2 from it.
s1 = dynamic_config_get('dbmind_config', 'cipher_s1')
s2 = dynamic_config_get('dbmind_config', 'cipher_s2')
if not (s1 and s2):
# If s1 or s2 is invalid, it indicates that an broken event may occurred while generating
# the dynamic config file. Hence, the whole process of generation is unreliable and we have to
# generate a new dynamic config file.
os.unlink(dynamic_config_path)
s1, s2 = _create_dynamic_config_schema_and_generate_keys()
# Check some configurations and encrypt passwords.
with ConfigUpdater(dbmind_conf_path) as config:
if not interactive:
for section, section_comment in config.sections(SKIP_LIST):
for option, value, inline_comment in config.items(section):
valid, invalid_reason = check_config_validity(
section, option, value)
if not valid:
raise SetupError(
"Wrong %s-%s in the file dbmind.conf due to '%s'. Please revise it."
% (section, option, invalid_reason))
utils.write_to_terminal(
'Starting to encrypt the plain-text passwords in the config file...',
color='green')
for section, section_comment in config.sections(SKIP_LIST):
for option, value, inline_comment in config.items(section):
if 'password' in option and value != NULL_TYPE:
# Skip when the password has encrypted.
if value.startswith(ENCRYPTED_SIGNAL):
continue
# Every time a new password is generated, update the IV.
iv = security.generate_an_iv()
dynamic_config_set('iv_table', '%s-%s' % (section, option),
iv)
cipher_text = security.encrypt(s1, s2, iv, value)
# Use a signal ENCRYPTED_SIGNAL to mark the password that has been encrypted.
decorated_cipher_text = ENCRYPTED_SIGNAL + cipher_text
config.set(section, option, decorated_cipher_text,
inline_comment)
# config and initialize meta-data database.
utils.write_to_terminal(
'Starting to initialize and check the essential variables...',
color='green')
global_vars.dynamic_configs = DynamicConfig
global_vars.configs = load_sys_configs(constants.CONFILE_NAME)
utils.write_to_terminal(
'Starting to connect to meta-database and create tables...',
color='green')
try:
create_metadatabase_schema(check_first=False)
utils.write_to_terminal('The setup process finished successfully.',
color='green')
except DuplicateTableError:
utils.write_to_terminal(
'The given database has duplicate tables. '
'If you want to reinitialize the database, press [R]. '
'If you want to keep the existent tables, press [K].',
color='red')
input_char = ''
while input_char not in ('R', 'K'):
input_char = input(
'Press [R] to reinitialize; Press [K] to keep and ignore:'
).upper()
if input_char == 'R':
utils.write_to_terminal(
'Starting to drop existent tables in meta-database...',
color='green')
destroy_metadatabase()
utils.write_to_terminal(
'Starting to create tables for meta-database...',
color='green')
create_metadatabase_schema(check_first=True)
if input_char == 'K':
utils.write_to_terminal('Ignoring...', color='green')
utils.write_to_terminal('The setup process finished successfully.',
color='green')
except SQLExecutionError:
utils.write_to_terminal(
'Failed to link metadatabase due to unknown error, '
'please check the database and its configuration.',
color='red')
def setup_directory_interactive(confpath):
# Determine whether the directory is empty.
if os.path.exists(confpath) and len(os.listdir(confpath)) > 0:
raise SetupError("Given setup directory '%s' already exists." %
confpath)
# Make the confpath directory and copy all files
# (basically all files are config files) from MISC directory.
shutil.copytree(src=constants.MISC_PATH, dst=confpath)
utils.write_to_terminal('Starting to configure...', color='green')
# Generate an initial configuration file.
config_src = os.path.join(constants.MISC_PATH, constants.CONFILE_NAME)
config_dst = os.path.join(confpath, constants.CONFILE_NAME)
# read configurations
config = ConfigParser(inline_comment_prefixes=None)
with open(file=config_src, mode='r', errors='ignore') as fp:
config.read_file(fp)
try:
# Modify configuration items by user's typing.
for section in config.sections():
if section in SKIP_LIST:
continue
section_comment = config.get('COMMENT', section, fallback='')
utils.write_to_terminal('[%s]' % section, color='white')
utils.write_to_terminal(section_comment, color='yellow')
# Get each configuration item.
for option, values in config.items(section):
try:
default_value, inline_comment = map(
str.strip, values.rsplit('#', 1))
except ValueError:
default_value, inline_comment = values.strip(), ''
# If not set default value, the default value is null.
if default_value.strip() == '':
default_value = NULL_TYPE
# hidden password
input_value = ''
if 'password' in option:
input_func = getpass.getpass
else:
input_func = input
while input_value.strip() == '':
# Ask for options.
input_value = input_func(
'%s (%s) [default: %s]:' %
(option, inline_comment, default_value))
# If user does not set the option, set default target.
if input_value.strip() == '':
input_value = default_value
valid, invalid_reason = check_config_validity(
section, option, input_value)
if not valid:
utils.write_to_terminal("Please retype due to '%s'." %
invalid_reason,
level='error',
color='red')
input_value = ''
config.set(section, option,
'%s # %s' % (input_value, inline_comment))
except (KeyboardInterrupt, EOFError):
utils.write_to_terminal(
'Removing generated files due to keyboard interrupt.')
shutil.rmtree(path=confpath)
return
# output configurations
with open(file=config_dst, mode='w+') as fp:
# Add header comments (including license and notice).
fp.write(DBMIND_CONF_HEADER)
config.write(fp)
initialize_and_check_config(confpath, interactive=True)