def do_special_upgrade(conn, cur, tenant_id_list, user, passwd):
# special upgrade action
#升级语句对应的action要写在下面的actions begin和actions end这两行之间，
#因为基准版本更新的时候会调用reset_upgrade_scripts.py来清空actions begin和actions end
#这两行之间的这些代码，如果不写在这两行之间的话会导致清空不掉相应的代码。
# 主库升级流程没加滚动升级步骤，或混部阶段DDL测试有相关case覆盖前，混部开始禁DDL
actions.set_parameter(cur, 'enable_ddl', 'False')
####========******####======== actions begin ========####******========####
return
def do_normal_ddl_actions(cur):
import normal_ddl_actions_post
cls_list = reflect_action_cls_list(normal_ddl_actions_post,
'NormalDDLActionPost')
# set parameter
if len(cls_list) > 0:
actions.set_parameter(cur, 'enable_sys_table_ddl', 'True')
ori_enable_ddl = actions.get_ori_enable_ddl(cur)
if ori_enable_ddl == 0:
actions.set_parameter(cur, 'enable_ddl', 'True')
for cls in cls_list:
logging.info('do normal ddl acion, seq_num: %d', cls.get_seq_num())
action = cls(cur)
action.dump_before_do_action()
if False == action.skip_action():
action.check_before_do_action()
action.do_action()
else:
logging.info("skip ddl action, seq_num: %d", cls.get_seq_num())
action.dump_after_do_action()
action.check_after_do_action()
# reset parameter
if len(cls_list) > 0:
if ori_enable_ddl == 0:
actions.set_parameter(cur, 'enable_ddl', 'False')
actions.set_parameter(cur, 'enable_sys_table_ddl', 'False')
def do_each_tenant_ddl_actions(cur, tenant_id_list):
import each_tenant_ddl_actions_post
# 组户级系统表没法通过虚拟表暴露，需要根据版本决定查哪张实体表
query_cur = QueryCursor(cur)
version = fetch_observer_version(query_cur)
all_table_name = "__all_table"
if (cmp(version, "2.2.60") >= 0):
all_table_name = "__all_table_v2"
cls_list = reflect_action_cls_list(each_tenant_ddl_actions_post,
'EachTenantDDLActionPost')
# set parameter
if len(cls_list) > 0:
actions.set_parameter(cur, 'enable_sys_table_ddl', 'True')
ori_enable_ddl = actions.get_ori_enable_ddl(cur)
if ori_enable_ddl == 0:
actions.set_parameter(cur, 'enable_ddl', 'True')
for cls in cls_list:
logging.info('do each tenant ddl acion, seq_num: %d',
cls.get_seq_num())
action = cls(cur, tenant_id_list)
action.set_all_table_name(all_table_name)
action.dump_before_do_action()
if False == action.skip_pre_check():
action.check_before_do_action()
else:
logging.info("skip pre check. seq_num: %d", cls.get_seq_num())
# 系统租户组户级系统表创建成功会覆盖普通租户系统表，所以系统租户要最后建表
for tenant_id in action.get_tenant_id_list():
action.dump_before_do_each_tenant_action(tenant_id)
if False == action.skip_each_tenant_action(tenant_id):
action.check_before_do_each_tenant_action(tenant_id)
action.do_each_tenant_action(tenant_id)
else:
logging.info(
"skip each tenant ddl action, seq_num: %d, tenant_id: %d",
cls.get_seq_num(), tenant_id)
action.dump_after_do_each_tenant_action(tenant_id)
action.check_after_do_each_tenant_action(tenant_id)
action.dump_after_do_action()
action.check_after_do_action()
# reset parameter
if len(cls_list) > 0:
if ori_enable_ddl == 0:
actions.set_parameter(cur, 'enable_ddl', 'False')
actions.set_parameter(cur, 'enable_sys_table_ddl', 'False')
def run_upgrade_job(conn, cur, version):
try:
logging.info("start to run upgrade job, version:{0}".format(version))
# pre check
if (check_can_run_upgrade_job(cur, version) == True):
conn.autocommit = True
# disable enable_ddl
ori_enable_ddl = actions.get_ori_enable_ddl(cur)
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'False')
# get max_used_job_id
max_used_job_id = get_max_used_job_id(cur)
# run upgrade job
sql = """alter system run upgrade job '{0}'""".format(version)
logging.info(sql)
cur.execute(sql)
# check upgrade job result
check_upgrade_job_result(cur, version, max_used_job_id)
# reset enable_ddl
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'True')
except Exception, e:
logging.warn("run upgrade job failed, version:{0}".format(version))
raise e
def do_normal_ddl_actions(cur):
import normal_ddl_actions_pre
upgrade_params = UpgradeParams()
cls_list = reflect_action_cls_list(normal_ddl_actions_pre,
'NormalDDLActionPre')
# check if pre upgrade script can run reentrantly
query_cur = QueryCursor(cur)
version = fetch_observer_version(query_cur)
can_skip = False
if (cmp(version, "2.2.77") >= 0 and cmp(version, "3.0.0") < 0):
can_skip = True
elif (cmp(version, "3.1.1") >= 0):
can_skip = True
else:
can_skip = False
# set parameter
if len(cls_list) > 0:
actions.set_parameter(cur, 'enable_sys_table_ddl', 'True')
ori_enable_ddl = actions.get_ori_enable_ddl(cur)
if ori_enable_ddl == 0:
actions.set_parameter(cur, 'enable_ddl', 'True')
for cls in cls_list:
logging.info('do normal ddl acion, seq_num: %d', cls.get_seq_num())
action = cls(cur)
action.dump_before_do_action()
if False == can_skip or False == action.skip_action():
action.check_before_do_action()
action.do_action()
else:
logging.info("skip ddl action, seq_num: %d", cls.get_seq_num())
action.dump_after_do_action()
action.check_after_do_action()
# reset parameter
if len(cls_list) > 0:
if ori_enable_ddl == 0:
actions.set_parameter(cur, 'enable_ddl', 'False')
actions.set_parameter(cur, 'enable_sys_table_ddl', 'False')
def modify_foreign_key_column_position_info(conn, cur):
try:
conn.autocommit = True
# disable ddl
ori_enable_ddl = actions.get_ori_enable_ddl(cur)
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'False')
tenant_ids = get_tenant_ids(cur)
log('tenant_ids: {0}'.format(tenant_ids))
for tenant_id in tenant_ids:
sql = """alter system change tenant tenant_id = {0}""".format(
tenant_id)
log(sql)
cur.execute(sql)
tenant_id_in_sql = 0
if 1 == tenant_id:
tenant_id_in_sql = 1
# 查出租户下所有未被删除的外键
sql = """select /*+ QUERY_TIMEOUT(1500000000) */ foreign_key_id from oceanbase.__all_foreign_key where tenant_id = {0}""".format(
tenant_id_in_sql)
log(sql)
foreign_key_id_rows = query(cur, sql)
fk_num = len(foreign_key_id_rows)
cnt = 0
# 遍历每个外键，检查 oceanbase.__all_foreign_key_column 中记录的 position 信息是否为 0，如果为 0，需要更新为正确的值
while cnt < fk_num:
foreign_key_id = foreign_key_id_rows[cnt][0]
sql = """select /*+ QUERY_TIMEOUT(1500000000) */ child_column_id, parent_column_id from oceanbase.__all_foreign_key_column where foreign_key_id = {0} and position = 0 and tenant_id = {1} order by gmt_create asc""".format(
foreign_key_id, tenant_id_in_sql)
log(sql)
need_update_rows = query(cur, sql)
fk_col_num = len(need_update_rows)
if fk_col_num > 0:
position = 1
# 遍历特定外键里的每个 position 信息为 0 的列
while position <= fk_col_num:
child_column_id = need_update_rows[position - 1][0]
parent_column_id = need_update_rows[position - 1][1]
# 在 oceanbase.__all_foreign_key_column_history 里面更新 position 的值
sql = """update /*+ QUERY_TIMEOUT(150000000) */ oceanbase.__all_foreign_key_column_history set position = {0} where foreign_key_id = {1} and child_column_id = {2} and parent_column_id = {3} and tenant_id = {4}""".format(
position, foreign_key_id, child_column_id,
parent_column_id, tenant_id_in_sql)
log(sql)
cur.execute(sql)
if cur.rowcount == 0:
logging.warn(
"affected rows is 0 when update oceanbase.__all_foreign_key_column_history"
)
raise e
# 在 oceanbase.__all_foreign_key_column 里面更新 position 的值
sql = """update /*+ QUERY_TIMEOUT(150000000) */ oceanbase.__all_foreign_key_column set position = {0} where foreign_key_id = {1} and child_column_id = {2} and parent_column_id = {3} and tenant_id = {4}""".format(
position, foreign_key_id, child_column_id,
parent_column_id, tenant_id_in_sql)
log(sql)
cur.execute(sql)
if cur.rowcount != 1:
logging.warn(
"affected rows is not 1 when update oceanbase.__all_foreign_key_column"
)
raise e
position = position + 1
cnt = cnt + 1
# 还原默认 tenant
sys_tenant_id = 1
sql = """alter system change tenant tenant_id = {0}""".format(
sys_tenant_id)
log(sql)
cur.execute(sql)
# enable ddl
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'True')
except Exception, e:
logging.warn("modify foreign key column position failed")
raise e
def fill_priv_file_column_for_all_user(conn, cur):
try:
conn.autocommit = True
# disable ddl
ori_enable_ddl = actions.get_ori_enable_ddl(cur)
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'False')
tenant_ids = get_tenant_ids(cur)
log('tenant_ids: {0}'.format(tenant_ids))
for tenant_id in tenant_ids:
sql = """alter system change tenant tenant_id = {0}""".format(
tenant_id)
log(sql)
cur.execute(sql)
tenant_id_in_sql = 0
if 1 == tenant_id:
tenant_id_in_sql = 1
begin_user_id = 0
begin_schema_version = 0
fetch_num = 1000
while (True):
query_limit = """
where tenant_id = {0} and (user_id, schema_version) > ({1}, {2})
order by tenant_id, user_id, schema_version
limit {3}""".format(tenant_id_in_sql, begin_user_id,
begin_schema_version, fetch_num)
sql = """select /*+ QUERY_TIMEOUT(1500000000) */ user_id, schema_version
from oceanbase.__all_user_history""" + query_limit
log(sql)
result_rows = query(cur, sql)
log("select rows = " + str(cur.rowcount))
if len(result_rows) <= 0:
break
else:
last_schema_version = result_rows[-1][1]
last_user_id = result_rows[-1][0]
condition = """
where priv_alter = 1 and priv_create = 1 and priv_create_user = 1 and priv_delete = 1
and priv_drop = 1 and priv_insert = 1 and priv_update = 1 and priv_select = 1
and priv_index = 1 and priv_create_view = 1 and priv_show_view = 1 and priv_show_db = 1
and priv_super = 1 and priv_create_synonym = 1
and tenant_id = {0} and (user_id, schema_version) > ({1}, {2})
and (user_id, schema_version) <= ({3}, {4})
""".format(tenant_id_in_sql, begin_user_id,
begin_schema_version, last_user_id,
last_schema_version)
sql = """update /*+ QUERY_TIMEOUT(150000000) */ oceanbase.__all_user_history
set priv_file = 1""" + condition
log(sql)
cur.execute(sql)
log("update rows = " + str(cur.rowcount))
condition = """
where priv_super = 1 and tenant_id = {0} and (user_id, schema_version) > ({1}, {2})
and (user_id, schema_version) <= ({3}, {4})
""".format(tenant_id_in_sql, begin_user_id,
begin_schema_version, last_user_id,
last_schema_version)
sql = """update /*+ QUERY_TIMEOUT(150000000) */ oceanbase.__all_user_history
set priv_alter_tenant = 1,
priv_alter_system = 1,
priv_create_resource_unit = 1,
priv_create_resource_pool = 1 """ + condition
log(sql)
cur.execute(sql)
begin_schema_version = last_schema_version
begin_user_id = last_user_id
begin_user_id = 0
while (True):
query_limit = """
where tenant_id = {0} and user_id > {1}
order by tenant_id, user_id
limit {2}""".format(tenant_id_in_sql, begin_user_id,
fetch_num)
sql = """select /*+ QUERY_TIMEOUT(1500000000) */ user_id
from oceanbase.__all_user""" + query_limit
log(sql)
result_rows = query(cur, sql)
log("select rows = " + str(cur.rowcount))
if len(result_rows) <= 0:
break
else:
end_user_id = result_rows[-1][0]
condition = """
where priv_alter = 1 and priv_create = 1 and priv_create_user = 1 and priv_delete = 1
and priv_drop = 1 and priv_insert = 1 and priv_update = 1 and priv_select = 1
and priv_index = 1 and priv_create_view = 1 and priv_show_view = 1 and priv_show_db = 1
and priv_super = 1 and priv_create_synonym = 1
and tenant_id = {0} and user_id > {1} and user_id <= {2}
""".format(tenant_id_in_sql, begin_user_id, end_user_id)
sql = """update /*+ QUERY_TIMEOUT(150000000) */ oceanbase.__all_user
set priv_file = 1 """ + condition
log(sql)
cur.execute(sql)
log("update rows = " + str(cur.rowcount))
condition = """
where priv_super = 1
and tenant_id = {0} and user_id > {1} and user_id <= {2}
""".format(tenant_id_in_sql, begin_user_id, end_user_id)
sql = """update /*+ QUERY_TIMEOUT(150000000) */ oceanbase.__all_user
set priv_alter_tenant = 1,
priv_alter_system = 1,
priv_create_resource_unit = 1,
priv_create_resource_pool = 1 """ + condition
log(sql)
cur.execute(sql)
begin_user_id = end_user_id
# 还原默认 tenant
sys_tenant_id = 1
sql = """alter system change tenant tenant_id = {0}""".format(
sys_tenant_id)
log(sql)
cur.execute(sql)
# enable ddl
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'True')
except Exception, e:
logging.warn("exec fill priv_file to all_user failed")
raise e
def modify_trigger(conn, cur, tenant_ids):
try:
conn.autocommit = True
# disable ddl
ori_enable_ddl = actions.get_ori_enable_ddl(cur)
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'False')
log('tenant_ids: {0}'.format(tenant_ids))
for tenant_id in tenant_ids:
sql = """alter system change tenant tenant_id = {0}""".format(
tenant_id)
log(sql)
cur.execute(sql)
#####implement#####
trigger_sql = """
update __all_tenant_trigger
set
package_spec_source = replace(
package_spec_source,
'FUNCTION UPDATING(column VARCHAR2 := NULL) RETURN BOOL\;',
'FUNCTION UPDATING(column_name VARCHAR2 := NULL) RETURN BOOL\;'
),
package_body_source = replace(replace(
package_body_source,
'
PROCEDURE init_trigger(update_columns IN STRINGARRAY) IS
BEGIN
NULL\;
END\;
',
'
PROCEDURE init_trigger(update_columns IN STRINGARRAY) IS
BEGIN
update_columns_ := STRINGARRAY()\;
update_columns_.EXTEND(update_columns.COUNT)\;
FOR i IN 1 .. update_columns.COUNT LOOP
update_columns_(i) := update_columns(i)\;
END LOOP\;
END\;
'),
'
FUNCTION UPDATING(column VARCHAR2 := NULL) RETURN BOOL IS
BEGIN
RETURN (dml_event_ = 4)\;
END\;
',
'
FUNCTION UPDATING(column_name VARCHAR2 := NULL) RETURN BOOL IS
is_updating BOOL\;
BEGIN
is_updating := (dml_event_ = 4)\;
IF (is_updating AND column_name IS NOT NULL) THEN
is_updating := FALSE\;
FOR i IN 1 .. update_columns_.COUNT LOOP
IF (UPPER(update_columns_(i)) = UPPER(column_name)) THEN is_updating := TRUE\; EXIT\; END IF\;
END LOOP\;
END IF\;
RETURN is_updating\;
END\;
'); """
log(trigger_sql)
cur.execute(trigger_sql)
log("update rows = " + str(cur.rowcount))
trigger_history_sql = """
update __all_tenant_trigger_history
set
package_spec_source = replace(
package_spec_source,
'FUNCTION UPDATING(column VARCHAR2 := NULL) RETURN BOOL\;',
'FUNCTION UPDATING(column_name VARCHAR2 := NULL) RETURN BOOL\;'
),
package_body_source = replace(replace(
package_body_source,
'
PROCEDURE init_trigger(update_columns IN STRINGARRAY) IS
BEGIN
NULL\;
END\;
',
'
PROCEDURE init_trigger(update_columns IN STRINGARRAY) IS
BEGIN
update_columns_ := STRINGARRAY()\;
update_columns_.EXTEND(update_columns.COUNT)\;
FOR i IN 1 .. update_columns.COUNT LOOP
update_columns_(i) := update_columns(i)\;
END LOOP\;
END\;
'),
'
FUNCTION UPDATING(column VARCHAR2 := NULL) RETURN BOOL IS
BEGIN
RETURN (dml_event_ = 4)\;
END\;
',
'
FUNCTION UPDATING(column_name VARCHAR2 := NULL) RETURN BOOL IS
is_updating BOOL\;
BEGIN
is_updating := (dml_event_ = 4)\;
IF (is_updating AND column_name IS NOT NULL) THEN
is_updating := FALSE\;
FOR i IN 1 .. update_columns_.COUNT LOOP
IF (UPPER(update_columns_(i)) = UPPER(column_name)) THEN is_updating := TRUE\; EXIT\; END IF\;
END LOOP\;
END IF\;
RETURN is_updating\;
END\;
')
where is_deleted = 0; """
log(trigger_history_sql)
cur.execute(trigger_history_sql)
log("update rows = " + str(cur.rowcount))
#####implement end#####
# 还原默认 tenant
sys_tenant_id = 1
sql = """alter system change tenant tenant_id = {0}""".format(
sys_tenant_id)
log(sql)
cur.execute(sql)
# enable ddl
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'True')
except Exception, e:
logging.warn("exec modify trigger failed")
raise e
def disable_major_freeze(conn, cur):
try:
actions.set_parameter(cur, "enable_major_freeze", 'False')
except Exception, e:
logging.warn("disable enable_major_freeze failed")
raise e
def statistic_primary_zone_count(conn, cur):
try:
###### disable ddl
ori_enable_ddl = actions.get_ori_enable_ddl(cur)
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'False')
# check record in rs_job
count_sql = """select count(*) from oceanbase.__all_rootservice_job
where job_type = 'STATISTIC_PRIMARY_ZONE_ENTITY_COUNT';"""
result = query(cur, count_sql)
job_count = 0
if (1 != len(result)) :
logging.warn("unexpected sql output")
raise e
else :
job_count = result[0][0]
# run job
sql = "alter system run job 'STATISTIC_PRIMARY_ZONE_ENTITY_COUNT';"
logging.info(sql)
cur.execute(sql)
# wait job finish
times = 180
## 先检查job count变化
count_check = False
while times > 0:
result = query(cur, count_sql)
if (1 != len(result)):
logging.warn("unexpected sql output")
raise e
elif (result[0][0] > job_count):
count_check = True
logging.info('statistic_primary_zone_entity_count job detected')
break
time.sleep(10)
times -= 1
if times == 0:
raise MyError('statistic_primary_zone_entity_count job failed!')
## 继续检查job status状态
status_sql = """select job_status from oceanbase.__all_rootservice_job
where job_type = 'STATISTIC_PRIMARY_ZONE_ENTITY_COUNT' order by job_id desc limit 1;"""
status_check = False
while times > 0 and count_check == True:
result = query(cur, status_sql)
if (0 == len(result)):
logging.warn("unexpected sql output")
raise e
elif (1 != len(result) or 1 != len(result[0])):
logging.warn("result len not match")
raise e
elif result[0][0] == "FAILED":
logging.warn("run statistic_primary_zone_entity_count job faild")
raise e
elif result[0][0] == "INPROGRESS":
logging.info('statistic_primary_zone_entity_count job is still running')
elif result[0][0] == "SUCCESS":
status_check = True
break;
else:
logging.warn("invalid result: {0}" % (result[0][0]))
raise e
time.sleep(10)
times -= 1
if times == 0:
raise MyError('check statistic_primary_zone_entity_count job failed!')
if (status_check == True and count_check == True):
logging.info('check statistic_primary_zone_entity_count job success')
else:
logging.warn("run statistic_primary_zone_entity_count job faild")
raise e
# enable ddl
if ori_enable_ddl == 1:
actions.set_parameter(cur, 'enable_ddl', 'True')
except Exception, e:
logging.warn("run statistic_primary_zone_entity_count job failed")
raise e