How to Resolve the 'Loading Local Data is Disabled' Error in MySQL?

Resolving "Loading Local Data is Disabled" Error: A Step-by-Step Guide

When attempting to upload local data using LOAD DATA LOCAL, you may encounter the error message:

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

This error indicates that local data loading is prohibited on either the client or server. Here's a detailed guide to enable local data on both sides:

Client-Side Configuration

1. Ensure that the client MySQL library is compiled with --enable-local-infile.
2. Set the MYSQL_ENABLE_LOCAL_INFILE environment variable to 1 before connecting to MySQL.

Server-Side Configuration

1. Connect to the MySQL server.
2. Execute the following command to enable local data loading on the server:

mysql> SET GLOBAL local_infile=1;

3. Quit the current server session:

mysql> quit

4. Restart the MySQL server with the --local-infile flag to make the change persistent.

Loading Data into MySQL

Once both the client and server sides are configured, you can proceed with importing data using the LOAD DATA LOCAL statement:

mysql> USE <database_name>;
mysql> LOAD DATA LOCAL INFILE '<file_path>' INTO TABLE <table_name>;

Example

For instance, to load a CSV file named amazonsample.csv into the toys table, use this command:

mysql> USE toys_db;
mysql> LOAD DATA LOCAL INFILE '/Users/BruddaDave/Desktop/amazonsample.csv' INTO TABLE toys
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

By following these steps, you should now be able to import local data into your MySQL database without encountering the "Loading Local Data is Disabled" error.

The above is the detailed content of How to Resolve the 'Loading Local Data is Disabled' Error in MySQL?. For more information, please follow other related articles on the PHP Chinese website!