def createConn(context, num, sql):
num = int(num)
global errs
errs = []
if sql:
exec_sql = sql
else:
exec_sql = "insert into test_table values(1)"
try:
context.logger.info(
"create {0} conn begin:##########################".format(num))
conn = get_dble_conn(context)
conn.autocommit(0)
res, err = conn.query(exec_sql)
if err:
context.logger.info(
"create {0} conn fail!:##########################".format(num))
errs.append(err)
time.sleep(5)
conn.commit()
conn.close()
except MySQLdb.Error, e:
conn.close()
if e.args:
context.logger.info(
"get error{0}:##########################".format(e.args))
def test_schema_permission(context):
dble_conn = get_dble_conn(context)
for row in context.table:
# prepare table
test_conn = DBUtil(context.cfg_dble['dble']['ip'], row['user'],
row['password'], row['schema'],
context.cfg_dble['client_port'], context)
sql = "drop table if exists {0}".format(row['table'])
test_conn.query(sql)
sql = "create table {0}(id int, data varchar(10))".format(row['table'])
test_conn.query(sql)
sql = "insert into {0} values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd')".format(
row['table'])
dble_conn.query(sql)
# prepare sql
value = row['dml']
LOGGER.info("dml:{0}".format(value))
del_sql = "delete from {0} where id = 1".format(row['table'])
sel_sql = "select * from {0}".format(row['table'])
upd_sql = "update {0} set data='AAA' where id = 1".format(row['table'])
ins_sql = "insert into {0} values (5, 'eee')".format(row['table'])
lack_priv_msg = "The statement DML privilege check is not passed"
sqls_in_priv_order = [ins_sql, upd_sql, sel_sql, del_sql]
# do test
for priv, sql in zip(value, sqls_in_priv_order):
res, errs = test_conn.query(sql)
if priv == "1":
assert_that(errs, is_(None))
else:
assert_that(errs[1], contains_string(lack_priv_msg))
test_conn.close()
dble_conn.close()
def step_impl(context):
for row in context.table:
sql = row["query"]
conn = get_dble_conn(context)
res, err = conn.query(sql)
assert len(res) == int(
row["expect_result_count"]
), "query: {0}'s execute result seems not as except, real result:{1}".format(
sql, res)
def insertOneRow(context, table, rowsPerThread):
conn = get_dble_conn(context)
sql = "insert into {0} values('abc')".format(table)
context.logger.debug("execute query in subthread")
for i in range(rowsPerThread):
res, errMsg = conn.query(sql)
assert errMsg is None, "expect '{0}' success, but get: {1}".format(
sql, errMsg)
conn.close()
def test_readonly_schema(context):
text = json.loads(context.text)
dble_conn = get_dble_conn(context)
for item in text:
test_conn = DBUtil(context.cfg_dble['dble']['ip'], item['user'],
item['password'], item['schema'],
context.cfg_dble['client_port'], context)
sql = "drop table if exists {0}".format(item['table'])
dble_conn.query(sql)
sql = "create table {0}(id int, data varchar(10))".format(
item['table'])
dble_conn.query(sql)
sql = "insert into {0} values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd')".format(
item['table'])
dble_conn.query(sql)
permission = []
value = int(item['dml'])
LOGGER.info("dml: {0}".format(value / 1))
for i in range(4):
if value % 10 == 1:
if i == 0:
permission.append("DELETE")
if i == 1:
permission.append("SELECT")
if i == 2:
permission.append("UPDATE")
if i == 3:
permission.append("INSERT")
value = value / 10
LOGGER.info("permission: {0}".format(permission))
del_sql = "delete from {0} where id = 1".format(item['table'])
sel_sql = "select * from {0}".format(item['table'])
upd_sql = "update {0} set data='AAA' where id = 1".format(
item['table'])
ins_sql = "insert into {0} values (5, 'eee')".format(item['table'])
if "DELETE" in permission:
res, errs = test_conn.query(del_sql)
assert_that(errs[1], contains_string('User READ ONLY'))
if "SELECT" in permission:
res, errs = test_conn.query(sel_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(sel_sql)
assert_that(
errs[1],
contains_string(
'The statement DML privilege check is not passed'))
if "UPDATE" in permission:
res, errs = test_conn.query(upd_sql)
assert_that(errs[1], contains_string('User READ ONLY'))
if "INSERT" in permission:
res, errs = test_conn.query(ins_sql)
assert_that(errs[1], contains_string('User READ ONLY'))
test_conn.close()
dble_conn.close()
def get_compare_conn(context, default_db="schema1"):
m_ip = context.cfg_mysql['compare_mysql']['master1']['ip']
m_port = context.cfg_mysql['compare_mysql']['master1']['port']
m_user = context.cfg_mysql['user']
m_passwd = context.cfg_mysql['password']
conn_mysql = DBUtil(m_ip, m_user, m_passwd, default_db, m_port, context)
conn_dble = get_dble_conn(context, default_db)
return conn_mysql, conn_dble
def get_compare_conn(context, default_db="schema1"):
node = get_node("mysql")
m_ip = node.ip
m_port = node.mysql_port
m_user = node.mysql_user
m_passwd = node.mysql_password
conn_mysql = DBUtil(m_ip, m_user, m_passwd, default_db, m_port, context)
conn_dble = get_dble_conn(context, default_db)
return conn_mysql, conn_dble
def test_use_limit(context):
map = eval(context.text)
tb = map["table"]
t_key = map["key"]
conn = get_dble_conn(context)
drop_sql = "drop table if exists {0}".format(tb)
conn.query(drop_sql)
gen = generate()
name = gen.rand_string(10)
sql = "create table {0}({1} int, data varchar(10))".format(tb, t_key)
conn.query(sql)
sql = "alter table {0} drop column {1}".format(tb, t_key)
result, errMes = conn.query(sql)
assert_that(
str(errMes[1]),
contains_string(
"The columns may be sharding keys or ER keys, are not allowed to alter sql"
))
sql = "update {0} set {1} = 1".format(tb, t_key)
result, errMes = conn.query(sql)
assert_that(str(errMes[1]),
contains_string("Sharding column can't be updated"))
sql = "insert into {0} (data) values ('{1}')".format(tb, name)
result, errMes = conn.query(sql)
assert_that(
str(errMes[1]),
contains_string(
"bad insert sql, sharding column/joinKey:ID not provided"))
sql = "insert into {0} values (1+1, '{1}')".format(tb, name)
result, errMes = conn.query(sql)
assert_that(str(errMes[1]),
contains_string("Not Supported of Sharding Value EXPR"))
conn.query(drop_sql)
sql = "create table {0}(data varchar(10))".format(tb)
conn.query(sql)
sql = "insert into {0} values ('{1}')".format(tb, name)
result, errMes = conn.query(sql)
assert_that(
str(errMes[1]),
contains_string(
"bad insert sql, sharding column/joinKey:ID not provided"))
conn.query(drop_sql)
def get_result(context, sql):
dble_conn = get_dble_conn(context)
result, error = dble_conn.query(sql)
assert error is None, "execute usersql {0}, get error:{1}".format(
sql, error)
return result
def test_schema_table(context):
text = json.loads(context.text)
dble_conn = get_dble_conn(context)
node = get_node("dble-1")
for item in text:
test_conn = DBUtil(node.ip, item['user'], item['password'], item['schema'],
node.client_port, context)
sql = "drop table if exists {0}".format(item['single_table'])
test_conn.query(sql)
sql = "create table {0}(id int, data varchar(10))".format(item['single_table'])
test_conn.query(sql)
sql = "insert into {0} values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd')".format(item['single_table'])
dble_conn.query(sql)
schema_permission = []
value = int(item['schema_dml'])
LOGGER.info("dml: {0}".format(value / 1))
for i in range(4):
if value % 10 == 1:
if i == 0:
schema_permission.append("DELETE")
if i == 1:
schema_permission.append("SELECT")
if i == 2:
schema_permission.append("UPDATE")
if i == 3:
schema_permission.append("INSERT")
value = value / 10
LOGGER.info("permission: {0}".format(schema_permission))
del_sql = "delete from {0} where id = 1".format(item['single_table'])
sel_sql = "select * from {0}".format(item['single_table'])
upd_sql = "update {0} set data='AAA' where id = 1".format(item['single_table'])
ins_sql = "insert into {0} values (5, 'eee')".format(item['single_table'])
if "DELETE" in schema_permission:
res, errs = test_conn.query(del_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(del_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "SELECT" in schema_permission:
res, errs = test_conn.query(sel_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(sel_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "UPDATE" in schema_permission:
res, errs = test_conn.query(upd_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(upd_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "INSERT" in schema_permission:
res, errs = test_conn.query(ins_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(ins_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
for conf_table in item['tables_config']['tables']:
sql = "drop table if exists {0}".format(conf_table['table'])
test_conn.query(sql)
sql = "create table {0}(id int, data varchar(10))".format(conf_table['table'])
test_conn.query(sql)
sql = "insert into {0} values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd')".format(conf_table['table'])
dble_conn.query(sql)
table_permission = []
value = int(conf_table['dml'])
for i in range(4):
if value % 10 == 1:
if i == 0:
table_permission.append("DELETE")
if i == 1:
table_permission.append("SELECT")
if i == 2:
table_permission.append("UPDATE")
if i == 3:
table_permission.append("INSERT")
value = value / 10
del_sql = "delete from {0} where id = 1".format(conf_table['table'])
sel_sql = "select * from {0}".format(conf_table['table'])
upd_sql = "update {0} set data='AAA' where id = 1".format(conf_table['table'])
ins_sql = "insert into {0} values (5, 'eee')".format(conf_table['table'])
join_sql = "select a.*,b.* from {0} a,{1} b where a.id = b.id".format(item['single_table'], conf_table['table'])
if "DELETE" in table_permission:
res, errs = test_conn.query(del_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(del_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "SELECT" in table_permission:
res, errs = test_conn.query(sel_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(sel_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "SELECT" not in table_permission or "SELECT" not in schema_permission:
res, errs = test_conn.query(join_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
else:
res, errs = test_conn.query(join_sql)
assert_that(errs, is_(None))
if "UPDATE" in table_permission:
res, errs = test_conn.query(upd_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(upd_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
if "INSERT" in table_permission:
res, errs = test_conn.query(ins_sql)
assert_that(errs, is_(None))
else:
res, errs = test_conn.query(ins_sql)
assert_that(errs[1], contains_string('The statement DML privilege check is not passed'))
sql = "drop table if exists {0}".format(conf_table['table'])
test_conn.query(sql)
test_conn.close()
dble_conn.close()
