使用 SELECT INTO OUTFILE 语句迁移发生IO error报错

达瓦里希1001 · 2024 年1 月 3 日 10:13

【使用环境】生产环境
【 OB 】
【使用版本】3.1.4-CE
【问题描述】使用SELECT INTO OUTFILE语句进行数据导出，发生报错ERROR 1086 (58030): IO error
【复现路径】`set global secure_file_priv=’/tmp’;

select * into outfile ‘/tmp/20240101.csv’ fields terminated by ‘,’ optionally enclosed by ‘"’ lines terminated by ‘\n’ from tbl_test limit 100;`

绵阳 · 2024 年1 月 3 日 10:22

请问是直连observer执行的这条语句吗

达瓦里希1001 · 2024 年1 月 3 日 10:25

是的，直接在observer节点通过2881端口连接执行的

绵阳 · 2024 年1 月 3 日 10:40

设置这个全局变量后有没有退出数据库连接重新建连？

达瓦里希1001 · 2024 年1 月 3 日 10:56

有的，重连之后还show variables like 检查过，如果这个参数没改成功会报文件权限问题

绵阳 · 2024 年1 月 3 日 14:10

这个报错的具体日志信息能贴一下吗

绵阳 · 2024 年1 月 3 日 14:24

我本地使用314版本尝试了一下，执行没有出现问题，建议确认下自己配置等操作是否正确

达瓦里希1001 · 2024 年1 月 3 日 15:05

