执行数据库脚本失败后，再次执行报错[ERR] 4179 - Operation not allowed now

AntTech_5N9HRW · 2024 年6 月 27 日 15:50

【使用环境】生产环境 or 测试环境生产环境
【 OB or 其他组件】OceanBase
【使用版本】 OceabBase_CE 4.2.1.2
【问题描述】执行数据库脚本失败后，再次执行报错[ERR] 4179 - Operation not allowed now。请问下，数据库是不是被锁住了？请问下，这种情况下怎么解锁。如果还想继续执行脚本，怎么操作。
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

旭辉 · 2024 年6 月 27 日 15:52

把脚本及报错截图发下看下

AntTech_5N9HRW · 2024 年6 月 27 日 16:02

delimiter //

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

– 新增TUserLoginInfoNew表

DROP TABLE IF EXISTS TUserLoginInfoNew;

CREATE TABLE TUserLoginInfoNew (

FSerialId int(11) NOT NULL AUTO_INCREMENT COMMENT ‘流号’,

FLinkId int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Link id’,

FUserNo varchar(21) COLLATE utf8mb4_bin NOT NULL COMMENT ‘用户号’,

FUserName varchar(21) COLLATE utf8mb4_bin NOT NULL COMMENT ‘用户名’,

FRightName varchar(21) COLLATE utf8mb4_bin NOT NULL COMMENT ‘权限名称’,

FDevFirmName varchar(21) COLLATE utf8mb4_bin NOT NULL COMMENT ‘终端名称’,

FApplicationNo varchar(31) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘app no’,

FProtocolVersion int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘协议版本’,

FIsForceModifyPassword char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ COMMENT ‘是否强制修改密码’,

FIsDDA char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ COMMENT ‘是否动态认证’,

FLoginTime varchar(41) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘登录时间’,

FLoginIP varchar(41) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘登录ip’,

FLoginPort int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘登录port’,

FLoginInfo varchar(300) COLLATE utf8mb4_bin NOT NULL COMMENT ‘登录信息’,

FServerFlag char(1) COLLATE utf8mb4_bin NOT NULL COMMENT ‘服务器标识’,

FLogoutTime varchar(41) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘登出时间’,

FImportDate varchar(11) COLLATE utf8mb4_bin NOT NULL COMMENT ‘导入日期’,

FServerInfo varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘服务器信息’,

FUUID varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘UUID’,

FLicenseNo varchar(51) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘软件授权号’,

FServerFlagInfo varchar(51) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘服务器标识信息’,

FClientType char(1) COLLATE utf8mb4_bin NOT NULL COMMENT ‘终端类型’,

FServerStartTime varchar(24) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘服务启动时间’,

FLoginErrorCode int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘错误码’,

FOperatorNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘操作人’,

FOperateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘操作时间’,

PRIMARY KEY (FUserNo, FServerFlag, FServerInfo, FLinkId, FServerStartTime),

UNIQUE KEY FSerialId (FSerialId) BLOCK_SIZE 16384 GLOBAL

) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘新用户登录信息表’;

– 新增TAccountCashTransfer表

DROP TABLE IF EXISTS TAccountCashTransfer;

CREATE TABLE TAccountCashTransfer (

FSerialId int(11) NOT NULL AUTO_INCREMENT COMMENT ‘流号’,

FAccountNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘资金账号’,

FRate decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘可调拨资金比例’,

FOperatorNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘操作人’,

FOperateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘操作时间’,

PRIMARY KEY (FAccountNo),

UNIQUE KEY FSerialID (FSerialId) BLOCK_SIZE 16384 LOCAL

) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘资金调拨账号表’;

– Table structure for THisPosition

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

CREATE PROCEDURE EXE_SQL_BY_COND()

BEGIN

IF NOT EXISTS

(select 1 from information_schema.key_column_usage

where table_schema = ‘vxdatabase’

and table_name = ‘THisPosition’ and column_name = ‘FHedgeFlag’)

THEN

    ALTER TABLE `THisPosition` ADD COLUMN `FSettlePrice` decimal(38,8) NOT NULL DEFAULT '0.00000000' COMMENT '结算价';

    ALTER TABLE `THisPosition` DROP PRIMARY KEY, ADD PRIMARY KEY(`FSettleDate`, `FAccountNo`, `FPositionId`, `FPositionCmbId`, `FMatchSide`, `FExchangeNo`, `FHedgeFlag`);

end IF;

END;

CALL EXE_SQL_BY_COND();

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

– Table structure for TAccountCashInOut

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

CREATE PROCEDURE EXE_SQL_BY_COND()

BEGIN

IF NOT EXISTS

(select 1 from information_schema.COLUMNS

where table_schema = ‘vxdatabase’

and table_name = ‘TAccountCashInOut’ and column_name = ‘FRandId’)

