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