[2024-01-03 14:42:42.575130] WARN  [SQL.ENG] alloc_op_spec (ob_operator_factory.cpp:290) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=6] [dc=0] static engine not implement, will retry(type=39, ret=-5833)
[2024-01-03 14:42:42.575153] WARN  [SQL.ENG] alloc_op_spec (ob_physical_plan.cpp:935) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=22] [dc=0] allocate operator spec failed(ret=-5833)
[2024-01-03 14:42:42.575165] WARN  [SQL.CG] postorder_generate_op (ob_static_engine_cg.cpp:197) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=6] [dc=0] allocate operator spec failed(ret=-5833, phy_plan_=0x146175f289d0, ob_phy_operator_type_str(type)="PHY_SELECT_INTO")
[2024-01-03 14:42:42.575170] WARN  [SQL.CG] generate (ob_static_engine_cg.cpp:132) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=4] [dc=0] failed to generate plan(ret=-5833)
[2024-01-03 14:42:42.575175] WARN  [SQL.CG] generate_operators (ob_code_generator.cpp:77) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=4] [dc=0] fail to code generate(ret=-5833)
[2024-01-03 14:42:42.575179] WARN  [SQL.CG] generate (ob_code_generator.cpp:34) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=4] [dc=0] fail to generate plan(ret=-5833)
[2024-01-03 14:42:42.575372] WARN  [SQL] code_generate (ob_sql.cpp:2013) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=3] [dc=0] Failed to generate physical plan(logical_plan========================================================
|ID|OPERATOR                  |NAME    |EST. ROWS|COST|
-------------------------------------------------------
|0 |SELECT INTO               |        |0        |501 |
|1 | LIMIT                    |        |100      |501 |
|2 |  PX COORDINATOR          |        |100      |487 |
|3 |   EXCHANGE OUT DISTR     |:EX10000|100      |124 |
|4 |    LIMIT                 |        |100      |124 |
|5 |     PX PARTITION ITERATOR|        |100      |110 |
|6 |      TABLE SCAN          |tbl_test|100      |110 |
=======================================================

Outputs & filters: 
-------------------------------------
  0 - output([SYS_OP_TO_OUTFILE_ROW(',', '\n', '', 0, tbl_test.entity, tbl_test.usr_id, tbl_test.entity_tp, tbl_test.usr_tp, tbl_test.usr_st, tbl_test.src_crt_ts, tbl_test.src_upd_ts, tbl_test.rec_crt_ts, tbl_test.rec_upd_ts, tbl_test.ver_no, tbl_test.entity_sm3)]), filter(nil)
  1 - output([SYS_OP_TO_OUTFILE_ROW(',', '\n', '', 0, tbl_test.entity, tbl_test.usr_id, tbl_test.entity_tp, tbl_test.usr_tp, tbl_test.usr_st, tbl_test.src_crt_ts, tbl_test.src_upd_ts, tbl_test.rec_crt_ts, tbl_test.rec_upd_ts, tbl_test.ver_no, tbl_test.entity_sm3)]), filter(nil), limit(100), offset(nil)
  2 - output([tbl_test.entity], [tbl_test.usr_id], [tbl_test.entity_tp], [tbl_test.usr_tp], [tbl_test.usr_st], [tbl_test.src_crt_ts], [tbl_test.src_upd_ts], [tbl_test.rec_crt_ts], [tbl_test.rec_upd_ts], [tbl_test.ver_no], [tbl_test.entity_sm3]), filter(nil)
  3 - output([tbl_test.entity], [tbl_test.usr_id], [tbl_test.entity_tp], [tbl_test.usr_tp], [tbl_test.usr_st], [tbl_test.src_crt_ts], [tbl_test.src_upd_ts], [tbl_test.rec_crt_ts], [tbl_test.rec_upd_ts], [tbl_test.ver_no], [tbl_test.entity_sm3]), filter(nil), dop=1
  4 - output([tbl_test.entity], [tbl_test.usr_id], [tbl_test.entity_tp], [tbl_test.usr_tp], [tbl_test.usr_st], [tbl_test.src_crt_ts], [tbl_test.src_upd_ts], [tbl_test.rec_crt_ts], [tbl_test.rec_upd_ts], [tbl_test.ver_no], [tbl_test.entity_sm3]), filter(nil), limit(100), offset(nil)
  5 - output([tbl_test.entity], [tbl_test.rec_upd_ts], [tbl_test.usr_id], [tbl_test.entity_tp], [tbl_test.usr_tp], [tbl_test.usr_st], [tbl_test.src_crt_ts], [tbl_test.src_upd_ts], [tbl_test.rec_crt_ts], [tbl_test.ver_no], [tbl_test.entity_sm3]), filter(nil)
  6 - output([tbl_test.entity], [tbl_test.rec_upd_ts], [tbl_test.usr_id], [tbl_test.entity_tp], [tbl_test.usr_tp], [tbl_test.usr_st], [tbl_test.src_crt_ts], [tbl_test.src_upd_ts], [tbl_test.rec_crt_ts], [tbl_test.ver_no], [tbl_test.entity_sm3]), filter(nil), 
      access([tbl_test.entity], [tbl_test.rec_upd_ts], [tbl_test.usr_id], [tbl_test.entity_tp], [tbl_test.usr_tp], [tbl_test.usr_st], [tbl_test.src_crt_ts], [tbl_test.src_upd_ts], [tbl_test.rec_crt_ts], [tbl_test.ver_no], [tbl_test.entity_sm3]), partitions(p[0-99]), 
      limit(100), offset(nil)
, ret=-5833)
[2024-01-03 14:42:42.575381] WARN  [SQL] generate_physical_plan (ob_sql.cpp:1632) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=7] [dc=0] Failed to genenrate phy plan(ret=-5833)
[2024-01-03 14:42:42.575419] WARN  [SQL] handle_physical_plan (ob_sql.cpp:3228) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=5] [dc=0] Failed to generate plan(ret=-5833, result.get_exec_context().need_disconnect()=false)
[2024-01-03 14:42:42.575426] WARN  [SQL] handle_text_query (ob_sql.cpp:1209) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=6] [dc=0] fail to handle physical plan(ret=-5833)
[2024-01-03 14:42:42.575432] WARN  [SQL] stmt_query (ob_sql.cpp:171) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=3] [dc=0] fail to handle text query(stmt=select * into outfile '/root/data0103.csv' fields terminated by ',' from tbl_test limit 100, ret=-5833)
[2024-01-03 14:42:42.575448] WARN  [SERVER] do_process (obmp_query.cpp:638) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=7] [dc=0] run stmt_query failed, check if need retry(ret=-5833, cli_ret=-5833, retry_ctrl_.need_retry()=1, sql=select * into outfile '/root/data0103.csv' fields terminated by ',' from tbl_test limit 100)
[2024-01-03 14:42:42.575463] WARN  [SERVER] do_process (obmp_query.cpp:724) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=6] [dc=0] try to execute again(ret=-5833, type=1, retry_type=1, timeout_remain=19999999995911)
[2024-01-03 14:42:42.580528] WARN  open (ob_file.cpp:52) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=5] [dc=0] open fname=[/root/data0103.csv] fail errno=13
[2024-01-03 14:42:42.580539] WARN  create (ob_file.cpp:299) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=11] [dc=0] open file error:ret=-4009,fname=/root/data0103.csv,fd_=-1
[2024-01-03 14:42:42.580551] WARN  [SQL.ENG] into_outfile (ob_select_into.cpp:323) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=4] [dc=0] create dumpfile failed(ret=-4009, into_ctx.file_name_={"VARCHAR":"/root/data0103.csv", collation:"utf8mb4_general_ci", coercibility:"COERCIBLE"})
[2024-01-03 14:42:42.580557] WARN  [SQL.ENG] inner_open (ob_select_into.cpp:170) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=5] [dc=0] into outfile failed(ret=-4009)
[2024-01-03 14:42:42.580561] WARN  [SQL.ENG] open (ob_phy_operator.cpp:137) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=3] [dc=0] Open this operator failed(ret=-4009, op_type="PHY_SELECT_INTO")
[2024-01-03 14:42:42.580565] WARN  [SQL.ENG] open (ob_phy_operator.cpp:158) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=3] [dc=0] fail to process error(ret=-4009)
[2024-01-03 14:42:42.580567] WARN  [SQL.EXE] open (ob_execute_result.cpp:40) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=2] [dc=0] root op fail to open(ret=-4009)
[2024-01-03 14:42:42.580571] WARN  [SQL] open (ob_result_set.cpp:189) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=3] [dc=0] fail open main query(ret=-4009)
[2024-01-03 14:42:42.580574] WARN  [SQL] sync_open (ob_result_set.cpp:134) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=2] [dc=0] fail to exec open()(ret=-4009)
[2024-01-03 14:42:42.580586] WARN  [SERVER] test_and_save_retry_state (ob_query_retry_ctrl.cpp:446) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=2] [dc=0] do not need retry(client_ret=-4009, err=-4009, expected_stmt=true, THIS_WORKER.get_timeout_ts()=1724264162571372, retry_type_=0, result.get_stmt_type()=1, result.get_exec_context().need_change_timeout_ret()=true, session->get_retry_info().get_last_query_retry_err()=0)
[2024-01-03 14:42:42.580596] WARN  [SERVER] response_result (ob_sync_plan_driver.cpp:64) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=7] [dc=0] result set open failed, check if need retry(ret=-4009, cli_ret=-4009, retry_ctrl_.need_retry()=0)
[2024-01-03 14:42:42.580713] WARN  [SERVER] response_result (ob_sync_plan_driver.cpp:69) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=5] [dc=0] close result set fail(cret=-4009)
[2024-01-03 14:42:42.580726] WARN  [SERVER] do_process (obmp_query.cpp:674) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=5] [dc=0] execute query fail(ret=-4009, timeout_timestamp=1724264162571372)
[2024-01-03 14:42:42.580782] WARN  [SERVER] process (obmp_query.cpp:291) [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=4] [dc=0] fail execute sql(sql_id="", sql=select * into outfile '/root/data0103.csv' fields terminated by ',' from tbl_test limit 100, sessid=3222189175, ret=-4009, ret="OB_IO_ERROR", need_disconnect=false)
[2024-01-03 14:42:42.580848] TRACE [TRACE]obmp_base.cpp:915 [391398][1536][YB42AC156FB4-0005FC896A4A88C0] [lt=7] [dc=0] [err query](TRACE=begin_ts=1704264162571382 2024-01-03 06:42:42.571382|[process_begin] u=0 in_queue_time:9, receive_ts:1704264162571372, enqueue_ts:1704264162571373|[start_sql] u=1 addr:{ip:"172.21.111.180", port:15838}|[query_begin] u=0 trace_id:YB42AC156FB4-0005FC896A4A88C0|[before_processor_run] u=6 |[session] u=3 sid:3222189175, tenant_id:1003|[parse_begin] u=28 stmt:"select * into outfile '/root/data0103.csv' fields terminated by ',' from tbl_test limit 100", stmt_len:91|[cache_get_plan_begin] u=4 |[cache_get_plan_end] u=25 |[transform_with_outline_begin] u=1 |[pc_fast_parse_start] u=40 |[pc_fast_parse_end] u=3 |[transform_with_outline_end] u=8 |[resolve_begin] u=28 |[resolve_end] u=235 |[transform_begin] u=37 |[transform_end] u=143 |[optimize_begin] u=2 |[tl_calc_by_range_end] u=145 |[tl_calc_part_id_end] u=0 |[get_location_cache_begin] u=2 |[get_location_cache_end] u=208 |[optimize_end] u=2709 |[cg_begin] u=0 |[cg_end] u=370 |[parse_begin] u=127 stmt:"select * into outfile '/root/data0103.csv' fields terminated by ',' from tbl_test limit 100", stmt_len:91|[cache_get_plan_begin] u=3 |[cache_get_plan_end] u=10 |[transform_with_outline_begin] u=1 |[pc_fast_parse_start] u=17 |[pc_fast_parse_end] u=2 |[transform_with_outline_end] u=4 |[resolve_begin] u=16 |[resolve_end] u=132 |[transform_begin] u=14 |[transform_end] u=87 |[optimize_begin] u=1 |[tl_calc_by_range_end] u=85 |[tl_calc_part_id_end] u=1 |[get_location_cache_begin] u=0 |[get_location_cache_end] u=193 |[optimize_end] u=2025 |[cg_begin] u=0 |[cg_end] u=97 |[plan_id] u=143 plan_id:30656|[exec_begin] u=2 arg1:false, end_trans_cb:false|[post_packet] u=45 ret:0, pcode:1808, addr:{ip:"172.21.111.181", port:2882}|[do_open_plan_begin] u=517 plan_id:30656|[sql_start_stmt_begin] u=1 |[sql_start_stmt_end] u=1 |[exec_plan_begin] u=0 |[exec_plan_end] u=9 |[sql_start_participant_begin] u=0 |[sql_start_participant_end] u=1 |[do_open_plan_end] u=0 |[post_packet] u=343 ret:0, pcode:1311, addr:{ip:"172.21.111.181", port:2882}|[post_packet] u=75 ret:0, pcode:1311, addr:{ip:"172.21.111.180", port:2882}|[post_packet] u=61 ret:0, pcode:1311, addr:{ip:"172.21.111.182", port:2882}|[sqc_finish] u=1033 dfo_id:0, sqc_id:1|[close_plan_begin] u=175 |[post_packet] u=16 ret:0, pcode:494, addr:{ip:"172.21.111.181", port:2882}|[post_packet] u=5 ret:0, pcode:494, addr:{ip:"172.21.111.182", port:2882}|[post_packet] u=8 ret:0, pcode:494, addr:{ip:"172.21.111.181", port:2882}|[post_packet] u=6 ret:0, pcode:494, addr:{ip:"172.21.111.182", port:2882}|[sqc_finish] u=6 dfo_id:0, sqc_id:2|[sqc_finish] u=38 dfo_id:0, sqc_id:0|[end_participant_begin] u=28 |[end_participant_end] u=1 |[start_end_stmt] u=0 |[end_stmt] u=0 |[close_plan_end] u=0 |[auto_end_plan_begin] u=0 |[auto_end_plan_end] u=1 |[result_set_close] u=0 ret:-4009, arg1:-4009, arg2:0, arg3:-4009, async:false|[exec_end] u=15 |[query_end] u=53 |[process_end] u=18 run_ts:1704264162571389|[process_ret] u=0 process_ret:-4009|total_timeu=9414)