THEN

    ALTER TABLE `TAccountCashInOut` ADD COLUMN `FRandId` int(11) NOT NULL DEFAULT '0' COMMENT '随机数' AFTER FOutSerialId;

    ALTER TABLE `TAccountCashInOut` ADD COLUMN `FState` char(1) NOT NULL DEFAULT '0' COMMENT '状态' AFTER FRandId;

    ALTER TABLE `TAccountCashInOut` ADD COLUMN `FSource` varchar(8) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '出入金来源(离线文件回导填服务器标识)' AFTER FState;

    ALTER TABLE `TAccountCashInOut` DROP PRIMARY KEY, ADD PRIMARY KEY (`FSerialId`, `FDate`, `FAccountNo`, `FSource`);

end IF;

END;

CALL EXE_SQL_BY_COND();

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

– Table structure for TRightInfo

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

CREATE PROCEDURE EXE_SQL_BY_COND()

BEGIN

IF NOT EXISTS

(select 1 from TRightInfo where FRightID = “57001”)

THEN

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57001, '系统品种白名单查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57002, '系统品种白名单操作', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57011, '阶梯申报费查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57012, '阶梯申报费操作', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57021, '账号组合保证金倍数查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57022, '账号组合保证金倍数操作', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57031, '品种信息量查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57032, '品种信息量操作', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57041, '账号品种白名单查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57042, '账号品种白名单操作', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57051, '中金所组合保证金信息查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57052, '中金所组合保证金信息操作', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57061, '上期所组合保证金信息查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57062, '上期所组合保证金信息操作', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57071, '大商所组合保证金信息查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57072, '大商所组合保证金信息操作', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57081, '资金分配查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57082, '资金分配操作', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57091, '资金调拨账号信息查询', 'script');

  INSERT INTO `TRightInfo` (`FRightID`, `FRightName`, `FOperatorNo`) VALUES (57092, '资金调拨账号信息操作', 'script');

end IF;

END;

CALL EXE_SQL_BY_COND();

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

– Table structure for TRoleRight

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

CREATE PROCEDURE EXE_SQL_BY_COND()

BEGIN

IF NOT EXISTS

(select 1 from TRoleRight where FRightID = “57001” and FRoleNo = “KEFU”)

