0

I've seen this question asked about a dozen times and the answers that seem to work for everyone are the same: add local-infile = 1 to [mysql], [mysqld], and add [client] loose-local-infile=1 to my.conf. I've done all of that as well as adding file permission to my DB user for . and I still get the same error.

I'm running MySQL 5.6 on a virtual Ubuntu 14.04 server. Can anyone let me know what I'm missing? I've got this working successfully on my live server which is a VPS running CentOS and MySQL 5.6 as well and I don't remember having to jump through any hoops to get it working, though it was some time ago that I set it up.

If it matters, I'm attempting this import through the below PHP function:

// Connect to a database without using prepared statements (for LOAD FILE feed commands)
// $query = query to execute; $rs = return a result set if true
function db_query_unprepared($query, $rs = false) {
    // Create database connection
    $link = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
    if (!$link) {
        print 'Error connecting to MySQL Server. Errorcode: ' . mysqli_connect_error(); 
    exit; 
    }
    // Execute the query
    if ($rs) {
        $queryresults = array();
        $counter = 0;
        if (mysqli_multi_query($link, $query)) {
            do {
                // Save results to an array
                if ($result = mysqli_store_result($link)) {
                    while ($row = mysqli_fetch_assoc($result)) {
                        $queryresults[$counter][] = $row;
                    }
                    mysqli_free_result($result);
                    $counter++;
                }
            } while (mysqli_next_result($link));
        }
        else {
            print mysqli_error($link);
        }
    }
    else {
        mysqli_multi_query($link, $query);
    }
    mysqli_close($link);

    // Return the results
    if (isset($queryresults)) {
        return $queryresults;
    }
}

That in turn is called like so:

db_query_unprepared('LOAD DATA LOCAL INFILE \'/path/to/mydatafile.csv' . '\' INTO TABLE feed_mydata FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\r\n\' IGNORE 1 LINES ( field1, field2, field3 )');
Marc B
  • 356,200
  • 43
  • 426
  • 500
Sean Cunningham
  • 3,006
  • 5
  • 24
  • 35
  • did you restart mysql after making your .conf changes? – Marc B Aug 13 '14 at 21:47
  • I did, sorry, meant to include that bit as well. – Sean Cunningham Aug 13 '14 at 21:47
  • http://stackoverflow.com/questions/20226631/load-data-local-infile-does-not-work-from-php-5-5-using-pdo – Marc B Aug 13 '14 at 21:50
  • 1
    `LOCAL` is intended for use where the MySQL client is located on a different machine from the MySQL server. It appears you have both in the same place and you're not using the client software - try dropping the `LOCAL` keyword. –  Aug 13 '14 at 21:52
  • Didn't know that about the LOCAL keyword -- I took it out and now I'm at least getting a different error: Error in query (29): File '/path/to/mydatafiles.csv' not found (Errcode: 13 - Permission denied). I ran chmod -R ug+rw /path/to but have had no luck. I also verified that the file does exist in that folder. – Sean Cunningham Aug 13 '14 at 22:02
  • @MarcB Thanks, forgot to mention that I've already verified that mysql.allow_local_infile was set in php.ini. – Sean Cunningham Aug 13 '14 at 22:03
  • 1
    If you don't have the `LOCAL` keyword, then it's the mysqld server process that is reading the file. MySQL normally runs as user `mysql`, if that user is not the owner of the file, and is not a member of the group, you'd need to grant read privilege on the file to "others"... **`chmod o+r /path/to/mydatafile.csv`**. – spencer7593 Aug 13 '14 at 22:10
  • Make sure that the Linux user that MySQL is executing as has read permission in that folder. Make sure also that the MySQL user that you're using to run your query has been granted the `FILE` privilege. –  Aug 13 '14 at 22:14
  • Ok, I ran the chmod o+r command, same problem unfortunately. I also ensured that the MySQL user has ben given the FILE privilege on all databases. No dice. – Sean Cunningham Aug 13 '14 at 23:19
  • EDIT: ahh, looks like I also had a formatting issue with my file as I was getting duplicate ID values. I think the chmod must have done it. Thanks a bunch to both Mike W and spencer! I also found this which seems to have helped as well: http://stackoverflow.com/questions/4215231/load-data-infile-error-code-13 – Sean Cunningham Aug 13 '14 at 23:36

0 Answers0