达瓦里希1001 · 2024 年1 月 3 日 15:09

我用select last_trace_id(); 把相关日志从observer.log 捞出来了，麻烦绵阳老师帮忙瞄一眼。提供个后续排查方向

绵阳 · 2024 年1 月 3 日 15:09

这个日志在observer.log里的相关内容能贴一下吗，使用grep YB42AC156FB4-0005FC896A4A88C0 observer.log*命令查询

绵阳 · 2024 年1 月 3 日 15:34

当前问题是一个已知问题，31x后续不会有版本发布，如果是测试环境建议使用已修复该问题的4.x版本。

如果是生产环境建议换一种导数方式，比如obdumper工具：https://www.oceanbase.com/docs/oceanbase-dumper-loader

达瓦里希1001 · 2024 年1 月 3 日 16:44

绵阳老师，那这边您使用的环境是4.x的版本了吗？

达瓦里希1001 · 2024 年1 月 3 日 16:48

谢谢绵阳老师～，我们之所以使用SELECT INTO OUTFILE是因为相比于Mysqldump, OUTFILE 的形式能够对需要导出的字段做出限制，同时还能控制抽取的数据量（通过limit子句实现）。

我看obdumper工具好像没办法实现上述两点需求，不知道是我打开方式不正确或者对obdumper的命令参数和使用不够了解导致的。

大川 · 2024 年1 月 4 日 11:01

你好，关于obdumper的使用，根据提问的场景可以参考以下几点：
select * into outfile ‘/tmp/20240101.csv’ fields terminated by ‘,’ optionally enclosed by ‘"’ lines terminated by ‘\n’ from tbl_test limit 100;`

obdumper实现这个语句的话可以 obdumper -h ‘xx’ -P ‘xx’ -u ‘xx’ -t ‘xx’ -D ‘xx’ -p ‘xx’ --table ‘tbl_test’ --csv --column-separator ‘"’ --query-sql ‘select * from tbl_test limit 100;’ -f ‘<导出数据存放路径>’
指定字段可以使用 --exclude-column-names 去指定不导出的字段，或者在 query-sql的语句中定义

达瓦里希1001 · 2024 年1 月 5 日 14:02

谢谢大川老师！刚好在学习obloader/obdumper的命令行参数