Need Help with LOAD DATA LOCAL INFILE in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
BobH - TAN
Forum Newbie
Posts: 20
Joined: Mon Mar 20, 2006 7:09 pm
Location: Texas

Need Help with LOAD DATA LOCAL INFILE in MySQL

Post by BobH - TAN »

I'm trying to load a new, empty table from a tab separated fields file created in MSWord and saved as plain text with carriage return and line feed (CR/LF) after each line of data. Where there is no data in a field '\N' is placed to indicate a null value and is properly followed by either a tab character or a CR/LF The file is located in the root directory of my C: drive and is named 'names2.txt'. My database is hosted on GoDaddy which means that I can access it only through the PHPMyAdmn (PAM) interface. The table is named 'Names'.

The statement executes without returning any error, but the table is not updated. I have scoured my data looking for anomalies and can find none (doesn't mean there aren't any). I have also tried using comma separated values (CSV) with appropriate change to the LOAD statement. I changed the backslash in the path/filename statement to a forward slash based on reading the MySQL forum. I also tried using a double backslash (first one escapes the second). I get no difference in result; however, if I make a typo in the filename field or otherwise create a syntax error, I get a MySQL error. I also tried to run it without the final "LINES TERMINATED BY '\r\n'' clause; same result - no errors, no records updated.

What am I doing wrong? I've had help from someone else on a database forum and he was able to upload the data using the exact same file and MySQL statements. He offered to load it for me and send it to me as a backup file to use to replace the existing file, but I need to learn what I'm doing wrong as much as I need to load the data. Can anyone help? I'd be much obliged for suggestions about other things to try.

Here is the statement I put in the SQL panel of PAM:
LOAD DATA LOCAL INFILE 'C:/names2.txt' INTO TABLE Names FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n';
Here is the table definition copied from the PAM EXPORT function:
-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: 10.0.11.64
-- Generation Time: May 06, 2006 at 03:10 PM
-- Server version: 4.0.24
-- PHP Version: 4.3.2
--
-- Database: `broughton`
--

-- --------------------------------------------------------

--
-- Table structure for table `Names`
--

CREATE TABLE `Names` (
`student_id` tinyint(3) unsigned zerofill NOT NULL auto_increment,
`firstname` varchar(15) NOT NULL default '',
`middle` varchar(15) default NULL,
`lastname` varchar(20) NOT NULL default '',
`suffix` varchar(5) default NULL,
`marriedname` varchar(20) default NULL,
`male_female` char(1) default 'F',
UNIQUE KEY `primary_key` (`student_id`)
) TYPE=MyISAM COMMENT='Broughton HS Class of 1960 Members' AUTO_INCREMENT=2 ;

--
-- Dumping data for table `Names`
--
Here are the first few records from the input file (don't know why the CR/LF don't appear or don't seem to work in a 'copy' function):
000 James McKenzie Adams \N \N M 001 Jane Laverne Adams \N \N \N 002 John W Allen \N \N M 003 Joyce Lyvonne Allen \N \N \N 004 Margaret Jean Allen \N \N \N 005 Ellen Donna Allsbrook \N \N \N 006 Robert Chapman Anderson \N \N M 007 Azalee Truedell Archer \N \N \N 008 Donald Tex Bagwell \N \N M 009 Cynthia Anne Bailey \N \N \N 010 Nancy Ruth Bailey \N \N \N 011 Robert Lander Baird \N \N M 012 Benjamin Whiteley Baker \N \N M 013 Walter Wray Baker \N \N M 014 Frances Lewis Barbour \N \N \N 015 Max O \'Brien Barbour \N \N M 016 James Grover Barefoot \N \N M 017 Rebecca Sue Barnes \N \N \N 018 Sarah Hill Barnhardt \N \N \N 019 Thomas Jefferson Bashford \N \N M 020 John Alsgood Bassler \N \N M 021 Donna Rae Beaird \N \N \N 022 Baker Guy Beattie Jr \N M 023 George Williams Bensch \N \N M 024 Clifton Linwood Benson Jr \N M 025 William Allen Benton \N \N M
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Need Help with LOAD DATA LOCAL INFILE in MySQL

Post by timvw »

BobH - TAN wrote: 1-) The file is located in the root directory of my C: drive and is named 'names2.txt'.
2-) My database is hosted on GoDaddy which means that I can access it only through the PHPMyAdmn (PAM) interface
How should godaddy be able to access your 'C:\' drive? I believe you can upload the dump via phpmyadmin.. Or simply copy-paste the sql-statements, ..
BobH - TAN
Forum Newbie
Posts: 20
Joined: Mon Mar 20, 2006 7:09 pm
Location: Texas

