LOAD DATA INFILE fails with error 1227 due to secure_file_priv restriction on Windows

Description

During the execution of the string_type.string_type_direct_load_inc mysqltest, the LOAD DATA INFILE command fails with error 1227 because the secure_file_priv system variable does not permit the directory specified in the INFILE clause.

Actual Result

The LOAD DATA INFILE operation fails. (Note: The original content contained embedded image cards which cannot be directly translated. The issue is the error 1227).

Steps to Reproduce

1. Access the machine.
2. Navigate to the directory: /obdata/data/AndroidTest/ob_opensource_test/tools/deploy.
3. Execute the following commands to set up the test environment and run the specific test:

   export OCEANBASE_HOME=/obdata/data/AndroidTest/ob_opensource_test/
   cd ${OCEANBASE_HOME}/tools/deploy
   source activate_obd.sh
   export OBD_SEEKDB_STATUS_FALLBACK_TCP=1
   
   obd test mysqltest test_env --mysqltest-bin=${OCEANBASE_HOME}/deps/3rd/u01/obclient/bin/mysqltest --obclient-bin=${OCEANBASE_HOME}/deps/3rd/u01/obclient/bin/obclient --init-only --init-sql-dir=${OCEANBASE_HOME}/tools/deploy --init-sql-files='init.sql,init_user.sql|root@sys|test'
   obd test mysqltest test_env --mysqltest-bin=${OCEANBASE_HOME}/deps/3rd/u01/obclient/bin/mysqltest --obclient-bin=${OCEANBASE_HOME}/deps/3rd/u01/obclient/bin/obclient --disable-reboot --test-set=string_type.string_type_direct_load_inc
   

