用oat部署ocp时报错

OceanBase 技术问题
#1

版本：

[root@centos images]# ls -ltr
total 1870036
-rw-r--r-- 1 root root  741434405 May 13 09:11 meta_OB2277_OBP329_x86_20230330.tgz
-rw-r--r-- 1 root root  165162494 May 13 09:11 nlb_3.5.1_20230210194413_x86.tgz
-rw-rw-r-- 1 root root 1008308212 May 15 17:43 ocp334.tar.gz

报错：

[2025-05-15T18:09:29.754+0800] INFO - [2025-05-15 18:07:06] run sql: ALTER TABLE `ocp2_alarm_filter` ADD COLUMN `filter_time_range_json` varchar(2048) NOT NULL DEFAULT '{}' COMMENT '屏蔽时间段匹配条件 MatchTimeRange json数组'; failed, got duplicate column error 1060 (42S21): Duplicate column name 'filter_time_range_json', just skip
[2025-05-15T18:09:29.754+0800] INFO - 
[2025-05-15T18:09:29.754+0800] INFO - 
[2025-05-15T18:09:29.754+0800] INFO - +++++++++++++++++++++++++++++++++
[2025-05-15T18:09:29.755+0800] INFO - + wait for election, sleep 120S +
[2025-05-15T18:09:29.755+0800] INFO - +++++++++++++++++++++++++++++++++
[2025-05-15T18:09:29.755+0800] INFO - 
[2025-05-15T18:09:29.755+0800] INFO - replace table_group in sql file: update_dml_2.2.0.sql
[2025-05-15T18:09:29.755+0800] INFO - sed 's/$VAR_TABLEGROUP_NAME/ocp_meta/g' ../../sqls/update_dml_2.2.0.sql > ../../sqls/real/update_dml_2.2.0.sql
[2025-05-15T18:09:29.755+0800] INFO - executing real sql script: real/update_dml_2.2.0.sql
[2025-05-15T18:09:29.755+0800] INFO - replace table_group in sql file: update_dml_2.3.0.sql
[2025-05-15T18:09:29.755+0800] INFO - sed 's/$VAR_TABLEGROUP_NAME/ocp_meta/g' ../../sqls/update_dml_2.3.0.sql > ../../sqls/real/update_dml_2.3.0.sql
[2025-05-15T18:09:29.755+0800] INFO - executing real sql script: real/update_dml_2.3.0.sql
[2025-05-15T18:09:29.755+0800] INFO - [2025-05-15 18:09:29] run sql: INSERT INTO `ocp_obstat_config` VALUES (3000022,'OBProxy','cpu','percent','cpu_idle,cpu_iowait,cpu_irq,cpu_nice,cpu_softirq,cpu_steal,cpu_system,cpu_user',0,'',0,0,0,'2019-05-31 15:22:27','2019-05-31 15:22:27'),(3000010,'OBProxy','current_session','count','current_session_client,current_session_server',0,'',0,0,0,'2019-05-29 15:00:59','2019-05-29 15:00:59'),(3000026,'OBProxy','disk_percent','count','disk_percent',0,'',0,0,0,'2019-05-31 15:22:27','2019-05-31 15:22:27'),(3000012,'OBProxy','elogs_total','count','elogs_total_error,elogs_total_warn',0,'',0,0,0,'2019-05-29 15:00:59','2019-05-29 15:00:59'),(3000011,'OBProxy','entry_total','count','entry_total_hit,entry_total_partition,entry_total_route,entry_total_success,entry_total_table',0,'',0,0,0,'2019-05-29 15:00:59','2019-05-29 15:00:59'),(3000009,'OBProxy','error_response','count','error_response_commit,error_response_delete,error_response_insert,error_response_select,error_response_sequence,error_response_update',0,'',0,0,0,'2019-05-29 15:00:59','2019-05-29 15:00:59'),(3000023,'OBProxy','io','count','ioread,iowrite',0,'',0,0,0,'2019-05-31 15:22:27','2019-05-31 15:22:27'),(3000024,'OBProxy','io_byte','count','ioread_byte,iowrite_byte',0,'',0,0,0,'2019-05-31 15:22:27','2019-05-31 15:22:27'),(3000025,'OBProxy','io_time','count','ioread_time,iowrite_time',0,'',0,0,0,'2019-05-31 15:22:27','2019-05-31 15:22:27'),(3000030,'OBProxy','load','count','load1,load5,load15',0,'',0,0,0,'2019-05-31 15:22:27','2019-06-03 14:50:45'),(3000027,'OBProxy','memory','count','memory_buffers,memory_cached,memory_free,memory_used',0,'',0,0,0,'2019-05-31 15:22:27','2019-05-31 15:22:27'),(3000028,'OBProxy','memory_percent','count','memory_percent',0,'',0,0,0,'2019-05-31 15:22:27','2019-05-31 15:22:27'),(3000029,'OBProxy','net','count','net_receive,net_transmit',0,'',0,0,0,'2019-05-31 15:22:27','2019-05-31 15:22:27'),(3000013,'OBProxy','request_byte','count','request_byte_client,request_byte_request,request_byte_response,request_byte_server',0,'',0,0,0,'2019-05-29 15:00:59','2019-05-29 15:00:59'),(3000008,'OBProxy','request_cost','ms','request_cost_commit,request_cost_delete,request_cost_insert,request_cost_prepare_send_request_time,request_cost_select,request_cost_sequence,request_cost_server_process_request_time,request_cost_update',0,'',0,0,0,'2019-05-29 15:00:59','2019-05-29 15:00:59'),(3000007,'OBProxy','request_total','count','request_total_commit,request_total_delete,request_total_insert,request_total_large,request_total_select,request_total_sequence,request_total_slow,request_total_update',0,'',0,0,0,'2019-05-29 15:00:59','2019-05-29 15:00:59'),(3000006,'OBProxy','transaction_total','count','transaction_total',0,'',0,0,0,'2019-05-29 15:00:59','2019-05-29 15:00:59'); failed, got exception Traceback (most recent call last):
[2025-05-15T18:09:29.755+0800] INFO -   File "/home/admin/ocp-init/src/ocp-init/common.py", line 207, in source_sql_file
[2025-05-15T18:09:29.755+0800] INFO -     run_sql_no_result(sql, config)
[2025-05-15T18:09:29.755+0800] INFO -   File "/home/admin/ocp-init/src/ocp-init/common.py", line 77, in run_sql_no_result
[2025-05-15T18:09:29.755+0800] INFO -     run_sql(sql, db_config, True)
[2025-05-15T18:09:29.755+0800] INFO -   File "/home/admin/ocp-init/src/ocp-init/common.py", line 61, in run_sql
[2025-05-15T18:09:29.756+0800] INFO -     cursor.execute(sql)
[2025-05-15T18:09:29.756+0800] INFO -   File "/usr/lib64/python2.7/site-packages/mysql/connector/cursor.py", line 569, in execute
[2025-05-15T18:09:29.756+0800] INFO -     self._handle_result(self._connection.cmd_query(stmt))
[2025-05-15T18:09:29.756+0800] INFO -   File "/usr/lib64/python2.7/site-packages/mysql/connector/connection.py", line 590, in cmd_query
[2025-05-15T18:09:29.756+0800] INFO -     result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
[2025-05-15T18:09:29.756+0800] INFO -   File "/usr/lib64/python2.7/site-packages/mysql/connector/connection.py", line 478, in _handle_result
[2025-05-15T18:09:29.756+0800] INFO -     raise errors.get_exception(packet)
[2025-05-15T18:09:29.756+0800] INFO - IntegrityError: 1062 (23000): Duplicate entry '3000006' for key 'PRIMARY'
[2025-05-15T18:09:29.756+0800] INFO - 
[2025-05-15T18:09:29.756+0800] INFO - Traceback (most recent call last):
[2025-05-15T18:09:29.756+0800] INFO -   File "create_metadb.py", line 189, in <module>
[2025-05-15T18:09:29.756+0800] INFO -     init_ocp(filename)
[2025-05-15T18:09:29.756+0800] INFO -   File "create_metadb.py", line 63, in init_ocp
[2025-05-15T18:09:29.756+0800] INFO -     init_meta_db(meta_db_connect_info)
[2025-05-15T18:09:29.756+0800] INFO -   File "create_metadb.py", line 88, in init_meta_db
[2025-05-15T18:09:29.757+0800] INFO -     metadb.source_sqls(db_connect_info, sqls)
[2025-05-15T18:09:29.757+0800] INFO -   File "/home/admin/ocp-init/src/ocp-init/metadb.py", line 450, in source_sqls
[2025-05-15T18:09:29.757+0800] INFO -     common.source_sql_file(config, "../../sqls/{}".format(real_file), force=force)
[2025-05-15T18:09:29.757+0800] INFO -   File "/home/admin/ocp-init/src/ocp-init/common.py", line 217, in source_sql_file
[2025-05-15T18:09:29.757+0800] INFO -     raise e
[2025-05-15T18:09:29.757+0800] INFO - mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '3000006' for key 'PRIMARY'
[2025-05-15T18:09:29.865+0800] ERROR - Task failed with exception
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/airflow/decorators/base.py", line 217, in execute
    return_value = super().execute(context)
  File "/usr/local/lib/python3.9/site-packages/airflow/operators/python.py", line 175, in execute
    return_value = self.execute_callable()
  File "/usr/local/lib/python3.9/site-packages/airflow/operators/python.py", line 192, in execute_callable
    return self.python_callable(*self.op_args, **self.op_kwargs)
  File "/oat/task_engine/dags/init_ocp.py", line 147, in init_ocp_meta
    raise RuntimeError('init metadb structure failed')
