def test_shard_prune(self):
status = self.proxy.sharding.prune_shard("db2.t2")
self.check_xmlrpc_command_result(status)
status = self.proxy.sharding.lookup_servers("db2.t2", 1, "LOCAL")
info = self.check_xmlrpc_simple(status, {})
shard_uuid = info['server_uuid']
shard_server = fetch_test_server(shard_uuid)
shard_server.connect()
rows = shard_server.exec_stmt("SELECT COUNT(*) FROM db2.t2",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 100)
rows = shard_server.exec_stmt("SELECT MAX(userID2) FROM db2.t2",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 100)
rows = shard_server.exec_stmt("SELECT MIN(userID2) FROM db2.t2",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 1)
status = self.proxy.sharding.lookup_servers("db2.t2", 101, "LOCAL")
info = self.check_xmlrpc_simple(status, {})
shard_uuid = info['server_uuid']
shard_server = fetch_test_server(shard_uuid)
shard_server.connect()
rows = shard_server.exec_stmt("SELECT COUNT(*) FROM db2.t2",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 100)
rows = shard_server.exec_stmt("SELECT MAX(userID2) FROM db2.t2",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 200)
rows = shard_server.exec_stmt("SELECT MIN(userID2) FROM db2.t2",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 101)
def test_shard_server_added_later(self):
self.proxy.sharding.disable_shard("1")
self.proxy.sharding.remove_shard("1")
sleep(3)
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
global_master = fetch_test_server(row['server_uuid'])
global_master.connect()
global_master.exec_stmt("DROP DATABASE IF EXISTS global_db")
global_master.exec_stmt("CREATE DATABASE global_db")
global_master.exec_stmt("CREATE TABLE global_db.global_table"
"(userID INT, name VARCHAR(30))")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(101, 'TEST 1')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(202, 'TEST 2')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(303, 'TEST 3')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(404, 'TEST 4')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(505, 'TEST 5')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(606, 'TEST 6')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(707, 'TEST 7')")
sleep(5)
status = self.proxy.sharding.add_shard(1, "GROUPID2/0", "ENABLED")
self.check_xmlrpc_command_result(status, has_error=True)
sleep(5)
status = self.proxy.sharding.lookup_servers("db1.t1", 1500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(row['server_uuid'])
shard_server.connect()
try:
rows = shard_server.exec_stmt(
"SELECT NAME FROM global_db.global_table",
{"fetch": True})
except _errors.DatabaseError:
raise Exception("Enable Shard failed to enable shard.")
self.assertEqual(len(rows), 7)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
self.assertEqual(rows[2][0], 'TEST 3')
self.assertEqual(rows[3][0], 'TEST 4')
self.assertEqual(rows[4][0], 'TEST 5')
self.assertEqual(rows[5][0], 'TEST 6')
self.assertEqual(rows[6][0], 'TEST 7')
def test_shard_enable(self):
self.proxy.sharding.disable_shard("1")
self.proxy.sharding.disable_shard("2")
sleep(5)
self.proxy.sharding.enable_shard("1")
self.proxy.sharding.enable_shard("2")
sleep(3)
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
global_master = fetch_test_server(row['server_uuid'])
global_master.connect()
global_master.exec_stmt("DROP DATABASE IF EXISTS global_db")
global_master.exec_stmt("CREATE DATABASE global_db")
global_master.exec_stmt("CREATE TABLE global_db.global_table"
"(userID INT, name VARCHAR(30))")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(101, 'TEST 1')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(202, 'TEST 2')")
sleep(3)
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(row['server_uuid'])
shard_server.connect()
try:
rows = shard_server.exec_stmt(
"SELECT NAME FROM global_db.global_table",
{"fetch": True})
except _errors.DatabaseError:
raise Exception("Enable Shard failed to enable shard.")
self.assertEqual(len(rows), 2)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
status = self.proxy.sharding.lookup_servers("db1.t1", 1500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(row['server_uuid'])
shard_server.connect()
try:
rows = shard_server.exec_stmt(
"SELECT NAME FROM global_db.global_table",
{"fetch": True})
except _errors.DatabaseError:
raise Exception("Enable Shard failed to enable shard.")
self.assertEqual(len(rows), 2)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
def test_shard_server_add_disabled(self):
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
shard_server_1 = fetch_test_server(row['server_uuid'])
shard_server_1.connect()
self.proxy.sharding.disable_shard("1")
self.proxy.sharding.remove_shard("1")
sleep(3)
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
global_master = fetch_test_server(row['server_uuid'])
global_master.connect()
global_master.exec_stmt("DROP DATABASE IF EXISTS global_db")
global_master.exec_stmt("CREATE DATABASE global_db")
global_master.exec_stmt("CREATE TABLE global_db.global_table"
"(userID INT, name VARCHAR(30))")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(101, 'TEST 1')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(202, 'TEST 2')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(303, 'TEST 3')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(404, 'TEST 4')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(505, 'TEST 5')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(606, 'TEST 6')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(707, 'TEST 7')")
sleep(5)
status = self.proxy.sharding.add_shard(1, "GROUPID2/0", "DISABLED")
self.check_xmlrpc_command_result(status, has_error=True)
sleep(5)
try:
shard_server_1.exec_stmt("SELECT NAME FROM global_db.global_table",
{"fetch": True})
raise Exception("Adding a disabled shard did not stop replication")
except _errors.DatabaseError:
#The table should not have been created.
pass
def test_MD5_HEX(self):
"""Ensure the STRICT_ALL_TABLES mode catches large inserts into columns.
"""
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for info in self.check_xmlrpc_iter(status):
shard_uuid = info['server_uuid']
shard_server = fetch_test_server(shard_uuid)
shard_server.connect()
shard_server.exec_stmt("CREATE DATABASE SAMPDB")
shard_server.exec_stmt("USE SAMPDB")
shard_server.exec_stmt("SET SESSION sql_mode = STRICT_ALL_TABLES")
shard_server.exec_stmt("CREATE TABLE sample(empno int)")
shard_server.exec_stmt("CREATE TABLE sample1(s VARBINARY(16))")
shard_server.exec_stmt("INSERT INTO sample VALUES(1)")
shard_server.exec_stmt("INSERT INTO sample VALUES(2)")
shard_server.exec_stmt("SELECT HEX(MD5(MAX(empno))) "
"INTO @a FROM sample")
shard_server.exec_stmt("SELECT MD5(MAX(empno)) " "INTO @b FROM sample")
self.assertRaises(_errors.DatabaseError, shard_server.exec_stmt,
"INSERT INTO sample1 VALUES (UNHEX(@a))")
shard_server.exec_stmt("INSERT INTO sample1 VALUES (UNHEX(@b))")
shard_server.exec_stmt("DROP DATABASE SAMPDB")
def test_global_update_propogation(self):
"""Ensure the global updates are passed to all the shards.
"""
#Lookup the global server and run some DDL statements
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
global_master = fetch_test_server(row['server_uuid'])
global_master.connect()
global_master.exec_stmt("DROP DATABASE IF EXISTS global_db")
global_master.exec_stmt("CREATE DATABASE global_db")
global_master.exec_stmt("CREATE TABLE global_db.global_table"
"(userID INT, name VARCHAR(30))")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(101, 'TEST 1')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(202, 'TEST 2')")
#Give some times for replication to update the shards
sleep(3)
#Lookup and verify that the data is updated in the other shards.
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(row['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT NAME FROM global_db.global_table", {"fetch": True})
self.assertEqual(len(rows), 2)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
status = self.proxy.sharding.lookup_servers("db1.t1", 1500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(row['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT NAME FROM global_db.global_table", {"fetch": True})
self.assertEqual(len(rows), 2)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
def test_split_table_not_exists(self):
"""Delete the database in the shard server and verify that the split
does not fail once the database on which the prune is being done
has been removed.
"""
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for info in self.check_xmlrpc_iter(status):
shard_uuid = info['server_uuid']
shard_server = fetch_test_server(shard_uuid)
shard_server.connect()
shard_server.exec_stmt("DROP DATABASE IF EXISTS db1")
status = self.proxy.sharding.split_shard("1", "GROUPID3", "600")
self.check_xmlrpc_command_result(status)
def test_sync_readonly_servers(self):
status = self.proxy.group.lookup_servers("GROUPID3")
for info in self.check_xmlrpc_iter(status):
if info['status'] == MySQLServer.SECONDARY:
slave_uuid = info['server_uuid']
slave_server = fetch_test_server(slave_uuid)
slave_server.connect()
_group_replication.setup_group_replication("GROUPID2", "GROUPID3")
_replication.synchronize_with_read_only(slave_server,
self.shard_server, 3, 5)
_group_replication.stop_group_slave("GROUPID2", "GROUPID3", True)
try:
rows = self.shard_server.exec_stmt("SELECT NAME FROM db1.t1",
{"fetch": True})
except _errors.DatabaseError:
raise Exception("Enable Shard failed to enable shard.")
self.assertEqual(len(rows), 15)
def test_shard_split_fail_GTID_EXECUTED(self):
self.split_fail = True
status = self.proxy.group.lookup_servers("GROUPID3")
info = self.check_xmlrpc_simple(status, {
'status': "PRIMARY",
}, index=1)
shard_uuid = info["server_uuid"]
shard_server = fetch_test_server(shard_uuid)
shard_server.connect()
shard_server.exec_stmt("DROP DATABASE IF EXISTS Extra")
shard_server.exec_stmt("CREATE DATABASE Extra")
shard_server.exec_stmt("CREATE TABLE Extra.Extra_Table"
"(userID INT, name VARCHAR(30))")
shard_server.exec_stmt("INSERT INTO Extra.Extra_Table "
"VALUES(101, 'TEST 1')")
shard_server.exec_stmt("INSERT INTO Extra.Extra_Table "
"VALUES(102, 'TEST 2')")
shard_server.exec_stmt("INSERT INTO Extra.Extra_Table "
"VALUES(103, 'TEST 3')")
shard_server.exec_stmt("INSERT INTO Extra.Extra_Table "
"VALUES(701, 'TEST 4')")
status = self.proxy.sharding.split_shard("1", "GROUPID3", "600")
self.check_xmlrpc_command_result(status, has_error=1)
def test_switchover_with_no_master(self):
"""Ensure that a switchover/failover happens when masters in the
shard and global groups are dead.
"""
# Check that a shard group has it master pointing to a the master
# in the global group.
global_group = Group.fetch("GROUPID1")
shard_group = Group.fetch("GROUPID2")
other_shard_group = Group.fetch("GROUPID3")
global_master = fetch_test_server(global_group.master)
global_master.connect()
shard_master = fetch_test_server(shard_group.master)
shard_master.connect()
other_shard_master = fetch_test_server(other_shard_group.master)
other_shard_master.connect()
self.assertEqual(_replication.slave_has_master(shard_master),
str(global_group.master))
self.assertEqual(_replication.slave_has_master(other_shard_master),
str(global_group.master))
# Demote the master in the global group and check that a
# shard group points to None.
global_group = Group.fetch("GROUPID1")
self.assertEqual(global_group.master, global_master.uuid)
self.proxy.group.demote("GROUPID1")
global_group = Group.fetch("GROUPID1")
self.assertEqual(global_group.master, None)
self.assertEqual(_replication.slave_has_master(shard_master), None)
self.assertEqual(_replication.slave_has_master(other_shard_master),
None)
# Demote the master in a shard group and promote the master
# in the global group.
global_group = Group.fetch("GROUPID1")
self.assertEqual(global_group.master, None)
shard_group = Group.fetch("GROUPID2")
self.assertEqual(shard_group.master, shard_master.uuid)
self.proxy.group.demote("GROUPID2")
shard_group = Group.fetch("GROUPID2")
self.assertEqual(shard_group.master, None)
self.proxy.group.promote("GROUPID1", str(global_master.uuid))
global_group = Group.fetch("GROUPID1")
self.assertEqual(global_group.master, global_master.uuid)
self.assertEqual(_replication.slave_has_master(shard_master), None)
self.assertEqual(_replication.slave_has_master(other_shard_master),
str(global_group.master))
# Promote the master in the previous shard group and check that
# everything is back to normal.
global_group = Group.fetch("GROUPID1")
self.assertEqual(global_group.master, global_master.uuid)
self.assertEqual(_replication.slave_has_master(shard_master), None)
shard_group = Group.fetch("GROUPID2")
self.assertEqual(shard_group.master, None)
self.proxy.group.promote("GROUPID2", str(shard_master.uuid))
self.assertEqual(_replication.slave_has_master(shard_master),
str(global_group.master))
self.assertEqual(_replication.slave_has_master(other_shard_master),
str(global_group.master))
shard_group = Group.fetch("GROUPID2")
self.assertEqual(shard_group.master, shard_master.uuid)
# Demote the master in the global group, check that a shard group
# points to None, promot it again and check that everything is back
# to normal
global_group = Group.fetch("GROUPID1")
self.assertEqual(global_group.master, global_master.uuid)
shard_group = Group.fetch("GROUPID2")
self.assertEqual(shard_group.master, shard_master.uuid)
self.proxy.group.demote("GROUPID1")
global_group = Group.fetch("GROUPID1")
self.assertEqual(global_group.master, None)
self.assertEqual(_replication.slave_has_master(shard_master), None)
self.proxy.group.promote("GROUPID1", str(global_master.uuid))
global_group = Group.fetch("GROUPID1")
self.assertEqual(global_group.master, global_master.uuid)
self.assertEqual(_replication.slave_has_master(shard_master),
str(global_group.master))
self.assertEqual(_replication.slave_has_master(other_shard_master),
str(global_group.master))
def test_global_update_propogation_switchover(self):
"""Ensure that the global data propogation is not impacted when a
switchover is triggered. Basically it should ensure that the new
master is redirected to replicate to all the other shards.
"""
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
global_master = fetch_test_server(row['server_uuid'])
global_master.connect()
global_master.exec_stmt("DROP DATABASE IF EXISTS global_db")
global_master.exec_stmt("CREATE DATABASE global_db")
global_master.exec_stmt("CREATE TABLE global_db.global_table"
"(userID INT, name VARCHAR(30))")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(101, 'TEST 1')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(202, 'TEST 2')")
status = self.proxy.group.promote("GROUPID1")
self.check_xmlrpc_command_result(status)
sleep(5)
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
global_master = fetch_test_server(row['server_uuid'])
global_master.connect()
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(303, 'TEST 3')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(404, 'TEST 4')")
status = self.proxy.group.promote("GROUPID2")
self.check_xmlrpc_command_result(status)
sleep(5)
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(505, 'TEST 5')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(606, 'TEST 6')")
status = self.proxy.group.promote("GROUPID3")
self.check_xmlrpc_command_result(status)
sleep(5)
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(505, 'TEST 7')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(606, 'TEST 8')")
sleep(5)
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(row['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT NAME FROM global_db.global_table", {"fetch": True})
self.assertEqual(len(rows), 8)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
self.assertEqual(rows[2][0], 'TEST 3')
self.assertEqual(rows[3][0], 'TEST 4')
self.assertEqual(rows[4][0], 'TEST 5')
self.assertEqual(rows[5][0], 'TEST 6')
self.assertEqual(rows[6][0], 'TEST 7')
self.assertEqual(rows[7][0], 'TEST 8')
status = self.proxy.sharding.lookup_servers("db1.t1", 1500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(row['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT NAME FROM global_db.global_table", {"fetch": True})
self.assertEqual(len(rows), 8)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
self.assertEqual(rows[2][0], 'TEST 3')
self.assertEqual(rows[3][0], 'TEST 4')
self.assertEqual(rows[4][0], 'TEST 5')
self.assertEqual(rows[5][0], 'TEST 6')
self.assertEqual(rows[6][0], 'TEST 7')
self.assertEqual(rows[7][0], 'TEST 8')
def test_server_permissions(self):
"""Verify the server user permissions.
"""
#
# Skip test in trial-mode. Users are the same a s admin user.
# Admin won't be able to grant a privilege back to itself.
#
if (MySQLInstances().server_user == MySQLInstances().user):
# The trailing comma prevents a newline.
print "Skipping test_server_permissions in trial-mode --- ",
return
#
# Prepare group and servers
#
self.proxy.group.create("group", "Testing group...")
address_0 = tests.utils.MySQLInstances().get_address(0)
address_1 = tests.utils.MySQLInstances().get_address(1)
address_2 = tests.utils.MySQLInstances().get_address(2)
status_uuid = self.proxy.server.lookup_uuid(address_0)
uuid_0 = status_uuid[2]
status_uuid = self.proxy.server.lookup_uuid(address_1)
uuid_1 = status_uuid[2]
status_uuid = self.proxy.server.lookup_uuid(address_2)
uuid_2 = status_uuid[2]
status = self.proxy.group.add("group", address_0)
self.check_xmlrpc_command_result(status, False)
server_0 = fetch_test_server(address_0)
server_0.connect()
#
# Change password of the the server user, remove server from
# group, try to add server to group, which fails, because the
# Fabric instance does still use the old password, change
# password back, add server to group. The remove operation is
# required, so that fabric purges all connections to this
# server. Otherwise it would use existing connections, which do
# not care about a changed password. Only the add server to
# group command after a remove server from group cpmmand forces
# Fabric to establish a new connection, which fails on the wrong
# password.
#
server_0.exec_stmt(
"SET PASSWORD FOR '{user}'@'%' ="
" PASSWORD('foobar')".format(user=MySQLInstances().server_user))
status = self.proxy.group.remove("group", address_0)
self.check_xmlrpc_command_result(status, False)
status = self.proxy.group.add("group", address_0)
self.check_xmlrpc_command_result(status, has_error=True)
server_0.connect()
server_0.exec_stmt("SET PASSWORD FOR '{user}'@'%' ="
" PASSWORD('{passwd}')".format(
user=MySQLInstances().server_user,
passwd=MySQLInstances().server_passwd))
status = self.proxy.group.add("group", address_0)
self.check_xmlrpc_command_result(status, False)
#
# Revoke the REPLICATION SLAVE privilege from the server user,
# try a promote, which fails, grant REPLICATION SLAVE back.
#
server_0.exec_stmt(
"REVOKE REPLICATION SLAVE ON *.* FROM '{user}'@'%'".format(
user=MySQLInstances().server_user))
status = self.proxy.group.promote("group")
self.check_xmlrpc_command_result(status, has_error=True)
server_0.exec_stmt(
"GRANT REPLICATION SLAVE ON *.* TO '{user}'@'%'".format(
user=MySQLInstances().server_user))
#
# Do a successful promote.
#
status = self.proxy.group.promote("group")
self.check_xmlrpc_command_result(status, False)
def test_shard_split(self):
status = self.proxy.sharding.split_shard("1", "GROUPID3", "600")
self.check_xmlrpc_command_result(status)
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for info in self.check_xmlrpc_iter(status):
server_uuid = info['server_uuid']
shard_server = fetch_test_server(server_uuid)
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT NAME FROM db1.t1",
{"fetch" : True})
self.assertEqual(len(rows), 3)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
self.assertEqual(rows[2][0], 'TEST 3')
status = self.proxy.sharding.lookup_servers("db1.t1", 800, "LOCAL")
for info in self.check_xmlrpc_iter(status):
server_uuid = info['server_uuid']
shard_server = fetch_test_server(server_uuid)
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT NAME FROM db1.t1",
{"fetch" : True})
self.assertEqual(len(rows), 4)
self.assertEqual(rows[0][0], 'TEST 4')
self.assertEqual(rows[1][0], 'TEST 5')
self.assertEqual(rows[2][0], 'TEST 6')
self.assertEqual(rows[3][0], 'TEST 7')
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for info in self.check_xmlrpc_iter(status):
if info['status'] == MySQLServer.PRIMARY:
global_master = fetch_test_server(info['server_uuid'])
global_master.connect()
global_master.exec_stmt("DROP DATABASE IF EXISTS global_db")
global_master.exec_stmt("CREATE DATABASE global_db")
global_master.exec_stmt("CREATE TABLE global_db.global_table"
"(userID INT, name VARCHAR(30))")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(101, 'TEST 1')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(202, 'TEST 2')")
status = self.proxy.group.promote("GROUPID1")
self.check_xmlrpc_command_result(status)
sleep(5)
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for info in self.check_xmlrpc_iter(status):
if info['status'] == MySQLServer.PRIMARY:
global_master = fetch_test_server(info['server_uuid'])
global_master.connect()
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(303, 'TEST 3')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(404, 'TEST 4')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(505, 'TEST 5')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(606, 'TEST 6')")
sleep(5)
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for info in self.check_xmlrpc_iter(status):
if info['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT NAME FROM global_db.global_table", {"fetch" : True}
)
self.assertEqual(len(rows), 6)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
self.assertEqual(rows[2][0], 'TEST 3')
self.assertEqual(rows[3][0], 'TEST 4')
self.assertEqual(rows[4][0], 'TEST 5')
self.assertEqual(rows[5][0], 'TEST 6')
#Ensure tha two new shard_ids have been generated.
range_sharding_specifications = RangeShardingSpecification.list(1)
self.assertTrue(ShardingUtils.compare_range_specifications(
range_sharding_specifications[0],
RangeShardingSpecification.fetch(2)))
self.assertTrue(ShardingUtils.compare_range_specifications(
range_sharding_specifications[1],
RangeShardingSpecification.fetch(3)))
def test_prune_shard(self):
status = self.proxy.sharding.prune_shard("db1.t1")
self.check_xmlrpc_command_result(status)
status = self.proxy.sharding.lookup_servers("db1.t1", "a3", "LOCAL")
for info in self.check_xmlrpc_iter(status):
if info['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT COUNT(*) FROM db1.t1", {"fetch" : True}
)
self.assertTrue(int(rows[0][0]) == 70)
status = self.proxy.sharding.prune_shard("db1.t1")
self.check_xmlrpc_command_result(status)
status = self.proxy.sharding.lookup_servers("db1.t1", "b12", "LOCAL")
for info in self.check_xmlrpc_iter(status):
if info['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT COUNT(*) FROM db1.t1", {"fetch" : True}
)
self.assertTrue(int(rows[0][0]) == 300)
rows = shard_server.exec_stmt(
"SELECT COUNT(*) FROM db2.t2", {"fetch" : True}
)
self.assertTrue(int(rows[0][0]) == 300)
status = self.proxy.sharding.prune_shard("db1.t1")
self.check_xmlrpc_command_result(status)
status = self.proxy.sharding.lookup_servers("db1.t1", "c35", "LOCAL")
for info in self.check_xmlrpc_iter(status):
if info['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT COUNT(*) FROM db1.t1", {"fetch" : True}
)
self.assertTrue(int(rows[0][0]) == 200)
rows = shard_server.exec_stmt(
"SELECT COUNT(*) FROM db2.t2", {"fetch" : True}
)
self.assertTrue(int(rows[0][0]) == 200)
status = self.proxy.sharding.prune_shard("db1.t1")
self.check_xmlrpc_command_result(status)
status = self.proxy.sharding.lookup_servers("db1.t1", "d21", "LOCAL")
for info in self.check_xmlrpc_iter(status):
if info['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT COUNT(*) FROM db1.t1", {"fetch" : True}
)
self.assertTrue(int(rows[0][0]) == 600)
rows = shard_server.exec_stmt(
"SELECT COUNT(*) FROM db2.t2", {"fetch" : True}
)
self.assertTrue(int(rows[0][0]) == 600)
status = self.proxy.sharding.prune_shard("db1.t1")
self.check_xmlrpc_command_result(status)
status = self.proxy.sharding.lookup_servers("db1.t1", "e31", "LOCAL")
for info in self.check_xmlrpc_iter(status):
if info['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT COUNT(*) FROM db1.t1", {"fetch" : True}
)
self.assertTrue(int(rows[0][0]) == 800)
rows = shard_server.exec_stmt(
"SELECT COUNT(*) FROM db2.t2", {"fetch" : True}
)
self.assertTrue(int(rows[0][0]) == 800)
def test_prune_shard(self):
status = self.proxy.sharding.prune_shard("db1.t1")
self.check_xmlrpc_command_result(status)
status = self.proxy.sharding.lookup_servers("db1.t1", 1, "LOCAL")
info = self.check_xmlrpc_simple(status, {}, rowcount=1)
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt("SELECT COUNT(*) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 70)
rows = shard_server.exec_stmt("SELECT MAX(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 70)
rows = shard_server.exec_stmt("SELECT MIN(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 1)
status = self.proxy.sharding.lookup_servers("db1.t1", 101, "LOCAL")
info = self.check_xmlrpc_simple(status, {}, rowcount=1)
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt("SELECT COUNT(*) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 200)
rows = shard_server.exec_stmt("SELECT MAX(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 300)
rows = shard_server.exec_stmt("SELECT MIN(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 101)
status = self.proxy.sharding.lookup_servers("db1.t1", 1202, "LOCAL")
info = self.check_xmlrpc_simple(status, {}, rowcount=1)
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt("SELECT COUNT(*) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 200)
rows = shard_server.exec_stmt("SELECT MAX(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 1200)
rows = shard_server.exec_stmt("SELECT MIN(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 1001)
status = self.proxy.sharding.lookup_servers("db1.t1", 11000, "LOCAL")
info = self.check_xmlrpc_simple(status, {}, rowcount=1)
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt("SELECT COUNT(*) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 200)
rows = shard_server.exec_stmt("SELECT MAX(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 10200)
rows = shard_server.exec_stmt("SELECT MIN(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 10001)
status = self.proxy.sharding.lookup_servers("db1.t1", 100100, "LOCAL")
info = self.check_xmlrpc_simple(status, {}, rowcount=1)
shard_server = fetch_test_server(info['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt("SELECT COUNT(*) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 200)
rows = shard_server.exec_stmt("SELECT MAX(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 100200)
rows = shard_server.exec_stmt("SELECT MIN(userID) FROM db1.t1",
{"fetch": True})
self.assertTrue(int(rows[0][0]) == 100001)
def test_shard_move(self):
status = self.proxy.sharding.move_shard("1", "GROUPID3")
self.check_xmlrpc_command_result(status)
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
shard_server = fetch_test_server(row['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt("SELECT NAME FROM db1.t1",
{"fetch": True})
self.assertEqual(len(rows), 7)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
self.assertEqual(rows[2][0], 'TEST 3')
self.assertEqual(rows[3][0], 'TEST 4')
self.assertEqual(rows[4][0], 'TEST 5')
self.assertEqual(rows[5][0], 'TEST 6')
self.assertEqual(rows[6][0], 'TEST 7')
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
global_master_uuid = row['server_uuid']
break
global_master = fetch_test_server(global_master_uuid)
global_master.connect()
global_master.exec_stmt("DROP DATABASE IF EXISTS global_db")
global_master.exec_stmt("CREATE DATABASE global_db")
global_master.exec_stmt("CREATE TABLE global_db.global_table"
"(userID INT, name VARCHAR(30))")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(101, 'TEST 1')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(202, 'TEST 2')")
status = self.proxy.group.promote("GROUPID1")
self.check_xmlrpc_command_result(status)
sleep(5)
status = self.proxy.sharding.lookup_servers("1", 500, "GLOBAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
global_master_uuid = row['server_uuid']
break
global_master = fetch_test_server(global_master_uuid)
global_master.connect()
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(303, 'TEST 3')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(404, 'TEST 4')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(505, 'TEST 5')")
global_master.exec_stmt("INSERT INTO global_db.global_table "
"VALUES(606, 'TEST 6')")
sleep(5)
status = self.proxy.sharding.lookup_servers("db1.t1", 500, "LOCAL")
for row in self.check_xmlrpc_iter(status):
if row['status'] == MySQLServer.PRIMARY:
shard_server = fetch_test_server(row['server_uuid'])
shard_server.connect()
rows = shard_server.exec_stmt(
"SELECT NAME FROM global_db.global_table", {"fetch": True})
self.assertEqual(len(rows), 6)
self.assertEqual(rows[0][0], 'TEST 1')
self.assertEqual(rows[1][0], 'TEST 2')
self.assertEqual(rows[2][0], 'TEST 3')
self.assertEqual(rows[3][0], 'TEST 4')
self.assertEqual(rows[4][0], 'TEST 5')
self.assertEqual(rows[5][0], 'TEST 6')
def test_shard_split(self):
split_cnt_1 = 0
split_cnt_2 = 0
shard_server_1 = None
shard_server_2 = None
expected_address_list_1 = \
[MySQLInstances().get_address(2), MySQLInstances().get_address(3)]
expected_address_list_2 = \
[MySQLInstances().get_address(4), MySQLInstances().get_address(5)]
status = self.proxy.sharding.split_shard("1", "GROUPID3")
self.check_xmlrpc_command_result(status)
for i in range(1, 100):
status = self.proxy.sharding.lookup_servers("db1.t1", i, "LOCAL")
obtained_uuid_list = [
info['server_uuid'] for info in self.check_xmlrpc_iter(status)
]
obtained_address_list = [
info['address'] for info in self.check_xmlrpc_iter(status)
]
try:
self.assertEqual(set(expected_address_list_1),
set(obtained_address_list))
split_cnt_1 = split_cnt_1 + 1
if shard_server_1 is None:
shard_server_1 = fetch_test_server(obtained_uuid_list[0])
except AssertionError:
self.assertEqual(set(expected_address_list_2),
set(obtained_address_list))
split_cnt_2 = split_cnt_2 + 1
if shard_server_2 is None:
shard_server_2 = fetch_test_server(obtained_uuid_list[0])
#Ensure that both the splits have been utilized.
self.assertTrue(split_cnt_1 > 0)
self.assertTrue(split_cnt_2 > 0)
shard_server_1.connect()
shard_server_2.connect()
row_cnt_shard_1 = shard_server_1.exec_stmt(
"SELECT COUNT(*) FROM db1.t1", {"fetch": True})
row_cnt_shard_2 = shard_server_2.exec_stmt(
"SELECT COUNT(*) FROM db1.t1", {"fetch": True})
#Ensure that the split has happened, the number of values in
#each shard should be less than the original.
self.assertTrue(int(row_cnt_shard_1[0][0]) < 100)
self.assertTrue(int(row_cnt_shard_2[0][0]) < 100)
#Ensure tha two new shard_ids have been generated.
hash_sharding_specifications = HashShardingSpecification.list(1)
self.assertTrue(
ShardingUtils.compare_hash_specifications(
hash_sharding_specifications[1],
HashShardingSpecification.fetch(2)))
self.assertTrue(
ShardingUtils.compare_hash_specifications(
hash_sharding_specifications[0],
HashShardingSpecification.fetch(3)))