MySQL: SELECT * INTO OUTFILE returns always empty array and no error_code?

Question

I'm trying to dump a table into a MySQL file using PHP. But what I get is an empty array, and not any file is created. While if I remove the instruction " INTO OUTFILE '$mysqldumpfile'", everything works fine. please here the code:

<?php
$tablename = "song";
$mysqldumpfile = "mysql_dump.sql";
$pdo_options = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', );

try {
    // Call MySQL DB
    $sql = new PDO($servername, $username, $password, $pdo_options);
} catch (PDOException $e) {
    die("DB not available");
}

// Dump MySQL
$sth = $sql->prepare("SELECT * INTO OUTFILE '$mysqldumpfile' FROM $tablename");
$sth->execute();
$result = $sth->fetchAll();
print_r($result);
if(! $result) {
  die('Could not load data : ' . mysql_error());
}
$sql = null;
?>

just in case, documentation: http://dev.mysql.com/doc/refman/5.6/en/select-into.html — boisvert, Apr 24 '15 at 09:08
Several thoughts: fetchall - will be empty, since the result is not returned, but saved; so there won't be much to print_r. Your file: file permissions on the server? The script needs to have permission to write the file. — boisvert, Apr 24 '15 at 09:14
As I said in the beginning of the post, there is not any file created. and the array is empty. — Tormy Van Cool, Apr 24 '15 at 09:25

score 1 · Answer 1 · answered Apr 24 '15 at 09:03

1

make sure mysql has the rights to access that file, also the fiel can not exist prior to the query, mysql has to be the one generating the file, and the filepath has to be absolute

answered Apr 24 '15 at 09:03

Victor Radu

2,262
1
12
18

1

Yes the rights there are, and I tried also with absolute path without getting results. The file doesn't exist prior the query. – Tormy Van Cool Apr 24 '15 at 09:08
4

try setting 777 acces to the folder, also try the query in a mysql manager like phpmyadmin or something and see if that works – Victor Radu Apr 24 '15 at 09:30
1

Giving 777 the server returns Error 500. Seding the SELECT with phpMyAdmin it returns that the user has not the right to execute that command: #1045 - Access denied for user 'admin'@'%' (using password: YES) May be the provider's settings? – Tormy Van Cool Apr 24 '15 at 09:50
2

ok well there you go, your mysql user is not allowed to run this command – Victor Radu Apr 24 '15 at 10:02

score -1 · Answer 2 · answered Apr 24 '15 at 09:19

-1

See if this works for you:

http://stackoverflow.com/a/16910191/2511206
OR
http://stackoverflow.com/a/26749685/2511206

answered Apr 24 '15 at 09:19

Red Acid

217
1
3
14

MySQL: SELECT * INTO OUTFILE returns always empty array and no error_code?

2 Answers2