def test_query_charset(self):
sql_create_table = (
'CREATE TABLE `test`.`q` ( `name` varchar(128) NOT NULL)'
' ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin')
pool = mysqlconnpool.make({
'host': mysql_test_ip,
'port': mysql_test_port,
'user': mysql_test_user,
'passwd': mysql_test_password,
'charset': 'utf8',
})
rst = pool.query('CREATE DATABASE `test`')
dd('create database rst: ', rst)
rst = pool.query(sql_create_table)
dd('create table rst: ', rst)
rst = pool.query('INSERT INTO `test`.`q` (`name`) VALUES ("我")')
dd('insert rst: ', rst)
rst = pool.query('SELECT * FROM `test`.`q`')
dd('select rst: ', rst)
self.assertEqual(({'name': u'\u6211'}, ), rst)
def test_query_charset(self):
sql_create_table = ('CREATE TABLE `test`.`q` ( `name` varchar(128) NOT NULL)'
' ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin')
pool = mysqlconnpool.make({
'host': mysql_test_ip,
'port': mysql_test_port,
'user': mysql_test_user,
'passwd': mysql_test_password,
'charset': 'utf8',
})
rst = pool.query('CREATE DATABASE `test`')
dd('create database rst: ', rst)
rst = pool.query(sql_create_table)
dd('create table rst: ', rst)
rst = pool.query('INSERT INTO `test`.`q` (`name`) VALUES ("我")')
dd('insert rst: ', rst)
rst = pool.query('SELECT * FROM `test`.`q`')
dd('select rst: ', rst)
self.assertEqual(({'name': u'\u6211'},), rst)
def group_replication_setup_channel(self):
# replication:
# user: "******"
# password: "******"
# source: [...]
rpl = self.bkp_conf['replication']
alive = self.is_instance_alive()
proc = None
if not alive:
proc = self.start_tmp_mysql()
try:
pool = mysqlconnpool.make(self.mysql_addr)
sql = ('CHANGE MASTER TO'
' MASTER_USER="******"'
' , MASTER_PASSWORD="******"'
' FOR CHANNEL "group_replication_recovery"').format(**rpl)
self.mysql_pool_query(pool, sql)
finally:
if not alive:
self.stop_tmp_mysql(proc)
def make_sharding(db, table, conn, shard_fields, start, number_per_shard, tolerance_of_shard,
shard_maker=list):
scan_args = [(db, table), shard_fields, shard_fields, start]
scan_kwargs = {"left_open": False, "use_dict": False, "retry": 3}
connpool = mysqlconnpool.make(conn)
record_iter = scan_index(connpool, *scan_args, **scan_kwargs)
shards = strutil.sharding(
record_iter, number_per_shard, accuracy=tolerance_of_shard, joiner=list)
_, count = shards[0]
result = {
"shard": [shard_maker(start)],
"number": [count],
"total": count,
}
for shard, count in shards[1:]:
result['shard'].append(shard_maker(shard))
result['number'].append(count)
result['total'] += count
return result
def setup_replication(self):
# replication:
# user: "******"
# password: "******"
# source:
# - host:
# port:
# id:
# - host:
# port:
# id:
alive = self.is_instance_alive()
proc = None
if not alive:
proc = self.start_tmp_mysql()
try:
pool = mysqlconnpool.make(self.mysql_addr)
if self.bkp_conf['replication'].get('group_replication') == 1:
self._group_replication_reset_relay(pool)
if alive:
self.mysql_pool_query(pool, 'START group_replication')
else:
self._slave_reset(pool)
if alive:
self.mysql_pool_query(pool, 'START SLAVE')
finally:
if not alive:
self.stop_tmp_mysql(proc)
def diff_replication(self):
self.assert_instance_is_alive()
rpl = self.bkp_conf['replication']
sql = 'show slave status'
mine = self.mysql_query(sql)[0]
rst = {}
for src in rpl['source']:
pool = mysqlconnpool.make({
'host': src['host'],
'port': int(src['port']),
'user': rpl['user'],
'passwd': rpl['password'],
})
k = '{host}:{port}-[{id}]'.format(**src)
try:
r = pool.query(sql)[0]
except MySQLdb.OperationalError as e:
rst[k] = 'Unreachable: ' + repr(e)
else:
diff = self.diff_slave_status_gtidset(mine, r)
rst[k] = diff
return rst
def table_sizes(self, db=None, sortby=None):
if db is None:
db = self.get_dbs()[0]
if sortby is None:
sortby = 'total'
mp = {
'free': lambda x: x['Data_free'],
'used': lambda x: x['Data_length'],
'total': lambda x: x['Data_free'] + x['Data_length'],
}
sort_key = mp[sortby]
addr = {}
addr.update(self.mysql_addr)
addr.update({
'db': db,
})
pool = mysqlconnpool.make(addr)
rsts = pool.query('show table status')
for rst in rsts:
self._rst_to_number(rst)
rsts = list(rsts)
rsts.sort(key=sort_key, reverse=True)
rsts = [('{Data_length:>6} free-able: {Data_free:>6} {Name}'.format(
**humannum.humannum(x)), x) for x in rsts]
return rsts
def make_sharding(db,
table,
conn,
shard_fields,
start,
number_per_shard,
tolerance_of_shard,
shard_maker=list):
scan_args = [(db, table), shard_fields, shard_fields, start]
scan_kwargs = {"left_open": False, "use_dict": False, "retry": 3}
connpool = mysqlconnpool.make(conn)
record_iter = scan_index(connpool, *scan_args, **scan_kwargs)
shards = strutil.sharding(record_iter,
number_per_shard,
accuracy=tolerance_of_shard,
joiner=list)
_, count = shards[0]
result = {
"shard": [shard_maker(start)],
"number": [count],
"total": count,
}
for shard, count in shards[1:]:
result['shard'].append(shard_maker(shard))
result['number'].append(count)
result['total'] += count
return result
def setUp(self):
utdocker.start_container(
mysql_test_name,
mysql_test_tag,
ip=mysql_test_ip,
env={
'MYSQL_ROOT_PASSWORD': mysql_test_password,
},
port_bindings={mysql_test_port: mysql_test_port})
addr = (mysql_test_ip, mysql_test_port)
# some time it takes several seconds to start listening
for ii in range(40):
try:
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
sock.connect(addr)
break
except socket.error:
dd('trying to connect to {0} failed'.format(str(addr)))
sock.close()
time.sleep(.4)
else:
raise
self.pool = mysqlconnpool.make({
'host': mysql_test_ip,
'port': mysql_test_port,
'user': mysql_test_user,
'passwd': mysql_test_password,
})
def setUp(self):
self.mysql_ip = start_mysql_server()
addr = (self.mysql_ip, mysql_test_port)
# some time it takes several seconds to start listening
for ii in range(40):
try:
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
sock.connect(addr)
break
except socket.error:
dd('trying to connect to {0} failed'.format(str(addr)))
sock.close()
time.sleep(.4)
else:
raise
self.pool = mysqlconnpool.make({
'host': self.mysql_ip,
'port': mysql_test_port,
'user': mysql_test_user,
'passwd': mysql_test_password,
})
def clean_binlog(self, before_days=5):
ts = timeutil.ts()
ts = ts - 86400 * before_days
dt = timeutil.format_ts(ts, 'daily')
pool = mysqlconnpool.make(self.mysql_addr)
self.mysql_pool_query(
pool, 'PURGE BINARY LOGS BEFORE "{dt} 00:00:00"'.format(dt=dt))
def scan_index(connpool, table, result_fields, index_fields, index_values,
left_open=False, limit=None, index_name=None, use_dict=True, retry=0):
if type(connpool) == dict:
# an address
pool = mysqlconnpool.make(connpool)
elif isinstance(connpool, mysqlconnpool.MysqlConnectionPool):
pool = connpool
else:
raise ConnectionTypeError
if len(index_values) != len(index_fields):
raise InvalidLength(
'number of index fields and values are not equal: '
'index fields: {fld}; '
'index values: {val}'.format(fld=index_fields, val=index_values))
req_fields = list(index_fields)
req_values = list(index_values)
strict = True
if limit is None:
strict = False
limit = 1024
while True:
sql = make_index_scan_sql(table, None, req_fields, req_values,
left_open=left_open, limit=limit, index_name=index_name)
rst = pool.query(sql, retry=retry)
for rr in rst:
if use_dict:
yield dict([(k, rr[k]) for k in result_fields])
else:
yield [rr[k] for k in result_fields]
if strict:
break
if len(rst) > 0:
last_row = rst[-1]
req_fields = list(index_fields)
req_values = [str(last_row[x]) for x in req_fields]
left_open = True
continue
req_fields = req_fields[:-1]
req_values = req_values[:-1]
if len(req_fields) > 0:
continue
break
def setUp(self):
super(TestMysql, self).setUp()
self.addr = {
'host': base.mysql_test_ip,
'port': base.mysql_test_port,
'user': mysql_test_user,
'passwd': base.mysql_test_password,
}
self.conn_pool = mysqlconnpool.make(self.addr)
self.conn = self.conn_pool.get_conn()
def group_replication_bootstrap(self):
pool = mysqlconnpool.make(self.mysql_addr)
sqls = (
"SET GLOBAL group_replication_bootstrap_group=ON",
"START GROUP_REPLICATION",
"SET GLOBAL group_replication_bootstrap_group=OFF",
)
for sql in sqls:
self.mysql_pool_query(pool, sql)
def get_slave_status(port):
pool = mysqlconnpool.make(
{'unix_socket': '/var/run/mysqld/mysqld-{p}.sock'.format(p=port)})
try:
slave_status = pool.query('show slave status')
except (MySQLdb.OperationalError, MySQLdb.InternalError):
return None
return slave_status
def __init__(self, bkp_conf):
self.bkp_conf = self.extend_backup_conf(bkp_conf)
self.mysql_addr = {
'unix_socket': self.bkp_conf['mysql_socket'],
'user': '******'
}
if 'root_password' in self.bkp_conf:
self.mysql_addr.update({
'passwd': self.bkp_conf['root_password'],
})
self.mysql_conn_pool = mysqlconnpool.make(self.mysql_addr)
def optimize_tables(self, db=None):
if db is None:
db = self.get_dbs()[0]
addr = {}
addr.update(self.mysql_addr)
addr.update({
'db': db,
})
pool = mysqlconnpool.make(addr)
rsts = pool.query('show table status')
for rst in rsts:
self._rst_to_number(rst)
msg = self._table_status_str(rst)
need_optimize = False
if rst['Data_free'] > 10 * (1024**3):
self.info(msg + ' Need optimize, Data_free > 10G')
need_optimize = True
if float(rst['Data_free']) / float(rst['Data_length']) > 0.5:
self.info(msg + ' Need optimize, Data_free/Data_length > 0.5')
need_optimize = True
if need_optimize:
_rsts = pool.query(
'optimize local table `{Name}`'.format(**rst))
self.info('optimize done')
for _r in _rsts:
self.info(str(_r))
else:
self.info(msg + ' Do not need optimize')
rsts = pool.query('show table status')
self.info('After optimize:')
for rst in rsts:
self._rst_to_number(rst)
msg = self._table_status_str(rst)
self.info(msg)
def get_dbs(self, exclude=None):
addr = {}
addr.update(self.mysql_addr)
if exclude is None:
exclude = (
'information_schema',
'mysql',
'performance_schema',
'sys',
)
pool = mysqlconnpool.make(addr)
rsts = pool.query('show databases')
dbs = [x['Database'] for x in rsts if x['Database'] not in exclude]
return dbs
def setUp(self):
utdocker.create_network()
utdocker.start_container(mysql_test_name,
mysql_test_tag,
ip=mysql_test_ip,
env={
'MYSQL_ROOT_PASSWORD':
mysql_test_password,
})
ututil.wait_listening(mysql_test_ip, mysql_test_port)
self.pool = mysqlconnpool.make({
'host': mysql_test_ip,
'port': mysql_test_port,
'user': mysql_test_user,
'passwd': mysql_test_password,
})
def setUp(self):
utdocker.create_network()
utdocker.start_container(
mysql_test_name,
mysql_test_tag,
ip=mysql_test_ip,
env={
'MYSQL_ROOT_PASSWORD': mysql_test_password,
}
)
ututil.wait_listening(mysql_test_ip, mysql_test_port)
self.pool = mysqlconnpool.make({
'host': mysql_test_ip,
'port': mysql_test_port,
'user': mysql_test_user,
'passwd': mysql_test_password,
})
def test_scan_index(self):
addr = {
'host': mysql_test_ip,
'port': mysql_test_port,
'user': mysql_test_user,
'passwd': mysql_test_password,
}
conns = (
addr,
mysqlconnpool.make(addr),
)
table = ('test', 'errlog')
result_fields = ['_id']
cases = (
(
[['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']],
{},
('8', '12', '18', '20', '32', '2', '3', '13', '19', '27', '30',
'11', '22', '29', '31', '14', '28', '4', '5', '9', '24', '6',
'15', '21', '23', '25', '26', '10', '16', '7', '17'),
'test common',
),
(
[['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']],
{
'left_open': True
},
('12', '18', '20', '32', '2', '3', '13', '19', '27', '30',
'11', '22', '29', '31', '14', '28', '4', '5', '9', '24', '6',
'15', '21', '23', '25', '26', '10', '16', '7', '17'),
'test left_open',
),
(
[['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']],
{
'limit': 3
},
(
'8',
'12',
'18',
),
'test limit',
),
(
[['autolvl', 'service', 'ip', '_id'],
['stable', 'common0', '127.0.0.1', '8']],
{
'index_name': 'idx_service_ip__id'
},
('12', '32', '2', '13', '19', '30', '22', '28', '6', '15',
'21', '7'),
'test index_name',
),
(
[['service', 'ip', '_id'], ['common0', '127.0.0.1', 8]],
{
'left_open': True,
'limit': 3,
'index_name': 'idx_time__id'
},
(
'12',
'18',
'20',
),
'test all kwargs',
),
)
for conn in conns:
dd('conn: ', conn)
for args, kwargs, rst_expect, msg in cases:
args = [conn, table, result_fields] + args
kwargs['use_dict'] = False
dd('msg: ', msg)
rst = mysqlutil.scan_index(*args, **kwargs)
for i, rr in enumerate(rst):
dd('rst:', rr)
dd('except: ', rst_expect[i])
self.assertEqual(rr[0], long(rst_expect[i]))
self.assertEqual(len(rst_expect), i + 1)
error_cases = (
(
[
addr, table, result_fields, ['service', 'ip', '_id'],
['common0', '127.0.0.2']
],
{},
mysqlutil.InvalidLength,
'test index_fields amount greater than index_values',
),
(
[
addr, table, result_fields, ['service', 'ip'],
['common0', '127.0.0.2', '13']
],
{},
mysqlutil.InvalidLength,
'test index_fields amount less than index_values',
),
(
[
'addr', table, result_fields, ['service', 'ip', '_id'],
['common0', '127.0.0.1', '8']
],
{},
mysqlutil.ConnectionTypeError,
'test conn type error',
),
)
for args, kwargs, error, msg in error_cases:
dd('msg: ', msg)
try:
rst = mysqlutil.scan_index(*args, **kwargs)
except error as e:
self.assertEqual(type(e), error)
def mysql_query(self, sql):
pool = mysqlconnpool.make(self.mysql_addr)
return self.mysql_pool_query(pool, sql)
def create_user(self,
username,
password,
host='%',
privileges=None,
binlog=True):
# To setup replication, a replicator user should be created on each
# instance, before binlog can be sync-ed.
# But after setting up binlog, the row in mysql.user of the `replicator`
# will be sync-ed and results in conflicting user row.
#
# Thus we might need to disable binlog when creating replication user.
# See: https://dev.mysql.com/doc/refman/5.7/en/group-replication-user-credentials.html
self.info('create or change user:'******' {username}@{host},'
' password: {password}'
' privileges: {privileges}'
' binlog: {binlog}'.format(username=username,
password=password,
host=host,
privileges=privileges,
binlog=binlog))
username = MySQLdb.escape_string(username)
password = MySQLdb.escape_string(password)
host = MySQLdb.escape_string(host)
# privileges is a list of tuple (<table>, <privilege>)
alive = self.is_instance_alive()
proc = None
if not alive:
proc = self.start_tmp_mysql()
try:
pool = mysqlconnpool.make(self.mysql_addr)
if not binlog:
self.mysql_pool_query(pool, 'SET SQL_LOG_BIN=0')
rst = self.mysql_pool_query(
pool, 'SELECT `Host`, `User` FROM `mysql`.`user`'
' WHERE `Host`="{host}" AND `User`="{username}"'.format(
host=host, username=username))
if len(rst) == 0:
self.mysql_pool_query(
pool,
'CREATE USER "{username}"@"{host}" IDENTIFIED BY "{password}"'
.format(host=host, username=username, password=password))
else:
# user exists, just set password
self.mysql_pool_query(
pool,
'SET PASSWORD FOR "{username}"@"{host}" = "{password}"'.
format(host=host, username=username, password=password))
for tbl, prv in privileges:
# `prv` is a key of privilege set defined in
# mysqlutil.privileges, or a tuple of mysql privileges.
# Such as: "monitor", "readwrite", or just ("REPLICATION SLAVE")
#
# Convert it to tuple.
if isinstance(prv, basestring):
prv = mysqlutil.privileges[prv]
prv = ','.join(prv)
self.mysql_pool_query(
pool,
'GRANT {prv} ON {tbl} TO "{username}"@"{host}"'.format(
host=host, username=username, prv=prv, tbl=tbl))
self.mysql_pool_query(pool, 'FLUSH PRIVILEGES')
if not binlog:
self.mysql_pool_query(pool, 'SET SQL_LOG_BIN=1')
finally:
if not alive:
self.stop_tmp_mysql(proc)
def scan_index(connpool,
table,
result_fields,
index_fields,
index_values,
left_open=False,
limit=None,
index_name=None,
use_dict=True,
retry=0):
if type(connpool) == dict:
# an address
pool = mysqlconnpool.make(connpool)
elif isinstance(connpool, mysqlconnpool.MysqlConnectionPool):
pool = connpool
else:
raise ConnectionTypeError
if len(index_values) != len(index_fields):
raise InvalidLength('number of index fields and values are not equal: '
'index fields: {fld}; '
'index values: {val}'.format(fld=index_fields,
val=index_values))
req_fields = list(index_fields)
req_values = list(index_values)
strict = True
if limit is None:
strict = False
limit = 1024
while True:
sql = make_index_scan_sql(table,
None,
req_fields,
req_values,
left_open=left_open,
limit=limit,
index_name=index_name)
rst = pool.query(sql, retry=retry)
for rr in rst:
if use_dict:
yield dict([(k, rr[k]) for k in result_fields])
else:
yield [rr[k] for k in result_fields]
if strict:
break
if len(rst) > 0:
last_row = rst[-1]
req_fields = list(index_fields)
req_values = [str(last_row[x]) for x in req_fields]
left_open = True
continue
req_fields = req_fields[:-1]
req_values = req_values[:-1]
if len(req_fields) > 0:
continue
break
def test_scan_index(self):
addr = {
'host': base.mysql_test_ip,
'port': base.mysql_test_port,
'user': mysql_test_user,
'passwd': base.mysql_test_password,
}
conns = (addr,
mysqlconnpool.make(addr),
)
table = ('test', 'errlog')
result_fields = ['_id']
cases = (
([['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']],
{},
('8', '12', '18', '20', '32', '2', '3', '13', '19', '27', '30', '11', '22', '29', '31', '14',
'28', '4', '5', '9', '24', '6', '15', '21', '23', '25', '26', '10', '16', '7', '17'),
'test common',
),
([['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']],
{'left_open': True},
('12', '18', '20', '32', '2', '3', '13', '19', '27', '30', '11', '22', '29', '31', '14',
'28', '4', '5', '9', '24', '6', '15', '21', '23', '25', '26', '10', '16', '7', '17'),
'test left_open',
),
([['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']],
{'limit': 3},
('8', '12', '18', ),
'test limit',
),
([['autolvl', 'service', 'ip', '_id'], ['stable', 'common0', '127.0.0.1', '8']],
{'index_name': 'idx_service_ip__id'},
('12', '32', '2', '13', '19', '30', '22', '28', '6', '15', '21', '7'),
'test index_name',
),
([['service', 'ip', '_id'], ['common0', '127.0.0.1', 8]],
{'left_open': True, 'limit': 3, 'index_name': 'idx_time__id'},
('12', '18', '20', ),
'test all kwargs',
),
)
for conn in conns:
dd('conn: ', conn)
for args, kwargs, rst_expect, msg in cases:
args = [conn, table, result_fields] + args
kwargs['use_dict'] = False
dd('msg: ', msg)
rst = mysqlutil.scan_index(*args, **kwargs)
for i, rr in enumerate(rst):
dd('rst:', rr)
dd('except: ', rst_expect[i])
self.assertEqual(rr[0], long(rst_expect[i]))
self.assertEqual(len(rst_expect), i+1)
error_cases = (
([addr, table, result_fields, ['service', 'ip', '_id'], ['common0', '127.0.0.2']],
{},
mysqlutil.InvalidLength,
'test index_fields amount greater than index_values',
),
([addr, table, result_fields, ['service', 'ip'], ['common0', '127.0.0.2', '13']],
{},
mysqlutil.InvalidLength,
'test index_fields amount less than index_values',
),
(['addr', table, result_fields, ['service', 'ip', '_id'], ['common0', '127.0.0.1', '8']],
{},
mysqlutil.ConnectionTypeError,
'test conn type error',
),
)
for args, kwargs, error, msg in error_cases:
dd('msg: ', msg)
try:
rst = mysqlutil.scan_index(*args, **kwargs)
except error as e:
self.assertEqual(type(e), error)
def test_setup_user(self):
# test grant
cases = (
(
"'foo'@'127.0.0.1'",
[{"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:INSERT", "state": "present"}],
({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"},
{'Grants for [email protected]': "GRANT INSERT ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}),
),
(
"'foo'@'127.0.0.1'",
[{"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:INSERT,UPDATE"}],
({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"},
{'Grants for [email protected]': "GRANT INSERT, UPDATE ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}),
),
(
"'foo'@'127.0.0.1'",
[{"name": 'foo', "host": "127.0.0.1", "priv": {"test.errlog": ['INSERT']}}],
({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"},
{'Grants for [email protected]': "GRANT INSERT ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}),
),
(
"'foo'@'127.0.0.1'",
[{"name": 'foo', "host": "127.0.0.1", "priv": {"test.errlog": ['INSERT', "SELECT"]}}],
({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"},
{'Grants for [email protected]': "GRANT SELECT, INSERT ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}),
),
(
"'foo'@'127.0.0.1'",
[{"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:INSERT"},
{"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:UPDATE"}],
({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"},
{'Grants for [email protected]': "GRANT INSERT, UPDATE ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}),
),
(
"'foo'@'127.0.0.1'",
[{"name": 'foo', "host": "127.0.0.1", "priv": {"test.errlog": ['INSERT', "SELECT"]}},
{"name": 'foo', "host": "127.0.0.1", "priv": {"test.errlog": ['UPDATE']}}],
({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"},
{'Grants for [email protected]': "GRANT SELECT, INSERT, UPDATE ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}),
),
(
"'foo'@'127.0.0.1'",
[{"name": 'foo', "host": "127.0.0.1", "priv": "test.errlog:INSERT,UPDATE"},
{"name": 'foo', "host": "127.0.0.1", "priv": "*.*:SELECT"}],
({'Grants for [email protected]': "GRANT SELECT ON *.* TO 'foo'@'127.0.0.1'"},
{'Grants for [email protected]': "GRANT INSERT, UPDATE ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}),
),
(
"foo",
[{"name": 'foo', "host": "%", "priv": "test.errlog:INSERT", "state": "present"}],
({'Grants for foo@%': "GRANT USAGE ON *.* TO 'foo'@'%'"},
{'Grants for foo@%': "GRANT INSERT ON `test`.`errlog` TO 'foo'@'%'"}),
),
(
"foo",
[{"name": 'foo', "priv": "test.errlog:INSERT"}],
({'Grants for foo@%': "GRANT USAGE ON *.* TO 'foo'@'%'"},
{'Grants for foo@%': "GRANT INSERT ON `test`.`errlog` TO 'foo'@'%'"}),
),
(
"'foo'@'127.0.0.1'",
[{"name": 'foo', "host": "127.0.0.1", "priv": {('test', 'errlog'): ['INSERT', "SELECT"]}}],
({'Grants for [email protected]': "GRANT USAGE ON *.* TO 'foo'@'127.0.0.1'"},
{'Grants for [email protected]': "GRANT SELECT, INSERT ON `test`.`errlog` TO 'foo'@'127.0.0.1'"}),
),
)
for name, c, expected in cases:
mysqlutil.setup_user(self.addr, c)
pri = mysqlconnpool.conn_query(self.conn, 'SHOW GRANTS FOR ' + name)
self.assertEqual(expected, pri)
mysqlconnpool.conn_query(self.conn, 'DROP USER ' + name)
# test revoke
cases = (
[{"name": 'foo', "priv": "test.errlog:INSERT", 'state': 'absent'}],
[{"name": 'foo', "priv": {"test.errlog": ["INSERT"]}, 'state': 'absent'}],
[{"name": 'foo', "priv": {("test", "errlog"): ["INSERT"]}, 'state': 'absent'}],
)
pris = [{"name": 'foo', "priv": "test.errlog:SELECT,INSERT,UPDATE"}]
for c in cases:
mysqlutil.setup_user(self.conn, pris)
mysqlutil.setup_user(self.conn, c)
res = mysqlconnpool.conn_query(self.conn, 'SHOW GRANTS FOR foo')
expected = ({'Grants for foo@%': "GRANT USAGE ON *.* TO 'foo'@'%'"},
{'Grants for foo@%': "GRANT SELECT, UPDATE ON `test`.`errlog` TO 'foo'@'%'"})
self.assertEqual(expected, res)
mysqlconnpool.conn_query(self.conn, 'DROP USER foo')
# test password
pris = [{"name": 'foo', 'host': '192.168.52.254', 'password': '******', "priv": "test.errlog:SELECT"}]
mysqlutil.setup_user(self.conn_pool, pris)
addr = {
'host': base.mysql_test_ip,
'port': base.mysql_test_port,
'user': '******',
'passwd': 'abcdef',
}
pool = mysqlconnpool.make(addr)
rst = pool.query('SELECT * FROM `test`.`errlog`')
self.assertGreater(len(rst), 0)
# wrong password
addr['passwd'] = '123456'
pool = mysqlconnpool.make(addr)
self.assertRaises(MySQLdb.OperationalError, pool.query, 'SELECT * FROM `test`.`errlog`')
# use another host connect mysql
pris = [{"name": 'bar', 'host': '10.10.10.10', 'password': '******', "priv": "test.errlog:SELECT"}]
mysqlutil.setup_user(self.conn_pool, pris)
addr['passwd'] = 'abcdef'
addr['user'] = '******'
pool = mysqlconnpool.make(addr)
self.assertRaises(MySQLdb.OperationalError, pool.query, 'SELECT * FROM `test`.`errlog`')
# test Exception
self.assertRaises(ValueError, mysqlutil.setup_user, self.addr, [{'state': 'xx'}])
self.assertRaises(ValueError, mysqlutil.setup_user, self.addr, [{'priv': 123}])
self.assertRaises(ValueError, mysqlutil.setup_user, self.addr, [{'priv': 'db.tb'}])
self.assertRaises(ValueError, mysqlutil.setup_user, self.addr, [{'priv': 'db.tb:FOO'}])