THEN

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57001', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57002', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57031', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57032', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57041', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57042', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57081', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57082', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57091', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('KEFU', '57092', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57001', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57011', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57021', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57031', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57041', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57051', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57061', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57071', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57081', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEQRY', '57091', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57001', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57002', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57011', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57012', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57021', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57022', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57031', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57032', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57041', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57042', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57051', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57052', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57061', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57062', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57071', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57072', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57081', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57082', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57091', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('MANAGEOPER', '57092', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57011', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57012', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57021', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57022', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57051', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57052', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57061', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57062', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57071', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57072', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57081', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57082', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57091', 'script');

  INSERT INTO `TRoleRight`(`FRoleNo`, `FRightID`, `FOperatorNo`) VALUES ('SETTLE', '57092', 'script');

end IF;

END;

CALL EXE_SQL_BY_COND();

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

– Table structure for TPositionMortgage

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

CREATE PROCEDURE EXE_SQL_BY_COND()

BEGIN

IF EXISTS

(select 1 from information_schema.TABLE_CONSTRAINTS

where table_schema = ‘vxdatabase’

and table_name = ‘TPositionMortgage’ and CONSTRAINT_TYPE=‘PRIMARY KEY’)

THEN

    ALTER TABLE `TPositionMortgage` DROP PRIMARY KEY;

end IF;

END;

CALL EXE_SQL_BY_COND();

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;

– 【最重要的版本信息】Table structure for TVersionInfo

INSERT INTO TVersionInfo(FBigVersion, FServiceOrScriptName, FUpdateTime) VALUES(“10.1.38.3.01”, “vxdatabase”, CURRENT_TIMESTAMP);

COMMIT;

//

AntTech_5N9HRW · 2024 年6 月 27 日 16:03

报错日志如下：
[SQL] Query [10.1.38.3.01]update20240423 start
[ERR] 4179 - Operation not allowed now
[ERR]
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

– 新增TUserLoginInfoNew表
DROP TABLE IF EXISTS TUserLoginInfoNew;
CREATE TABLE TUserLoginInfoNew (
FSerialId int(11) NOT NULL AUTO_INCREMENT COMMENT ‘流号’,
FLinkId int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Link id’,
FUserNo varchar(21) COLLATE utf8mb4_bin NOT NULL COMMENT ‘用户号’,
FUserName varchar(21) COLLATE utf8mb4_bin NOT NULL COMMENT ‘用户名’,
FRightName varchar(21) COLLATE utf8mb4_bin NOT NULL COMMENT ‘权限名称’,
FDevFirmName varchar(21) COLLATE utf8mb4_bin NOT NULL COMMENT ‘终端名称’,
FApplicationNo varchar(31) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘app no’,
FProtocolVersion int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘协议版本’,
FIsForceModifyPassword char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ COMMENT ‘是否强制修改密码’,
FIsDDA char(1) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ COMMENT ‘是否动态认证’,
FLoginTime varchar(41) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘登录时间’,
FLoginIP varchar(41) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘登录ip’,
FLoginPort int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘登录port’,
FLoginInfo varchar(300) COLLATE utf8mb4_bin NOT NULL COMMENT ‘登录信息’,
FServerFlag char(1) COLLATE utf8mb4_bin NOT NULL COMMENT ‘服务器标识’,
FLogoutTime varchar(41) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘登出时间’,
FImportDate varchar(11) COLLATE utf8mb4_bin NOT NULL COMMENT ‘导入日期’,
FServerInfo varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘服务器信息’,
FUUID varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘UUID’,
FLicenseNo varchar(51) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘软件授权号’,
FServerFlagInfo varchar(51) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘服务器标识信息’,
FClientType char(1) COLLATE utf8mb4_bin NOT NULL COMMENT ‘终端类型’,
FServerStartTime varchar(24) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘服务启动时间’,
FLoginErrorCode int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘错误码’,
FOperatorNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘操作人’,
FOperateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘操作时间’,
PRIMARY KEY (FUserNo, FServerFlag, FServerInfo, FLinkId, FServerStartTime),
UNIQUE KEY FSerialId (FSerialId) BLOCK_SIZE 16384 GLOBAL
) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘新用户登录信息表’;

– 新增TAccountCashTransfer表
DROP TABLE IF EXISTS TAccountCashTransfer;
CREATE TABLE TAccountCashTransfer (
FSerialId int(11) NOT NULL AUTO_INCREMENT COMMENT ‘流号’,
FAccountNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘资金账号’,
FRate decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘可调拨资金比例’,
FOperatorNo varchar(21) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘操作人’,
FOperateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘操作时间’,
PRIMARY KEY (FAccountNo),
UNIQUE KEY FSerialID (FSerialId) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 1 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘资金调拨账号表’;

– Table structure for THisPosition

DROP PROCEDURE IF EXISTS EXE_SQL_BY_COND;
CREATE PROCEDURE EXE_SQL_BY_COND()
BEGIN
IF NOT EXISTS
(select 1 from information_schema.key_column_usage
where table_schema = ‘vxdatabase’
and table_name = ‘THisPosition’ and column_name = ‘FHedgeFlag’)
THEN
ALTER TABLE THisPosition ADD COLUMN FSettlePrice decimal(38,8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘结算价’;
ALTER TABLE THisPosition DROP PRIMARY KEY, ADD PRIMARY KEY(FSettleDate, FAccountNo, FPositionId, FPositionCmbId, FMatchSide, `FExchan
[SQL] Finished with error

旭辉 · 2024 年6 月 27 日 17:10

请按如下步骤提供下日志
1.开启 Trace 功能
SET ob_enable_show_trace=ON;
2.执行问题SQL
3.获取SQL trace_id
SELECT last_trace_id() FROM DUAL;
4.登录对应 OBServer 节点，进入到日志文件所在目录
cd /home/admin/oceanbase/log
5.获取trace_id对应的日志
grep xxxxxxx observer.log --填写第3步获取的trace_id

AntTech_5N9HRW · 2024 年6 月 27 日 17:28

老师，我们手动执行成功了。问一个别的问题可以吗？就是，我们有一个表，客户的数据量很大，并且这个表有分区。导致，向表里增加字段，有时候会报错Time Out。我们有一个方案，您看有没有什么需要补充的。最好是有一个系统的流程。谢谢老师

旭辉 · 2024 年6 月 28 日 09:56

你这个方案基本没问题，创建索引的处理逻辑：
一条SQL语句的超时时间应该收ob_query_timeout（租户级别的系统变量）控制，但是为了不让DDL语句失败，DDL在执行的时候会使用_ob_ddl_timeout(默认1000s，集群级别配置项)作为超时时间。

简单来说客户端在收到一个创建索引的SQL语句时，需要经历以下步骤（简单来说）：

词法语法解析，构造出语法树。
经过resolve，构造出物理执行计划。
物理执行计划在执行时发现是一个DDL操作，需要设置新的超时时间（_ob_ddl_timeout），然后通过RPC发送到RS的DDL线程去执行。代码位置：int ObCmdExecutor::execute(ObExecContext &ctx, ObICmd &cmd)
DDL线程收到这个DDL后，开始创建索引。索引创建成功后返回到observer端。
observer端开始等索引表生效。代码位置：ObDDLExecutorUtil::wait_ddl_finish。在这段逻辑中超时时间就会被修改成102年，const int64_t OB_MAX_USER_SPECIFIED_TIMEOUT = 102L * 365L * 24L * 60L * 60L * 1000L * 1000L; 在这102年里面。

总之：在ObCmdExecutor之前的逻辑，超时时间是ob_query_timeout，在此之后，超时时间是_ob_ddl_timeout，等待RS把索引创建成功返回到observer端后，超时时间是102年。

另关于ddl超时可以看下这篇帖子

兹拉坦 · 2024 年6 月 28 日 17:26

哈哈，你这是翻了眼代码吗？赞

不过 DDL 和 DML 还不太一样，是不会生成计划的~

兹拉坦 · 2024 年6 月 28 日 17:28

执行了多长时间超时了？DDL 默认的超时时间是 1000 秒，1000 秒还没加完字段吗？

印象中加字段好像是很快的，除非有一些 DDL 在前面排队。如果真的超过一千秒了，调大 _ob_ddl_timeout 应该就好了。