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_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_create_statement(self, db, name, obj_type): """Return the create statement for the object db[in] Database name name[in] Name of the object obj_type[in] Object type (string) e.g. DATABASE Note: this is used to form the correct SHOW command Returns create statement """ row = None q_name = name if is_quoted_with_backticks(name) else quote_with_backticks(name) if obj_type == _DATABASE: name_str = q_name else: q_db = db if is_quoted_with_backticks(db) else quote_with_backticks(db) name_str = q_db + "." + q_name row = self.source.exec_query("SHOW CREATE %s %s" % (obj_type, name_str)) create_statement = None if row: if obj_type == _TABLE or obj_type == _VIEW or obj_type == _DATABASE: create_statement = row[0][1] elif obj_type == _EVENT: create_statement = row[0][3] else: create_statement = row[0][2] return create_statement
def database_diff(server1_val, server2_val, db1, db2, options): """Find differences among objects from two databases. This method compares the object definitions among two databases. If any differences are found, the differences are printed in the format chosen and the method returns False. A True result is returned only when all object definitions match. The method will stop and return False on the first difference found unless the option force is set to True (default = False). server1_val[in] a dictionary containing connection information for the first server including: (user, password, host, port, socket) server2_val[in] a dictionary containing connection information for the second server including: (user, password, host, port, socket) db1[in] the first database in the compare db2[in] the second database in the compare options[in] a dictionary containing the options for the operation: (quiet, verbosity, difftype, force) Returns bool True if all object match, False if partial match """ from mysql.utilities.common.dbcompare import get_common_objects from mysql.utilities.common.dbcompare import server_connect force = options.get("force", False) server1, server2 = server_connect(server1_val, server2_val, db1, db2, options) in_both, in_db1, in_db2 = get_common_objects(server1, server2, db1, db2, True, options) in_both.sort() if (len(in_db1) > 0 or len(in_db2) > 0) and not force: return False # Do the diff for the databases themselves result = object_diff(server1, server2, db1, db2, options) if result is not None: success = False if not force: return False # For each that match, do object diff success = True for item in in_both: obj_name1 = quote_with_backticks(item[1][0]) \ if is_quoted_with_backticks(db1) else item[1][0] obj_name2 = quote_with_backticks(item[1][0]) \ if is_quoted_with_backticks(db2) else item[1][0] object1 = "%s.%s" % (db1, obj_name1) object2 = "%s.%s" % (db2, obj_name2) result = object_diff(server1, server2, object1, object2, options) if result is not None: success = False if not force: return False return success
def has_privilege(self, db, obj, access, allow_skip_grant_tables=True): """Check to see user has a specific access to a db.object. db[in] Name of database obj[in] Name of object access[in] MySQL privilege to check (e.g. SELECT, SUPER, DROP) allow_skip_grant_tables[in] If True, allow silent failure for cases where the server is started with --skip-grant-tables. Default=True Returns True if user has access, False if not """ grants_enabled = self.server1.grant_tables_enabled() # If grants are disabled and it is Ok to allow skipped grant tables, # return True - privileges disabled so user can do anything. if allow_skip_grant_tables and not grants_enabled: return True # Convert privilege to upper cases. access = access.upper() # Get grant dictionary grant_dict = self.get_grants(globals_privs=True, as_dict=True) # If self has all privileges for all databases, no need to check, # simply return True if ("ALL PRIVILEGES" in grant_dict['*']['*'] and "GRANT OPTION" in grant_dict['*']['*']): return True # Quote db and obj with backticks if necessary if not is_quoted_with_backticks(db) and db != '*': db = quote_with_backticks(db) if not is_quoted_with_backticks(obj) and obj != '*': obj = quote_with_backticks(obj) # USAGE privilege is the same as no privileges, # so everyone has it. if access == "USAGE": return True # Even if we have ALL PRIVILEGES grant, we might not have WITH GRANT # OPTION privilege. # Check server wide grants. elif (access in grant_dict['*']['*'] or "ALL PRIVILEGES" in grant_dict['*']['*'] and access != "GRANT OPTION"): return True # Check database level grants. elif (access in grant_dict[db]['*'] or "ALL PRIVILEGES" in grant_dict[db]['*'] and access != "GRANT OPTION"): return True # Check object level grants. elif (access in grant_dict[db][obj] or "ALL PRIVILEGES" in grant_dict[db][obj] and access != "GRANT OPTION"): return True else: return False
def _parse_grant_statement(statement): """ Returns a namedtuple with the parsed GRANT information. statement[in] Grant string in the sql format returned by the server. Returns named tuple with GRANT information or None. """ grant_parse_re = re.compile(r""" GRANT\s(.+)?\sON\s # grant or list of grants (?:(?:PROCEDURE\s)|(?:FUNCTION\s))? # optional for routines only (?:(?:(\*|`?[^']+`?)\.(\*|`?[^']+`?)) # object where grant applies | ('[^']*'@'[^']*')) # For proxy grants user/host \sTO\s([^@]+@[\S]+) # grantee (?:\sIDENTIFIED\sBY\sPASSWORD(?:\s\'[^\']+\')?)? # optional pwd (?:\sREQUIRE\sSSL)? # optional SSL (\sWITH\sGRANT\sOPTION)? # optional grant option $ # End of grant statement """, re.VERBOSE) grant_tpl_factory = namedtuple("grant_info", "privileges proxy_user " "db object user") match = re.match(grant_parse_re, statement) if match: # quote database name and object name with backticks if match.group(1).upper() != 'PROXY': db = match.group(2) if not is_quoted_with_backticks(db) and db != '*': db = quote_with_backticks(db) obj = match.group(3) if not is_quoted_with_backticks(obj) and obj != '*': obj = quote_with_backticks(obj) else: # if it is not a proxy grant db = obj = None grants = grant_tpl_factory( # privileges set([priv.strip() for priv in match.group(1).split(",")]), match.group(4), # proxied user db, # database obj, # object match.group(5), # user ) # If user has grant option, add it to the list of privileges if match.group(6) is not None: grants.privileges.add("GRANT OPTION") else: raise UtilError("Unable to parse grant statement " "{0}".format(statement)) return grants
def drop(self, server, quiet, db_name=None): """Drop the database server[in] A Server object quiet[in] ignore error on drop db_name[in] database name (optional) If omitted, operation is performed on the class instance table name. return True = database successfully dropped, False = error """ db = None if db_name: db = db_name if is_quoted_with_backticks(db_name) else quote_with_backticks(db_name) else: db = self.q_db_name op_ok = False if quiet: try: res = server.exec_query("DROP DATABASE %s" % (db), self.query_options) op_ok = True except: pass else: res = server.exec_query("DROP DATABASE %s" % (db), self.query_options) op_ok = True return op_ok
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 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 create(self, server, db_name=None): """Create the database server[in] A Server object db_name[in] database name (optional) If omitted, operation is performed on the class instance table name. return True = database successfully created, False = error """ db = None if db_name: db = db_name if is_quoted_with_backticks(db_name) else quote_with_backticks(db_name) else: db = self.q_db_name op_ok = False res = server.exec_query("CREATE DATABASE %s" % (db), self.query_options) op_ok = True return op_ok
def copy_data(self, destination, cloning=False, new_db=None, connections=1): """Retrieve data from a table and copy to another server and database. Reads data from a table and inserts the correct INSERT statements into the file provided. Note: if connections < 1 - retrieve the data one row at-a-time destination[in] Destination server cloning[in] If True, we are copying on the same server new_db[in] Rename the db to this name connections[in] Number of threads(connections) to use for insert """ if new_db is None: new_db = self.q_db_name else: # If need quote new_db identifier with backticks if not is_quoted_with_backticks(new_db): new_db = quote_with_backticks(new_db) num_conn = int(connections) if cloning: self._clone_data(new_db) else: # Read and copy the data pthreads = [] for rows in self.retrieve_rows(num_conn): p = self.insert_rows(rows, new_db, destination, num_conn > 1) if p is not None: p.start() pthreads.append(p) if num_conn > 1: # Wait for all to finish num_complete = 0 while num_complete < len(pthreads): for p in pthreads: if not p.is_alive(): num_complete += 1
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
def _build_create_table(db_name, tbl_name, engine, columns, col_ref={}): """Build the CREATE TABLE command for a table. This method uses the data from the _read_next() method to build a table from its parts as read from a non-SQL formatted file. db_name[in] Database name for the object tbl_name[in] Name of the table engine[in] Storage engine name for the table columns[in] A list of the column definitions for the table col_ref[in] A dictionary of column names/indexes Returns (string) the CREATE TABLE statement. """ # Quote db_name and tbl_name with backticks if needed if not is_quoted_with_backticks(db_name): db_name = quote_with_backticks(db_name) if not is_quoted_with_backticks(tbl_name): tbl_name = quote_with_backticks(tbl_name) create_str = "CREATE TABLE %s.%s (\n" % (db_name, tbl_name) stop = len(columns) pri_keys = [] keys = [] key_str = "" col_name_index = col_ref.get("COLUMN_NAME", 0) col_type_index = col_ref.get("COLUMN_TYPE", 1) is_null_index = col_ref.get("IS_NULLABLE", 2) def_index = col_ref.get("COLUMN_DEFAULT", 3) col_key_index = col_ref.get("COLUMN_KEY", 4) const_name_index = col_ref.get("CONSTRAINT_NAME", 7) ref_tbl_index = col_ref.get("REFERENCED_TABLE_NAME", 8) ref_col_index = col_ref.get("COL_NAME", 13) ref_col_ref = col_ref.get("REFERENCED_COLUMN_NAME", 15) constraints = [] for column in range(0,stop): cur_col = columns[column] # Quote column name with backticks if needed col_name = cur_col[col_name_index] if not is_quoted_with_backticks(col_name): col_name = quote_with_backticks(col_name) create_str = "%s %s %s" % (create_str, col_name, cur_col[col_type_index]) if cur_col[is_null_index].upper() != "YES": create_str += " NOT NULL" if len(cur_col[def_index]) > 0 and cur_col[def_index].upper() != "NONE": create_str += " DEFAULT %s" % cur_col[def_index] elif cur_col[is_null_index].upper == "YES": create_str += " DEFAULT NULL" if len(cur_col[col_key_index]) > 0: if cur_col[col_key_index] == "PRI": pri_keys.append(cur_col[col_name_index]) else: keys.append(cur_col[col_name_index]) if column+1 < stop: create_str += ",\n" if len(pri_keys) > 0: key_list = pri_keys key_str = ",\n PRIMARY KEY(" elif len(keys) > 0: key_list = keys # Quote constraint name with backticks if needed const_name = cur_col[const_name_index] if const_name and not is_quoted_with_backticks(const_name): const_name = quote_with_backticks(const_name) key_str = ",\n KEY %s (" % const_name constraints.append([const_name, cur_col[ref_tbl_index], cur_col[ref_col_index], cur_col[ref_col_ref]]) if len(key_str) > 0: stop = len(key_list) for key in range(0,stop): # Quote keys with backticks if needed if key_list[key] and not is_quoted_with_backticks(key_list[key]): key_list[key] = quote_with_backticks(key_list[key]) key_str += "%s" % key_list[key] if key+1 < stop-1: key_str += ", " key_str += ")" create_str += key_str if len(constraints) > 0: for constraint in constraints: # Quote keys with backticks if needed for key in constraint: if key and not is_quoted_with_backticks(key): key = quote_with_backticks(key) c_str = (" CONSTRAINT {cstr} FOREIGN KEY ({fk}) REFERENCES " "{ref1} ({ref2})") constraint_str = c_str.format(cstr=constraint[0], fk=constraint[2], ref1=constraint[1], ref2=constraint[3]) create_str = "%s,\n%s" % (create_str, constraint_str) create_str = "%s\n)" % create_str if engine and len(engine) > 0: create_str = "%s ENGINE=%s" % (create_str, engine) create_str = "%s;" % create_str return create_str
# 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:
def get_grants_for_object(self, qualified_obj_name, obj_type_str, global_privs=False): """ Retrieves the list of grants that the current user has that that have effect over a given object. qualified_obj_name[in] String with the qualified name of the object. obj_type_str[in] String with the type of the object that we are working with, must be one of 'ROUTINE', 'TABLE' or 'DATABASE'. global_privs[in] If True, the wildcard'%' host privileges are also taken into account This method takes the MySQL privilege hierarchy into account, e.g, if the qualified object is a table, it returns all the grant statements for this user regarding that table, as well as the grant statements for this user regarding the db where the table is at and finally any global grants that the user might have. Returns a list of strings with the grant statements. """ grant_stm_lst = self.get_grants(global_privs) obj_name_regexp = re.compile(REGEXP_QUALIFIED_OBJ_NAME) m_obj = obj_name_regexp.match(qualified_obj_name) grants = [] if not m_obj: raise UtilError("Cannot parse the specified qualified name " "'{0}'".format(qualified_obj_name)) else: db_name, obj_name = m_obj.groups() # Quote database and object name if necessary if not is_quoted_with_backticks(db_name): db_name = quote_with_backticks(db_name) if obj_name and obj_name != '*': if not is_quoted_with_backticks(obj_name): obj_name = quote_with_backticks(obj_name) # For each grant statement look for the ones that apply to this # user and object for grant_stm in grant_stm_lst: grant_tpl = self._parse_grant_statement(grant_stm[0]) if grant_tpl: # Check if any of the privileges applies to this object # and if it does then check if it inherited from this # statement if filter_grants(grant_tpl.privileges, obj_type_str): # Add global grants if grant_tpl.db == '*': grants.append(grant_stm[0]) continue # Add database level grants if grant_tpl.db == db_name and grant_tpl.object == '*': grants.append(grant_stm[0]) continue # If it is an object, add existing object level grants # as well. if obj_name: if (grant_tpl.db == db_name and grant_tpl.object == obj_name): grants.append(grant_stm[0]) return grants
def validate_obj_type_dict(server, obj_type_dict): """Validates the dictionary of objects against the specified server This function builds a dict with the types of the objects in obj_type_dict, filtering out non existing databases and objects. Returns a dictionary with only the existing objects, using object_types as keys and as values a list of tuples (<DB NAME>, <OBJ_NAME>). """ valid_obj_dict = defaultdict(list) server_dbs = set(row[0] for row in server.get_all_databases( ignore_internal_dbs=False)) argument_dbs = set(obj_type_dict.keys()) # Get non existing_databases and dbs to check non_existing_dbs = argument_dbs.difference(server_dbs) dbs_to_check = server_dbs.intersection(argument_dbs) if non_existing_dbs: if len(non_existing_dbs) > 1: plurals = ('s', '', 'them') else: plurals = ('', 'es', 'it') print('# WARNING: specified database{0} do{1} not ' 'exist on base server and will be skipped along ' 'any tables and routines belonging to {2}: ' '{3}.'.format(plurals[0], plurals[1], plurals[2], ", ".join(non_existing_dbs))) # Now for each db that actually exists, get the type of the specified # objects for db_name in dbs_to_check: db = Database(server, db_name) # quote database name if necessary quoted_db_name = db_name if not is_quoted_with_backticks(db_name): quoted_db_name = quote_with_backticks(db_name) for obj_name in obj_type_dict[db_name]: if obj_name is None: # We must consider the database itself valid_obj_dict[DATABASE_TYPE].append((quoted_db_name, quoted_db_name)) else: # get quoted name for obj_name quoted_obj_name = obj_name if not is_quoted_with_backticks(obj_name): quoted_obj_name = quote_with_backticks(obj_name) # Test if the object exists and if it does, test if it # is one of the supported object types, else # print a warning and skip the object obj_type = db.get_object_type(obj_name) if obj_type is None: print("# WARNING: specified object does not exist. " "{0}.{1} will be skipped." "".format(quoted_db_name, quoted_obj_name)) elif 'PROCEDURE' in obj_type or 'FUNCTION' in obj_type: valid_obj_dict[ROUTINE_TYPE].append((quoted_db_name, quoted_obj_name)) elif 'TABLE' in obj_type: valid_obj_dict[TABLE_TYPE].append((quoted_db_name, quoted_obj_name)) else: print('# WARNING: specified object is not supported ' '(not a DATABASE, FUNCTION, PROCEDURE or TABLE),' ' as such it will be skipped: {0}.{1}.' ''.format(quoted_db_name, quoted_obj_name)) return valid_obj_dict
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, 'db_list': [db],
def _build_create_objects(obj_type, db, definitions): """Build the CREATE and GRANT SQL statments for object definitions. This method takes the object information read from the file using the _read_next() method and constructs SQL definition statements for each object. It receives a block of objects and creates a statement for each object. obj_type[in] The object type db[in] The database definitions[in] The list of object definition data from the file Returns (string[]) - a list of SQL statements for the objects """ create_strings = [] skip_header = True obj_db = "" obj_name = "" col_list = [] stop = len(definitions) col_ref = {} engine = None # Now the tricky part. for i in range(0, stop): if skip_header: skip_header = False col_ref = _build_column_ref(definitions[i]) continue defn = definitions[i] # Read engine from first row and save old value. old_engine = engine engine = defn[col_ref.get("ENGINE", 2)] create_str = "" if obj_type == "TABLE": if (obj_db == "" and obj_name == ""): obj_db = defn[col_ref.get("TABLE_SCHEMA", 0)] obj_name = defn[col_ref.get("TABLE_NAME", 1)] if (obj_db == defn[col_ref.get("TABLE_SCHEMA",0)] and \ obj_name == defn[col_ref.get("TABLE_NAME",1)]): col_list.append(defn) else: create_str = _build_create_table(obj_db, obj_name, old_engine, col_list, col_ref) create_strings.append(create_str) obj_db = defn[col_ref.get("TABLE_SCHEMA", 0)] obj_name = defn[col_ref.get("TABLE_NAME", 1)] col_list = [] col_list.append(defn) # check for end. if i + 1 == stop: create_str = _build_create_table(obj_db, obj_name, engine, col_list, col_ref) create_strings.append(create_str) elif obj_type == "VIEW": # Quote table schema and name with backticks if needed if not is_quoted_with_backticks(defn[col_ref.get( "TABLE_SCHEMA", 0)]): obj_db = quote_with_backticks(defn[col_ref.get( "TABLE_SCHEMA", 0)]) else: obj_db = defn[col_ref.get("TABLE_SCHEMA", 0)] if not is_quoted_with_backticks(defn[col_ref.get("TABLE_NAME", 1)]): obj_name = quote_with_backticks(defn[col_ref.get( "TABLE_NAME", 1)]) else: obj_name = defn[col_ref.get("TABLE_NAME", 1)] # Create VIEW statement create_str = ( "CREATE ALGORITHM=UNDEFINED DEFINER={defr} " "SQL SECURITY {sec} VIEW {scma}.{tbl} AS {defv}; ").format( defr=defn[col_ref.get("DEFINER", 2)], sec=defn[col_ref.get("SECURITY_TYPE", 3)], scma=obj_db, tbl=obj_name, defv=defn[col_ref.get("VIEW_DEFINITION", 4)]) create_strings.append(create_str) elif obj_type == "TRIGGER": # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db if not is_quoted_with_backticks(defn[col_ref.get( "TRIGGER_NAME", 0)]): obj_name = quote_with_backticks(defn[col_ref.get( "TRIGGER_NAME", 0)]) else: obj_name = defn[col_ref.get("TRIGGER_NAME", 0)] if not is_quoted_with_backticks(defn[col_ref.get( "EVENT_OBJECT_SCHEMA", 3)]): evt_scma = quote_with_backticks(defn[col_ref.get( "EVENT_OBJECT_SCHEMA", 3)]) else: evt_scma = defn[col_ref.get("EVENT_OBJECT_SCHEMA", 3)] if not is_quoted_with_backticks(defn[col_ref.get( "EVENT_OBJECT_TABLE", 4)]): evt_tbl = quote_with_backticks(defn[col_ref.get( "EVENT_OBJECT_TABLE", 4)]) else: evt_tbl = defn[col_ref.get("EVENT_OBJECT_TABLE", 4)] # Create TRIGGER statement # Important Note: There is a bug in the server when backticks are # used in the trigger statement, i.e. the ACTION_STATEMENT value in # INFORMATION_SCHEMA.TRIGGERS is incorrect (see BUG##16291011). create_str = ( "CREATE DEFINER={defr} " "TRIGGER {scma}.{trg} {act_t} {evt_m} " "ON {evt_s}.{evt_t} FOR EACH {act_o} {act_s};").format( defr=defn[col_ref.get("DEFINER", 1)], scma=obj_db, trg=obj_name, act_t=defn[col_ref.get("ACTION_TIMING", 6)], evt_m=defn[col_ref.get("EVENT_MANIPULATION", 2)], evt_s=evt_scma, evt_t=evt_tbl, act_o=defn[col_ref.get("ACTION_ORIENTATION", 5)], act_s=defn[col_ref.get("ACTION_STATEMENT", 7)]) create_strings.append(create_str) elif obj_type in ("PROCEDURE", "FUNCTION"): # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db if not is_quoted_with_backticks(defn[col_ref.get("NAME", 0)]): obj_name = quote_with_backticks(defn[col_ref.get("NAME", 0)]) else: obj_name = defn[col_ref.get("NAME", 0)] # Create PROCEDURE or FUNCTION statement if obj_type == "FUNCTION": func_str = " RETURNS %s" % defn[col_ref.get("RETURNS", 7)] if defn[col_ref.get("IS_DETERMINISTI", 3)] == 'YES': func_str = "%s DETERMINISTIC" % func_str else: func_str = "" create_str = ("CREATE DEFINER={defr}" " {type} {scma}.{name}({par_lst})" "{func_ret} {body};").format( defr=defn[col_ref.get("DEFINER", 5)], type=obj_type, scma=obj_db, name=obj_name, par_lst=defn[col_ref.get("PARAM_LIST", 6)], func_ret=func_str, body=defn[col_ref.get("BODY", 8)]) create_strings.append(create_str) elif obj_type == "EVENT": # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db if not is_quoted_with_backticks(defn[col_ref.get("NAME", 0)]): obj_name = quote_with_backticks(defn[col_ref.get("NAME", 0)]) else: obj_name = defn[col_ref.get("NAME", 0)] # Create EVENT statement create_str = ("CREATE EVENT {scma}.{name} " "ON SCHEDULE EVERY {int_v} {int_f} " "STARTS '{starts}' ").format( scma=obj_db, name=obj_name, int_v=defn[col_ref.get("INTERVAL_VALUE", 5)], int_f=defn[col_ref.get("INTERVAL_FIELD", 6)], starts=defn[col_ref.get("STARTS", 8)]) ends_index = col_ref.get("ENDS", 9) if len(defn[ends_index]) > 0 and \ defn[ends_index].upper() != "NONE": create_str = "%s ENDS '%s' " % (create_str, defn[ends_index]) if defn[col_ref.get("ON_COMPLETION", 11)] == "DROP": create_str = "%s ON COMPLETION NOT PRESERVE " % create_str if defn[col_ref.get("STATUS", 10)] == "DISABLED": create_str = "%s DISABLE " % create_str create_str = "%s DO %s;" % (create_str, defn[col_ref.get( "BODY", 2)]) create_strings.append(create_str) elif obj_type == "GRANT": try: user, priv, db, tbl = defn[0:4] except: raise UtilError("Object data invalid: %s : %s" % \ (obj_type, defn)) if not tbl: tbl = "*" elif tbl.upper() == "NONE": tbl = "*" # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db obj_tbl = quote_with_backticks(tbl) \ if (tbl != '*' and not is_quoted_with_backticks(tbl)) else tbl # Create GRANT statement create_str = "GRANT %s ON %s.%s TO %s" % (priv, obj_db, obj_tbl, user) create_strings.append(create_str) elif obj_type in ["RPL_COMMAND", "GTID_COMMAND"]: create_strings.append([defn]) else: raise UtilError("Unknown object type discovered: %s" % obj_type) return create_strings
# 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: check_failed = 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 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."
# check unique keys ukey_regexp = re.compile(r'(?:(?:;){{0,1}}{0}\.{0})' ''.format(REGEXP_OBJ_NAME)) db_idxes_l = None # Split the table names considering backtick quotes if opt.use_indexes: grp = ukey_regexp.findall(opt.use_indexes) if not grp: parser.error("Can't parse the specified --use-indexes argument {0}" "".format(opt.use_indexes)) db_idxes_l = [] for table, index in grp: table_uc = (table if is_quoted_with_backticks(table) else quote_with_backticks(table)) index_uc = (index if is_quoted_with_backticks(index) else quote_with_backticks(index)) db_idxes_l.append((table_uc, index_uc)) # Set options for database operations. options = { "quiet": opt.quiet, "verbosity": opt.verbosity, "difftype": opt.difftype, "run_all_tests": opt.run_all_tests, "width": opt.width, "no_checksum_table": opt.no_checksum_table, "no_object_check": opt.no_object_check, "no_diff": opt.no_diff,
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
def import_file(dest_val, file_name, options): """Import a file This method reads a file and, if needed, transforms the file into discrete SQL statements for execution on the destination server. It accepts any of the formal structured files produced by the mysqlexport utility including formats SQL, CSV, TAB, GRID, and VERTICAL. It will read these files and skip or include the definitions or data as specified in the options. An error is raised for any conversion errors or errors while executing the statements. Users are highly encouraged to use the --dryrun option which will print the SQL statements without executing them. dest_val[in] a dictionary containing connection information for the destination including: (user, password, host, port, socket) file_name[in] name (and path) of the file to import options[in] a dictionary containing the options for the import: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, and debug) Returns bool True = success, False = error """ from mysql.utilities.common.database import Database from mysql.utilities.common.options import check_engine_options from mysql.utilities.common.table import Table from mysql.utilities.common.server import connect_servers # Helper method to dig through the definitions for create statements def _process_definitions(statements, table_col_list, db_name): # First, get the SQL strings sql_strs = _build_create_objects(obj_type, db_name, definitions) statements.extend(sql_strs) # Now, save the column list col_list = _build_col_metadata(obj_type, definitions) if len(col_list) > 0: table_col_list.extend(col_list) def _process_data(tbl_name, statements, columns, table_col_list, table_rows, skip_blobs): # if there is data here, build bulk inserts # First, create table reference, then call insert_rows() tbl = Table(destination, tbl_name) # Need to check to see if table exists! if tbl.exists(): tbl.get_column_metadata() col_meta = True elif len(table_col_list) > 0: col_meta = _get_column_metadata(tbl, table_col_list) else: fix_cols = [] fix_cols.append((tbl.tbl_name, columns)) col_meta = _get_column_metadata(tbl, fix_cols) if not col_meta: raise UtilError("Cannot build bulk insert statements without " "the table definition.") ins_strs = tbl.make_bulk_insert(table_rows, tbl.q_db_name) if len(ins_strs[0]) > 0: statements.extend(ins_strs[0]) if len(ins_strs[1]) > 0 and not skip_blobs: for update in ins_strs[1]: statements.append(update) # Gather options format = options.get("format", "sql") no_headers = options.get("no_headers", False) quiet = options.get("quiet", False) import_type = options.get("import_type", "definitions") single = options.get("single", True) dryrun = options.get("dryrun", False) do_drop = options.get("do_drop", False) skip_blobs = options.get("skip_blobs", False) skip_gtid = options.get("skip_gtid", False) # Attempt to connect to the destination server conn_options = { 'quiet': quiet, 'version': "5.1.30", } servers = connect_servers(dest_val, None, conn_options) destination = servers[0] # Check storage engines check_engine_options(destination, options.get("new_engine", None), options.get("def_engine", None), False, options.get("quiet", False)) if not quiet: if import_type == "both": str = "definitions and data" else: str = import_type print "# Importing %s from %s." % (str, file_name) # Setup variables we will need skip_header = not no_headers if format == "sql": skip_header = False get_db = True check_privileges = False db_name = None file = open(file_name) columns = [] read_columns = False table_rows = [] obj_type = "" definitions = [] statements = [] table_col_list = [] tbl_name = "" skip_rpl = options.get("skip_rpl", False) gtid_command_found = False supports_gtid = servers[0].supports_gtid() == 'ON' skip_gtid_warning_printed = False gtid_version_checked = False # Read the file one object/definition group at a time for row in read_next(file, format): # Check for replication command if row[0] == "RPL_COMMAND": if not skip_rpl: statements.append(row[1]) continue if row[0] == "GTID_COMMAND": gtid_command_found = True if not supports_gtid: # only display warning once if not skip_gtid_warning_printed: print _GTID_SKIP_WARNING skip_gtid_warning_printed = True elif not skip_gtid: if not gtid_version_checked: gtid_version_checked = True # Check GTID version for complete feature support servers[0].check_gtid_version() # Check the gtid_purged value too servers[0].check_gtid_executed("import") statements.append(row[1]) continue # If this is the first pass, get the database name from the file if get_db: if skip_header: skip_header = False else: db_name = _get_db(row) # quote db_name with backticks if needed if db_name and not is_quoted_with_backticks(db_name): db_name = quote_with_backticks(db_name) get_db = False if do_drop and import_type != "data": statements.append("DROP DATABASE IF EXISTS %s;" % db_name) if import_type != "data": if not _skip_object("CREATE_DB", options) and \ not format == 'sql': statements.append("CREATE DATABASE %s;" % db_name) # This is the first time through the loop so we must # check user permissions on source for all databases if db_name is not None: dest_db = Database(destination, db_name) # Make a dictionary of the options access_options = options.copy() dest_db.check_write_access(dest_val['user'], dest_val['host'], access_options) # Now check to see if we want definitions, data, or both: if row[0] == "sql" or row[0] in _DEFINITION_LIST: if format != "sql" and len(row[1]) == 1: raise UtilError("Cannot read an import file generated with " "--display=NAMES") if import_type in ("definitions", "both"): if format == "sql": statements.append(row[1]) else: if obj_type == "": obj_type = row[0] if obj_type != row[0]: if len(definitions) > 0: _process_definitions(statements, table_col_list, db_name) obj_type = row[0] definitions = [] if not _skip_object(row[0], options): definitions.append(row[1]) else: # see if there are any definitions to process if len(definitions) > 0: _process_definitions(statements, table_col_list, db_name) definitions = [] if import_type in ("data", "both"): if _skip_object("DATA", options): continue # skip data elif format == "sql": statements.append(row[1]) else: if row[0] == "BEGIN_DATA": # Start of table so first row is columns. if len(table_rows) > 0: _process_data(tbl_name, statements, columns, table_col_list, table_rows, skip_blobs) table_rows = [] read_columns = True tbl_name = row[1] if not is_quoted_with_backticks(tbl_name): db, sep, tbl = tbl_name.partition('.') q_db = quote_with_backticks(db) q_tbl = quote_with_backticks(tbl) tbl_name = ".".join([q_db, q_tbl]) else: if read_columns: columns = row[1] read_columns = False else: if not single: table_rows.append(row[1]) else: str = _build_insert_data( columns, tbl_name, row[1]) statements.append(str) # Process remaining definitions if len(definitions) > 0: _process_definitions(statements, table_col_list, db_name) definitions = [] # Process remaining data rows if len(table_rows) > 0: _process_data(tbl_name, statements, columns, table_col_list, table_rows, skip_blobs) table_rows = [] # Now process the statements _exec_statements(statements, destination, format, options, dryrun) file.close() # Check gtid process if supports_gtid and not gtid_command_found: print _GTID_MISSING_WARNING if not quiet: print "#...done." return True
def _build_create_objects(obj_type, db, definitions): """Build the CREATE and GRANT SQL statments for object definitions. This method takes the object information read from the file using the _read_next() method and constructs SQL definition statements for each object. It receives a block of objects and creates a statement for each object. obj_type[in] The object type db[in] The database definitions[in] The list of object definition data from the file Returns (string[]) - a list of SQL statements for the objects """ create_strings = [] skip_header = True obj_db = "" obj_name = "" col_list = [] stop = len(definitions) col_ref = {} engine = None # Now the tricky part. for i in range(0,stop): if skip_header: skip_header = False col_ref = _build_column_ref(definitions[i]) continue defn = definitions[i] # Read engine from first row and save old value. old_engine = engine engine = defn[col_ref.get("ENGINE",2)] create_str = "" if obj_type == "TABLE": if (obj_db == "" and obj_name == ""): obj_db = defn[col_ref.get("TABLE_SCHEMA",0)] obj_name = defn[col_ref.get("TABLE_NAME",1)] if (obj_db == defn[col_ref.get("TABLE_SCHEMA",0)] and \ obj_name == defn[col_ref.get("TABLE_NAME",1)]): col_list.append(defn) else: create_str = _build_create_table(obj_db, obj_name, old_engine, col_list, col_ref) create_strings.append(create_str) obj_db = defn[col_ref.get("TABLE_SCHEMA",0)] obj_name = defn[col_ref.get("TABLE_NAME",1)] col_list = [] col_list.append(defn) # check for end. if i+1 == stop: create_str = _build_create_table(obj_db, obj_name, engine, col_list, col_ref) create_strings.append(create_str) elif obj_type == "VIEW": # Quote table schema and name with backticks if needed if not is_quoted_with_backticks(defn[col_ref.get("TABLE_SCHEMA", 0)]): obj_db = quote_with_backticks(defn[col_ref.get("TABLE_SCHEMA", 0)]) else: obj_db = defn[col_ref.get("TABLE_SCHEMA", 0)] if not is_quoted_with_backticks(defn[col_ref.get("TABLE_NAME", 1)]): obj_name = quote_with_backticks(defn[col_ref.get("TABLE_NAME", 1)]) else: obj_name = defn[col_ref.get("TABLE_NAME", 1)] # Create VIEW statement create_str = ("CREATE ALGORITHM=UNDEFINED DEFINER={defr} " "SQL SECURITY {sec} VIEW {scma}.{tbl} AS {defv}; " ).format(defr=defn[col_ref.get("DEFINER", 2)], sec=defn[col_ref.get("SECURITY_TYPE", 3)], scma=obj_db, tbl=obj_name, defv=defn[col_ref.get("VIEW_DEFINITION", 4)]) create_strings.append(create_str) elif obj_type == "TRIGGER": # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db if not is_quoted_with_backticks(defn[col_ref.get("TRIGGER_NAME", 0)]): obj_name = quote_with_backticks(defn[col_ref.get("TRIGGER_NAME", 0)]) else: obj_name = defn[col_ref.get("TRIGGER_NAME", 0)] if not is_quoted_with_backticks( defn[col_ref.get("EVENT_OBJECT_SCHEMA", 3)]): evt_scma = quote_with_backticks( defn[col_ref.get("EVENT_OBJECT_SCHEMA", 3)]) else: evt_scma = defn[col_ref.get("EVENT_OBJECT_SCHEMA", 3)] if not is_quoted_with_backticks( defn[col_ref.get("EVENT_OBJECT_TABLE", 4)]): evt_tbl = quote_with_backticks( defn[col_ref.get("EVENT_OBJECT_TABLE", 4)]) else: evt_tbl = defn[col_ref.get("EVENT_OBJECT_TABLE", 4)] # Create TRIGGER statement # Important Note: There is a bug in the server when backticks are # used in the trigger statement, i.e. the ACTION_STATEMENT value in # INFORMATION_SCHEMA.TRIGGERS is incorrect (see BUG##16291011). create_str = ("CREATE DEFINER={defr} " "TRIGGER {scma}.{trg} {act_t} {evt_m} " "ON {evt_s}.{evt_t} FOR EACH {act_o} {act_s};" ).format(defr=defn[col_ref.get("DEFINER", 1)], scma=obj_db, trg=obj_name, act_t=defn[col_ref.get("ACTION_TIMING", 6)], evt_m=defn[col_ref.get("EVENT_MANIPULATION", 2)], evt_s=evt_scma, evt_t=evt_tbl, act_o=defn[col_ref.get("ACTION_ORIENTATION", 5)], act_s=defn[col_ref.get("ACTION_STATEMENT", 7)]) create_strings.append(create_str) elif obj_type in ("PROCEDURE", "FUNCTION"): # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db if not is_quoted_with_backticks(defn[col_ref.get("NAME", 0)]): obj_name = quote_with_backticks(defn[col_ref.get("NAME", 0)]) else: obj_name = defn[col_ref.get("NAME", 0)] # Create PROCEDURE or FUNCTION statement if obj_type == "FUNCTION": func_str = " RETURNS %s" % defn[col_ref.get("RETURNS", 7)] if defn[col_ref.get("IS_DETERMINISTI", 3)] == 'YES': func_str = "%s DETERMINISTIC" % func_str else: func_str = "" create_str = ("CREATE DEFINER={defr}" " {type} {scma}.{name}({par_lst})" "{func_ret} {body};" ).format(defr=defn[col_ref.get("DEFINER", 5)], type=obj_type, scma=obj_db, name=obj_name, par_lst=defn[col_ref.get("PARAM_LIST", 6)], func_ret=func_str, body=defn[col_ref.get("BODY", 8)]) create_strings.append(create_str) elif obj_type == "EVENT": # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db if not is_quoted_with_backticks(defn[col_ref.get("NAME", 0)]): obj_name = quote_with_backticks(defn[col_ref.get("NAME", 0)]) else: obj_name = defn[col_ref.get("NAME", 0)] # Create EVENT statement create_str = ("CREATE EVENT {scma}.{name} " "ON SCHEDULE EVERY {int_v} {int_f} " "STARTS '{starts}' " ).format(scma=obj_db, name=obj_name, int_v=defn[col_ref.get("INTERVAL_VALUE", 5)], int_f=defn[col_ref.get("INTERVAL_FIELD", 6)], starts=defn[col_ref.get("STARTS", 8)] ) ends_index = col_ref.get("ENDS", 9) if len(defn[ends_index]) > 0 and \ defn[ends_index].upper() != "NONE": create_str = "%s ENDS '%s' " % (create_str, defn[ends_index]) if defn[col_ref.get("ON_COMPLETION", 11)] == "DROP": create_str = "%s ON COMPLETION NOT PRESERVE " % create_str if defn[col_ref.get("STATUS", 10)] == "DISABLED": create_str = "%s DISABLE " % create_str create_str = "%s DO %s;" % (create_str, defn[col_ref.get("BODY", 2)]) create_strings.append(create_str) elif obj_type == "GRANT": try: user, priv, db, tbl = defn[0:4] except: raise UtilError("Object data invalid: %s : %s" % \ (obj_type, defn)) if not tbl: tbl = "*" elif tbl.upper() == "NONE": tbl = "*" # Quote required identifiers with backticks obj_db = quote_with_backticks(db) \ if not is_quoted_with_backticks(db) else db obj_tbl = quote_with_backticks(tbl) \ if (tbl != '*' and not is_quoted_with_backticks(tbl)) else tbl # Create GRANT statement create_str = "GRANT %s ON %s.%s TO %s" % (priv, obj_db, obj_tbl, user) create_strings.append(create_str) elif obj_type in ["RPL_COMMAND", "GTID_COMMAND"]: create_strings.append([defn]) else: raise UtilError("Unknown object type discovered: %s" % obj_type) return create_strings
def import_file(dest_val, file_name, options): """Import a file This method reads a file and, if needed, transforms the file into discrete SQL statements for execution on the destination server. It accepts any of the formal structured files produced by the mysqlexport utility including formats SQL, CSV, TAB, GRID, and VERTICAL. It will read these files and skip or include the definitions or data as specified in the options. An error is raised for any conversion errors or errors while executing the statements. Users are highly encouraged to use the --dryrun option which will print the SQL statements without executing them. dest_val[in] a dictionary containing connection information for the destination including: (user, password, host, port, socket) file_name[in] name (and path) of the file to import options[in] a dictionary containing the options for the import: (skip_tables, skip_views, skip_triggers, skip_procs, skip_funcs, skip_events, skip_grants, skip_create, skip_data, no_header, display, format, and debug) Returns bool True = success, False = error """ from mysql.utilities.common.database import Database from mysql.utilities.common.options import check_engine_options from mysql.utilities.common.table import Table from mysql.utilities.common.server import connect_servers # Helper method to dig through the definitions for create statements def _process_definitions(statements, table_col_list, db_name): # First, get the SQL strings sql_strs = _build_create_objects(obj_type, db_name, definitions) statements.extend(sql_strs) # Now, save the column list col_list = _build_col_metadata(obj_type, definitions) if len(col_list) > 0: table_col_list.extend(col_list) def _process_data(tbl_name, statements, columns, table_col_list, table_rows, skip_blobs): # if there is data here, build bulk inserts # First, create table reference, then call insert_rows() tbl = Table(destination, tbl_name) # Need to check to see if table exists! if tbl.exists(): tbl.get_column_metadata() col_meta = True elif len(table_col_list) > 0: col_meta = _get_column_metadata(tbl, table_col_list) else: fix_cols = [] fix_cols.append((tbl.tbl_name, columns)) col_meta = _get_column_metadata(tbl, fix_cols) if not col_meta: raise UtilError("Cannot build bulk insert statements without " "the table definition.") ins_strs = tbl.make_bulk_insert(table_rows, tbl.q_db_name) if len(ins_strs[0]) > 0: statements.extend(ins_strs[0]) if len(ins_strs[1]) > 0 and not skip_blobs: for update in ins_strs[1]: statements.append(update) # Gather options format = options.get("format", "sql") no_headers = options.get("no_headers", False) quiet = options.get("quiet", False) import_type = options.get("import_type", "definitions") single = options.get("single", True) dryrun = options.get("dryrun", False) do_drop = options.get("do_drop", False) skip_blobs = options.get("skip_blobs", False) skip_gtid = options.get("skip_gtid", False) # Attempt to connect to the destination server conn_options = { 'quiet' : quiet, 'version' : "5.1.30", } servers = connect_servers(dest_val, None, conn_options) destination = servers[0] # Check storage engines check_engine_options(destination, options.get("new_engine", None), options.get("def_engine", None), False, options.get("quiet", False)) if not quiet: if import_type == "both": str = "definitions and data" else: str = import_type print "# Importing %s from %s." % (str, file_name) # Setup variables we will need skip_header = not no_headers if format == "sql": skip_header = False get_db = True check_privileges = False db_name = None file = open(file_name) columns = [] read_columns = False table_rows = [] obj_type = "" definitions = [] statements = [] table_col_list = [] tbl_name = "" skip_rpl = options.get("skip_rpl", False) gtid_command_found = False supports_gtid = servers[0].supports_gtid() == 'ON' skip_gtid_warning_printed = False gtid_version_checked = False # Read the file one object/definition group at a time for row in read_next(file, format): # Check for replication command if row[0] == "RPL_COMMAND": if not skip_rpl: statements.append(row[1]) continue if row[0] == "GTID_COMMAND": gtid_command_found = True if not supports_gtid: # only display warning once if not skip_gtid_warning_printed: print _GTID_SKIP_WARNING skip_gtid_warning_printed = True elif not skip_gtid: if not gtid_version_checked: gtid_version_checked = True # Check GTID version for complete feature support servers[0].check_gtid_version() # Check the gtid_purged value too servers[0].check_gtid_executed("import") statements.append(row[1]) continue # If this is the first pass, get the database name from the file if get_db: if skip_header: skip_header = False else: db_name = _get_db(row) # quote db_name with backticks if needed if db_name and not is_quoted_with_backticks(db_name): db_name = quote_with_backticks(db_name) get_db = False if do_drop and import_type != "data": statements.append("DROP DATABASE IF EXISTS %s;" % db_name) if import_type != "data": if not _skip_object("CREATE_DB", options) and \ not format == 'sql': statements.append("CREATE DATABASE %s;" % db_name) # This is the first time through the loop so we must # check user permissions on source for all databases if db_name is not None: dest_db = Database(destination, db_name) # Make a dictionary of the options access_options = options.copy() dest_db.check_write_access(dest_val['user'], dest_val['host'], access_options) # Now check to see if we want definitions, data, or both: if row[0] == "sql" or row[0] in _DEFINITION_LIST: if format != "sql" and len(row[1]) == 1: raise UtilError("Cannot read an import file generated with " "--display=NAMES") if import_type in ("definitions", "both"): if format == "sql": statements.append(row[1]) else: if obj_type == "": obj_type = row[0] if obj_type != row[0]: if len(definitions) > 0: _process_definitions(statements, table_col_list, db_name) obj_type = row[0] definitions = [] if not _skip_object(row[0], options): definitions.append(row[1]) else: # see if there are any definitions to process if len(definitions) > 0: _process_definitions(statements, table_col_list, db_name) definitions = [] if import_type in ("data", "both"): if _skip_object("DATA", options): continue # skip data elif format == "sql": statements.append(row[1]) else: if row[0] == "BEGIN_DATA": # Start of table so first row is columns. if len(table_rows) > 0: _process_data(tbl_name, statements, columns, table_col_list, table_rows, skip_blobs) table_rows = [] read_columns = True tbl_name = row[1] if not is_quoted_with_backticks(tbl_name): db, sep, tbl = tbl_name.partition('.') q_db = quote_with_backticks(db) q_tbl = quote_with_backticks(tbl) tbl_name = ".".join([q_db, q_tbl]) else: if read_columns: columns = row[1] read_columns = False else: if not single: table_rows.append(row[1]) else: str = _build_insert_data(columns, tbl_name, row[1]) statements.append(str) # Process remaining definitions if len(definitions) > 0: _process_definitions(statements, table_col_list, db_name) definitions = [] # Process remaining data rows if len(table_rows) > 0: _process_data(tbl_name, statements, columns, table_col_list, table_rows, skip_blobs) table_rows = [] # Now process the statements _exec_statements(statements, destination, format, options, dryrun) file.close() # Check gtid process if supports_gtid and not gtid_command_found: print _GTID_MISSING_WARNING if not quiet: print "#...done." return True
# 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() print("ERROR: %s" % e.errmsg)