1

I read every single thread about this, but none of the solutions work for my situation. I made a function to dump a MySQL database table to a CSV/TSV file, heres the code:

function db_to_tsv($conn,$table) {

  $file_path = "C:/wamp64/tmp/{$table}_dump.tsv";

  $columns_array = $conn->query("SELECT * FROM $table LIMIT 1")->fetch();
  unset($columns_array['id']);
  $columns = implode(', ',array_keys($columns_array));

  $headers_array = array_map(
    function($value,$key) { return "'$key'"; },
    $columns_array,
    array_keys($columns_array)
  );

  $headers = implode(', ',$headers_array);

  $query = "(SELECT $headers)
    UNION 
    (SELECT $columns
    FROM $table
    INTO OUTFILE '$file_path'
    FIELDS ENCLOSED BY '\"' 
    TERMINATED BY '\n' 
    ESCAPED BY '\"'
    LINES TERMINATED BY '\r\n');
    ";

    $export = $conn->query($query);

}

So the SQL query is this:

(SELECT $headers)
    UNION 
    (SELECT $columns
    FROM $table
    INTO OUTFILE 'C:/wamp64/tmp/{$table}_dump.csv'
    FIELDS ENCLOSED BY '\"' 
    TERMINATED BY '\n' 
    ESCAPED BY '\"'
    LINES TERMINATED BY '\r\n');
    ";

When I run the function, I get the error about secure-file-priv being set.

When I run SHOW VARIABLES LIKE "secure_file_priv" heres what it outputs:

enter image description here

Which is why I pointed the output file to C:/wamp64/tmp

Heres the error thats showing up:

PS C:\wamp64\www\spider\cheb> php dump.php actions
"Dumping actions to C:/wamp64/actions_dump.tsv"

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement' in C:\wamp64\
www\spider\cheb\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOConnection.php:104
Stack trace:
#0 C:\wamp64\www\spider\cheb\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\PDOConnection.php(104): PDO->query('(SELECT 'name',...')
#1 C:\wamp64\www\spider\cheb\vendor\doctrine\dbal\lib\Doctrine\DBAL\Connection.php(935): Doctrine\DBAL\Driver\PDOConnection->query('(SELECT 'name',...')
#2 C:\wamp64\www\spider\cheb\inc\spider_tools.php(275): Doctrine\DBAL\Connection->query('(SELECT 'name',...')
#3 C:\wamp64\www\spider\cheb\dump.php(9): Spider\db_to_tsv(Object(Doctrine\DBAL\Connection), 'actions')
#4 {main}
Next exception 'Doctrine\DBAL\Driver\PDOException' with message 'SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement' in C:\wam
p64\www\ in C:\wamp64\www\spider\cheb\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractMySQLDriver.php on line 115

I had the same issue when importing TSV files like this:

LOAD DATA INFILE 'c:/wamp64/tmp/chebiId_inchi.tsv' 
INTO TABLE chebi_inchi  
FIELDS TERMINATED BY "\t" 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS
(chebi_id, inchi)
SET ID = NULL ;

but I got around that by adding LOCAL to the code, so this worked:

LOAD DATA LOCAL INFILE 'c:/wamp64/tmp/chebiId_inchi.tsv' 
INTO TABLE chebi_inchi  
FIELDS TERMINATED BY "\t" 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS
(chebi_id, inchi)
SET ID = NULL ;

But for outputting files, there no LOCAL option. I tried adding this line: secure_file_priv='' to the top of the my.ini file, but that causes an error of some kind, I can't open the MySQL console with that line in there.

How do you go about disabling that secure-file-priv setting?

Horse O'Houlihan
  • 1,659
  • 4
  • 14
  • 29
  • 1
    if you execute `SHOW VARIABLES LIKE "secure_file_priv";` is your `C:/tmp/` listed there? does it actually exist? or `C:/wamp64/tmp/`? when you open `my.ini` and search for `secure-file-priv` - what can you see there? – Elen Nov 29 '16 at 10:38
  • `SHOW VARIABLES LIKE "secure_file_priv"` outputs `c:\wamp64\tmp\`. I can't find secure-file-priv in the `my.ini` file. I tried using `PHPMyAdmin` to output the data to a TSV file, and it worked. It did it pretty quickly too, 80,000 rows in under a minute. It outputted it to the Downloads directory, so somehow PHPMyAdmin bypasses these restrictions. – Horse O'Houlihan Nov 29 '16 at 11:50
  • ok change `$file_path = "C:/tmp/{$table}_dump.tsv";` to `$file_path = "C:/wamp64/tmp/{$table_dump}.tsv";` and `$query = "(SELECT $headers) UNION (SELECT $columns FROM $table INTO OUTFILE 'C:/tmp/{$table}_dump.csv' FIELDS ENCLOSED BY '\"' TERMINATED BY '\n' ESCAPED BY '\"' LINES TERMINATED BY '\r\n'); ";` to `$query = "(SELECT $headers) UNION (SELECT $columns FROM $table INTO OUTFILE $file_path FIELDS ENCLOSED BY '\"' TERMINATED BY '\n' ESCAPED BY '\"' LINES TERMINATED BY '\r\n'); ";` in your phpfile – Elen Nov 29 '16 at 12:52
  • Sorry I was meant to edit the post, I already did change the file path to `C:/wamp64/tmp` I'm having the same issue with trying to LOAD DATA INFILE if I don't use the LOCAL option. The reason I need to do this, is because the LOCAL option seems to prevent me from running this command through PHP (I can do it from the MySQL console). I put everything in `C:/wamp64/tmp` but it still gives me the same error. Surely there must be a way to disable this option in MySQL. – Horse O'Houlihan Nov 30 '16 at 21:14
  • I'm not sure what level of access you have to MySQL setup, perhaps you need to ask your server / hosting provider to fix it for you. Perhaps, this threat you will find useful - http://stackoverflow.com/questions/37596163/disable-secure-priv-for-data-loading-on-mysql – Elen Dec 01 '16 at 11:01
  • I'm supplying PHP with the root user login details, it has all privileges enabled. – Horse O'Houlihan Dec 02 '16 at 00:40

0 Answers0