Let’s break down what’s going on here: your first export worked because both the staging and target tables were empty—no need to clear any existing data. But subsequent runs fail because Sqoop can’t clear the staging_weather_data table before starting the new export. Here are the most practical fixes, ordered by how common they are:

1. Verify MySQL User Permissions #

Sqoop needs explicit permissions to clear the staging table. If the user you’re using for the export lacks DELETE or TRUNCATE access to staging_weather_data, this error is almost guaranteed.

• Check the current grants for your Sqoop user:

  SHOW GRANTS FOR 'your_sqoop_user'@'your_client_host';
  

• If DELETE/TRUNCATE aren’t listed, grant them:

  GRANT DELETE, TRUNCATE ON your_database.staging_weather_data TO 'your_sqoop_user'@'your_client_host';
  FLUSH PRIVILEGES;
  

2. Clear Stuck Table Locks #

Sometimes a previous export might have crashed or hung, leaving a lingering lock on the staging table that blocks Sqoop’s clear operation.

• Check for active locks on the table:

  SHOW OPEN TABLES WHERE In_use > 0 AND Table_name = 'staging_weather_data';
  

• Or scan all running MySQL processes to find stuck sessions:

  SHOW PROCESSLIST;
  

• Kill any process holding a lock on the staging table (replace [process_id] with the actual ID):

  KILL [process_id];
  

3. Use TRUNCATE Instead of DELETE for Clearing #

With 80M+ records, Sqoop’s default DELETE operation to clear the staging table is slow, prone to timeouts, and resource-heavy. Switching to TRUNCATE is way faster and avoids these issues.

Add this parameter to your Sqoop export command:

--staging-table-clearing-mode truncate

4. Resolve Connection Timeouts or Direct Mode Conflicts #

• If you’re using the --direct parameter in your Sqoop command, try removing it—direct mode can sometimes interfere with staging table operations.
• If the issue stems from a timeout while clearing the large table, increase connection timeouts. Create a file (e.g., mysql_timeout_params.txt) with:

  connectTimeout=300000
  socketTimeout=300000
  

  Then reference it in your Sqoop command:

  --connection-param-file mysql_timeout_params.txt
  

5. Manually Test and Repair the Staging Table #

If none of the above works, manually clear the table to rule out corruption:

TRUNCATE TABLE staging_weather_data;

If this fails, check for table corruption:

CHECK TABLE staging_weather_data;

If corruption is found, repair it:

REPAIR TABLE staging_weather_data;

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