使用mysqli向MySQL导入大CSV文件失败求助

Hey there, let's troubleshoot why your CSV import script stopped working out of the blue—even though the temp table gets cleared, the LOAD DATA INFILE step isn't running, and you're not seeing any errors. Here are the key areas to check:

1. Verify local_infile is enabled (server + client side) #

The LOAD DATA LOCAL INFILE command requires both your MySQL server and PHP's mysqli client to have this setting enabled. It’s common for server configs to change unexpectedly, even if you didn’t touch them:

• First, log into MySQL (via command line or phpMyAdmin) and run:

  SHOW VARIABLES LIKE 'local_infile';
  

  If the result is OFF, you need to turn it on:
  • Temporary fix: Run SET GLOBAL local_infile = 1; (note: this resets after MySQL restarts, and you need admin privileges).
  • Permanent fix: Edit your my.cnf (Linux) or my.ini (Windows) file, add local_infile = 1 under both the [mysqld] and [mysql] sections, then restart the MySQL service.
• Next, update your dbconnect.php to allow local file imports via mysqli. When establishing the connection, add the MYSQLI_CLIENT_LOCAL_FILES flag:

  // Example connection code with the flag
  $link = mysqli_init();
  mysqli_real_connect($link, $host, $user, $password, $dbname, null, null, MYSQLI_CLIENT_LOCAL_FILES);
  

2. Double-check the CSV file path and permissions #

A missing or inaccessible CSV file can cause LOAD DATA to fail silently (or without obvious errors):

• Add a quick check in your script to confirm the file exists:

  $csvPath = '/home/data.csv';
  if (!file_exists($csvPath)) {
      fwrite($log, "ERROR: CSV file not found at {$csvPath}\n");
  } elseif (!is_readable($csvPath)) {
      fwrite($log, "ERROR: No read permissions for {$csvPath}\n");
  }
  

• Ensure the MySQL process user (usually mysql or www-data, depending on your setup) has read access to the file. Run ls -l /home/data.csv to check permissions, and use chmod o+r /home/data.csv to grant read access to all users (or adjust the file's group to match the MySQL user's group for tighter security).
• Note: Some systems restrict access to the /home directory (default permissions are often 700). If that’s the case, move your CSV to a more accessible location like /var/www/data.csv.

3. Inspect the CSV file for formatting issues #

Even a tiny change in the CSV structure can break the import:

• Line endings: If the CSV was generated on Windows, it might use \r\n instead of \n for line breaks. Update your LOAD DATA query to match:

  LOAD DATA LOCAL INFILE '/home/data.csv' 
  INTO TABLE rim6jtvnox6vmwxk.TEMPDATA 
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
  LINES TERMINATED BY '\r\n' 
  IGNORE 1 ROWS;
  

• Unescaped quotes: Look for fields containing unescaped double quotes (e.g., Gift with "free" bonus). These break the ENCLOSED BY '"' rule—you’ll need to fix the CSV to escape inner quotes with another double quote (like Gift with ""free"" bonus).
• UTF-8 BOM: If your CSV has a UTF-8 byte order mark (BOM), the first row’s header will be corrupted. Use a text editor like Notepad++ to convert the file to "UTF-8 without BOM".

4. Improve error logging to catch hidden warnings #

Your current script only logs a boolean value for $update—this misses critical warnings that don’t trigger a full error but still prevent imports:

• Replace the LOAD DATA logging section with this more detailed code:

  $query = "LOAD DATA LOCAL INFILE '/home/data.csv' INTO TABLE rim6jtvnox6vmwxk.TEMPDATA FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS";
  $update = mysqli_query($dbh, $query);
  
  if ($update) {
      // Check for warnings (e.g., data type mismatches, truncated fields)
      $warningCount = mysqli_warning_count($dbh);
      if ($warningCount > 0) {
          $warning = mysqli_get_warnings($dbh);
          fwrite($log, "CSV updated with warnings: {$warning->message}\n");
      } else {
          fwrite($log, "TEMPDATA Table updated with CSV\n");
      }
  } else {
      fwrite($log, "Failed CSV update - Error: " . mysqli_error($dbh) . "\n");
      fwrite($log, "Error code: " . mysqli_errno($dbh) . "\n");
  }
  

  Warnings often reveal issues like mismatched column counts or invalid data types that don’t throw a full error but stop the import from completing.

5. Check MySQL’s general query log for server-side clues #

If all else fails, enable MySQL’s general query log to see exactly what’s happening when your script runs the LOAD DATA command:

• Temporarily enable the log via MySQL command line:

  SET GLOBAL general_log = 1;
  SET GLOBAL general_log_file = '/var/log/mysql/general.log';
  

• Run your PHP script, then disable the log to avoid filling up disk space:

  SET GLOBAL general_log = 0;
  

• Open the log file and look for the LOAD DATA statement. You’ll see if MySQL received the query, and any server-side errors or messages that didn’t make it to your PHP script.

内容的提问来源于stack exchange，提问作者Philip Saville