LOAD DATA INFILE must be enabled at mysql start

November 14, 2009 § 1 Comment

LOAD DATA INFILE works only if you enable –local-infile at the start of mysql. In mac osx use the terminal’s drag and drop capability to insert the full path of the import file.

Note: WordPress automatically remove the double dash in blog post. Be sure you enter 2 dashes in front of local and a single dash in front of infile in your command line.

Syntax:
mysql –local-infile -u <username> -p <DatabaseName>
mysql> load data local infile ‘<drag input file here>’ into table <TableName>;

Use LOCAL only if you are adding the import file into MySQL’s data folder. Be aware you would have to figure out where the MySQL database directory. Defaulted MySQL’s directory structure is very different from macport installed MySQL.
mysql> LOAD DATA LOCAL INFILE ‘<dir>/pet.txt’ INTO TABLE pet FIELDS terminated by ‘<delimiter>’;

Note: WordPress automatically remove the double dash in blog post. Be sure you enter 2 dashes in front of local and a single dash in front of infile in your command line.

Code Example:
mysql –local-infile -u root -p sample
Enter password:
mysql> use sample;
Database changed
mysql> LOAD DATA INFILE ‘/Users/<Username>/database/company.txt’ INTO TABLE company (name, title, company);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from company;

Enable LOAD DATA INFILE:
–local-infile must contain 2 dashes instead of 1 dash
If you don’t enable it at the start of mysql, you’ll receive this error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘/Users/<Username>/database/country INTO TABLE country’ at line 1

Give full permission on .txt file
sudo chmod -R 777 country
If you don’t give permission, you’ll receive this error

ERROR 13 (HY000): Can’t get stat of ‘/Users/MediNexus/database/country.txt’ (Errcode: 2)

Must include single quote ‘ after dragging the file into terminal and close it with single quote ‘
If you don’t include single quote before and after dragging the file, you’ll receive this error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘/Users/<Username>/database/country INTO TABLE country (country, country_code)’ at line 1

mysql> LOAD DATA INFILE ‘/Users/<Username>/database/country’ INTO TABLE country (country, country_code);
Query OK, 248 rows affected (0.00 sec)
Records: 248  Deleted: 0  Skipped: 0  Warnings: 0

Advertisements

§ One Response to LOAD DATA INFILE must be enabled at mysql start

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

What’s this?

You are currently reading LOAD DATA INFILE must be enabled at mysql start at Web Builders.

meta

%d bloggers like this: