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