Bug #69252 All the parts.partition_max* tests are broken with MTR --parallel
Submitted: 16 May 2013 6:25 Modified: 17 Mar 2014 13:17
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Tests: Server Severity:S3 (Non-critical)
Version:5.6.11, probably 5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: mtr, parallel, secure_file_priv, ulimit

[16 May 2013 6:25] Laurynas Biveinis
Description:
The parts suite has several tests that are normally disabled because they require high ulimit -n value.

02:02:19.760 parts.partition_max_parts_hash_myisam w2 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:02:19.991 parts.partition_max_parts_inv_myisam w2 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:02:20.211 parts.partition_max_parts_key_myisam w2 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:02:20.545 parts.partition_max_parts_list_myisam w2 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:02:20.791 parts.partition_max_parts_range_myisam w2 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:02:21.055 parts.partition_max_sub_parts_key_list_myisam w2 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:02:21.281 parts.partition_max_sub_parts_key_range_myisam w2 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:02:21.527 parts.partition_max_sub_parts_list_myisam w2 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:02:21.851 parts.partition_max_sub_parts_range_myisam w2 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:25:09.874 parts.partition_max_parts_hash_innodb w1 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:25:11.212 parts.partition_max_parts_inv_innodb w1 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:25:11.213 parts.partition_max_parts_key_innodb w1 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:25:11.214 parts.partition_max_parts_list_innodb w1 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:25:11.214 parts.partition_max_parts_range_innodb w1 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:25:12.551 parts.partition_max_sub_parts_key_list_innodb w1 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:25:12.552 parts.partition_max_sub_parts_key_range_innodb w1 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:25:13.993 parts.partition_max_sub_parts_list_innodb w1 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)
02:25:13.993 parts.partition_max_sub_parts_range_innodb w1 [ skipped ] Need open_files_limit >= 16450 (see ulimit -n)

If one setups this ulimit high enough, and runs the testsuite with --parallel, all of them fail as follows:

00:46:24.443 CURRENT_TEST: parts.partition_max_parts_hash_innodb
00:46:24.443 mysqltest: In included file "./suite/parts/inc/partition_max_parts_hash.inc": 
00:46:24.443 included from ./suite/parts/inc/partition_max_parts_hash.inc at line 51:
00:46:24.443 At line 51: query 'load data infile '$MYSQL_TMP_DIR/data01' into table t2 partition (p8191) fields terminated by ','' failed: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The reason being that, on parallel runs, $MYSQL_TMP_DIR != mysql var dir, which MTR sets secure-file-priv for.

How to repeat:
I have reduced this to the following (so you don't need ulimit etc):

foo1.test:
---------------
CREATE table t1(a INT) ENGINE=INNODB;

SELECT @@global.secure_file_priv;
echo MYSQL_TMP_DIR = $MYSQL_TMP_DIR;

write_file $MYSQL_TMP_DIR/data01;
100
2000
EOF

eval LOAD DATA INFILE '$MYSQL_TMP_DIR/data01' INTO TABLE t1;

DROP TABLE t1;
---------------
foo2.test:
---------------
SELECT @@global.secure_file_priv;
echo MYSQL_TMP_DIR = $MYSQL_TMP_DIR;
---------------

foo1.test passes when run alone, e.g. mysql-test-run foo1

CREATE table t1(a INT) ENGINE=INNODB;
SELECT @@global.secure_file_priv;
@@global.secure_file_priv
/home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/
MYSQL_TMP_DIR = /home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/tmp
LOAD DATA INFILE '/home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/tmp/data01' INTO TABLE t1;
DROP TABLE t1;
main.foo1                                [ pass ]     91

foo1.test fails with parallel:

./mysql-test-run --parallel=2 foo1 foo2

CREATE table t1(a INT) ENGINE=INNODB;
SELECT @@global.secure_file_priv;
@@global.secure_file_priv
/home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/1/
MYSQL_TMP_DIR = /home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/tmp/1
SELECT @@global.secure_file_priv;
@@global.secure_file_priv
/home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/2/
MYSQL_TMP_DIR = /home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/tmp/2
main.foo1                                w1 [ fail ]
        Test ended at 2013-05-16 09:12:43

CURRENT_TEST: main.foo1
mysqltest: At line 11: query 'LOAD DATA INFILE '$MYSQL_TMP_DIR/data01' INTO TABLE t1' failed: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Suggested fix:
Other tests in the testsuite that LOAD DATA INFILE from $MYSQL_TMP_DIR, have -master.opt files allowing this. E.g. row_count_func-master.opt.

Adding such foo1-master.opt fixes this too:
--secure-file-priv=$MYSQL_TMP_DIR

SELECT @@global.secure_file_priv;
@@global.secure_file_priv
/home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/1/
MYSQL_TMP_DIR = /home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/tmp/1
CREATE table t1(a INT) ENGINE=INNODB;
SELECT @@global.secure_file_priv;
@@global.secure_file_priv
/home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/tmp/2/
MYSQL_TMP_DIR = /home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/tmp/2
LOAD DATA INFILE '/home/laurynas/percona/src/secure-file-priv-exp-5.6/obj-debug/mysql-test/var/tmp/2/data01' INTO TABLE t1;
DROP TABLE t1;
main.foo2                                w1 [ pass ]       
main.foo1                                w2 [ pass ]     65

But it seems to me that a better fix would be for the MTR framework to put tmpdir under the vardir in case of parallel, i.e. .../var/2/tmp instead of .../var/tmp/2.
[16 May 2013 6:48] Laurynas Biveinis
Err S3 not S2
[21 May 2013 7:03] Umesh Shastry
Hello Laurynas,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[4 Dec 2013 11:18] Laurynas Biveinis
It looks like it has been fixed with

5.6$ bzr log -r 5575
------------------------------------------------------------
revno: 5575
committer: horst.hunger@oracle.com
branch nick: mysql-5.6
timestamp: Thu 2013-10-31 11:30:31 +0100
message:
  Repeat for Bug #17711364: Added the opt files to the tests to run them in binaries.