Client Side Function in LOAD DATA

Post by BobH - TAN »

Reading the MySQL documentation, it clearly states that inclusion of the term LOCAL after LOAD DATA and before INFILE sets up execution of the update file reads on the client side. The data are then passed over the connection (in this case the Internet) and the table loading is done on the server side. The documentation even gives the rev level and version number where this functionality was introduced. I'm using MySQL at a higher release level.

GoDaddy.com keeps the addressing of user databases to themselves; that is, they do not divulge the server address pathname; therefore one cannot upload the data file and have the LOAD executed solely on the server side. They do this intentionally so that they maintain total control of the database servers hardware without interference from their users. This statement is true in so far as database management at the low cost end of their hosting services. They might have another offering that would let users have that knowledge of physical addresses of their databases. This business decision on the part of GoDaddy forces the low-end user (me) to use the client side data file in the load process.

Unless you can point me to something other than the source documentation from MySQL, I must believe that my approach is valid. I believe the problem lies in the syntax of my load statement or in unknown conditions in my data; however, because no MySQL errors result from execution, I don't have any specific indicators pointing to either or both of my suspected causes.

Please don't think I'm being defensive. I'm only trying to make sense of the documentation I read and my interpretation of it within the confines of my hosting paradigm. I'll be perfectly happy to be shown wrong.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I hope you realize that the client in this case is where the connection to MySQL is created, i.e. where the script is being run from on the server, not the requesting agent a.k.a. browser.
BobH - TAN
Forum Newbie
Posts: 20
Joined: Mon Mar 20, 2006 7:09 pm
Location: Texas

LOAD DATA LOCAL

Post by BobH - TAN »

Hi feyd!

No, I don't understand your statement. In this case I truly don't know if I'm running anything on my local (client) machine other than the browser. From the way I invoke PAM via the browser, I assume that PAM is executing on the host server side. From reading the MySQL documentation, I understand that MySQL will (should) execute on the server side but will issue a remote read on my local machine to get the data from the pathname/filename I specify.

What I don't understand is why I can execute other commands from the panel that comes up when I click the PAM SQL tab while my table structure is displayed but can't execute this one. Well, it executes (says it took nnn seconds to run the query) but doesn't give an error or do any updating.

Thanks for the response.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

the mysql client (in this case built-in the php-engine executes at the godaddy webserver... I
f you tell the client (through the phpmyadmin webinterface) to access *local* files it considers the files on the webserver *local* and knows absolutely about your computer.. (Imagine this would be possible, would you really want to browse websites that can access your filesystem? ;))

Thus yes, you can issue command to the client via the webinterface, and then the client (at the webserver) will issue the commands... But the clients 'local' context remains at the webserver.
BobH - TAN
Forum Newbie
Posts: 20
Joined: Mon Mar 20, 2006 7:09 pm
Location: Texas

LOAD DATA LOCAL

Post by BobH - TAN »

Hi timvw!

Thanks for the response.

I understand what you are saying; however, GoDaddy gives me no way to FTP (or transfer otherwise) the *.txt file to the host. I thought the whole purpose of the 'LOCAL' clause in the LOAD DATA statement was to allow the server side app to issue a read on the local (client) side file system. Otherwise, why have an optional LOCAL qualifier? This is clearly inferred from the MySQL doc and from MySQL forum tutorials and other statements I've read. I'm reading section 7.23 of MySQL documentation (it doesn't contain a rev level reference that I could find) that I downloaded from the MySQL site.

Also, by allowing the user to specify the pathname/filename, the issue of client side invasion from the host is mitigated.

What am I missing?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

In PHPMyAdmin, you can upload that SQL dump you have by clicking on the SQL tab.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
BobH - TAN
Forum Newbie
Posts: 20
Joined: Mon Mar 20, 2006 7:09 pm
Location: Texas

LOAD DATA LOCAL

Post by BobH - TAN »

Hi Scottayy!

Yes, I realize I can upload the table structure dump but there is no data in it. The table exists as the dump demonstrates. What I need to do is to populate its fields from the plain text file that contains my list of ~500 names. I don't know how to upload a *.txt file using PAM. Is that possible?
Post Reply