def run_query_mysql_container(self, query, db, container_id, return_result=True):
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
hostname = config.get('ProxySQL', 'hostname')
username = config.get('ProxySQL', 'username')
password = config.get('ProxySQL', 'password')
metadata = self.docker_inspect(container_id)
mysql_port = metadata.get('NetworkSettings', {})\
.get('Ports', {})\
.get('3306/tcp', [{}])[0]\
.get('HostPort', None)
if mysql_port is not None:
mysql_connection = MySQLdb.connect(host=hostname,
user=username,
passwd=password,
port=int(mysql_port),
db=db)
cursor = mysql_connection.cursor()
cursor.execute(query)
if return_result:
rows = cursor.fetchall()
cursor.close()
mysql_connection.close()
if return_result:
return rows
else:
return None
def get_all_mysql_connection_credentials(self, hostgroup=None):
# Figure out which are the containers for the specified hostgroup
mysql_backend_ids = self.get_mysql_containers(hostgroup=hostgroup)
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
hostname = config.get('ProxySQL', 'hostname')
username = config.get('ProxySQL', 'username')
password = config.get('ProxySQL', 'password')
result = []
for container_id in mysql_backend_ids:
metadata = self.docker_inspect(container_id)
mysql_port = metadata.get('NetworkSettings', {})\
.get('Ports', {})\
.get('3306/tcp', [{}])[0]\
.get('HostPort', None)
if mysql_port is not None:
result.append({
'hostname': hostname,
'port': mysql_port,
'username': username,
'password': password
})
return result
def get_docker_scenario_templates(self, scenarios=[]):
"""Retrieve the list of docker templates that will be used to generate
scenarios.
Why do we need templates for scenarios? Because the scenario will be
the same, the only difference will be the configuration of the machines
involved (for example, it might be a different operating system).
"""
if len(scenarios) == 0:
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
scenarios = config.get('Scenarios', 'default_scenarios').split(',')
files = {}
dockercompose_path = os.path.dirname(__file__) + "/../docker/scenarios"
for item in os.listdir(dockercompose_path):
# Filter based on received default scenarios
if item not in scenarios:
continue
dir_path = dockercompose_path + os.sep + item
if path.isdir(dir_path):
dockercomposefile = dir_path + os.sep + "docker-compose.yml"
if path.isfile(dockercomposefile):
files[item] = {
"dir": dir_path,
"dockercomposefile": dockercomposefile
}
with open (dockercomposefile, "r") as myfile:
files[item]["contents"] = data=myfile.read()
return files
def get_all_mysql_connection_credentials(self, hostgroup=None):
# Figure out which are the containers for the specified hostgroup
mysql_backend_ids = self.get_mysql_containers(hostgroup=hostgroup)
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
hostname = config.get('ProxySQL', 'hostname')
username = config.get('ProxySQL', 'username')
password = config.get('ProxySQL', 'password')
result = []
for container_id in mysql_backend_ids:
metadata = self.docker_inspect(container_id)
mysql_port = metadata.get('NetworkSettings', {})\
.get('Ports', {})\
.get('3306/tcp', [{}])[0]\
.get('HostPort', None)
if mysql_port is not None:
result.append({
'hostname': hostname,
'port': mysql_port,
'username': username,
'password': password
})
return result
def run_query_mysql_container(self,
query,
db,
container_id,
return_result=True):
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
hostname = config.get('ProxySQL', 'hostname')
username = config.get('ProxySQL', 'username')
password = config.get('ProxySQL', 'password')
metadata = self.docker_inspect(container_id)
mysql_port = metadata.get('NetworkSettings', {})\
.get('Ports', {})\
.get('3306/tcp', [{}])[0]\
.get('HostPort', None)
if mysql_port is not None:
mysql_connection = MySQLdb.connect(host=hostname,
user=username,
passwd=password,
port=int(mysql_port),
db=db)
cursor = mysql_connection.cursor()
cursor.execute(query)
if return_result:
rows = cursor.fetchall()
cursor.close()
mysql_connection.close()
if return_result:
return rows
else:
return None
def run_query_proxysql(cls,
query,
db,
return_result=True,
username=None,
password=None,
port=None):
"""Run a query against the ProxySQL proxy and optionally return its
results as a set of rows."""
config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES)
username = username or config.get('ProxySQL', 'username')
password = password or config.get('ProxySQL', 'password')
port = port or int(config.get('ProxySQL', 'port'))
hostname = config.get('ProxySQL', 'hostname')
proxy_connection = MySQLdb.connect(hostname,
username,
password,
port=port,
db=db)
cursor = proxy_connection.cursor()
cursor.execute(query)
if return_result:
rows = cursor.fetchall()
cursor.close()
proxy_connection.close()
if return_result:
return rows
def get_docker_scenario_templates(self, scenarios=[]):
"""Retrieve the list of docker templates that will be used to generate
scenarios.
Why do we need templates for scenarios? Because the scenario will be
the same, the only difference will be the configuration of the machines
involved (for example, it might be a different operating system).
"""
if len(scenarios) == 0:
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
scenarios = config.get('Scenarios', 'default_scenarios').split(',')
files = {}
dockercompose_path = os.path.dirname(__file__) + "/../docker/scenarios"
for item in os.listdir(dockercompose_path):
# Filter based on received default scenarios
if item not in scenarios:
continue
dir_path = dockercompose_path + os.sep + item
if path.isdir(dir_path):
dockercomposefile = dir_path + os.sep + "docker-compose.yml"
if path.isfile(dockercomposefile):
files[item] = {
"dir": dir_path,
"dockercomposefile": dockercomposefile
}
with open(dockercomposefile, "r") as myfile:
files[item]["contents"] = data = myfile.read()
return files
def get_proxysql_admin_connection_credentials(self):
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
return {
"hostname": config.get("ProxySQL", "hostname"),
"port": config.get("ProxySQL", "admin_port"),
"username": config.get("ProxySQL", "admin_username"),
"password": config.get("ProxySQL", "admin_password")
}
def get_proxysql_admin_connection_credentials(self):
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
return {
"hostname": config.get("ProxySQL", "hostname"),
"port": config.get("ProxySQL", "admin_port"),
"username": config.get("ProxySQL", "admin_username"),
"password": config.get("ProxySQL", "admin_password")
}
def run_query_mysql(cls, query, db, return_result=True, hostgroup=0,
username=None, password=None):
"""Run a query against the MySQL backend and optionally return its
results as a set of rows.
IMPORTANT: since the queries are actually ran against the MySQL backend,
that backend needs to expose its MySQL port to the outside through
docker compose's port mapping mechanism.
This will actually parse the docker-compose configuration file to
retrieve the available backends and hostgroups and will pick a backend
from the specified hostgroup."""
# Figure out which are the containers for the specified hostgroup
mysql_backends = cls._get_mysql_containers()
mysql_backends_in_hostgroup = []
for backend in mysql_backends:
container_name = backend['Names'][0][1:].upper()
backend_hostgroup = cls._extract_hostgroup_from_container_name(container_name)
mysql_port_exposed=False
if not backend.get('Ports'):
continue
for exposed_port in backend.get('Ports', []):
if exposed_port['PrivatePort'] == 3306:
mysql_port_exposed = True
if backend_hostgroup == hostgroup and mysql_port_exposed:
mysql_backends_in_hostgroup.append(backend)
if len(mysql_backends_in_hostgroup) == 0:
raise Exception('No backends with a publicly exposed port were '
'found in hostgroup %d' % hostgroup)
# Pick a random container, extract its connection details
container = random.choice(mysql_backends_in_hostgroup)
for exposed_port in container.get('Ports', []):
if exposed_port['PrivatePort'] == 3306:
mysql_port = exposed_port['PublicPort']
config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES)
hostname = config.get('ProxySQL', 'hostname')
username = username or config.get('ProxySQL', 'username')
password = password or config.get('ProxySQL', 'password')
mysql_connection = MySQLdb.connect(hostname,
username,
password,
port=mysql_port,
db=db)
cursor = mysql_connection.cursor()
cursor.execute(query)
if return_result:
rows = cursor.fetchall()
cursor.close()
mysql_connection.close()
if return_result:
return rows
def _start_proxysql_pings(self): """During the running of the tests, the test suite will continuously monitor the ProxySQL daemon in order to check that it's up. This special thread will do exactly that.""" config = ProxySQL_Tests_Config(overrides=ProxySQLBaseTest.CONFIG_OVERRIDES) self.ping_thread = ProxySQL_Ping_Thread(config) self.ping_thread.start()
def _populate_proxy_configuration_with_backends(self):
"""Populate ProxySQL's admin information with the MySQL backends
and their associated hostgroups.
This is needed because I do not want to hardcode this into the ProxySQL
config file of the test scenario, as it leaves more room for quick
iteration.
In order to configure ProxySQL with the correct backends, we are using
the MySQL admin interface of ProxySQL, and inserting rows into the
`mysql_servers` table, which contains a list of which servers go into
which hostgroup.
"""
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
proxysql_container_id = self.get_proxysql_container()
mysql_container_ids = self.get_mysql_containers()
environment_variables = self.get_environment_variables_from_container(
proxysql_container_id)
proxy_admin_connection = MySQLdb.connect(
config.get('ProxySQL', 'hostname'),
config.get('ProxySQL', 'admin_username'),
config.get('ProxySQL', 'admin_password'),
port=int(config.get('ProxySQL', 'admin_port')))
cursor = proxy_admin_connection.cursor()
for mysql_container_id in mysql_container_ids:
metadata = self.docker_inspect(mysql_container_id)
container_name = metadata['Name'][1:].upper()
port_uri = environment_variables['%s_PORT' % container_name]
port_no = self._extract_port_number_from_uri(port_uri)
ip = environment_variables['%s_PORT_%d_TCP_ADDR' %
(container_name, port_no)]
hostgroup = int(
metadata.get('Config',
{}).get('Labels',
{}).get('com.proxysql.hostgroup'))
cursor.execute(
"INSERT INTO mysql_servers(hostgroup_id, hostname, port, status) "
"VALUES(%d, '%s', %d, 'ONLINE')" % (hostgroup, ip, port_no))
cursor.execute("LOAD MYSQL SERVERS TO RUNTIME")
cursor.close()
proxy_admin_connection.close()
def run_query_proxysql_admin(cls, query, return_result=True):
"""Run a query against the ProxySQL admin.
Note: we do not need to specify a db for this query, as it's always
against the "main" database.
TODO(andrei): revisit db assumption once stats databases from ProxySQL
are accessible via the MySQL interface.
"""
config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES)
return cls.run_query_proxysql(
query,
# "main" database is hardcoded within the
# ProxySQL admin -- it contains the SQLite3
# tables with metadata about servers and users
"main",
return_result,
username=config.get('ProxySQL', 'admin_username'),
password=config.get('ProxySQL', 'admin_password'),
port=int(config.get('ProxySQL', 'admin_port')))
def run_query_proxysql_admin(cls, query, return_result=True):
"""Run a query against the ProxySQL admin.
Note: we do not need to specify a db for this query, as it's always
against the "main" database.
TODO(andrei): revisit db assumption once stats databases from ProxySQL
are accessible via the MySQL interface.
"""
config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES)
return cls.run_query_proxysql(
query,
# "main" database is hardcoded within the
# ProxySQL admin -- it contains the SQLite3
# tables with metadata about servers and users
"main",
return_result,
username=config.get('ProxySQL', 'admin_username'),
password=config.get('ProxySQL', 'admin_password'),
port=int(config.get('ProxySQL', 'admin_port'))
)
def run_sysbench_proxysql(cls, threads=4, time=60, db="test",
username=None, password=None, port=None):
"""Runs a sysbench test with the given parameters against the given
ProxySQL instance.
In this case, due to better encapsulation and reduced latency to
ProxySQL, we are assuming that sysbench is installed on the same
container with it.
"""
proxysql_container_id = ProxySQLBaseTest._get_proxysql_container()['Id']
config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES)
hostname = config.get('ProxySQL', 'hostname')
username = username or config.get('ProxySQL', 'username')
password = password or config.get('ProxySQL', 'password')
port = port or config.get('ProxySQL', 'port')
params = [
"sysbench",
"--test=/opt/sysbench/sysbench/tests/db/oltp.lua",
"--num-threads=%d" % threads,
"--max-requests=0",
"--max-time=%d" % time,
"--mysql-user=%s" % username,
"--mysql-password=%s" % password,
"--mysql-db=%s" % db,
"--db-driver=mysql",
"--oltp-tables-count=4",
"--oltp-read-only=on",
"--oltp-skip-trx=on",
"--report-interval=1",
"--oltp-point-selects=100",
"--oltp-table-size=400000",
"--mysql-host=%s" % hostname,
"--mysql-port=%s" % port
]
cls.run_bash_command_within_proxysql(params + ["prepare"])
cls.run_bash_command_within_proxysql(params + ["run"])
cls.run_bash_command_within_proxysql(params + ["cleanup"])
def get_docker_images(self, filters):
names = filters.pop('names', [])
if len(names) == 0:
if filters.get('com.proxysql.type') == 'proxysql':
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
names = config.get('Scenarios',
'default_proxysql_images').split(',')
elif filters.get('com.proxysql.type') == 'mysql':
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
names = config.get('Scenarios',
'default_mysql_images').split(',')
args = ["docker", "images"]
for k, v in filters.iteritems():
args.append("--filter")
args.append("label=%s=%s" % (k, v))
nonemtpy_lines = self._get_stdout_as_lines(args)
results = nonemtpy_lines[1:]
images = []
for (i, r) in enumerate(results):
tokens = r.split(' ')
nonempty_tokens = [t for t in tokens if len(t.strip()) > 0]
image = nonempty_tokens[1]
if image in names:
images.append(image)
return images
def run_query_proxysql(cls, query, db, return_result=True,
username=None, password=None, port=None):
"""Run a query against the ProxySQL proxy and optionally return its
results as a set of rows."""
config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES)
username = username or config.get('ProxySQL', 'username')
password = password or config.get('ProxySQL', 'password')
port = port or int(config.get('ProxySQL', 'port'))
hostname = config.get('ProxySQL', 'hostname')
proxy_connection = MySQLdb.connect(hostname,
username,
password,
port=port,
db=db)
cursor = proxy_connection.cursor()
cursor.execute(query)
if return_result:
rows = cursor.fetchall()
cursor.close()
proxy_connection.close()
if return_result:
return rows
def run_sysbench_proxysql(self, threads=4, time=60, db="test",
username=None, password=None, port=None):
"""Runs a sysbench test with the given parameters against the given
ProxySQL instance.
In this case, due to better encapsulation and reduced latency to
ProxySQL, we are assuming that sysbench is installed on the same
container with it.
"""
config = ProxySQL_Tests_Config(overrides=ProxySQLBaseTest.CONFIG_OVERRIDES)
hostname = config.get('ProxySQL', 'hostname')
username = username or config.get('ProxySQL', 'username')
password = password or config.get('ProxySQL', 'password')
port = port or config.get('ProxySQL', 'port')
params = [
"sysbench",
"--test=/opt/sysbench/sysbench/tests/db/oltp.lua",
"--num-threads=%d" % threads,
"--max-requests=0",
"--max-time=%d" % time,
"--mysql-user=%s" % username,
"--mysql-password=%s" % password,
"--mysql-db=%s" % db,
"--db-driver=mysql",
"--oltp-tables-count=4",
"--oltp-read-only=on",
"--oltp-skip-trx=on",
"--report-interval=1",
"--oltp-point-selects=100",
"--oltp-table-size=400000",
"--mysql-host=%s" % hostname,
"--mysql-port=%s" % port
]
self.run_bash_command_within_proxysql(params + ["prepare"])
self.run_bash_command_within_proxysql(params + ["run"])
self.run_bash_command_within_proxysql(params + ["cleanup"])
def _populate_proxy_configuration_with_backends(cls):
"""Populate ProxySQL's admin information with the MySQL backends
and their associated hostgroups.
This is needed because I do not want to hardcode this into the ProxySQL
config file of the test scenario, as it leaves more room for quick
iteration.
In order to configure ProxySQL with the correct backends, we are using
the MySQL admin interface of ProxySQL, and inserting rows into the
`mysql_servers` table, which contains a list of which servers go into
which hostgroup.
"""
config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES)
proxysql_container = cls._get_proxysql_container()
mysql_containers = cls._get_mysql_containers()
environment_variables = cls._get_environment_variables_from_container(
proxysql_container['Names'][0][1:])
proxy_admin_connection = MySQLdb.connect(config.get('ProxySQL', 'hostname'),
config.get('ProxySQL', 'admin_username'),
config.get('ProxySQL', 'admin_password'),
port=int(config.get('ProxySQL', 'admin_port')))
cursor = proxy_admin_connection.cursor()
for mysql_container in mysql_containers:
container_name = mysql_container['Names'][0][1:].upper()
port_uri = environment_variables['%s_PORT' % container_name]
port_no = cls._extract_port_number_from_uri(port_uri)
ip = environment_variables['%s_PORT_%d_TCP_ADDR' % (container_name, port_no)]
hostgroup = cls._extract_hostgroup_from_container_name(container_name)
cursor.execute("INSERT INTO mysql_servers(hostgroup_id, hostname, port, status) "
"VALUES(%d, '%s', %d, 'ONLINE')" %
(hostgroup, ip, port_no))
cursor.execute("LOAD MYSQL SERVERS TO RUNTIME")
cursor.close()
proxy_admin_connection.close()
def get_docker_images(self, filters):
names = filters.pop('names', [])
if len(names) == 0:
if filters.get('com.proxysql.type') == 'proxysql':
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
names = config.get('Scenarios', 'default_proxysql_images').split(',')
elif filters.get('com.proxysql.type') == 'mysql':
config = ProxySQL_Tests_Config(overrides=self.config_overrides)
names = config.get('Scenarios', 'default_mysql_images').split(',')
args = ["docker", "images"]
for k, v in filters.iteritems():
args.append("--filter")
args.append("label=%s=%s" % (k, v))
nonemtpy_lines = self._get_stdout_as_lines(args)
results = nonemtpy_lines[1:]
images = []
for (i, r) in enumerate(results):
tokens = r.split(' ')
nonempty_tokens = [t for t in tokens if len(t.strip()) > 0]
image = nonempty_tokens[1]
if image in names:
images.append(image)
return images
def get_tests_config(self): return ProxySQL_Tests_Config(overrides=ProxySQLBaseTest.CONFIG_OVERRIDES)
def run_query_mysql(cls,
query,
db,
return_result=True,
hostgroup=0,
username=None,
password=None):
"""Run a query against the MySQL backend and optionally return its
results as a set of rows.
IMPORTANT: since the queries are actually ran against the MySQL backend,
that backend needs to expose its MySQL port to the outside through
docker compose's port mapping mechanism.
This will actually parse the docker-compose configuration file to
retrieve the available backends and hostgroups and will pick a backend
from the specified hostgroup."""
# Figure out which are the containers for the specified hostgroup
mysql_backends = cls._get_mysql_containers()
mysql_backends_in_hostgroup = []
for backend in mysql_backends:
container_name = backend['Names'][0][1:].upper()
backend_hostgroup = cls._extract_hostgroup_from_container_name(
container_name)
mysql_port_exposed = False
if not backend.get('Ports'):
continue
for exposed_port in backend.get('Ports', []):
if exposed_port['PrivatePort'] == 3306:
mysql_port_exposed = True
if backend_hostgroup == hostgroup and mysql_port_exposed:
mysql_backends_in_hostgroup.append(backend)
if len(mysql_backends_in_hostgroup) == 0:
raise Exception('No backends with a publicly exposed port were '
'found in hostgroup %d' % hostgroup)
# Pick a random container, extract its connection details
container = random.choice(mysql_backends_in_hostgroup)
for exposed_port in container.get('Ports', []):
if exposed_port['PrivatePort'] == 3306:
mysql_port = exposed_port['PublicPort']
config = ProxySQL_Tests_Config(overrides=cls.CONFIG_OVERRIDES)
hostname = config.get('ProxySQL', 'hostname')
username = username or config.get('ProxySQL', 'username')
password = password or config.get('ProxySQL', 'password')
mysql_connection = MySQLdb.connect(hostname,
username,
password,
port=mysql_port,
db=db)
cursor = mysql_connection.cursor()
cursor.execute(query)
if return_result:
rows = cursor.fetchall()
cursor.close()
mysql_connection.close()
if return_result:
return rows
