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)))