RuntimeError: init metadb structure failed
[2025-05-15T18:09:29.870+0800] INFO - Marking task as FAILED. dag_id=init_ocp, task_id=init_ocp_meta, execution_date=20250515T100257, start_date=20250515T100340,

会什么会出现主键重复呢？

[2025-05-15T18:09:29.757+0800] INFO - mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '3000006' for key 'PRIMARY'
[2025-05-15T18:09:29.865+0800] ERROR - Task failed with exception
#3

@论坛小助手

#4

根据您提供的报错信息和上下文，问题的核心是 ocp_obstat_config 表中插入数据时出现了主键重复的错误。以下是详细的分析和解决方案：

1. 问题分析

从报错信息中可以看到：

  • 报错代码为 ERROR 1062 (23000)，这是 MySQL 模式下的主键重复错误。
  • 错误提示 Duplicate entry '3000006' for key 'PRIMARY'，表明在插入数据时，主键值 3000006 已经存在于表中。

结合上下文，可以推测以下几种可能原因：

  • SQL 脚本重复执行：如果 create_metadb.py 或相关 SQL 文件被多次执行，可能会导致重复插入相同的数据。
  • 数据初始化冲突：在部署 OCP 时，可能存在多个任务同时尝试初始化数据库，导致数据冲突。
  • 历史数据残留：目标数据库中可能已经存在部分初始化数据，而当前脚本未检查这些数据的存在性。

