Background: I moved some websites from a shared hosting account to a VPS. Everything works extremely smooth with the only exception of lines of php code which execute queries with LOAD DATA LOCAL INFILE, using data sources from external servers (i.e. http://server1.net/getfile.php?f=101). This code works (and worked for long time) at the shared hosting account, however, this account is reaching resource limits, especially in the area of database size/speed.
Problem: Code with LOAD DATA INFILE which downloads from a http:// address does not work anymore after been moved to a (new) VPS.
System and software: The VPS is used as a web-server, running Ubuntu (Ubuntu 14.04) with Vesta (0.9.8 (amd64)), apache, nginx, mysql (5.5.44), app-armor among others. All recently installed and very little or not changed.
Selection from phpinfo() output:
System Linux MyServer 3.13.0-62-generic #102-Ubuntu SMP Tue Aug 11 14:29:36 UTC 2015 x86_64
Apache Version Apache/2.4.7 (Ubuntu) mod_fcgid/2.3.9 PHP/5.5.9-1ubuntu4.11 OpenSSL/1.0.1f
Server Root /etc/apache2
PATH /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
SERVER_SOFTWARE Apache/2.4.7 (Ubuntu) mod_fcgid/2.3.9 PHP/5.5.9-1ubuntu4.11 OpenSSL/1.0.1f
allow_url_fopen On On
allow_url_include Off
doc_root no value
docref_ext no value
docref_root no value
file_uploads On
open_basedir no value
sys_temp_dir /tmp
upload_tmp_dir /tmp
user_dir no value
cURL support enabled
mysql
Directive Local Value Master Value
mysql.allow_local_infile On On
mysqli
Directive Local Value Master Value
mysqli.allow_local_infile On On
A few lines from /etc/mysql/my.cnf
[client]
loose-local-infile=1
local-infile=1
[mysqld]
basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
local-infile=1
Below code runs in the same directory as the file with phpinfo()
Test code:
$url = "http://myserver.net/x.csv";
$file = "/var/lib/mysql/test2.csv";
$handle = curl_init($url);
curl_setopt($handle, CURLOPT_RETURNTRANSFER, TRUE);
/* Get the HTML or whatever is linked in $url. */
$response = curl_exec($handle);
/* Check for 404 (file not found). */
$httpCode = curl_getinfo($handle, CURLINFO_HTTP_CODE);
echo("<br>Http code: ". $httpCode);
curl_close($handle);
echo "<br>Temp_name: ". $_FILES["file"]["tmp_name"];
echo "<br>Host info: " . $conn->host_info . "\n";
echo "<br>Client info: ".mysqli_get_client_info($conn) ."\n";
$conn = mysqli_init();
if (!$conn) {
die('mysqli_init failed');
}
if (!$conn->options(MYSQLI_OPT_LOCAL_INFILE, true)) {
die('Setting MYSQLI_OPT_LOCAL_INFILE failed');
}
if (!$conn->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5)) {
die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}
if (!$conn->real_connect('localhost','root','pass','database')) {
die('Connect Error (' . mysqli_connect_errno() . ') '
. mysqli_connect_error());
}
$conn->query("LOAD DATA INFILE '". $file ."' INTO TABLE x_import (@col1) SET ID = @col1;");
echo("<br>Error file: " . mysqli_error($conn));
$conn->query("LOAD DATA INFILE '". $url ."' INTO TABLE x_import (@col1) SET ID = @col1;");
echo("<br>Error url: " . mysqli_error($conn));
$conn->query("LOAD DATA LOCAL INFILE '". $file ."' INTO TABLE x_import (@col1) SET ID = @col1;");
echo("<br>Error file (LOCAL): " . mysqli_error($conn));
$conn->query("LOAD DATA LOCAL INFILE '". $url ."' INTO TABLE x_import (@col1) SET ID = @col1;");
echo("<br>Error url (LOCAL): " . mysqli_error($conn));
$conn->query("SELECT * INTO OUTFILE 'andreas.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM x_import;");
echo("<br>Error OutFile: " . mysqli_error($conn));
Code result:
Http code: 200
Temp_name:
Host info:
Client info: 5.5.44
Error file:
Error url: Can't get stat of '/var/lib/mysql/http:/myserver.net/x.csv' (Errcode: 2)
Error file (LOCAL): File '/var/lib/mysql/test2.csv' not found (Errcode: 13)
Error url (LOCAL): File 'http:/myserver.net/x.csv' not found (Errcode: 2)
Error OutFile: File 'andreas.txt' already exists
The php code using the root account with mysql, so full access/privileges there.
The first line with sql code (LOAD DATA INFILE without LOCAL using the local file) adds the data from the file in the database correctly.
The last line with the INTO OUTFILE code creates correctly a file in /var/lib/mysql/database/ (and 2nd time gives the already exists error)
From all research so far I understand:
- Error code 13 is a permission issues.
- Error code 02 is when it is unable to find the file.
- "LOAD DATA" is for a file on the database server.
- "LOAD DATA LOCAL" is for a file on the client (apache/php-code).
What I do not understand is why there is a / missing in the URL when using LOCAL (see code output). I also do not understand why there is a error code 13 when LOCAL is added to the URL (php receives code 200 in first part of code). It seems to me that both php and mysql are not able to look at the right place for several reasons.
Below I will explain a few attempts of many things I have tried (mostly from loads of other answers on this website):
I have changed the file permissions for /var/ and /var/lib/ to 755 (and once to 777) but this did not change my test code output. /tmp/ was already set with 777.
In app-armor I have changed mysql into complain mode, as well tried some change in the mysql config file for app-armor (usr.sbin.mysqld), but both did not change the test code output.
Useful documentation (but maybe not fully understood by me): http://dev.mysql.com/doc/refman/5.5/en/load-data-local.html
I prefer using the LOAD DATA LOCAL but if it works in any other way I am happy too.
Please help. Any questions and suggestions are welcome.