4. The test executes the following SQL, where the LOAD DATA INFILE statements will fail with error 1227:

   CREATE DATABASE IF NOT EXISTS test;
   USE test;
   
   -- Execute LOAD DATA. If lacking permission, execute with sys tenant:
   -- SET GLOBAL secure_file_priv = '/obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data';
   
   SET GLOBAL max_allowed_packet = 60000000;
   SET GLOBAL ob_sql_work_area_percentage = 80;
   SET @@recyclebin = off;
   SET ob_query_timeout = 1000000000;
   SET ob_trx_timeout = 1000000000;
   
   DROP TABLE IF EXISTS t_src, t_dst_inc, t_dst_heap, t_ld_inc, t_ld_heap;
   DROP TABLE IF EXISTS t1, t2;
   
   CREATE TABLE t_src (c1 string, c2 string) lob_inrow_threshold = 8192;
   CREATE TABLE t_dst_inc (c1 string PRIMARY KEY, c2 string, INDEX idx_c2(c2) LOCAL);
   CREATE TABLE t_dst_heap (c1 string, c2 string) lob_inrow_threshold = 8192;
   
   INSERT INTO t_src VALUES ('aaa', 'bbb');
   INSERT INTO t_src VALUES (REPEAT('c', 8193), 'ddd');
   INSERT INTO t_src VALUES (REPEAT('e', 16381), 'fff');
   INSERT INTO t_src VALUES ('ggg', REPEAT('h', 8193));
   INSERT INTO t_src VALUES ('hhhh', REPEAT('i', 16368));
   INSERT INTO t_src VALUES ('iiii', REPEAT('j', 16376));
   INSERT INTO t_src VALUES ('kkkk', REPEAT('l', 16377));
   
   CREATE TABLE t1 (c1 string, c2 int AUTO_INCREMENT, UNIQUE KEY pk (c1) STORING (c2));
   CREATE TABLE t2 (c1 string, c2 int);
   INSERT INTO t2 VALUES ('string', 1);
   INSERT /*+parallel(2) direct(true, 0, 'inc') enable_parallel_dml*/ INTO t1 SELECT * FROM t2;
   SELECT * FROM t2;
   
   INSERT /*+direct(true, 0, 'inc') enable_parallel_dml parallel(4)*/ INTO t_dst_inc SELECT c1, c2 FROM t_src WHERE c1 = 'aaa';
   -- The following 4 lines are expected to produce 1062 / 1071 in mysqltest; sourcing the entire file will be interrupted, please execute line by line or run only with mysqltest:
   -- INSERT /*+direct(true, 0, 'inc') enable_parallel_dml parallel(4)*/ INTO t_dst_inc SELECT c1, c2 FROM t_src WHERE c1 = 'aaa';
   -- INSERT /*+direct(true, 0, 'inc') enable_parallel_dml parallel(4)*/ INTO t_dst_inc SELECT c1, c2 FROM t_src WHERE c2 = 'ddd';
   -- INSERT /*+direct(true, 0, 'inc') enable_parallel_dml parallel(4)*/ INTO t_dst_inc SELECT c1, c2 FROM t_src WHERE c2 = 'fff';
   -- INSERT /*+direct(true, 0, 'inc') enable_parallel_dml parallel(4)*/ INTO t_dst_inc SELECT c1, c2 FROM t_src WHERE c1 = 'ggg';
   
   INSERT /*+direct(true, 0, 'inc') enable_parallel_dml parallel(4)*/ INTO t_dst_heap SELECT c1, c2 FROM t_src;
   
   CREATE TABLE t_ld_inc (c1 string PRIMARY KEY, c2 int, c3 string, INDEX idx_c3(c3) LOCAL);
   CREATE TABLE t_ld_heap (c1 string, c2 string, c3 string);
   
   LOAD DATA /*+direct(true, 0, 'inc') parallel(4)*/
   INFILE '/obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data/string_test/data_pk_100.csv'
   INTO TABLE t_ld_inc FIELDS TERMINATED BY ',';
   
   LOAD DATA /*+direct(true, 0, 'inc') parallel(4)*/
   INFILE '/obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data/string_test/data_pk_8193.csv'
   INTO TABLE t_ld_inc FIELDS TERMINATED BY ',';
   
   -- The following 2 LOAD statements into t_ld_inc are expected to produce 1071 in mysqltest; sourcing the entire file will be interrupted, please execute separately for verification:
   -- LOAD DATA /*+direct(true, 0, 'inc') parallel(4)*/
   -- INFILE '/obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data/string_test/data_pk_16373.csv'
   -- INTO TABLE t_ld_inc FIELDS TERMINATED BY ',';
   -- LOAD DATA /*+direct(true, 0, 'inc') parallel(4)*/
   -- INFILE '/obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data/string_test/data_idx_8193.csv'
   -- INTO TABLE t_ld_inc FIELDS TERMINATED BY ',';
   
   LOAD DATA /*+direct(true, 0, 'inc') parallel(4)*/
   INFILE '/obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data/string_test/data_pk_100.csv'
   INTO TABLE t_ld_heap FIELDS TERMINATED BY ',';
   
   LOAD DATA /*+direct(true, 0, 'inc') parallel(4)*/
   INFILE '/obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data/string_test/data_pk_8193.csv'
   INTO TABLE t_ld_heap FIELDS TERMINATED BY ',';
   
   LOAD DATA /*+direct(true, 0, 'inc') parallel(4)*/
   INFILE '/obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data/string_test/data_pk_16373.csv'
   INTO TABLE t_ld_heap FIELDS TERMINATED BY ',';
   
   LOAD DATA /*+direct(true, 0, 'inc') parallel(4)*/
   INFILE '/obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data/string_test/data_idx_8193.csv'
   INTO TABLE t_ld_heap FIELDS TERMINATED BY ',';
   
   SELECT COUNT(*) FROM t_src;
   SELECT CHAR_LENGTH(c1), CHAR_LENGTH(c2) FROM t_src;
   SELECT COUNT(*) FROM t_dst_inc;
   SELECT CHAR_LENGTH(c1), CHAR_LENGTH(c2) FROM t_dst_inc;
   SELECT COUNT(*) FROM t_dst_heap;
   SELECT CHAR_LENGTH(c1), CHAR_LENGTH(c2) FROM t_dst_heap ORDER BY 1, 2;
   SELECT COUNT(*) FROM t_ld_inc;
   SELECT CHAR_LENGTH(c1), c2, CHAR_LENGTH(c3) FROM t_ld_inc;
   SELECT COUNT(*) FROM t_ld_heap;
   SELECT CHAR_LENGTH(c1), c2, CHAR_LENGTH(c3) FROM t_ld_heap ORDER BY 1, 2, 3;
   
   -- Verify merge (optional; requires sys privileges, consistent with the example):
   -- ALTER SYSTEM MAJOR FREEZE;
   
   DROP TABLE IF EXISTS t_src, t_dst_inc, t_dst_heap, t_ld_inc, t_ld_heap;
   DROP TABLE IF EXISTS t1, t2;
   
   SET GLOBAL ob_sql_work_area_percentage = 5;
   SET @@recyclebin = on;
   

Environment

• Operating System: Windows (implied from original title)
• Test: mysqltest
• Test Case: string_type.string_type_direct_load_inc
• Error Code: 1227
• Affected Variable: secure_file_priv
• Test Directory: /obdata/data/AndroidTest/ob_opensource_test/tools/deploy/mysql_test/test_suite/string_type/data/string_test/

Start with the mysqltest case string_type.string_type_direct_load_inc and the SQL under tools/deploy/mysql_test/test_suite/string_type/data/string_test/. Run the provided obd test mysqltest commands on Windows, then inspect how secure_file_priv is configured for the INFILE paths. Done means the LOAD DATA statements complete without error 1227.