2. 解决方法

方法一：检查并清理重复数据

在插入数据之前，可以通过查询确认目标表中是否存在重复的主键值。例如：

SELECT * FROM ocp_obstat_config WHERE id = 3000006;

如果查询结果不为空，则说明该主键值已存在。您可以选择删除重复数据或更新现有数据。例如：

  • 删除重复数据：
    DELETE FROM ocp_obstat_config WHERE id = 3000006;
  • 更新现有数据（如果需要保留原有记录）：
    UPDATE ocp_obstat_config 
SET column1 = value1, column2 = value2 
WHERE id = 3000006;

方法二：修改 SQL 脚本以避免重复插入

为了防止重复插入，可以在 SQL 脚本中使用 INSERT IGNORE 或 REPLACE INTO 语句。例如：

  • 使用 INSERT IGNORE：

    INSERT IGNORE INTO ocp_obstat_config 
VALUES (3000006, 'OBProxy', 'transaction_total', 'count', 'transaction_total', 0, '', 0, 0, 0, '2019-05-29 15:00:59', '2019-05-29 15:00:59');

    INSERT IGNORE 会在遇到主键冲突时忽略插入操作，不会抛出错误。

  • 使用 REPLACE INTO：

    REPLACE INTO ocp_obstat_config 
VALUES (3000006, 'OBProxy', 'transaction_total', 'count', 'transaction_total', 0, '', 0, 0, 0, '2019-05-29 15:00:59', '2019-05-29 15:00:59');

    REPLACE INTO 会在遇到主键冲突时先删除旧记录，再插入新记录。

方法三：确保 SQL 脚本仅执行一次

如果问题是由脚本重复执行引起的，可以在部署流程中加入检查机制，确保初始化脚本只执行一次。例如，在脚本开头添加如下逻辑：

-- 检查表是否已初始化
SELECT COUNT(*) INTO @row_count FROM information_schema.columns 
WHERE table_name = 'ocp_obstat_config' AND column_name = 'id';

IF @row_count > 0 THEN
    -- 如果表已初始化，跳过后续操作
    LEAVE script;
END IF;

-- 执行初始化 SQL
INSERT INTO ocp_obstat_config ...

方法四：排查多任务并发问题

如果部署过程中存在多个任务同时初始化数据库，可能导致数据冲突。建议：

  • 确保部署流程串行化，避免并发执行。
  • 使用分布式锁（如 Redis、Zookeeper）控制初始化任务的执行顺序。

3. 预防措施

为了避免类似问题再次发生，可以采取以下措施：

  1. 版本控制：确保每次部署使用的 SQL 脚本版本唯一，并记录已执行的脚本。
  2. 幂等性设计：在 SQL 脚本中加入检查逻辑，确保重复执行不会引发错误。
  3. 日志记录：在部署过程中记录每一步的操作，便于排查问题。

4. 总结

主键重复错误通常是由于数据初始化脚本重复执行或并发冲突引起的。通过检查现有数据、修改 SQL 脚本逻辑或优化部署流程，可以有效解决此类问题。如果问题仍未解决，建议联系 OceanBase 的技术支持团队获取进一步帮助。

希望以上解答对您有所帮助！

检索到的文档如下:

  1. 插入数据时提示重复的输入，错误代码 ERROR 1062
  2. 修改登录密码
  3. 4000 ~ 4499
  4. 7000 ~ 7999
  5. 升级常见问题
  6. OCP 系统常见问题
  7. 登录 OceanBase 云平台
#5 
[root@centos sqls]# find / -name "ocp_metadb_ddl.sql"
/data/docker/overlay2/ef8ac28f7562fd9765022d478f7a13c6203a295a5f3c8247fd3983cc62efcab0/diff/home/admin/ocp-init/sqls/ocp_metadb_ddl.sql
/data/docker/overlay2/ef8ac28f7562fd9765022d478f7a13c6203a295a5f3c8247fd3983cc62efcab0/diff/home/admin/ocp-server/init/sqls/ocp_metadb_ddl.sql

sed -i 's/INSERT INTO/REPLACE INTO/g' update_dml_2.3.0.sql

问题解决