class Test(GadgetsTestCase):
"""Class to test mysql_gadgets.common.user module.
"""
@property
def num_servers_required(self):
"""Property defining the number of servers required by the test.
"""
return 1
def setUp(self):
""" Setup server connection
"""
self.server_cnx = {'conn_info': self.options[SERVER_CNX_OPT][0]}
self.server = Server(self.server_cnx)
self.server.connect()
def tearDown(self):
""" Disconnect base server (for all tests).
"""
self.server.disconnect()
def test_create_user(self):
""" Tests User.create_user methods"""
self.server.exec_query("Drop USER if exists 'joe'@'users'")
self.server.exec_query("Drop USER if exists 'Jonh_CAPITALS'@'{0}'"
"".format(self.server.host))
user_root = User(self.server, "{0}@{1}".format(self.server.user,
self.server.host))
user_name = 'Jonh_CAPITALS'
user_root.create(new_user="******".format(user_name,
self.server.host),
passwd="his_pass",
ssl=True,
disable_binlog=True)
user_root.exists("{0}@{1}".format(user_name, self.server.host))
user_obj2 = User(self.server, "{0}@{1}".format('joe', 'users'))
user_root.drop(new_user="******".format(user_name, self.server.host))
user_obj2.drop()
self.assertFalse((self.server.user_host_exists('Jonh_CAPITALS',
self.server.host)))
def test_check_privileges(self):
""" Tests check_privileges"""
self.server.exec_query("Drop USER if exists 'check_privs'@'%'")
self.server.exec_query("CREATE USER 'check_privs'@'%'")
operation = "can select?"
privileges = ['SELECT']
description = "checking privs"
server1 = Server({
"conn_info":
"check_privs@localhost:{0}"
"".format(self.server.port)
})
server1.connect()
# expect GadgetError: Query failed: Unknown system variable
with self.assertRaises(GadgetError) as test_raises:
check_privileges(server1, operation, privileges, description)
exception = test_raises.exception
self.assertTrue(
"not have sufficient privileges" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
self.server.exec_query("Drop USER if exists 'check_privs'@'%'")
def test_check_missing_privileges(self):
""" Tests the User's check_missing_privileges method"""
self.server.exec_query("Drop USER if exists 'Jonh_CAPITALS'@'{0}'"
"".format(self.server.host))
user_name = 'Jonh_CAPITALS'
user_obj = User(self.server,
"{0}@{1}".format(user_name, self.server.host),
verbosity=1)
self.assertFalse(user_obj.exists())
user_obj.create(disable_binlog=True, ssl=True)
self.assertTrue(user_obj.exists())
self.assertListEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"], as_string=False),
["CREATE USER", "REPLICATION SLAVE"])
self.assertEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"]),
"CREATE USER and REPLICATION SLAVE")
change_user_privileges(self.server,
user_name,
self.server.host,
grant_list=["REPLICATION SLAVE", "CREATE USER"],
disable_binlog=True)
self.assertListEqual(user_obj.get_grants(refresh=True),
[("GRANT REPLICATION SLAVE, CREATE USER ON *.* "
"TO 'Jonh_CAPITALS'@'localhost'", )])
self.assertListEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"], as_string=False), [])
self.assertEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"]), "")
user_obj.drop()
user_name = 'some_user'
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
change_user_privileges(self.server,
user_name,
self.server.host,
user_passwd="some pass",
grant_list=["REPLICATION SLAVE", "CREATE USER"],
disable_binlog=True,
create_user=True)
change_user_privileges(self.server,
user_name,
self.server.host,
revoke_list=["REPLICATION SLAVE"],
disable_binlog=True)
# expect GadgetError: Query failed: Unknown system variable
with self.assertRaises(GadgetError) as test_raises:
user_obj.drop(user_name)
exception = test_raises.exception
self.assertTrue("Cannot parse user@host" in exception.errmsg,
"The exception message was not the expected")
user_obj.drop("{0}@{1}".format(user_name, self.server.host))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
def test_get_grants(self):
""" Tests get_grants method"""
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', '%'))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', self.server.host))
user_obj2 = User(self.server,
"{0}@{1}".format('jose', '%'),
verbosity=True)
self.assertListEqual(user_obj2.get_grants(globals_privs=True), [])
user_obj2.create()
# Test user has none grants
self.assertListEqual(user_obj2.get_grants(globals_privs=True),
[("GRANT USAGE ON *.* TO 'jose'@'%'", )])
self.assertDictEqual(user_obj2.get_grants(as_dict=True),
{'*': {
'*': {'USAGE'}
}})
# Test get global privileges
self.server.exec_query("GRANT PROXY ON '{0}'@'{1}' TO '{0}'@'%'"
"".format('jose', self.server.host))
self.server.exec_query("GRANT UPDATE ON mysql.* TO '{0}'@'{1}'"
"".format('jose', '%'))
exp_list_res = [("GRANT USAGE ON *.* TO 'jose'@'%'", ),
("GRANT UPDATE ON `mysql`.* TO 'jose'@'%'", ),
("GRANT PROXY ON 'jose'@'{0}' TO 'jose'@'%'"
"".format(self.server.host), )]
self.assertListEqual(
user_obj2.get_grants(globals_privs=True, refresh=True),
exp_list_res)
self.assertDictEqual(user_obj2.get_grants(as_dict=True, refresh=True),
{
'*': {
'*': {'USAGE'}
},
'`mysql`': {
'*': {'UPDATE'}
}
})
user_obj2.drop()
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', '%'))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', self.server.host))
def test_user_has_privilege(self):
""" Tests USER.has_privilege method"""
user_name = 'Jonh_Update'
user_root = User(self.server, "{0}@{1}".format(self.server.user,
self.server.host))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
db_ = "mysql"
obj = "user"
access = "UPDATE"
skip_grant = False
# Test object level privilege with user with global * privilege
self.assertTrue(user_root.has_privilege(db_, obj, access, skip_grant))
# create new user to test missing privileges.
user_update = User(self.server,
"{0}@{1}".format(user_name, self.server.host))
user_update.create()
# Test privileges disabled
self.server.grants_enabled = False
skip_grant = True
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
self.server.grants_enabled = True
skip_grant = True
# Test missing privilege
self.assertFalse(
user_update.has_privilege(db_, obj, access, skip_grant))
access = "USAGE"
# Test default privilege USAGE at db level
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
self.server.exec_query("GRANT UPDATE ON mysql.user TO '{0}'@'{1}'"
"".format(user_name, self.server.host))
access = "UPDATE"
# Test privilege at object level
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
# Test privilege at db level
self.server.exec_query("GRANT UPDATE ON mysql.* TO '{0}'@'{1}'"
"".format(user_name, self.server.host))
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
user_update.drop()
def test_parse_grant_statement(self):
""" Tests parse_grant_statement Method.
"""
# Test function
parsed_statement = User.parse_grant_statement(
"GRANT ALTER ROUTINE, EXECUTE ON FUNCTION `util_test`.`f1` TO "
"'priv_test_user2'@'%' WITH GRANT OPTION")
self.assertEqual(
parsed_statement,
(set(['GRANT OPTION', 'EXECUTE', 'ALTER ROUTINE'
]), None, '`util_test`', '`f1`', "'priv_test_user2'@'%'"))
# Test procedure
parsed_statement = User.parse_grant_statement(
"GRANT ALTER ROUTINE ON PROCEDURE `util_test`.`p1` TO "
"'priv_test_user2'@'%' IDENTIFIED BY "
"PASSWORD '*123DD712CFDED6313E0DDD2A6E0D62F12E580A6F' "
"WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'ALTER ROUTINE']), None,
'`util_test`', '`p1`', "'priv_test_user2'@'%'"))
# Test with quoted objects
parsed_statement = User.parse_grant_statement(
"GRANT CREATE VIEW ON `db``:db`.```t``.``export_2` TO "
"'priv_test_user'@'%'")
self.assertEqual(parsed_statement,
(set(['CREATE VIEW']), None, '`db``:db`.```t``',
'``export_2`', "'priv_test_user'@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT CREATE VIEW ON `db``:db`.```t``.* TO "
"'priv_test_user'@'%'")
self.assertEqual(parsed_statement,
(set(['CREATE VIEW']), None, '`db``:db`.```t``', '*',
"'priv_test_user'@'%'"))
# Test multiple grants with password and grant option
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON `mysql`.* TO 'user2'@'%' IDENTIFIED BY "
"PASSWORD '*123DD712CFDED6313E0DDD2A6E0D62F12E580A6F' "
"REQUIRE SSL WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'UPDATE', 'SELECT'
]), None, '`mysql`', '*', "'user2'@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON `mysql`.* TO 'user2'@'%' IDENTIFIED BY "
"PASSWORD REQUIRE SSL WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'UPDATE', 'SELECT'
]), None, '`mysql`', '*', "'user2'@'%'"))
# Test proxy privileges
parsed_statement = User.parse_grant_statement(
"GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'PROXY'
]), "''@''", None, None, "'root'@'localhost'"))
parsed_statement = User.parse_grant_statement(
"GRANT PROXY ON 'root'@'%' TO 'root'@'localhost' WITH GRANT "
"OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'PROXY']), "'root'@'%'", None,
None, "'root'@'localhost'"))
# Test parse grant with ansi quotes
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'",
sql_mode="ANSI_QUOTES")
self.assertEqual(parsed_statement[4], ("user2@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'")
self.assertEqual(parsed_statement[2], '`mysql`')
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'")
self.assertEqual(parsed_statement[3], '`user`')
self.assertRaises(
GadgetError, User.parse_grant_statement,
"GRANT PROXY 'root'@'%' TO 'root'@'localhost' WITH "
"GRANT OPTION")
def test_check_server_requirements(self):
"""Tests check_server_requirements method"""
skip_if_not_GR_approved(self.server1)
self.server.exec_query("drop user if exists 'new_rpl_user'")
self.server.exec_query("drop user if exists 'replic_user'@'%'")
self.server.exec_query("drop user if exists 'replic_user'@'localhost'")
# Test with default values, server.user must have SUPER
options = {
"replication_user": "******",
"rep_user_passwd": "rplr_pass",
}
rpl_settings = get_rpl_usr(options)
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
self.assertTrue(
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True))
self.assertTrue(
User(self.server, "replic_user@%", None).exists(),
"User was not created on check_server_requirements")
# Test using an admin user without CREATE USER privilege.
grant_list = ["SELECT"]
self.server.exec_query("DROP USER IF EXISTS "
"'replic_user'@'localhost'")
change_user_privileges(self.server,
"replic_user",
'localhost',
"rplr_pass",
grant_list=grant_list,
create_user=True)
server2 = Server({
"conn_info":
"replic_user@localhost:{0}"
"".format(self.server.port)
})
server2.passwd = "rplr_pass"
server2.connect()
qry_key = ("select MEMBER_HOST, MEMBER_PORT from {0}"
"".format(REP_GROUP_MEMBERS_TABLE))
frozen_queries = {qry_key: [[server2.host, server2.port]]}
mock_server = get_mock_server(server2, variables=frozen_queries)
options = {
"replication_user": "******",
"rep_user_passwd": "rpl_pass",
}
rpl_settings = get_rpl_usr(options)
req_dict = get_req_dict(server2, rpl_settings["replication_user"])
# expect GadgetError: Query failed: No required privileges
# to create the replication user
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"required privileges to create" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test existing user and admin user without REPLICATION SLAVE grant
grant_list = ["SELECT"]
revoke_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list,
revoke_list=revoke_list,
create_user=True,
with_grant=True)
revoke_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"replic_user",
"%",
revoke_list=revoke_list)
# expect GadgetError: does not have the REPLICATION SLAVE privilege,
# and can not be granted by.
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"does not have the REPLICATION SLAVE privilege, "
"and can not be granted by" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
self.assertTrue(
"SUPER privilege required to disable the binlog."
in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# self.server.exec_query("drop user if exists 'replic_user'@'%'")
# Test existing user and admin user without REPLICATION SLAVE grant
grant_list = ["SELECT"]
revoke_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list,
revoke_list=revoke_list)
grant_list = ["REPLICATION SLAVE", "SUPER"]
change_user_privileges(self.server,
"replic_user",
"localhost",
grant_list=grant_list,
with_grant=True)
# reset session to get new privileges.
server2.disconnect()
server2.connect()
mock_server = get_mock_server(server2, variables=frozen_queries)
self.assertTrue(
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True))
# Test existing rpl user and admin user without grant
# admin user: replic_user
# rpl user: new_rpl_user
grant_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list)
grant_list = ["SELECT", "CREATE USER"]
change_user_privileges(self.server,
"create_rpl",
"127.0.0.1",
user_passwd="c_pass",
grant_list=grant_list,
create_user=True,
with_grant=True)
server3 = Server({
"conn_info":
"[email protected]:{0}"
"".format(self.server.port)
})
server3.passwd = "c_pass"
server3.connect()
req_dict3 = get_req_dict(server3, rpl_settings["replication_user"])
# expect GadgetError: No required privileges
# to grant Replication Slave privilege
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(server3,
req_dict3,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"SUPER privilege needed to run the CHANGE MASTER "
"command" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test invalid server_id
mock_server = get_mock_server(self.server,
variables={"server_id": "0"})
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
# expect GadgetError: server_id not valid
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"is not valid, it must be a positive integer" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test duplicate server_id
mock_server = get_mock_server(self.server,
variables={"server_id": "101"})
req_dict["SERVER_ID"] = {"peers": [mock_server]}
# expect GadgetError: server_id is already used by peer
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"is already used by peer" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test existing user and admin with required grants
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
grant_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list)
self.assertTrue(
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True))
# Tests server variables not meet required values
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
req_dict[SERVER_VARIABLES] = {
"log_bin": {
ONE_OF: ("0", )
},
"binlog_format": {
ONE_OF: ("", )
},
"binlog_checksum": {
ONE_OF: ("", )
},
"gtid_mode": {
ONE_OF: ("OFF", )
},
"log_slave_updates": {
ONE_OF: ("0", )
},
"enforce_gtid_consistency": {
ONE_OF: ("OFF", )
},
}
# expect GadgetError: change the configuration values
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=True,
update=False,
skip_backup=True)
exception = test_raises.exception
self.assertIn(
"on server {0} are incompatible with Group "
"Replication.".format(self.server), exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test server version
req_dict[SERVER_VERSION] = "99.9.9"
# expect GadgetError: Query failed: server version
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=True,
update=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"does not meet the required MySQL server version"
in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
self.server.exec_query("drop user if exists 'replic_user'")
self.server.exec_query("drop user if exists 'create_rpl'")
self.server.exec_query("drop user if exists 'new_rpl_user'")
class TestGroupReplication(GadgetsTestCase):
"""Test class for mysql_gadgets.common.group_replication
"""
@property
def num_servers_required(self):
"""Property defining the number of servers required by the test.
"""
return 1
def setUp(self):
""" Setup server connection
"""
server_cnx = {'conn_info': self.options[SERVER_CNX_OPT][0]}
self.server1 = Server(server_cnx)
self.server1.connect()
# default user
self.server1.exec_query("drop user if exists 'rpl_user'")
qry_key1 = ("select MEMBER_HOST, MEMBER_PORT from {0}"
"".format(REP_GROUP_MEMBERS_TABLE))
qry_key2 = "show variables like 'group_replication_%'"
frozen_queries = {
qry_key1: [[self.server1.host, self.server1.port]],
qry_key2: [("group_replication_group_name", "name"),
("group_replication_start_on_boot", "ON"),
("group_replication_group_seeds", "")]
}
variables = {GR_LOCAL_ADDRESS: "localhost:3307"}
self.server = get_mock_server(self.server1,
queries=frozen_queries,
variables=variables)
# Set directory with option files for tests.
self.option_file_dir = os.path.normpath(
os.path.join(__file__, "..", "std_data", "option_files"))
self.session_track_system_variables_bkp = self.server1.exec_query(
"select @@global.session_track_system_variables")[0][0]
def tearDown(self):
""" Disconnect base server (for all tests).
"""
queries = [
"drop user if exists 'rpl_user'",
"drop user if exists 'replic_user'@'localhost'",
"drop user if exists 'create_rpl'@'127.0.0.1'",
"drop user if exists 'replic_user'",
"set @@global.session_track_system_variables='{0}'".format(
self.session_track_system_variables_bkp),
]
for query in queries:
self.server1.exec_query(query)
self.server1.disconnect()
def test_get_gr_members(self):
"""Tests get_gr_members method"""
self.assertIsInstance(
get_gr_members(self.server)[0], Server,
"An object of class server was "
"expected")
def test__print_check_unique_id_results(self):
"""Tests _print_check_unique_id_results method"""
server_id_res = {"pass": True}
_print_check_unique_id_results(server_id_res)
server_id_res = {"pass": False, "duplicate": None}
_print_check_unique_id_results(server_id_res)
server_id_res = {"pass": False, "duplicate": self.server}
_print_check_unique_id_results(server_id_res)
def test_print_check_server_version_results(self):
"""Tests _print_check_server_version_results method"""
server_ver_res = {"pass": False, SERVER_VERSION: [5, 6, 4]}
self.assertIsNone(_print_check_server_version_results(server_ver_res))
def test_get_gr_name_from_peer(self):
"""Tests the get_gr_name_from_peer method"""
self.assertEqual(get_gr_name_from_peer(self.server),
"'b7286041-3016-11e6-ba52-507b9d87510a'")
def test_get_gr_local_address_from(self):
"""Tests the get_gr_local_address_from method"""
self.assertEqual(get_gr_local_address_from(self.server),
"'localhost:3307'")
def test_get_gr_configs_from_instance(self):
"""Tests the get_gr_configs_from_instance function"""
res = get_gr_configs_from_instance(self.server)
# check that result is as expected from the mock server
self.assertEqual(
res,
OrderedDict([('group_replication_group_name', 'name'),
('group_replication_start_on_boot', 'ON'),
('group_replication_group_seeds', '')]))
def test__print_check_variables_results(self):
"""Tests _print_check_variables_results method"""
_format_table_results({"pass": True})
server_var_res = {
"pass": False,
"var_name": (True, "expected val", "current val"),
"var_name2": (False, "exp_val", "cur_val"),
}
self.assertTrue(len(_format_table_results(server_var_res)), 2)
def test_check_server_requirements(self):
"""Tests check_server_requirements method"""
skip_if_not_GR_approved(self.server1)
self.server.exec_query("drop user if exists 'new_rpl_user'")
self.server.exec_query("drop user if exists 'replic_user'@'%'")
self.server.exec_query("drop user if exists 'replic_user'@'localhost'")
# Test with default values, server.user must have SUPER
options = {
"replication_user": "******",
"rep_user_passwd": "rplr_pass",
}
rpl_settings = get_rpl_usr(options)
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
self.assertTrue(
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True))
self.assertTrue(
User(self.server, "replic_user@%", None).exists(),
"User was not created on check_server_requirements")
# Test using an admin user without CREATE USER privilege.
grant_list = ["SELECT"]
self.server.exec_query("DROP USER IF EXISTS "
"'replic_user'@'localhost'")
change_user_privileges(self.server,
"replic_user",
'localhost',
"rplr_pass",
grant_list=grant_list,
create_user=True)
server2 = Server({
"conn_info":
"replic_user@localhost:{0}"
"".format(self.server.port)
})
server2.passwd = "rplr_pass"
server2.connect()
qry_key = ("select MEMBER_HOST, MEMBER_PORT from {0}"
"".format(REP_GROUP_MEMBERS_TABLE))
frozen_queries = {qry_key: [[server2.host, server2.port]]}
mock_server = get_mock_server(server2, variables=frozen_queries)
options = {
"replication_user": "******",
"rep_user_passwd": "rpl_pass",
}
rpl_settings = get_rpl_usr(options)
req_dict = get_req_dict(server2, rpl_settings["replication_user"])
# expect GadgetError: Query failed: No required privileges
# to create the replication user
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"required privileges to create" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test existing user and admin user without REPLICATION SLAVE grant
grant_list = ["SELECT"]
revoke_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list,
revoke_list=revoke_list,
create_user=True,
with_grant=True)
revoke_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"replic_user",
"%",
revoke_list=revoke_list)
# expect GadgetError: does not have the REPLICATION SLAVE privilege,
# and can not be granted by.
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"does not have the REPLICATION SLAVE privilege, "
"and can not be granted by" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
self.assertTrue(
"SUPER privilege required to disable the binlog."
in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# self.server.exec_query("drop user if exists 'replic_user'@'%'")
# Test existing user and admin user without REPLICATION SLAVE grant
grant_list = ["SELECT"]
revoke_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list,
revoke_list=revoke_list)
grant_list = ["REPLICATION SLAVE", "SUPER"]
change_user_privileges(self.server,
"replic_user",
"localhost",
grant_list=grant_list,
with_grant=True)
# reset session to get new privileges.
server2.disconnect()
server2.connect()
mock_server = get_mock_server(server2, variables=frozen_queries)
self.assertTrue(
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True))
# Test existing rpl user and admin user without grant
# admin user: replic_user
# rpl user: new_rpl_user
grant_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list)
grant_list = ["SELECT", "CREATE USER"]
change_user_privileges(self.server,
"create_rpl",
"127.0.0.1",
user_passwd="c_pass",
grant_list=grant_list,
create_user=True,
with_grant=True)
server3 = Server({
"conn_info":
"[email protected]:{0}"
"".format(self.server.port)
})
server3.passwd = "c_pass"
server3.connect()
req_dict3 = get_req_dict(server3, rpl_settings["replication_user"])
# expect GadgetError: No required privileges
# to grant Replication Slave privilege
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(server3,
req_dict3,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"SUPER privilege needed to run the CHANGE MASTER "
"command" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test invalid server_id
mock_server = get_mock_server(self.server,
variables={"server_id": "0"})
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
# expect GadgetError: server_id not valid
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"is not valid, it must be a positive integer" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test duplicate server_id
mock_server = get_mock_server(self.server,
variables={"server_id": "101"})
req_dict["SERVER_ID"] = {"peers": [mock_server]}
# expect GadgetError: server_id is already used by peer
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(mock_server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"is already used by peer" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test existing user and admin with required grants
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
grant_list = ["REPLICATION SLAVE"]
change_user_privileges(self.server,
"new_rpl_user",
"%",
grant_list=grant_list)
self.assertTrue(
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=False,
skip_backup=True))
# Tests server variables not meet required values
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
req_dict[SERVER_VARIABLES] = {
"log_bin": {
ONE_OF: ("0", )
},
"binlog_format": {
ONE_OF: ("", )
},
"binlog_checksum": {
ONE_OF: ("", )
},
"gtid_mode": {
ONE_OF: ("OFF", )
},
"log_slave_updates": {
ONE_OF: ("0", )
},
"enforce_gtid_consistency": {
ONE_OF: ("OFF", )
},
}
# expect GadgetError: change the configuration values
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=True,
update=False,
skip_backup=True)
exception = test_raises.exception
self.assertIn(
"on server {0} are incompatible with Group "
"Replication.".format(self.server), exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Test server version
req_dict[SERVER_VERSION] = "99.9.9"
# expect GadgetError: Query failed: server version
with self.assertRaises(GadgetError) as test_raises:
check_server_requirements(self.server,
req_dict,
rpl_settings,
verbose=True,
dry_run=True,
update=False,
skip_backup=True)
exception = test_raises.exception
self.assertTrue(
"does not meet the required MySQL server version"
in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
self.server.exec_query("drop user if exists 'replic_user'")
self.server.exec_query("drop user if exists 'create_rpl'")
self.server.exec_query("drop user if exists 'new_rpl_user'")
def test_check_server_requirements_config_vars(self):
"""Tests check_server_requirements method"""
# The dict object with the requirements to check.
req_dict = {}
# Add the variables to check on server.
req_dict[SERVER_VARIABLES] = {
'session_track_system_variables': {
ONE_OF: ("", )
},
"log_bin": {
ONE_OF: ("1", "ON")
},
}
self.server1.exec_query(
"set @@global.session_track_system_variables='time_zone'")
# Allow the variable to change dynamically.
dynamic_vars = set(DYNAMIC_SERVER_VARS)
dynamic_vars.add('session_track_system_variables')
self.assertTrue(
check_server_requirements(self.server1,
req_dict,
None,
dry_run=False,
skip_backup=True,
update=True,
dynamic_vars=dynamic_vars))
self.assertEqual(
self.server1.select_variable('session_track_system_variables',
"global"), "")
# Change the variables value.
req_dict[SERVER_VARIABLES]['session_track_system_variables'] = {
ONE_OF: ("", )
}
# check_server_requirements updates by default
self.assertTrue(
check_server_requirements(self.server1,
req_dict,
None,
verbose=True,
dry_run=False,
skip_backup=True,
dynamic_vars=dynamic_vars))
self.assertEqual(
self.server1.select_variable('session_track_system_variables',
"global"), "")
work_file = os.path.join(self.option_file_dir, "my_test.cnf")
orig_file = os.path.join(self.option_file_dir, "my.cnf")
# test requirements from a configuration file.
shutil.copy(orig_file, work_file)
req_dict[OPTION_PARSER] = MySQLOptionsParser(work_file)
req_dict[CONFIG_SETTINGS] = {
'session_track_system_variables': {
ONE_OF: ("autocommit", )
},
"log_bin": {
NOT_IN: ("ON", "1", "<not set>"),
DEFAULT: "0"
},
}
try:
opt_file_parser = MySQLOptionsParser(work_file)
# make sure section mysqld exist.
self.assertTrue(opt_file_parser.has_section('mysqld'))
if opt_file_parser.has_option('mysqld', 'log_bin'):
# delete it.
opt_file_parser.remove_option('mysqld', 'log_bin')
self.assertFalse(opt_file_parser.has_option('mysqld', 'log_bin'))
# write the changes
opt_file_parser.write()
self.assertTrue(
check_server_requirements(self.server1,
req_dict,
None,
verbose=True,
dry_run=False,
skip_backup=True,
dynamic_vars=dynamic_vars))
# check_server_requirements should change the value of
# session_track_system_variables and log_bin
opt_file_parser = MySQLOptionsParser(work_file)
self.assertTrue(opt_file_parser.has_option('mysqld', 'log_bin'))
self.assertEqual(opt_file_parser.get('mysqld', 'log_bin'), "0")
self.assertTrue(
opt_file_parser.has_option('mysqld',
'session_track_system_variables'))
self.assertEqual(
opt_file_parser.get('mysqld',
'session_track_system_variables'),
"autocommit")
req_dict[OPTION_PARSER] = MySQLOptionsParser(work_file)
req_dict[CONFIG_SETTINGS] = {
'session_track_system_variables': {
ONE_OF: ("<no value>", )
},
"log_bin": {
NOT_IN: ("OFF", "0", "<not set>"),
DEFAULT: "<no value>"
},
}
self.assertTrue(
check_server_requirements(self.server1,
req_dict,
None,
verbose=True,
dry_run=False,
skip_backup=True,
dynamic_vars=dynamic_vars))
# check_server_requirements should change the value of
# session_track_system_variables and log_bin
opt_file_parser = MySQLOptionsParser(work_file)
self.assertTrue(opt_file_parser.has_option('mysqld', 'log_bin'))
self.assertEqual(opt_file_parser.get('mysqld', 'log_bin'), None)
self.assertTrue(
opt_file_parser.has_option('mysqld',
'session_track_system_variables'))
self.assertEqual(
opt_file_parser.get('mysqld',
'session_track_system_variables'), None)
finally:
try:
os.remove(work_file)
except OSError:
pass
class TestGRPlugin(GadgetsTestCase):
"""This test class exercises the gr_plugin module at
mysql_gadgets.common.server.
"""
@property
def num_servers_required(self):
"""Property defining the number of servers required by the test.
"""
return 1
def setUp(self):
""" Setup server connection
"""
self.server_cnx = {'conn_info': self.options[SERVER_CNX_OPT][0]}
self.server = Server(self.server_cnx)
self.server.connect()
req_dict = get_req_dict(self.server, None, None, None)
try:
check_server_requirements(self.server, req_dict, None,
verbose=False, dry_run=False,
skip_schema_checks=False, update=False)
except:
raise unittest.SkipTest("Provided server must fulfill the GR "
"plugin requirements.")
def tearDown(self):
""" Disconnect base server (for all tests).
"""
self.server.exec_query("drop user if exists 'rpl_user'")
self.server.exec_query("drop user if exists 'replicator'")
self.server.disconnect()
def test_install_gr_plugin(self):
"""Test the install_gr_plugin method"""
if self.server.is_plugin_installed(GR_PLUGIN_NAME):
self.server.exec_query("UNINSTALL PLUGIN group_replication")
install_plugin(self.server, dry_run=True)
self.assertFalse(self.server.is_plugin_installed(GR_PLUGIN_NAME),
"GR plugin was expected to be loaded")
install_plugin(self.server)
self.assertTrue(self.server.is_plugin_installed(GR_PLUGIN_NAME),
"GR plugin was expected to be installed")
def test_get_rpl_usr(self):
"""Tests get_rpl_usr method"""
options = {"rep_user_passwd": "rpl_pass"}
rpl_user_dict = {
'host': '%',
'recovery_user': '******',
'rep_user_passwd': 'rpl_pass',
'replication_user': "******",
'ssl_mode': GR_SSL_REQUIRED,
}
self.assertDictEqual(get_rpl_usr(options), rpl_user_dict)
options = {
"rep_user_passwd": "my_password",
"replication_user": "******",
'ssl_mode': GR_SSL_DISABLED,
}
rpl_user_dict = {
'host': 'oracle.com',
"recovery_user": "******",
'rep_user_passwd': 'my_password',
'replication_user': "******",
'ssl_mode': GR_SSL_DISABLED,
}
self.assertDictEqual(get_rpl_usr(options), rpl_user_dict)
def test_get_gr_config_vars(self):
"""Test get_gr_config_vars method"""
options = {
"group_name": None,
}
local_address = "localhost:1234"
gr_config_vars = {
GR_LOCAL_ADDRESS: "'{0}'".format(local_address),
GR_SINGLE_PRIMARY_MODE: None,
GR_GROUP_NAME: None,
GR_GROUP_SEEDS: None,
GR_IP_WHITELIST: None,
GR_RECOVERY_USE_SSL: "'ON'",
GR_SSL_MODE: "'REQUIRED'"
}
self.assertDictEqual(get_gr_config_vars(local_address, options),
gr_config_vars)
options = {
"group_name": "18e76fd4-2d91-11e6-9b7e-507b9d87510a",
}
gr_config_vars = {
GR_GROUP_NAME: "'18e76fd4-2d91-11e6-9b7e-507b9d87510a'",
GR_LOCAL_ADDRESS: "'{0}'".format(local_address),
GR_SINGLE_PRIMARY_MODE: None,
GR_GROUP_SEEDS: None,
GR_IP_WHITELIST: None,
GR_RECOVERY_USE_SSL: "'ON'",
GR_SSL_MODE: "'REQUIRED'"
}
self.assertDictEqual(get_gr_config_vars(local_address, options),
gr_config_vars)
def test_check_server_requirements(self):
"""Test check_server_requirements method
"""
rpl_settings = {
"recovery_user": "******",
'rep_user_passwd': 'my_password',
'replication_user': "******",
"host": '%',
}
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
verbose = False
dry_run = True
check_server_requirements(self.server, req_dict, rpl_settings, verbose,
dry_run)
rpl_settings = {
"recovery_user": "******",
'rep_user_passwd': 'my_password',
'replication_user': "******",
"host": '%',
}
req_dict = get_req_dict(self.server, rpl_settings["replication_user"])
dry_run = True
check_server_requirements(self.server, req_dict, rpl_settings, verbose,
dry_run)
check_server_requirements(self.server, req_dict, rpl_settings)
class Test(GadgetsTestCase):
"""Unit Test Class for the mysql_gadgets.common.req_checker module.
"""
@property
def num_servers_required(self):
"""Property defining the number of servers required by the test.
"""
return 1
def setUp(self):
""" Setup server connection
"""
self.server_cnx = {'conn_info': self.options[SERVER_CNX_OPT][0]}
self.server = Server(self.server_cnx)
self.server.connect()
def tearDown(self):
""" restore server
"""
self.server.disconnect()
def test_requirement_checker_use_defaults(self):
""" Test requirement_checker fail with positive values.
"""
req_check = RequirementChecker()
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
self.assertTrue(results['pass'], "Check was expected to Pass.")
def test_requirement_checker_no_reqs(self):
""" Test test_requirement_checker_no_reqs pass without requirements.
"""
req_dict = {}
req_check = RequirementChecker(req_dict, server=self.server)
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
self.assertTrue(results['pass'], "Check was expected to Pass.")
def test_requirement_checker_server_var_empty(self):
""" Test requirement_checker pass with no variables to test.
"""
req_dict = {
SERVER_VARIABLES: {}
}
req_check = RequirementChecker(req_dict, server=self.server)
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
self.assertTrue(results['pass'], "Check was expected to Pass.")
def test_requirement_checker_server_invalid_reqs(self):
""" Test requirement_checker pass with invalid tests.
"""
req_dict = {
"invalid": {}
}
req_check = RequirementChecker(req_dict, server=self.server)
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
self.assertTrue(results['pass'], "Check was expected to Pass.")
def test_requirement_checker_allvalues_are_checked(self):
""" Test requirement_checker all values tested regardless of result.
"""
res = self.server.exec_query("show databases")
logging.debug("%s", res)
req_dict = {
SERVER_VARIABLES: {
"log_bin": {ONE_OF: ("1",)},
"binlog_format": {ONE_OF: ("ROW",)},
"binlog_checksum": {ONE_OF: ("NONE",)},
"gtid_mode": {ONE_OF: ("1", "ON")},
"log_slave_updates": {ONE_OF: ("1",)},
"enforce_gtid_consistency": {ONE_OF: ("1", "ON")},
"master_info_repository": {ONE_OF: ("TABLE",)},
"relay_log_info_repository": {ONE_OF: ("TABLE",)},
},
SERVER_VERSION: "5.7.10"
}
test_list = req_dict[SERVER_VARIABLES].keys()
req_check = RequirementChecker(req_dict, server=self.server)
results = req_check.check_requirements()
res_list = results[SERVER_VARIABLES]
res_list.pop('pass')
for var in res_list.keys():
self.assertIn(var, test_list, "{0} was not found".format(var))
for var in test_list:
self.assertIn(var, res_list, "{0} was not found".format(var))
def test_requirement_checker_fail(self):
""" Test requirement_checker fail check.
"""
logging.debug("\n-- test_requirement_checker_fail")
res = self.server.exec_query("SET SQL_LOG_BIN=1")
logging.debug("%s", res)
req_dict = {
SERVER_VARIABLES: {
"sql_log_bin": {ONE_OF: ("0",)},
},
SERVER_VERSION: [9, 9, 9]
}
req_check = RequirementChecker(req_dict, server=self.server)
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
self.assertTupleEqual(results[SERVER_VARIABLES]["sql_log_bin"],
(False, '0', '1'),
"sql_log_bin value is not correct in result.")
self.assertFalse(results['pass'], "Check was expected to fail.")
def test_requirement_checker_logging(self):
""" Test requirement_checker logging.
"""
req_dict = {
SERVER_VARIABLES: {
"sql_log_bin": {ONE_OF: ("1",)},
},
SERVER_VERSION: "5.7.11"
}
req_check = RequirementChecker(req_dict, self.server)
results = req_check.check_requirements()
self.assertNotEqual(results['pass'], None,
"Check must return a value.")
def test_requirement_checker_server_version(self):
""" Test requirement_checker server version.
"""
req_dict = {
SERVER_VARIABLES: {
"sql_log_bin": {ONE_OF: ("1",)},
},
SERVER_VERSION: "5.7.10"
}
req_check = RequirementChecker(req_dict, server=self.server)
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
self.assertTrue(results['pass'], "Check was expected to pass.")
self.assertTupleEqual(results[SERVER_VARIABLES]["sql_log_bin"],
(True, '1', '1'),
"sql_log_bin value is not correct in result.")
def test_requirement_checker_server_version_fail(self):
""" Test requirement_checker fail due to server version.
"""
req_dict = {
SERVER_VARIABLES: {
"sql_log_bin": {ONE_OF: ("1",)},
},
SERVER_VERSION: "11.1.1"
}
req_check = RequirementChecker(req_dict, server=self.server)
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
self.assertFalse(results['pass'], "Check was expected to fail.")
def test_requirement_checker_alt_server(self):
""" Test requirement_checker fail with positive values.
"""
res = self.server.exec_query("SET GLOBAL show_compatibility_56=1")
logging.debug("%s", res)
req_dict = {
SERVER_VARIABLES: {
"show_compatibility_56": {ONE_OF: ("0",)},
}
}
req_check = RequirementChecker(req_dict)
results = req_check.check_requirements(self.server)
logging.warning("check_requirements result %s", results)
self.assertFalse(results['pass'], "Check was expected to fail.")
self.assertTupleEqual(
results[SERVER_VARIABLES]["show_compatibility_56"],
(False, '0', '1'),
"show_compatibility_56 is not correct in result."
)
self.server.exec_query("SET GLOBAL show_compatibility_56=0")
def test_requirement_checker_invalid_version_format(self):
""" Test requirement_checker fail due to values found.
"""
req_dict = {
SERVER_VERSION: "x.y.z"
}
req_check = RequirementChecker(req_dict, self.server)
# expect GadgetError: Query failed: Unknown system variable
with self.assertRaises(GadgetError) as test_raises:
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
exception = test_raises.exception
logging.debug("%s", dir(exception))
self.assertTrue("does not have a valid format" in exception.errmsg,
"The exception message was not the expected")
def test_requirement_checker_unknown_system_variable(self):
""" Test requirement_checker fail due to Unknown system variable.
"""
req_dict = {
SERVER_VARIABLES: {
"invalid_var": {ONE_OF: ("no_exist")},
}
}
req_check = RequirementChecker(req_dict, server=self.server)
results = req_check.check_requirements()
self.assertFalse(results["pass"])
def test_requirement_checker_no_server(self):
""" Test requirement_checker no server has been set to check.
"""
req_dict = {
SERVER_VARIABLES: {
"invalid_var": {ONE_OF: ("no_exist",)},
}
}
req_check = RequirementChecker(req_dict)
# expect GadgetDBError: Query failed: Unknown system variable
with self.assertRaises(GadgetError) as test_raises:
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
exception = test_raises.exception
logging.debug("%s", dir(exception))
self.assertTrue("no server has been set to check" in exception.errmsg,
"The exception message was not the expected")
def test_requirement_checker_user_privileges(self):
"""Test requirement_checker user privileges
"""
self.server.exec_query("drop user IF EXISTS 'john_doe'@'localhost'")
req_dict = {
SERVER_VARIABLES: {
"sql_log_bin": {ONE_OF: ("1",)},
},
SERVER_VERSION: "5.7.10",
USER_PRIVILEGES: {
"john_doe@localhost": {"SUPER", "REPLICATION SLAVE", "INSERT",
"UPDATE", "DELETE"}
}
}
req_check = RequirementChecker(req_dict, server=self.server)
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
self.assertFalse(results['pass'], "Check was expected to fail.")
self.assertEqual(results[USER_PRIVILEGES]["john_doe@localhost"],
['NO EXISTS!'],
"missing privileges value is not correct in result.")
self.server.exec_query("create user 'john_doe'@'localhost'")
self.server.exec_query("grant SELECT,INSERT,UPDATE on *.* to "
"'john_doe'@'localhost'")
self.server.exec_query("grant REPLICATION SLAVE on *.* to"
" 'john_doe'@'localhost'")
results = req_check.check_requirements()
logging.debug("check_requirements result %s", results)
self.assertEqual(results[USER_PRIVILEGES]["john_doe@localhost"],
"DELETE and SUPER",
"sql_log_bin value is not correct in result.")
self.assertFalse(results['pass'], "Check was expected to fail.")
self.server.exec_query("drop user IF EXISTS 'john_doe'@'localhost'")
def test_check_unique_id(self):
"""Tests check_unique_id method"""
# Test duplicated server_id
mock_server = get_mock_server(self.server)
server_values = {"peers": [mock_server]}
req_check = RequirementChecker()
results = req_check.check_unique_id(server_values, mock_server)
self.assertFalse(results["pass"])
self.assertEqual(results["duplicate"], mock_server)
# Test invalid server_id = 0
frozen_variables = {"server_id": "0"}
mock_server_idz = get_mock_server(self.server,
variables=frozen_variables)
results = req_check.check_unique_id(server_values, mock_server_idz)
self.assertFalse(results["pass"])
# Servers with different server_id
frozen_variables = {"server_id": "777"}
mock_server2 = get_mock_server(self.server,
variables=frozen_variables)
server_values = {"peers": [mock_server, mock_server2]}
results = req_check.check_unique_id(server_values, self.server)
self.assertTrue(results["pass"])
def test_check_user_privileges(self):
"""Tests check_user_privileges method.
"""
req_check = RequirementChecker()
self.server.exec_query("Drop USER if exists 'check_user_privs'@'%'")
self.server.exec_query("CREATE USER 'check_user_privs'@'%'")
user_str = "check_user_privs@%"
priv_values = {user_str: ["NO EXISTING GRANT"]}
results = req_check.check_user_privileges(priv_values, self.server)
self.assertFalse(results["pass"])
self.server.exec_query("Drop USER if exists 'check_user_privs'@'%'")
class TestGRAdmin(GadgetsTestCase):
"""This class tests the methods in mysql_gadgets.command.gr_admin
"""
@property
def num_servers_required(self):
"""Property defining the number of servers required by the test.
"""
return 1
def setUp(self):
""" Setup server connection
"""
self.server_cnx = {'conn_info': self.options[SERVER_CNX_OPT][0]}
self.server = Server(self.server_cnx)
self.server.connect()
skip_if_not_GR_approved(self.server)
if self.server.select_variable(HAVE_SSL) != 'YES':
raise unittest.SkipTest("Provided server must have_ssl == 'YES'.")
# User without Create User privileges
self.server.exec_query("drop user if exists 'nop_user'@'localhost'")
self.server.exec_query("create user nop_user@'localhost'")
server_conn2 = self.server_cnx.copy()
server_conn2["conn_info"] = ("nop_user@localhost:{0}"
"".format(self.server.port))
self.server2 = Server(self.server_cnx)
self.server2.connect()
columns = [MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE]
qry_key = "SELECT {0} FROM {1}".format(", ".join(columns),
REP_GROUP_MEMBERS_TABLE)
qry_key2 = ("SELECT GROUP_NAME FROM {0} where "
"CHANNEL_NAME = 'group_replication_applier'"
"".format(REP_CONN_STATUS_TABLE))
frozen_queries = {
qry_key: [[], []],
qry_key2: [],
}
self.mock_no_member = get_mock_server(self.server,
queries=frozen_queries)
def tearDown(self):
""" Disconnect base server (for all tests).
"""
# reconnect the server.
self.server.connect()
leave(self.server)
# reconnect the server.
self.server.connect()
self.server.exec_query("drop user if exists 'rpl_user'")
self.server.exec_query("drop user if exists 'nop_user'@'localhost'")
self.server.exec_query("drop user if exists 'new_rpl_user'")
self.server.disconnect()
self.server2.disconnect()
def test_bootstrap(self):
"""Tests start method
"""
# Fill the options
options = {
"group_name": None,
"replication_user": None,
"rep_user_passwd": "passwd",
"verbose": True
}
# test start
if is_member_of_group(self.server):
leave(self.server, **options)
# reconnect the server.
self.server.connect()
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
# health
health(self.server, **options)
# reconnect the server.
self.server.connect()
# Bootstrap active server
# Trying to start a group with a server that is already a member
# expect GadgetError: Query failed: is already a member of a GR group.
with self.assertRaises(GadgetError) as test_raises:
start(self.server, **options)
exception = test_raises.exception
self.assertTrue(
"is already a member" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
leave(self.server, **options)
# reconnect the server.
self.server.connect()
group_name = "b7286041-3016-11e6-ba52-507b9d87510a"
# start using not defaults
options = {
"group_name": "b7286041-3016-11e6-ba52-507b9d87510a",
"gr_host": "{0}:".format(self.server.host),
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": True,
"dry_run": False,
}
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
self.assertTrue(is_member_of_group(self.server, group_name))
self.assertFalse(is_member_of_group(self.server, "group_name"))
if is_member_of_group(self.server):
leave(self.server, **options)
# reconnect the server.
self.server.connect()
def test_bootstrap_noselect_priv(self):
"""Tests commands without Select privilege.
"""
self.server.exec_query("drop user if exists 'not_select'@'%'")
change_user_privileges(self.server,
"not_select",
"%",
user_passwd="pass",
create_user=True)
server1 = Server(
{"conn_info": "not_select@localhost:{0}".format(self.server.port)})
server1.passwd = "pass"
server1.connect()
# Fill the options
options = {
"group_name": "b7286041-3016-11e6-ba52-507b9d87510a",
"gr_host": ":{0}".format(self.server.port),
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": True,
"dry_run": False,
}
# expect GadgetError: Query failed: not have enough privileges to
# verify server
with self.assertRaises(GadgetError) as test_raises:
leave(server1, **options)
exception = test_raises.exception
self.assertTrue(
"not have enough privileges to verify server" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
server1.connect()
# expect GadgetError: Query failed: not have enough privileges to
# verify server
with self.assertRaises(GadgetError) as test_raises:
start(server1, **options)
exception = test_raises.exception
self.assertTrue(
"The operation could not continue due to" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
server1.connect()
# expect GadgetError: Query failed: not have enough privileges to
# verify server
with self.assertRaises(GadgetError) as test_raises:
health(server1, **options)
exception = test_raises.exception
self.assertTrue(
"not have enough privileges to verify server" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
server1.connect()
# expect GadgetError: Query failed: not have enough privileges to
# verify server
with self.assertRaises(GadgetError) as test_raises:
leave(server1, **options)
exception = test_raises.exception
self.assertTrue(
"not have enough privileges to verify server" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
def test_re_bootstrap(self):
"""Tests start method over actively replicating server
"""
# Fill the options
options = {
"group_name": None,
"replication_user": None,
"rep_user_passwd": "passwd",
"gr_host": "",
}
# test start
if is_member_of_group(self.server):
leave(self.server, **options)
# reconnect the server.
self.server.connect()
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
# test health
health(self.server, **options)
# reconnect the server.
self.server.connect()
# Trying to start a group with a server that is already a member
# expect GadgetError: Query failed: is already a member of a GR group.
with self.assertRaises(GadgetError) as test_raises:
start(self.server, **options)
exception = test_raises.exception
self.assertTrue(
"is already a member" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
# Bootstrap with dry-run
options["dry_run"] = True
# Trying to start a group with a server that is already a member
# expect GadgetError: Query failed: is already a member of a GR group.
with self.assertRaises(GadgetError) as test_raises:
start(self.server, **options)
exception = test_raises.exception
self.assertTrue(
"is already a member" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
self.assertTrue(leave(self.server, **options))
# reconnect the server.
self.server.connect()
def test_join(self):
"""Tests join method
"""
# Fill the options
options = {
"group_name": None,
"gr_host": "{0}:{1}".format(self.server.host, self.server.port),
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": False,
"dry_run": False,
}
# join needs start
if is_member_of_group(self.server):
leave(self.server, **options)
# reconnect the server.
self.server.connect()
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
# health
health(self.server, **options)
# reconnect the server.
self.server.connect()
leave(self.server, **options)
# reconnect the server.
self.server.connect()
member_state_qry = ("SELECT MEMBER_STATE FROM {0} as m JOIN {1} "
"as s on m.MEMBER_ID = s.MEMBER_ID"
"".format(REP_GROUP_MEMBERS_TABLE,
REP_MEMBER_STATS_TABLE))
frozen_queries = {
member_state_qry: [
[
'ONLINE',
],
],
}
mock_server = get_mock_server(self.server, queries=frozen_queries)
# Join with defaults ("dry_run": True) and required password
options = {
"dry_run": True,
"replication_user": "******",
"rep_user_passwd": "passwd",
}
join(self.server, mock_server, **options)
# reconnect the server.
self.server.connect()
# Join with no defaults ("dry_run": True)
options = {
"group_name": None,
"gr_host": "{0}:{1}".format(self.server.host, self.server.port),
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": False,
"dry_run": True,
}
# leave the group
if is_member_of_group(self.server):
leave(self.server, **options)
self.assertFalse(join(self.server, mock_server, **options))
# reconnect the server.
self.server.connect()
self.assertFalse(leave(self.server, **options))
# reconnect the server.
self.server.connect()
def test_re_join(self):
"""Tests join method over actively replicating server
"""
# Fill the options
options = {
"group_name": None,
"gr_host": self.server.host,
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": False,
"dry_run": False,
}
# test start
leave(self.server, **options)
# reconnect the server.
self.server.connect()
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
# test health
health(self.server, **options)
# reconnect the server.
self.server.connect()
options["dry_run"] = True
# test join
mock_server = get_mock_server(self.server)
# Trying to add server to a group while is already a member of a group
# expect GadgetError: Query failed: is already a member of a GR group.
with self.assertRaises(GadgetError) as test_raises:
join(self.server, mock_server, **options)
exception = test_raises.exception
self.assertTrue(
"is already a member" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
self.assertTrue(leave(self.server, **options))
# reconnect the server.
self.server.connect()
def test_health_with_not_a_member(self):
"""Test the commands that requires a GR member or None value for a
required server connection information.
"""
# Fill the options
options = {}
# expect GadgetError: Query failed: no server was given
with self.assertRaises(GadgetError) as test_raises:
health(None, **options)
exception = test_raises.exception
self.assertTrue(
"No server was given" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# expect GadgetError: Query failed: not a member of a Group
with self.assertRaises(GadgetError) as test_raises:
health(self.mock_no_member, **options)
exception = test_raises.exception
self.assertTrue(
"not a member of a GR group" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Fill the options
options = {
"group_name": None,
"replication_user": None,
"rep_user_passwd": "passwd",
}
# expect GadgetError: Query failed: No server was given
with self.assertRaises(GadgetError) as test_raises:
start(None, **options)
exception = test_raises.exception
self.assertTrue(
"No server was given" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
def test_join_with_none_values(self):
"""Test the commands that requires a GR member or None value for a
required server connection information.
"""
# Fill the options
options = {
"group_name": None,
"replication_user": None,
"rep_user_passwd": "passwd",
"gr_host": None,
}
# expect GadgetError: Query failed: No peer server was given
with self.assertRaises(GadgetError) as test_raises:
join(None, None, **options)
exception = test_raises.exception
self.assertTrue(
"No server was given" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
# expect GadgetError: Query failed: No peer server was given
with self.assertRaises(GadgetError) as test_raises:
join(self.server, None, **options)
exception = test_raises.exception
self.assertTrue(
"No peer server provided" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
# expect GadgetError: Query failed: not a member of a Group
with self.assertRaises(GadgetError) as test_raises:
join(self.server, self.mock_no_member, **options)
exception = test_raises.exception
self.assertTrue(
"not a member of a GR group" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
def test_leave_with_none_values(self):
"""Test the commands that requires a GR member or None value for a
required server connection information.
"""
# Fill the options
options = {}
# expect GadgetError: Query failed: not a member of a Group
with self.assertRaises(GadgetError) as test_raises:
leave(None, **options)
exception = test_raises.exception
self.assertTrue(
"No server was given" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
# expect GadgetError: Query failed: not a member of a Group
with self.assertRaises(GadgetError) as test_raises:
leave(self.mock_no_member, **options)
exception = test_raises.exception
self.assertTrue(
"not a member of a Group" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
def test_resolve_gr_local_address(self):
"""Tests resolve_gr_local_address method.
"""
# Use server host and port
gr_host = ""
server_host = self.server.host
server_port = get_free_random_port()
self.assertEqual(
resolve_gr_local_address(gr_host, server_host, server_port),
(self.server.host, repr(server_port + 10000)))
# Use host from gr_host
gr_host = "host4321"
server_port = get_free_random_port()
self.assertEqual(
resolve_gr_local_address(gr_host, server_host, server_port),
("host4321", repr(server_port + 10000)))
# Use host from gr_host
gr_host = "127.0.0.1:"
server_port = get_free_random_port()
self.assertEqual(
resolve_gr_local_address(gr_host, server_host, server_port),
("127.0.0.1", repr(server_port + 10000)))
# Use given IPv6 host and port from gr_host
gr_host = "[1:2:3:4:5:6:7:8]:1234"
self.assertEqual(
resolve_gr_local_address(gr_host, server_host, server_port),
("1:2:3:4:5:6:7:8", "1234"))
# Use given IPv6 host and port from gr_host
server_port = get_free_random_port()
gr_host = "E3D7::51F4:9BC8:{0}".format(server_port)
self.assertEqual(
resolve_gr_local_address(gr_host, server_host, server_port),
("E3D7::51F4:9BC8", repr(server_port)))
# Use given port from gr_host
gr_host = "1234"
server_port = get_free_random_port()
self.assertEqual(
resolve_gr_local_address(gr_host, server_host, server_port),
(self.server.host, "1234"))
# Given port on gr_host is out of range, generate a new one instead
gr_host = "65536"
self.assertIsInstance(
int(
resolve_gr_local_address(gr_host, server_host,
server_port)[1]), int)
# Use given port from gr_host
gr_host = ":1234"
self.assertEqual(
resolve_gr_local_address(gr_host, server_host, server_port),
(self.server.host, "1234"))
# Use given IPv6 host from gr_host and generate random port.
gr_host = "E3D7::51F4:9BC8"
self.assertEqual(
resolve_gr_local_address(gr_host, server_host, server_port)[0],
"E3D7::51F4:9BC8")
self.assertIsInstance(
int(
resolve_gr_local_address(gr_host, server_host,
server_port)[1]), int)
class Test(GadgetsTestCase):
"""Class to test mysql_gadgets.common.user module.
"""
@property
def num_servers_required(self):
"""Property defining the number of servers required by the test.
"""
return 1
def setUp(self):
""" Setup server connection
"""
self.server_cnx = {'conn_info': self.options[SERVER_CNX_OPT][0]}
self.server = Server(self.server_cnx)
self.server.connect()
def tearDown(self):
""" Disconnect base server (for all tests).
"""
self.server.disconnect()
def test_create_user(self):
""" Tests User.create_user methods"""
self.server.exec_query("Drop USER if exists 'joe'@'users'")
self.server.exec_query("Drop USER if exists 'Jonh_CAPITALS'@'{0}'"
"".format(self.server.host))
user_root = User(self.server, "{0}@{1}".format(self.server.user,
self.server.host))
user_name = 'Jonh_CAPITALS'
user_root.create(new_user="******".format(user_name,
self.server.host),
passwd="his_pass", ssl=True, disable_binlog=True)
user_root.exists("{0}@{1}".format(user_name, self.server.host))
user_obj2 = User(self.server, "{0}@{1}".format('joe', 'users'))
user_root.drop(new_user="******".format(user_name, self.server.host))
user_obj2.drop()
self.assertFalse((self.server.user_host_exists('Jonh_CAPITALS',
self.server.host)))
def test_check_privileges(self):
""" Tests check_privileges"""
self.server.exec_query("Drop USER if exists 'check_privs'@'%'")
self.server.exec_query("CREATE USER 'check_privs'@'%'")
operation = "can select?"
privileges = ['SELECT']
description = "checking privs"
server1 = Server({"conn_info": "check_privs@localhost:{0}"
"".format(self.server.port)})
server1.connect()
# expect GadgetError: Query failed: Unknown system variable
with self.assertRaises(GadgetError) as test_raises:
check_privileges(server1, operation, privileges, description)
exception = test_raises.exception
self.assertTrue("not have sufficient privileges" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
self.server.exec_query("Drop USER if exists 'check_privs'@'%'")
def test_check_missing_privileges(self):
""" Tests the User's check_missing_privileges method"""
self.server.exec_query("Drop USER if exists 'Jonh_CAPITALS'@'{0}'"
"".format(self.server.host))
user_name = 'Jonh_CAPITALS'
user_obj = User(self.server, "{0}@{1}".format(user_name,
self.server.host),
verbosity=1)
self.assertFalse(user_obj.exists())
user_obj.create(disable_binlog=True, ssl=True)
self.assertTrue(user_obj.exists())
self.assertListEqual(
user_obj.check_missing_privileges(
["REPLICATION SLAVE", "CREATE USER"], as_string=False),
["CREATE USER", "REPLICATION SLAVE"])
self.assertEqual(
user_obj.check_missing_privileges(["REPLICATION SLAVE",
"CREATE USER"]),
"CREATE USER and REPLICATION SLAVE")
change_user_privileges(self.server, user_name, self.server.host,
grant_list=["REPLICATION SLAVE", "CREATE USER"],
disable_binlog=True)
self.assertListEqual(user_obj.get_grants(refresh=True),
[("GRANT REPLICATION SLAVE, CREATE USER ON *.* "
"TO 'Jonh_CAPITALS'@'localhost'",)])
self.assertListEqual(
user_obj.check_missing_privileges(["REPLICATION SLAVE",
"CREATE USER"],
as_string=False), [])
self.assertEqual(
user_obj.check_missing_privileges(["REPLICATION SLAVE",
"CREATE USER"]), "")
user_obj.drop()
user_name = 'some_user'
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
change_user_privileges(self.server, user_name, self.server.host,
user_passwd="some pass",
grant_list=["REPLICATION SLAVE", "CREATE USER"],
disable_binlog=True, create_user=True)
change_user_privileges(self.server, user_name, self.server.host,
revoke_list=["REPLICATION SLAVE"],
disable_binlog=True)
# expect GadgetError: Query failed: Unknown system variable
with self.assertRaises(GadgetError) as test_raises:
user_obj.drop(user_name)
exception = test_raises.exception
self.assertTrue("Cannot parse user@host" in exception.errmsg,
"The exception message was not the expected")
user_obj.drop("{0}@{1}".format(user_name, self.server.host))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
def test_get_grants(self):
""" Tests get_grants method"""
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', '%'))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', self.server.host))
user_obj2 = User(self.server, "{0}@{1}".format('jose', '%'),
verbosity=True)
self.assertListEqual(user_obj2.get_grants(globals_privs=True), [])
user_obj2.create()
# Test user has none grants
self.assertListEqual(user_obj2.get_grants(globals_privs=True),
[("GRANT USAGE ON *.* TO 'jose'@'%'",)])
self.assertDictEqual(user_obj2.get_grants(as_dict=True),
{'*': {'*': {'USAGE'}}})
# Test get global privileges
self.server.exec_query("GRANT PROXY ON '{0}'@'{1}' TO '{0}'@'%'"
"".format('jose', self.server.host))
self.server.exec_query("GRANT UPDATE ON mysql.* TO '{0}'@'{1}'"
"".format('jose', '%'))
exp_list_res = [("GRANT USAGE ON *.* TO 'jose'@'%'",),
("GRANT UPDATE ON `mysql`.* TO 'jose'@'%'",),
("GRANT PROXY ON 'jose'@'{0}' TO 'jose'@'%'"
"".format(self.server.host),)]
self.assertListEqual(user_obj2.get_grants(globals_privs=True,
refresh=True),
exp_list_res)
self.assertDictEqual(user_obj2.get_grants(as_dict=True, refresh=True),
{'*': {'*': {'USAGE'}},
'`mysql`': {'*': {'UPDATE'}}})
user_obj2.drop()
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', '%'))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format('jose', self.server.host))
def test_user_has_privilege(self):
""" Tests USER.has_privilege method"""
user_name = 'Jonh_Update'
user_root = User(self.server, "{0}@{1}".format(self.server.user,
self.server.host))
self.server.exec_query("Drop USER if exists '{0}'@'{1}'"
"".format(user_name, self.server.host))
db_ = "mysql"
obj = "user"
access = "UPDATE"
skip_grant = False
# Test object level privilege with user with global * privilege
self.assertTrue(user_root.has_privilege(db_, obj, access, skip_grant))
# create new user to test missing privileges.
user_update = User(self.server, "{0}@{1}".format(user_name,
self.server.host))
user_update.create()
# Test privileges disabled
self.server.grants_enabled = False
skip_grant = True
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
self.server.grants_enabled = True
skip_grant = True
# Test missing privilege
self.assertFalse(user_update.has_privilege(db_, obj, access,
skip_grant))
access = "USAGE"
# Test default privilege USAGE at db level
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
self.server.exec_query("GRANT UPDATE ON mysql.user TO '{0}'@'{1}'"
"".format(user_name, self.server.host))
access = "UPDATE"
# Test privilege at object level
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
# Test privilege at db level
self.server.exec_query("GRANT UPDATE ON mysql.* TO '{0}'@'{1}'"
"".format(user_name, self.server.host))
self.assertTrue(user_update.has_privilege(db_, obj, access,
skip_grant))
user_update.drop()
def test_parse_grant_statement(self):
""" Tests parse_grant_statement Method.
"""
# Test function
parsed_statement = User.parse_grant_statement(
"GRANT ALTER ROUTINE, EXECUTE ON FUNCTION `util_test`.`f1` TO "
"'priv_test_user2'@'%' WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'EXECUTE', 'ALTER ROUTINE']),
None, '`util_test`', '`f1`',
"'priv_test_user2'@'%'"))
# Test procedure
parsed_statement = User.parse_grant_statement(
"GRANT ALTER ROUTINE ON PROCEDURE `util_test`.`p1` TO "
"'priv_test_user2'@'%' IDENTIFIED BY "
"PASSWORD '*123DD712CFDED6313E0DDD2A6E0D62F12E580A6F' "
"WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'ALTER ROUTINE']), None,
'`util_test`', '`p1`', "'priv_test_user2'@'%'"))
# Test with quoted objects
parsed_statement = User.parse_grant_statement(
"GRANT CREATE VIEW ON `db``:db`.```t``.``export_2` TO "
"'priv_test_user'@'%'")
self.assertEqual(parsed_statement,
(set(['CREATE VIEW']), None, '`db``:db`.```t``',
'``export_2`', "'priv_test_user'@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT CREATE VIEW ON `db``:db`.```t``.* TO "
"'priv_test_user'@'%'")
self.assertEqual(parsed_statement,
(set(['CREATE VIEW']), None, '`db``:db`.```t``', '*',
"'priv_test_user'@'%'"))
# Test multiple grants with password and grant option
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON `mysql`.* TO 'user2'@'%' IDENTIFIED BY "
"PASSWORD '*123DD712CFDED6313E0DDD2A6E0D62F12E580A6F' "
"REQUIRE SSL WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'UPDATE', 'SELECT']), None,
'`mysql`', '*', "'user2'@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON `mysql`.* TO 'user2'@'%' IDENTIFIED BY "
"PASSWORD REQUIRE SSL WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'UPDATE', 'SELECT']), None,
'`mysql`', '*', "'user2'@'%'"))
# Test proxy privileges
parsed_statement = User.parse_grant_statement(
"GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'PROXY']), "''@''", None, None,
"'root'@'localhost'"))
parsed_statement = User.parse_grant_statement(
"GRANT PROXY ON 'root'@'%' TO 'root'@'localhost' WITH GRANT "
"OPTION")
self.assertEqual(parsed_statement,
(set(['GRANT OPTION', 'PROXY']), "'root'@'%'", None,
None, "'root'@'localhost'"))
# Test parse grant with ansi quotes
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'",
sql_mode="ANSI_QUOTES")
self.assertEqual(parsed_statement[4], ("user2@'%'"))
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'")
self.assertEqual(parsed_statement[2], '`mysql`')
parsed_statement = User.parse_grant_statement(
"GRANT UPDATE, SELECT ON mysql.user TO user2@'%'")
self.assertEqual(parsed_statement[3], '`user`')
self.assertRaises(GadgetError, User.parse_grant_statement,
"GRANT PROXY 'root'@'%' TO 'root'@'localhost' WITH "
"GRANT OPTION")
class TestGRAdmin(GadgetsTestCase):
"""This class tests the methods in mysql_gadgets.command.gr_admin
"""
@property
def num_servers_required(self):
"""Property defining the number of servers required by the test.
"""
return 1
def setUp(self):
""" Setup server connection
"""
self.server_cnx = {'conn_info': self.options[SERVER_CNX_OPT][0]}
self.server = Server(self.server_cnx)
self.server.connect()
skip_if_not_GR_approved(self.server)
if self.server.select_variable(HAVE_SSL) != 'YES':
raise unittest.SkipTest("Provided server must have_ssl == 'YES'.")
# User without Create User privileges
self.server.exec_query("drop user if exists 'nop_user'@'localhost'")
self.server.exec_query("create user nop_user@'localhost'")
server_conn2 = self.server_cnx.copy()
server_conn2["conn_info"] = ("nop_user@localhost:{0}"
"".format(self.server.port))
self.server2 = Server(self.server_cnx)
self.server2.connect()
columns = [MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE]
qry_key = "SELECT {0} FROM {1}".format(", ".join(columns),
REP_GROUP_MEMBERS_TABLE)
qry_key2 = ("SELECT GROUP_NAME FROM {0} where "
"CHANNEL_NAME = 'group_replication_applier'"
"".format(REP_CONN_STATUS_TABLE))
frozen_queries = {
qry_key: [[], []],
qry_key2: [],
}
self.mock_no_member = get_mock_server(self.server,
queries=frozen_queries)
def tearDown(self):
""" Disconnect base server (for all tests).
"""
# reconnect the server.
self.server.connect()
leave(self.server)
# reconnect the server.
self.server.connect()
self.server.exec_query("drop user if exists 'rpl_user'")
self.server.exec_query("drop user if exists 'nop_user'@'localhost'")
self.server.exec_query("drop user if exists 'new_rpl_user'")
self.server.disconnect()
self.server2.disconnect()
def test_bootstrap(self):
"""Tests start method
"""
# Fill the options
options = {
"group_name": None,
"replication_user": None,
"rep_user_passwd": "passwd",
"verbose": True
}
# test start
if is_member_of_group(self.server):
leave(self.server, **options)
# reconnect the server.
self.server.connect()
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
# health
health(self.server, **options)
# reconnect the server.
self.server.connect()
# Bootstrap active server
# Trying to start a group with a server that is already a member
# expect GadgetError: Query failed: is already a member of a GR group.
with self.assertRaises(GadgetError) as test_raises:
start(self.server, **options)
exception = test_raises.exception
self.assertTrue("is already a member" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
leave(self.server, **options)
# reconnect the server.
self.server.connect()
group_name = "b7286041-3016-11e6-ba52-507b9d87510a"
# start using not defaults
options = {
"group_name": "b7286041-3016-11e6-ba52-507b9d87510a",
"gr_host": "{0}:".format(self.server.host),
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": True,
"dry_run": False,
}
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
self.assertTrue(is_member_of_group(self.server, group_name))
self.assertFalse(is_member_of_group(self.server, "group_name"))
if is_member_of_group(self.server):
leave(self.server, **options)
# reconnect the server.
self.server.connect()
def test_bootstrap_noselect_priv(self):
"""Tests commands without Select privilege.
"""
self.server.exec_query("drop user if exists 'not_select'@'%'")
change_user_privileges(self.server, "not_select", "%",
user_passwd="pass", create_user=True)
server1 = Server({
"conn_info": "not_select@localhost:{0}".format(self.server.port)})
server1.passwd = "pass"
server1.connect()
# Fill the options
options = {
"group_name": "b7286041-3016-11e6-ba52-507b9d87510a",
"gr_host": ":{0}".format(self.server.port),
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": True,
"dry_run": False,
}
# expect GadgetError: Query failed: not have enough privileges to
# verify server
with self.assertRaises(GadgetError) as test_raises:
leave(server1, **options)
exception = test_raises.exception
self.assertTrue("not have enough privileges to verify server" in
exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
server1.connect()
# expect GadgetError: Query failed: not have enough privileges to
# verify server
with self.assertRaises(GadgetError) as test_raises:
start(server1, **options)
exception = test_raises.exception
self.assertTrue("The operation could not continue due to" in
exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
server1.connect()
# expect GadgetError: Query failed: not have enough privileges to
# verify server
with self.assertRaises(GadgetError) as test_raises:
health(server1, **options)
exception = test_raises.exception
self.assertTrue("not have enough privileges to verify server" in
exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
server1.connect()
# expect GadgetError: Query failed: not have enough privileges to
# verify server
with self.assertRaises(GadgetError) as test_raises:
leave(server1, **options)
exception = test_raises.exception
self.assertTrue("not have enough privileges to verify server" in
exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
def test_re_bootstrap(self):
"""Tests start method over actively replicating server
"""
# Fill the options
options = {
"group_name": None,
"replication_user": None,
"rep_user_passwd": "passwd",
"gr_host": "",
}
# test start
if is_member_of_group(self.server):
leave(self.server, **options)
# reconnect the server.
self.server.connect()
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
# test health
health(self.server, **options)
# reconnect the server.
self.server.connect()
# Trying to start a group with a server that is already a member
# expect GadgetError: Query failed: is already a member of a GR group.
with self.assertRaises(GadgetError) as test_raises:
start(self.server, **options)
exception = test_raises.exception
self.assertTrue("is already a member" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
# Bootstrap with dry-run
options["dry_run"] = True
# Trying to start a group with a server that is already a member
# expect GadgetError: Query failed: is already a member of a GR group.
with self.assertRaises(GadgetError) as test_raises:
start(self.server, **options)
exception = test_raises.exception
self.assertTrue("is already a member" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
self.assertTrue(leave(self.server, **options))
# reconnect the server.
self.server.connect()
def test_join(self):
"""Tests join method
"""
# Fill the options
options = {
"group_name": None,
"gr_host": "{0}:{1}".format(self.server.host, self.server.port),
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": False,
"dry_run": False,
}
# join needs start
if is_member_of_group(self.server):
leave(self.server, **options)
# reconnect the server.
self.server.connect()
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
# health
health(self.server, **options)
# reconnect the server.
self.server.connect()
leave(self.server, **options)
# reconnect the server.
self.server.connect()
member_state_qry = ("SELECT MEMBER_STATE FROM {0} as m JOIN {1} "
"as s on m.MEMBER_ID = s.MEMBER_ID"
"".format(REP_GROUP_MEMBERS_TABLE,
REP_MEMBER_STATS_TABLE))
frozen_queries = {
member_state_qry: [['ONLINE', ], ],
}
mock_server = get_mock_server(self.server, queries=frozen_queries)
# Join with defaults ("dry_run": True) and required password
options = {
"dry_run": True,
"replication_user": "******",
"rep_user_passwd": "passwd",
}
join(self.server, mock_server, **options)
# reconnect the server.
self.server.connect()
# Join with no defaults ("dry_run": True)
options = {
"group_name": None,
"gr_host": "{0}:{1}".format(self.server.host, self.server.port),
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": False,
"dry_run": True,
}
# leave the group
if is_member_of_group(self.server):
leave(self.server, **options)
self.assertFalse(join(self.server, mock_server, **options))
# reconnect the server.
self.server.connect()
self.assertFalse(leave(self.server, **options))
# reconnect the server.
self.server.connect()
def test_re_join(self):
"""Tests join method over actively replicating server
"""
# Fill the options
options = {
"group_name": None,
"gr_host": self.server.host,
"replication_user": "******",
"rep_user_passwd": "passwd",
"verbose": False,
"dry_run": False,
}
# test start
leave(self.server, **options)
# reconnect the server.
self.server.connect()
self.assertTrue(start(self.server, **options))
# reconnect the server.
self.server.connect()
# test health
health(self.server, **options)
# reconnect the server.
self.server.connect()
options["dry_run"] = True
# test join
mock_server = get_mock_server(self.server)
# Trying to add server to a group while is already a member of a group
# expect GadgetError: Query failed: is already a member of a GR group.
with self.assertRaises(GadgetError) as test_raises:
join(self.server, mock_server, **options)
exception = test_raises.exception
self.assertTrue("is already a member" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
self.assertTrue(leave(self.server, **options))
# reconnect the server.
self.server.connect()
def test_health_with_not_a_member(self):
"""Test the commands that requires a GR member or None value for a
required server connection information.
"""
# Fill the options
options = {}
# expect GadgetError: Query failed: no server was given
with self.assertRaises(GadgetError) as test_raises:
health(None, **options)
exception = test_raises.exception
self.assertTrue("No server was given" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# expect GadgetError: Query failed: not a member of a Group
with self.assertRaises(GadgetError) as test_raises:
health(self.mock_no_member, **options)
exception = test_raises.exception
self.assertTrue("not a member of a GR group" in exception.errmsg,
"The exception message was not the expected. {0}"
"".format(exception.errmsg))
# Fill the options
options = {
"group_name": None,
"replication_user": None,
"rep_user_passwd": "passwd",
}
# expect GadgetError: Query failed: No server was given
with self.assertRaises(GadgetError) as test_raises:
start(None, **options)
exception = test_raises.exception
self.assertTrue("No server was given" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
def test_join_with_none_values(self):
"""Test the commands that requires a GR member or None value for a
required server connection information.
"""
# Fill the options
options = {
"group_name": None,
"replication_user": None,
"rep_user_passwd": "passwd",
"gr_host": None,
}
# expect GadgetError: Query failed: No peer server was given
with self.assertRaises(GadgetError) as test_raises:
join(None, None, **options)
exception = test_raises.exception
self.assertTrue("No server was given" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
# expect GadgetError: Query failed: No peer server was given
with self.assertRaises(GadgetError) as test_raises:
join(self.server, None, **options)
exception = test_raises.exception
self.assertTrue("No peer server provided" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
# expect GadgetError: Query failed: not a member of a Group
with self.assertRaises(GadgetError) as test_raises:
join(self.server, self.mock_no_member, **options)
exception = test_raises.exception
self.assertTrue("not a member of a GR group" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
# reconnect the server.
self.server.connect()
def test_leave_with_none_values(self):
"""Test the commands that requires a GR member or None value for a
required server connection information.
"""
# Fill the options
options = {}
# expect GadgetError: Query failed: not a member of a Group
with self.assertRaises(GadgetError) as test_raises:
leave(None, **options)
exception = test_raises.exception
self.assertTrue("No server was given" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
# expect GadgetError: Query failed: not a member of a Group
with self.assertRaises(GadgetError) as test_raises:
leave(self.mock_no_member, **options)
exception = test_raises.exception
self.assertTrue("not a member of a Group" in exception.errmsg,
"The exception message was not the expected: {0}"
"".format(exception.errmsg))
def test_resolve_gr_local_address(self):
"""Tests resolve_gr_local_address method.
"""
# Use server host and port
gr_host = ""
server_host = self.server.host
server_port = get_free_random_port()
self.assertEqual(resolve_gr_local_address(gr_host, server_host,
server_port),
(self.server.host, repr(server_port + 10000)))
# Use host from gr_host
gr_host = "host4321"
server_port = get_free_random_port()
self.assertEqual(resolve_gr_local_address(gr_host, server_host,
server_port),
("host4321", repr(server_port + 10000)))
# Use host from gr_host
gr_host = "127.0.0.1:"
server_port = get_free_random_port()
self.assertEqual(resolve_gr_local_address(gr_host, server_host,
server_port),
("127.0.0.1", repr(server_port + 10000)))
# Use given IPv6 host and port from gr_host
gr_host = "[1:2:3:4:5:6:7:8]:1234"
self.assertEqual(resolve_gr_local_address(gr_host, server_host,
server_port),
("1:2:3:4:5:6:7:8", "1234"))
# Use given IPv6 host and port from gr_host
server_port = get_free_random_port()
gr_host = "E3D7::51F4:9BC8:{0}".format(server_port)
self.assertEqual(resolve_gr_local_address(gr_host, server_host,
server_port),
("E3D7::51F4:9BC8", repr(server_port)))
# Use given port from gr_host
gr_host = "1234"
server_port = get_free_random_port()
self.assertEqual(resolve_gr_local_address(gr_host, server_host,
server_port),
(self.server.host, "1234"))
# Given port on gr_host is out of range, generate a new one instead
gr_host = "65536"
self.assertIsInstance(
int(resolve_gr_local_address(gr_host, server_host,
server_port)[1]), int)
# Use given port from gr_host
gr_host = ":1234"
self.assertEqual(resolve_gr_local_address(gr_host, server_host,
server_port),
(self.server.host, "1234"))
# Use given IPv6 host from gr_host and generate random port.
gr_host = "E3D7::51F4:9BC8"
self.assertEqual(resolve_gr_local_address(gr_host, server_host,
server_port)[0],
"E3D7::51F4:9BC8")
self.assertIsInstance(
int(resolve_gr_local_address(gr_host, server_host,
server_port)[1]), int)