PHP - 用于将 TXT/CSV 导入 SQL 的加载文件在服务器上不起作用，但在本地起作用？

Question

我想知道是否有人可以验证我的代码，在 Linux 服务器上运行这段代码时，我似乎遇到了一些问题。

如果我运行此本地主机(Xammp)，它会顺利运行，不会出现任何错误，也不会超时。

脚本创建数据库并从 TXT 或 CSV 填充。

当我将脚本移动到网络服务器上运行时我最终遇到以下错误:

Error creating table: Table 'Sales' already exists
Error: LOAD DATA INFILE 'Salest.CSV' REPLACE INTO TABLE `Sales` FIELDS TERMINATED BY ',' IGNORE 1 LINES
Access denied for user 'USERNAME'@'%' (using password: YES)

我的访问被拒绝？但如果我删除该表:并重新运行代码:

Table Sales created successfully
Error: LOAD DATA INFILE 'Salest.CSV' REPLACE INTO TABLE `Sales` FIELDS TERMINATED BY ',' IGNORE 1 LINES
Access denied for user 'USERNAME'@'%' (using password: YES)

当然，用户名和密码是正确的，因为它已在数据库中创建了表，为什么我在填充它时遇到问题？

我错过了什么？

<?php

ini_set("memory_limit",-1);    // Unlimited Memory  handle this properly just quick internal testing


        // Lets start by connecting to the Database
$databasehost = "localhost";    // Connection Local? Remote?
$databasename = "DBNAME";       // Database Name
$databasetable = "DBTABLE"; // Database Table
$databaseusername ="USERNAME";    // Database Username
$databasepassword = "";       // Database Password
$filename = "Sales.txt";    // File we will import (This will be picked up from FTP
$fieldseparator = "\t";             // Field Deliminator / separator
$lineseparator = "\n";              // New Line Deliminator / separator

/********************************/
$addauto = 0;                             // add an empty field at the beginning of these records 1 = yes / 0 = no
/********************************/

/********************************/
$save = 0;                                // Convert the file to a SQL file   0 = Import to MYSQL or 1 = Save to SQL File
$outputfile = "output.sql";     // The name of the SQL file you are going to save
/********************************/


// As the above is configurable, we should use a config file for the above, and use the code beloew in static file
// below code can be one one file, and we can use a FTP Collect routine and call this after.


if (!file_exists($filename)) {                                               // Does the file exist?
        echo "File not found. Make sure you specified the correct path.\n";  // Print message if it does not
        exit;                                                                // Exit out of it
}

$file = fopen($filename,"r");               // open the CSV or TXT file  (Test if this works on TXT may need re-formatting)

if (!$file) {                               // statement if problem opening file
        echo "Error opening data file.\n";  // Print the error message
        exit;                               // exit out
}                                           // end of statement

$size = filesize($filename);                    //check file exists but has a valid size (not 0kb)

if (!$size) {                               // If the size is 0kb i.e. not valid then error
        echo "File is empty.\n";            // rpint the error message
        exit;                               // exit out
}                                           // end of statement

$filecontent = fread($file,$size);          //Declare varable CSV file - Read in the file and size

fclose($file);                              // close the file


                                // Now lets connect to the database and select the database we will work from
$conn = new mysqli($databasehost, $databaseusername, $databasepassword, $databasename);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


                            // Now we are connected to the Database, Lets create the table we will start to import data too.
              // List of colums we will be using in the Sales Table:
              //uniquekey, type, itemno, styleno, styledesc, variatdesc, date, hour, day, month, year, locn, dept2, dept1, dept0, depta, season, reason, supplier, brand, fullprice, soldat, cost, margin, qty, cashier, custkey, stockkey
$sqlcreate = "CREATE TABLE $databasetable (

uniquekey Int(11) PRIMARY KEY,
type VARCHAR(20) NULL,
itemno Int(11) NULL,
styleno Int(11) NULL,
styledesc VARCHAR(40) NULL,
variatdesc VARCHAR(20) NULL,
date DATE,
hour Int(11) NULL,
day Int(11) NULL,
month Int(11) NULL,
year Int(11) NULL,
locn VARCHAR(20) NULL,
dept2 VARCHAR(20) NULL,
dept1 VARCHAR(20) NULL,
dept0 VARCHAR(20) NULL,
depta VARCHAR(20) NULL,
season VARCHAR(20) NULL,
reason VARCHAR(20) NULL,
supplier VARCHAR(20) NULL,
brand VARCHAR(20) NULL,
fullprice FLOAT(9,3) NULL,
soldat FLOAT(9,3) NULL,
cost FLOAT(9,3) NULL,
margin FLOAT(9,3) NULL,
qty Int(11) NULL,
cashier VARCHAR(40) NULL,
custkey Int(11) NULL,
stockkey VARCHAR(40) NULL

)";

if ($conn->query($sqlcreate) === TRUE) {
    echo "Table <b>" . $databasetable ."</b> created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}



// exit ;   // Exit was used to end the script early, Write the code, and test the code in 2 sections.

// Now the table is created, How about grabbing a text file for an example and seeing if we can put it into the SQL
// So Another SQL Query  Instead of using INTO Table ****(Tablename)****  We will use REPLACE INTO TABLE
// So if the same data is re-sent up, it will replace its original, Need to check this method does not have issues
// down the line with Indexs, If so, then the replace statement could be met with When log_time is > than original

$sqlimport = "
    LOAD DATA INFILE '$filename'
    REPLACE INTO TABLE `$databasetable`
    FIELDS TERMINATED BY '$fieldseparator'
    IGNORE 1 LINES
    " ;

if ($conn->query($sqlimport) === TRUE) {
    echo "<br>Data Imported from " .$filename." OK!";
} else {
    echo "<br>Error: " . $sqlimport . "<br>" . $conn->error;
}

?>

Answer 1

经过多次尝试和错误，我终于解决了这个问题。

$sqlimport = "
LOAD DATA INFILE '$filename'

替换为:

$sqlimport = "
LOAD DATA LOCAL INFILE '$filename'

给我一​​个新错误，本地命令不允许使用 MySQL 版本。SQL 语句:SHOW VARIABLES LIKE 'local_infile'给我结果:local_infile OFF

宾果!!!更改为“开”，然后重新启动，脚本按预期工作。

感谢您的帮助和建议。