title: WAMP - ySQL - DIsabling secure-file-priv
body: <p>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:</p>
  
  <pre><code>function db_to_tsv($conn,$table) {
  
    $file_path = "C:/wamp64/tmp/{$table}_dump.tsv";
  
    $columns_array = $conn-&gt;query("SELECT * FROM $table LIMIT 1")-&gt;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-&gt;query($query);
  
  }
  </code></pre>
  
  <p>So the SQL query is this:</p>
  
  <pre><code>(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');
      ";
  </code></pre>
  
  <p>When I run the function, I get the error about <code>secure-file-priv</code> being set. </p>
  
  <p>When I run <code>SHOW VARIABLES LIKE "secure_file_priv"</code> heres what it outputs:</p>
  
  <p><a href="https://i.stack.imgur.com/g538N.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/g538N.png" alt="enter image description here"></a></p>
  
  <p>Which is why I pointed the output file to <code>C:/wamp64/tmp</code></p>
  
  <p>Heres the error thats showing up:</p>
  
  <pre><code>PS C:\wamp64\www\spider\cheb&gt; 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-&gt;query('(SELECT 'name',...')
  #1 C:\wamp64\www\spider\cheb\vendor\doctrine\dbal\lib\Doctrine\DBAL\Connection.php(935): Doctrine\DBAL\Driver\PDOConnection-&gt;query('(SELECT 'name',...')
  #2 C:\wamp64\www\spider\cheb\inc\spider_tools.php(275): Doctrine\DBAL\Connection-&gt;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
  </code></pre>
  
  <p>I had the same issue when importing TSV files like this:</p>
  
  <pre><code>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 ; 
  </code></pre>
  
  <p>but I got around that by adding <code>LOCAL</code> to the code, so this worked:</p>
  
  <pre><code>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 ; 
  </code></pre>
  
  <p>But for outputting files, there no LOCAL option. I tried adding this line: <code>secure_file_priv=''</code> to the top of the <code>my.ini</code> file, but that causes an error of some kind, I can't open the MySQL console with that line in there. </p>
  
  <p>How do you go about disabling that <code>secure-file-priv</code> setting?</p>