def get_routine_privs(server, db_name, routine_name):
""" Get the list of grantees and their privileges for a routine.
server[in] Instance of Server class, where the query will be
executed.
db_name[in] Name of the database where the table belongs to.
routine_name[in] Name of the routine to check.
Returns list of tuples (<GRANTEE>, <SET OF GRANTS>).
"""
tpl_lst = []
# remove backticks if necesssary
if is_quoted_with_backticks(db_name):
db_name = remove_backtick_quoting(db_name)
if is_quoted_with_backticks(routine_name):
routine_name = remove_backtick_quoting(routine_name)
# Build query
query = _PROCS_PRIV_QUERY.format(db_name, routine_name)
res = server.exec_query(query)
for user, host, grants in res:
grants = set((grant.upper() for grant in grants.split(',')))
# remove USAGE privilege since it does nothing.
grants.discard('USAGE')
if grants:
tpl_lst.append(("'{0}'@'{1}'".format(user, host), grants))
return tpl_lst
def get_routine_privs(server, db_name, routine_name):
""" Get the list of grantees and their privileges for a routine.
server[in] Instance of Server class, where the query will be
executed.
db_name[in] Name of the database where the table belongs to.
routine_name[in] Name of the routine to check.
Returns list of tuples (<GRANTEE>, <SET OF GRANTS>).
"""
tpl_lst = []
# Get sql_mode in server
sql_mode = server.select_variable("SQL_MODE")
# remove backticks if necesssary
if is_quoted_with_backticks(db_name, sql_mode):
db_name = remove_backtick_quoting(db_name, sql_mode)
if is_quoted_with_backticks(routine_name, sql_mode):
routine_name = remove_backtick_quoting(routine_name, sql_mode)
# Build query
query = _PROCS_PRIV_QUERY.format(db_name, routine_name)
res = server.exec_query(query)
for user, host, grants in res:
grants = set((grant.upper() for grant in grants.split(',')))
# remove USAGE privilege since it does nothing.
grants.discard('USAGE')
if grants:
tpl_lst.append(("'{0}'@'{1}'".format(user, host), grants))
return tpl_lst
def get_table_privs(server, db_name, table_name):
""" Get the list of grantees and their privileges for a specific table.
server[in] Instance of Server class, where the query will be
executed.
db_name[in] Name of the database where the table belongs to.
table_name[in] Name of the table to check.
Returns list of tuples (<Grantee>, <SET OF GRANTS>).
"""
tpl_lst = []
# Get sql_mode in server
sql_mode = server.select_variable("SQL_MODE")
# Remove backticks if necessary
if is_quoted_with_backticks(db_name, sql_mode):
db_name = remove_backtick_quoting(db_name, sql_mode)
if is_quoted_with_backticks(table_name, sql_mode):
table_name = remove_backtick_quoting(table_name, sql_mode)
# Build query
query = _TABLE_PRIV_QUERY.format(db_name, table_name)
res = server.exec_query(query)
for grantee, grant_option, grants in res:
grants = set((grant.upper() for grant in grants.split(',')))
# remove USAGE privilege since it does nothing.
grants.discard('USAGE')
if grants:
if 'Y' in grant_option.upper():
grants.add('GRANT OPTION')
tpl_lst.append((grantee, grants))
return tpl_lst
def get_table_privs(server, db_name, table_name):
""" Get the list of grantees and their privileges for a specific table.
server[in] Instance of Server class, where the query will be
executed.
db_name[in] Name of the database where the table belongs to.
table_name[in] Name of the table to check.
Returns list of tuples (<Grantee>, <SET OF GRANTS>).
"""
tpl_lst = []
# Remove backticks if necessary
if is_quoted_with_backticks(db_name):
db_name = remove_backtick_quoting(db_name)
if is_quoted_with_backticks(table_name):
table_name = remove_backtick_quoting(table_name)
# Build query
query = _TABLE_PRIV_QUERY.format(db_name, table_name)
res = server.exec_query(query)
for grantee, grant_option, grants in res:
grants = set((grant.upper() for grant in grants.split(',')))
# remove USAGE privilege since it does nothing.
grants.discard('USAGE')
if grants:
if 'Y' in grant_option.upper():
grants.add('GRANT OPTION')
tpl_lst.append((grantee, grants))
return tpl_lst
def get_object_type(self, object_name):
"""Return the object type of an object
This method attempts to locate the object name among the objects
in the database. It returns the object type if found or None
if not found.
object_name[in] Name of the object to find
Returns (string) object type or None if not found
"""
object_type = None
# Remove object backticks if needed
obj_name = remove_backtick_quoting(object_name) \
if is_quoted_with_backticks(object_name) else object_name
res = self.source.exec_query(_OBJTYPE_QUERY %
{ 'db_name' : self.db_name,
'obj_name' : obj_name })
if res != [] and res is not None and len(res) > 0:
object_type = res[0][0]
if object_type == 'BASE TABLE':
object_type = 'TABLE'
return object_type
def get_db_privs(server, db_name):
""" Get the list of grantees and their privileges for a database.
server[in] Instance of Server class, where the query will be
executed.
db_name[in] Name of the database to check.
Returns list of tuples (<Grantee>, <SET OF GRANTS>).
"""
tpl_lst = []
# remove backticks if necessary
if is_quoted_with_backticks(db_name):
db_name = remove_backtick_quoting(db_name)
# Build query
query = _DB_PRIVS_QUERY.format(db_name)
res = server.exec_query(query)
for grantee, grant_option, grants in res:
grants = set((grant.upper() for grant in grants.split(',')))
# remove USAGE privilege since it does nothing.
grants.discard('USAGE')
if grants:
if 'Y' in grant_option.upper():
grants.add('GRANT OPTION')
tpl_lst.append((grantee, grants))
return tpl_lst
def get_column_metadata(self, columns=None):
"""Get information about the table for the bulk insert operation.
This method builds lists that describe the metadata of the table. This
includes lists for:
column names
column format for building VALUES clause
blob fields - for use in generating INSERT/UPDATE for blobs
text fields - for use in checking for single quotes
columns[in] if None, use EXPLAIN else use column list.
"""
if columns is None:
columns = self.server.exec_query("explain %s" % self.q_table)
stop = len(columns)
self.column_names = []
self.q_column_names = []
col_format_values = [''] * stop
if columns is not None:
for col in range(0, stop):
if is_quoted_with_backticks(columns[col][0]):
self.column_names.append(
remove_backtick_quoting(columns[col][0]))
self.q_column_names.append(columns[col][0])
else:
self.column_names.append(columns[col][0])
self.q_column_names.append(
quote_with_backticks(columns[col][0]))
col_type_prefix = columns[col][1][0:4].lower()
if col_type_prefix in ('varc', 'char', 'enum', 'set('):
self.text_columns.append(col)
col_format_values[col] = "'%s'"
elif col_type_prefix in ("blob", "text"):
self.blob_columns.append(col)
col_format_values[col] = "%s"
elif col_type_prefix in ("date", "time"):
col_format_values[col] = "'%s'"
else:
col_format_values[col] = "%s"
self.column_format = "%s%s%s" % \
(" (", ', '.join(col_format_values), ")")
def __init__(self, source, name, options={}):
"""Constructor
source[in] A Server object
name[in] Name of database
verbose[in] print extra data during operations (optional)
default value = False
options[in] Array of options for controlling what is included
and how operations perform (e.g., verbose)
"""
self.source = source
# Keep database identifier considering backtick quotes
if is_quoted_with_backticks(name):
self.q_db_name = name
self.db_name = remove_backtick_quoting(self.q_db_name)
else:
self.db_name = name
self.q_db_name = quote_with_backticks(self.db_name)
self.verbose = options.get("verbose", False)
self.skip_tables = options.get("skip_tables", False)
self.skip_views = options.get("skip_views", False)
self.skip_triggers = options.get("skip_triggers", False)
self.skip_procs = options.get("skip_procs", False)
self.skip_funcs = options.get("skip_funcs", False)
self.skip_events = options.get("skip_events", False)
self.skip_grants = options.get("skip_grants", False)
self.skip_create = options.get("skip_create", False)
self.skip_data = options.get("skip_data", False)
self.exclude_patterns = options.get("exclude_patterns", None)
self.use_regexp = options.get("use_regexp", False)
self.new_db = None
self.q_new_db = None
self.init_called = False
self.destination = None # Used for copy mode
self.cloning = False # Used for clone mode
self.query_options = { # Used for skipping fetch of rows
'fetch' : False
}
self.objects = []
self.new_objects = []
def _drop_compare_object(server, db_name, tbl_name):
"""Drop the compare object table
server[in] Server instance
db_name[in] database name
tbl_name[in] table name
"""
# Quote compare table appropriately with backticks
q_db_name = db_name if is_quoted_with_backticks(db_name) \
else quote_with_backticks(db_name)
if is_quoted_with_backticks(tbl_name):
q_tbl_name = remove_backtick_quoting(tbl_name)
else:
q_tbl_name = tbl_name
q_tbl_name = quote_with_backticks(
_COMPARE_TABLE_NAME.format(tbl=q_tbl_name))
try:
server.exec_query(
_COMPARE_TABLE_DROP.format(db=q_db_name, compare_tbl=q_tbl_name))
except:
pass
def _drop_compare_object(server, db_name, tbl_name):
"""Drop the compare object table
server[in] Server instance
db_name[in] database name
tbl_name[in] table name
"""
# Quote compare table appropriately with backticks
q_db_name = db_name if is_quoted_with_backticks(db_name) \
else quote_with_backticks(db_name)
if is_quoted_with_backticks(tbl_name):
q_tbl_name = remove_backtick_quoting(tbl_name)
else:
q_tbl_name = tbl_name
q_tbl_name = quote_with_backticks(
_COMPARE_TABLE_NAME.format(tbl=q_tbl_name))
try:
server.exec_query(_COMPARE_TABLE_DROP.format(db=q_db_name,
compare_tbl=q_tbl_name))
except:
pass
def __init__(self, source, name, options={}):
"""Constructor
source[in] A Server object
name[in] Name of database
verbose[in] print extra data during operations (optional)
default value = False
options[in] Array of options for controlling what is included
and how operations perform (e.g., verbose)
"""
self.source = source
# Keep database identifier considering backtick quotes
if is_quoted_with_backticks(name):
self.q_db_name = name
self.db_name = remove_backtick_quoting(self.q_db_name)
else:
self.db_name = name
self.q_db_name = quote_with_backticks(self.db_name)
self.verbose = options.get("verbose", False)
self.skip_tables = options.get("skip_tables", False)
self.skip_views = options.get("skip_views", False)
self.skip_triggers = options.get("skip_triggers", False)
self.skip_procs = options.get("skip_procs", False)
self.skip_funcs = options.get("skip_funcs", False)
self.skip_events = options.get("skip_events", False)
self.skip_grants = options.get("skip_grants", False)
self.skip_create = options.get("skip_create", False)
self.skip_data = options.get("skip_data", False)
self.exclude_patterns = options.get("exclude_patterns", None)
self.use_regexp = options.get("use_regexp", False)
self.new_db = None
self.q_new_db = None
self.init_called = False
self.destination = None # Used for copy mode
self.cloning = False # Used for clone mode
self.query_options = {"fetch": False} # Used for skipping fetch of rows
self.objects = []
self.new_objects = []
def get_object_type(self, object_name):
"""Return the object type of an object
This method attempts to locate the object name among the objects
in the database. It returns the object type if found or None
if not found.
object_name[in] Name of the object to find
Returns (string) object type or None if not found
"""
object_type = None
# Remove object backticks if needed
obj_name = remove_backtick_quoting(object_name) if is_quoted_with_backticks(object_name) else object_name
res = self.source.exec_query(_OBJTYPE_QUERY % {"db_name": self.db_name, "obj_name": obj_name})
if res != [] and res is not None and len(res) > 0:
object_type = res[0][0]
if object_type == "BASE TABLE":
object_type = "TABLE"
return object_type
# Get the sql_mode set on source and destination server
conn_opts = {
'quiet': True,
'version': "5.1.30",
}
try:
servers = connect_servers(server_values, None, conn_opts)
server_sql_mode = servers[0].select_variable("SQL_MODE")
except UtilError:
server_sql_mode = ''
# Build list of databases to copy
db_list = []
for db in args:
# Remove backtick quotes (handled later)
db = remove_backtick_quoting(db, server_sql_mode) \
if is_quoted_with_backticks(db, server_sql_mode) else db
db_list.append(db)
try:
# record start time
if opt.verbosity >= 3:
start_export_time = time.time()
# Export databases concurrently for non posix systems (windows).
if options['multiprocess'] > 1 and os.name != 'posix':
# Create export databases tasks.
export_db_tasks = []
for db in db_list:
export_task = {
'srv_con': server_values,
try:
options.update(get_ssl_dict(opt))
server_values = parse_connection(opt.server, None, options)
except FormatError:
_, err, _ = sys.exc_info()
parser.error("Server connection values invalid: {0}.".format(err))
except UtilError:
_, err, _ = sys.exc_info()
parser.error("Server connection values invalid: "
"{0}.".format(err.errmsg))
# Build list of databases to copy
db_list = []
for db in args:
# Remove backtick quotes (handled later)
db = remove_backtick_quoting(db) \
if is_quoted_with_backticks(db) else db
db_list.append(db)
try:
# record start time
if opt.verbosity >= 3:
start_export_time = time.time()
# Export databases concurrently for non posix systems (windows).
if options['multiprocess'] > 1 and os.name != 'posix':
# Create export databases tasks.
export_db_tasks = []
for db in db_list:
export_task = {
'srv_con': server_values,
# the initial specified string. In general, this identifies the missing
# use of backticks.
matched_size = len(orig_db)
if new_db:
# add 1 for the separator ':'
matched_size += 1
matched_size += len(new_db)
if matched_size != len(db):
parser.error(
PARSE_ERR_DB_PAIR_EXT.format(
db_pair=db, db1_label="orig_db", db2_label="new_db", db1_value=orig_db, db2_value=new_db
)
)
# Remove backtick quotes (handled later)
orig_db = remove_backtick_quoting(orig_db) if is_quoted_with_backticks(orig_db) else orig_db
new_db = remove_backtick_quoting(new_db) if new_db and is_quoted_with_backticks(new_db) else new_db
db_entry = (orig_db, new_db)
db_list.append(db_entry)
try:
# Record start time.
if opt.verbosity >= 3:
start_copy_time = time.time()
# Copy databases concurrently for non posix systems (windows).
if options["multiprocess"] > 1 and os.name != "posix":
# Create copy databases tasks.
copy_db_tasks = []
for db in db_list:
copy_task = {"source_srv": source_values, "dest_srv": dest_values, "db_list": [db], "options": options}
# Parse server connection values
try:
server_values = parse_connection(opt.server, None, options)
except FormatError:
_, err, _ = sys.exc_info()
parser.error("Server connection values invalid: %s." % err)
except UtilError:
_, err, _ = sys.exc_info()
parser.error("Server connection values invalid: %s." % err.errmsg)
# Build list of databases to copy
db_list = []
for db in args:
# Remove backtick quotes (handled later)
db = remove_backtick_quoting(db) if is_quoted_with_backticks(db) else db
db_list.append(db)
try:
# record start time
if opt.verbosity >= 3:
start_test = time.time()
# Export all databases specified
export_databases(server_values, db_list, options)
# record elapsed time
if opt.verbosity >= 3:
print_elapsed_time(start_test)
except UtilError:
# initial specified string. In general, this identifies the missing use
# of backticks.
matched_size = len(orig_db)
if new_db:
# add 1 for the separator ':'
matched_size = matched_size + 1
matched_size = matched_size + len(new_db)
if matched_size != len(db):
parser.error(PARSE_ERR_DB_PAIR_EXT.format(db_pair=db,
db1_label='orig_db',
db2_label='new_db',
db1_value=orig_db,
db2_value=new_db))
# Remove backtick quotes (handled later)
orig_db = remove_backtick_quoting(orig_db) \
if is_quoted_with_backticks(orig_db) else orig_db
new_db = remove_backtick_quoting(new_db) \
if new_db and is_quoted_with_backticks(new_db) else new_db
db_entry = (orig_db, new_db)
db_list.append(db_entry)
try:
# record start time
if opt.verbosity >= 3:
start_test = time.time()
dbcopy.copy_db(source_values, dest_values, db_list, options)
if opt.verbosity >= 3:
print_elapsed_time(start_test)
except UtilError:
_, e, _ = sys.exc_info()
def copy_objects(self, new_db, options, new_server=None,
connections=1, check_exists=True):
"""Copy the database objects.
This method will copy a database and all of its objects and data
to another, new database. Options set at instantiation will determine
if there are objects that are excluded from the copy. Likewise,
the method will also skip data if that option was set and process
an input file with INSERT statements if that option was set.
The method can also be used to copy a database to another server
by providing the new server object (new_server). Copy to the same
name by setting new_db = old_db or as a new database.
new_db[in] Name of the new database
options[in] Options for copy e.g. force, etc.
new_server[in] Connection to another server for copying the db
Default is None (copy to same server - clone)
connections[in] Number of threads(connections) to use for insert
check_exists[in] If True, check for database existance before copy
Default is True
"""
from mysql.utilities.common.table import Table
# Must call init() first!
# Guard for init() prerequisite
assert self.init_called, "You must call db.init() before " + \
"db.copy_objects()."
grant_msg_displayed = False
if new_db:
# Assign new database identifier considering backtick quotes.
if is_quoted_with_backticks(new_db):
self.q_new_db = new_db
self.new_db = remove_backtick_quoting(new_db)
else:
self.new_db = new_db
self.q_new_db = quote_with_backticks(new_db)
else:
# If new_db is not defined use the same as source database.
self.new_db = self.db_name
self.q_new_db = self.q_db_name
self.destination = new_server
# We know we're cloning if there is no new connection.
self.cloning = (new_server == self.source)
if self.cloning:
self.destination = self.source
# Check to see if database exists
if check_exists:
exists = False
drop_server = None
if self.cloning:
exists = self.exists(self.source, new_db)
drop_server = self.source
else:
exists = self.exists(self.destination, new_db)
drop_server = self.destination
if exists:
if options.get("force", False):
self.drop(drop_server, True, new_db)
elif not self.skip_create:
raise UtilDBError("destination database exists. Use "
"--force to overwrite existing "
"database.", -1, new_db)
# Create new database first
if not self.skip_create:
if self.cloning:
self.create(self.source, new_db)
else:
self.create(self.destination, new_db)
# Create the objects in the new database
for obj in self.objects:
# Drop object if --force specified and database not dropped
# Grants do not need to be dropped for overwriting
if options.get("force", False) and obj[0] != _GRANT:
self.__drop_object(obj[0], obj[1][0])
# Create the object
self.__create_object(obj[0], obj[1], not grant_msg_displayed,
options.get("quiet", False),
options.get("new_engine", None),
options.get("def_engine", None))
if obj[0] == _GRANT and not grant_msg_displayed:
grant_msg_displayed = True
def check_index(src_val, table_args, options):
"""Check for duplicate or redundant indexes for one or more tables
This method will examine the indexes for one or more tables and identify
any indexes that are potential duplicates or redundant. It prints the
equivalent DROP statements if selected.
src_val[in] a dictionary containing connection information for the
source including:
(user, password, host, port, socket)
table_args[in] list of tables in the form 'db.table' or 'db'
options[in] dictionary of options to include:
show-drops : show drop statements for dupe indexes
skip : skip non-existent tables
verbosity : print extra information
show-indexes : show all indexes for each table
index-format : index format = sql, table, tab, csv
worst : show worst performing indexes
best : show best performing indexes
report-indexes : reports tables without PK or UK
Returns bool True = success, raises UtilError if error
"""
# Get options
show_drops = options.get("show-drops", False)
skip = options.get("skip", False)
verbosity = options.get("verbosity", False)
show_indexes = options.get("show-indexes", False)
index_format = options.get("index-format", False)
stats = options.get("stats", False)
first_indexes = options.get("best", None)
last_indexes = options.get("worst", None)
report_indexes = options.get("report-indexes", False)
# Try to connect to the MySQL database server.
conn_options = {
'quiet': verbosity == 1,
'version': "5.0.0",
}
servers = connect_servers(src_val, None, conn_options)
source = servers[0]
db_list = [] # list of databases
table_list = [] # list of all tables to process
# Build a list of objects to process
# 1. start with db_list if no objects present on command line
# 2. process command line options.
# 3. loop through database list and add all tables
# 4. check indexes
# Get sql_mode value set on servers
sql_mode = source.select_variable("SQL_MODE")
# Perform the options check here. Loop through objects presented.
for obj in table_args:
m_obj = parse_object_name(obj, sql_mode)
# Check if a valid database/table name is specified.
if m_obj[0] is None:
raise UtilError(
PARSE_ERR_OBJ_NAME_FORMAT.format(
obj_name=obj, option="the database/table arguments"))
else:
db_name, obj_name = m_obj
if obj_name:
# Table specified
table_list.append(obj)
# Else we are operating on a specific database.
else:
# Remove backtick quotes.
db_name = remove_backtick_quoting(db_name, sql_mode) \
if is_quoted_with_backticks(db_name, sql_mode) else db_name
db_list.append(db_name)
# Loop through database list adding tables
for db in db_list:
db_source = Database(source, db)
db_source.init()
tables = db_source.get_db_objects("TABLE")
if not tables and verbosity >= 1:
print("# Warning: database %s does not exist. Skipping." % (db))
for table in tables:
table_list.append("{0}.{1}".format(
quote_with_backticks(db, sql_mode),
quote_with_backticks(table[0], sql_mode)))
# Fail if no tables to check
if not table_list:
raise UtilError("No tables to check.")
if verbosity > 1:
print("# Checking indexes...")
# Check indexes for each table in the list
# pylint: disable=R0101
for table_name in table_list:
tbl_options = {
'verbose': verbosity >= 1,
'get_cols': False,
'quiet': verbosity is None or verbosity < 1
}
tbl = Table(source, table_name, tbl_options)
exists = tbl.exists()
if not exists and not skip:
raise UtilError("Table %s does not exist. Use --skip "
"to skip missing tables." % table_name)
if exists:
if not tbl.get_indexes():
if verbosity > 1 or report_indexes:
print("# Table %s is not indexed." % (table_name))
else:
if show_indexes:
tbl.print_indexes(index_format, verbosity)
# Show if table has primary key
if verbosity > 1 or report_indexes:
if not tbl.has_primary_key():
if not tbl.has_unique_key():
print("# Table {0} does not contain neither a "
"PRIMARY nor UNIQUE key.".format(table_name))
else:
print("# Table {0} does not contain a PRIMARY key."
"".format(table_name))
tbl.check_indexes(show_drops)
# Show best and/or worst indexes
if stats:
if first_indexes is not None:
tbl.show_special_indexes(index_format, first_indexes, True)
if last_indexes is not None:
tbl.show_special_indexes(index_format, last_indexes)
if verbosity > 1:
print("#")
if verbosity > 1:
print("# ...done.")
# use of backticks.
matched_size = len(orig_db)
if new_db:
# add 1 for the separator ':'
matched_size += 1
matched_size += len(new_db)
if matched_size != len(db):
parser.error(
PARSE_ERR_DB_PAIR_EXT.format(db_pair=db,
db1_label='orig_db',
db2_label='new_db',
db1_value=orig_db,
db2_value=new_db))
# Remove backtick quotes (handled later)
orig_db = remove_backtick_quoting(orig_db) \
if is_quoted_with_backticks(orig_db) else orig_db
new_db = remove_backtick_quoting(new_db) \
if new_db and is_quoted_with_backticks(new_db) else new_db
db_entry = (orig_db, new_db)
db_list.append(db_entry)
try:
# Record start time.
if opt.verbosity >= 3:
start_copy_time = time.time()
# Copy databases concurrently for non posix systems (windows).
if options['multiprocess'] > 1 and os.name != 'posix':
# Create copy databases tasks.
copy_db_tasks = []
# add 1 for the separator ':'
matched_size = matched_size + 1
matched_size = matched_size + len(parts[1])
# Verify if the size of the databases matched by the REGEX is equal to the
# initial specified string. In general, this identifies the missing use
# of backticks.
if matched_size != len(db):
parser.error(
PARSE_ERR_DB_PAIR_EXT.format(db_pair=db,
db1_label='db1',
db2_label='db2',
db1_value=parts[0],
db2_value=parts[1]))
# Remove backtick quotes (handled later)
db1 = remove_backtick_quoting(parts[0]) \
if is_quoted_with_backticks(parts[0]) else parts[0]
db2 = remove_backtick_quoting(parts[1]) \
if is_quoted_with_backticks(parts[1]) else parts[1]
try:
res = database_compare(server1_values, server2_values, db1, db2,
options)
print
except UtilError:
_, e, _ = sys.exc_info()
print("ERROR: %s" % e.errmsg)
check_failed = True
if not opt.run_all_tests:
break
if not res:
def __init__(self, server1, name, options=None):
"""Constructor
server[in] A Server object
name[in] Name of table in the form (db.table)
options[in] options for class: verbose, quiet, get_cols,
quiet If True, do not print information messages
verbose print extra data during operations (optional)
(default is False)
get_cols If True, get the column metadata on construction
(default is False)
"""
if options is None:
options = {}
self.verbose = options.get('verbose', False)
self.quiet = options.get('quiet', False)
self.server = server1
# Keep table identifier considering backtick quotes
if is_quoted_with_backticks(name):
self.q_table = name
self.q_db_name, self.q_tbl_name = Database.parse_object_name(name)
self.db_name = remove_backtick_quoting(self.q_db_name)
self.tbl_name = remove_backtick_quoting(self.q_tbl_name)
self.table = ".".join([self.db_name, self.tbl_name])
else:
self.table = name
self.db_name, self.tbl_name = Database.parse_object_name(name)
self.q_db_name = quote_with_backticks(self.db_name)
self.q_tbl_name = quote_with_backticks(self.tbl_name)
self.q_table = ".".join([self.q_db_name, self.q_tbl_name])
self.obj_type = "TABLE"
self.pri_idx = None
# We store each type of index in a separate list to make it easier
# to manipulate
self.btree_indexes = []
self.hash_indexes = []
self.rtree_indexes = []
self.fulltext_indexes = []
self.text_columns = []
self.blob_columns = []
self.column_format = None
self.column_names = []
self.q_column_names = []
if options.get('get_cols', False):
self.get_column_metadata()
self.dest_vals = None
self.storage_engine = None
# Get max allowed packet
res = self.server.exec_query("SELECT @@session.max_allowed_packet")
if res:
self.max_packet_size = res[0][0]
else:
self.max_packet_size = _MAXPACKET_SIZE
# Watch for invalid values
if self.max_packet_size > _MAXPACKET_SIZE:
self.max_packet_size = _MAXPACKET_SIZE
self._insert = "INSERT INTO %s.%s VALUES "
self.query_options = { # Used for skipping fetch of rows
'fetch': False
}
def copy_objects(self, new_db, options, new_server=None, connections=1, check_exists=True):
"""Copy the database objects.
This method will copy a database and all of its objects and data
to another, new database. Options set at instantiation will determine
if there are objects that are excluded from the copy. Likewise,
the method will also skip data if that option was set and process
an input file with INSERT statements if that option was set.
The method can also be used to copy a database to another server
by providing the new server object (new_server). Copy to the same
name by setting new_db = old_db or as a new database.
new_db[in] Name of the new database
options[in] Options for copy e.g. force, etc.
new_server[in] Connection to another server for copying the db
Default is None (copy to same server - clone)
connections[in] Number of threads(connections) to use for insert
check_exists[in] If True, check for database existance before copy
Default is True
"""
from mysql.utilities.common.table import Table
# Must call init() first!
# Guard for init() prerequisite
assert self.init_called, "You must call db.init() before " + "db.copy_objects()."
grant_msg_displayed = False
if new_db:
# Assign new database identifier considering backtick quotes.
if is_quoted_with_backticks(new_db):
self.q_new_db = new_db
self.new_db = remove_backtick_quoting(new_db)
else:
self.new_db = new_db
self.q_new_db = quote_with_backticks(new_db)
else:
# If new_db is not defined use the same as source database.
self.new_db = self.db_name
self.q_new_db = self.q_db_name
self.destination = new_server
# We know we're cloning if there is no new connection.
self.cloning = new_server == self.source
if self.cloning:
self.destination = self.source
# Check to see if database exists
if check_exists:
exists = False
drop_server = None
if self.cloning:
exists = self.exists(self.source, new_db)
drop_server = self.source
else:
exists = self.exists(self.destination, new_db)
drop_server = self.destination
if exists:
if options.get("force", False):
self.drop(drop_server, True, new_db)
elif not self.skip_create:
raise UtilDBError(
"destination database exists. Use " "--force to overwrite existing " "database.", -1, new_db
)
# Create new database first
if not self.skip_create:
if self.cloning:
self.create(self.source, new_db)
else:
self.create(self.destination, new_db)
# Create the objects in the new database
for obj in self.objects:
# Drop object if --force specified and database not dropped
# Grants do not need to be dropped for overwriting
if options.get("force", False) and obj[0] != _GRANT:
self.__drop_object(obj[0], obj[1][0])
# Create the object
self.__create_object(
obj[0],
obj[1],
not grant_msg_displayed,
options.get("quiet", False),
options.get("new_engine", None),
options.get("def_engine", None),
)
if obj[0] == _GRANT and not grant_msg_displayed:
grant_msg_displayed = True
# Parse server connection values
try:
server_values = parse_connection(opt.server, None, options)
except FormatError:
_, err, _ = sys.exc_info()
parser.error("Server connection values invalid: %s." % err)
except UtilError:
_, err, _ = sys.exc_info()
parser.error("Server connection values invalid: %s." % err.errmsg)
# Build list of databases to copy
db_list = []
for db in args:
# Remove backtick quotes (handled later)
db = remove_backtick_quoting(db) if is_quoted_with_backticks(db) else db
db_list.append(db)
try:
# record start time
if opt.verbosity >= 3:
start_test = time.time()
# Export all databases specified
export_databases(server_values, db_list, options)
# record elapsed time
if opt.verbosity >= 3:
print_elapsed_time(start_test)
except UtilError:
def check_index(src_val, table_args, options):
"""Check for duplicate or redundant indexes for one or more tables
This method will examine the indexes for one or more tables and identify
any indexes that are potential duplicates or redundant. It prints the
equivalent DROP statements if selected.
src_val[in] a dictionary containing connection information for the
source including:
(user, password, host, port, socket)
table_args[in] list of tables in the form 'db.table' or 'db'
options[in] dictionary of options to include:
show-drops : show drop statements for dupe indexes
skip : skip non-existent tables
verbosity : print extra information
show-indexes : show all indexes for each table
index-format : index format = sql, table, tab, csv
worst : show worst performing indexes
best : show best performing indexes
report-indexes : reports tables without PK or UK
Returns bool True = success, raises UtilError if error
"""
# Get options
show_drops = options.get("show-drops", False)
skip = options.get("skip", False)
verbosity = options.get("verbosity", False)
show_indexes = options.get("show-indexes", False)
index_format = options.get("index-format", False)
stats = options.get("stats", False)
first_indexes = options.get("best", None)
last_indexes = options.get("worst", None)
report_indexes = options.get("report-indexes", False)
# Try to connect to the MySQL database server.
conn_options = {
'quiet': verbosity == 1,
'version': "5.0.0",
}
servers = connect_servers(src_val, None, conn_options)
source = servers[0]
db_list = [] # list of databases
table_list = [] # list of all tables to process
# Build a list of objects to process
# 1. start with db_list if no objects present on command line
# 2. process command line options.
# 3. loop through database list and add all tables
# 4. check indexes
obj_name_regexp = re.compile(REGEXP_QUALIFIED_OBJ_NAME)
# Perform the options check here. Loop through objects presented.
for obj in table_args:
m_obj = obj_name_regexp.match(obj)
# Check if a valid database/table name is specified.
if not m_obj:
raise UtilError(PARSE_ERR_OBJ_NAME_FORMAT.format(
obj_name=obj, option="the database/table arguments"))
else:
db_name, obj_name = m_obj.groups()
if obj_name:
# Table specified
table_list.append(obj)
# Else we are operating on a specific database.
else:
# Remove backtick quotes.
db_name = remove_backtick_quoting(db_name) \
if is_quoted_with_backticks(db_name) else db_name
db_list.append(db_name)
# Loop through database list adding tables
for db in db_list:
db_source = Database(source, db)
db_source.init()
tables = db_source.get_db_objects("TABLE")
if not tables and verbosity >= 1:
print "# Warning: database %s does not exist. Skipping." % (db)
for table in tables:
table_list.append("{0}.{1}".format(quote_with_backticks(db),
quote_with_backticks(table[0])))
# Fail if no tables to check
if not table_list:
raise UtilError("No tables to check.")
if verbosity > 1:
print "# Checking indexes..."
# Check indexes for each table in the list
for table_name in table_list:
tbl_options = {
'verbose': verbosity >= 1,
'get_cols': False,
'quiet': verbosity is None or verbosity < 1
}
tbl = Table(source, table_name, tbl_options)
exists = tbl.exists()
if not exists and not skip:
raise UtilError("Table %s does not exist. Use --skip "
"to skip missing tables." % table_name)
if exists:
if not tbl.get_indexes():
if verbosity > 1 or report_indexes:
print "# Table %s is not indexed." % (table_name)
else:
if show_indexes:
tbl.print_indexes(index_format, verbosity)
# Show if table has primary key
if verbosity > 1 or report_indexes:
if not tbl.has_primary_key():
if not tbl.has_unique_key():
print("# Table {0} does not contain neither a "
"PRIMARY nor UNIQUE key.".format(table_name))
else:
print("# Table {0} does not contain a PRIMARY key."
"".format(table_name))
tbl.check_indexes(show_drops)
# Show best and/or worst indexes
if stats:
if first_indexes is not None:
tbl.show_special_indexes(index_format, first_indexes, True)
if last_indexes is not None:
tbl.show_special_indexes(index_format, last_indexes)
if verbosity > 1:
print "#"
if verbosity > 1:
print "# ...done."
# the initial specified string. In general, this identifies the missing
# use of backticks.
matched_size = len(orig_db)
if new_db:
# add 1 for the separator ':'
matched_size += 1
matched_size += len(new_db)
if matched_size != len(db):
parser.error(PARSE_ERR_DB_PAIR_EXT.format(db_pair=db,
db1_label='orig_db',
db2_label='new_db',
db1_value=orig_db,
db2_value=new_db))
# Remove backtick quotes (handled later)
orig_db = remove_backtick_quoting(orig_db, src_sql_mode) \
if is_quoted_with_backticks(orig_db, src_sql_mode) else orig_db
new_db = remove_backtick_quoting(new_db, dest_sql_mode) \
if new_db and is_quoted_with_backticks(new_db, dest_sql_mode) \
else new_db
db_entry = (orig_db, new_db)
db_list.append(db_entry)
# Check databases for blob fields set to NOT NULL
if servers and dbcopy.check_blobs_not_null(servers[0], db_list):
sys.exit(1)
try:
# Record start time.
if opt.verbosity >= 3:
start_copy_time = time.time()
else:
# add 1 for the separator ':'
matched_size = matched_size + 1
matched_size = matched_size + len(parts[1])
# Verify if the size of the databases matched by the REGEX is equal to the
# initial specified string. In general, this identifies the missing use
# of backticks.
if matched_size != len(db):
parser.error(PARSE_ERR_DB_PAIR_EXT.format(db_pair=db,
db1_label='db1',
db2_label='db2',
db1_value=parts[0],
db2_value=parts[1]))
# Remove backtick quotes (handled later)
db1 = remove_backtick_quoting(parts[0]) \
if is_quoted_with_backticks(parts[0]) else parts[0]
db2 = remove_backtick_quoting(parts[1]) \
if is_quoted_with_backticks(parts[1]) else parts[1]
try:
res = database_compare(server1_values, server2_values,
db1, db2, options)
print
except UtilError:
_, e, _ = sys.exc_info()
print("ERROR: %s" % e.errmsg)
check_failed = True
if not opt.run_all_tests:
break
if not res:
else:
# add 1 for the separator ':'
matched_size += 1
matched_size += len(parts[1])
# Verify if the size of the databases matched by the REGEX is equal
# to the initial specified string. In general, this identifies the
# missing use of backticks.
if matched_size != len(db):
parser.error(PARSE_ERR_DB_PAIR_EXT.format(db_pair=db,
db1_label='db1',
db2_label='db2',
db1_value=parts[0],
db2_value=parts[1]))
# Remove backtick quotes (handled later)
db1 = remove_backtick_quoting(parts[0], server1_sql_mode) \
if is_quoted_with_backticks(parts[0], server1_sql_mode) \
else parts[0]
db2 = remove_backtick_quoting(parts[1], server1_sql_mode) \
if is_quoted_with_backticks(parts[1], server1_sql_mode) \
else parts[1]
try:
res = database_compare(server1_values, server2_values,
db1, db2, options)
if not opt.quiet:
print
except UtilError:
_, e, _ = sys.exc_info()
print("ERROR: %s" % e.errmsg)
sys.exit(1)
def db_objects_list_to_dictionary(parser,
obj_list,
option_desc,
db_over_tables=True):
"""Process database object list and convert to a dictionary.
Check the qualified name format of the given database objects and convert
the given list of object to a dictionary organized by database names and
sets of specific objects.
Note: It is assumed that the given object list is obtained from the
arguments or an option returned by the parser.
parser[in] Instance of the used option/arguments parser
obj_list[in] List of objects to process.
option_desc[in] Short description of the option for the object list
(e.g., "the --exclude option", "the database/table
arguments") to refer appropriately in any parsing
error.
db_over_tables[in] If True specifying a db alone overrides all
occurrences of table objects from that db (e.g.
if True and we have both db and db.table1, db.table1
is ignored).
returns a dictionary with the objects grouped by database (without
duplicates). None value associated to a database entry means that all
objects are to be considered.
E.g. {'db_name1': set(['table1','table2']), 'db_name2': None}.
"""
db_objs_dict = {}
obj_name_regexp = re.compile(REGEXP_QUALIFIED_OBJ_NAME)
for obj_name in obj_list:
m_obj = obj_name_regexp.match(obj_name)
if not m_obj:
parser.error(
PARSE_ERR_OBJ_NAME_FORMAT.format(obj_name=obj_name,
option=option_desc))
else:
db_name, obj_name = m_obj.groups()
# Remove backtick quotes.
db_name = remove_backtick_quoting(db_name) \
if is_quoted_with_backticks(db_name) else db_name
obj_name = remove_backtick_quoting(obj_name) \
if obj_name and is_quoted_with_backticks(obj_name) \
else obj_name
# Add database object to result dictionary.
if not obj_name:
# If only the database is specified and db_over_tables is True,
# then add entry with db name and value None (to include all
# objects) even if a previous specific object was already
# added, else if db_over_tables is False, add None value to the
# list, so that we know db was specified without any
# table/routine.
if db_name in db_objs_dict:
if db_objs_dict[db_name] and not db_over_tables:
db_objs_dict[db_name].add(None)
else:
db_objs_dict[db_name] = None
else:
if db_over_tables:
db_objs_dict[db_name] = None
else:
db_objs_dict[db_name] = set([None])
else:
# If a specific object object is given add it to the set
# associated to the database, except if the database entry
# is None (meaning that all objects are included).
if db_name in db_objs_dict:
if db_objs_dict[db_name]:
db_objs_dict[db_name].add(obj_name)
else:
db_objs_dict[db_name] = set([obj_name])
return db_objs_dict
