LOAD DATA (LOCAL?) INFILE question 1

[VBAjedi]

THE QUESTION:
I'm trying to figure out the LOAD DATA INFILE command. Do you need the LOCAL keyword if the mySQL server and the mySQL client are on the same machine, but the text data file is not?

THE REASON:
I want to be able to load textfiles into my dbase from a remote location via a web-based MySQL admin tool (phpMyAdmin). I have installed phpMyAdmin (the mySQL client) on my webserver, but have been unable to get the setup configured to allow LOAD DATA LOCAL INFILE (I *think* this has to do with the fact that I can't get the mysqli extension set up for PHP in Windows).

THE SETUP:
MySQL 4.0.17, PHP 4.3.4, Apache 2.0.47, phpMyAdmin 2.6.0 Alpha1, all running on a Windows Server.

Thanks for any help you can offer!

VBAjedi

 

[sleipnir214]

The "LOCAL" keyword does not have to do with the relationship between the locations of the client and server.

The "LOCAL" keyword has to do with the relationship between the locations of the server and the file to be imported. The "LOCAL" keyword allows the datafile to be transmitted through the client connection to the server. Both the server and the client must be configured to allow this.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[VBAjedi]

That's how I interpreted the brief comments in the MySQL manual as well. So here's why I asked:

After spending a day and a half unsuccessfully attempting to configure everything to allow LOAD DATA LOCAL INFILE (again, apparently because I am using the latest versions of everything on a Windows server), on a whim I tried uploading the file without the LOCAL keyword, I.e. LOAD DATA INFILE. I connected from my desktop PC through the internet (via phpMyAdmin) to my webserver, browsed to the upload section for the desired data table, and specified the location (on my desktop PC's hard drive) of the text datafile. I was shocked - it WORKED. The file is definitely on a different computer than the mySQL server, and I definitely did NOT specify the LOCAL keyword.

I don't understand why it worked. Maybe I should leave well enough alone, but I don't feel like I can trust/rely on behavior that seems counter to what the manual describes.

VBAjedi

 

[sleipnir214]

upload section"?

It sounds to me like you're transmitting the file to the server via HTTP, then reading the file from the server's local filesystem.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[VBAjedi]

Have you ever used phpMyAdmin? I'm just learning it now, but I can't find anything in its documentation that indicates it facilitates that approach. It does create some kind of temporary file on the local (my desktop) drive, as you can see from the MySQL query it ran:

LOAD DATA INFILE 'C:\\WINDOWS\\php5B.tmp' INTO TABLE `vehkey` FIELDS TERMINATED BY '|' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'(
`V_VehKey` , `V_TransDist` , `V_VehID` , `V_Garage` , `D_GarArrival` , `V_YearMade` , `V_VehType` 
)

My source file was in a different folder with a different name. Again, this query successfully loaded 100 records (from a text file on my desktop PC) into my webservers MySQL dbase.

I just want to make sure I understand what's going on before I get to an office 500 miles from my webserver and find out I can't upload data after all.

VBAjedi

 

[VBAjedi]

Also, I've searched the drives of both my desktop PC and of the server for that tmp file, but it doesn't exist in either location. Obviously phpMyAdmin destroys the file when it is done with it.

VBAjedi

 

[sleipnir214]

I've never used PHPMyAdmin. I'm just assuming from your description that it's uploading the file.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[VBAjedi]

Nuts. That's where I was at too, but I HATE assuming. Think I'll submit a help request to the makers of phpMyAdmin for clarification.

Anyway, have a star for the info on the LOCAL keyword (even though I had already read one of your prior posts in which you said about the same thing). I appreciate your time!

VBAjedi

 

[sleipnir214]

I've installed PHPMyAdmin on my system. Where did you go to get to the spot where you selected a text file to upload?



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[VBAjedi]

Select your dbase in the frame at the left of the screen, then select the desired table from the list that appears. Now select the "SQL" tab at right, then click the "Insert data from a textfile into table" link at the bottom.

The frame that loads allows you to set all the upload options.

VBAjedi

 

[sleipnir214]

It's definite. PHPMyAdmin is using HTTP to upload the file to the server and use the local copy of the datafile.


Examine the HTML. That page uses an input of type FILE and submits to a script named ldi_check.php. ldi_check.php constructs a "LOAD DATA INFILE" (no "LOCAL") query using the local, uploaded file.

The script also deletes the file when it's done with it.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[VBAjedi]

Thanks! That's DEFINITELY worth another star.

Dumb that the phpMyAdmin documentation doesn't make that clear (unless I just missed it).

Think I'm gonna like this tool, though.

VBAjedi

 

[VBAjedi]

Daft. Star apparently didn't take. I'll star the other thread where you explained the LOCAL keyword.

VBAjedi

 

[sleipnir214]

It depends on your needs.

If you need the admin tools to be available regardless of where you go, phpMyAdmin is pretty good. Just watch security on the scripts, though, if PHPMyAdmin can be reached from the open internet.

From fixed worksites, I use MySQL-Front.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[VBAjedi]

I'm in the process of bringing a new inventory management web app online for my company. Previously, each region has used their own methods to track inventory, so there are no company-wide standards. I've decided it will be easiest to just travel to each company offices and create the data onsite, then upload it to the server from there.

I plan to just activate the tool when I'm out, and I have .htaccess authentication on the folder phpMyAdmin resides in. From what I've read, that should be fairly low-risk.

Does that raise any red flags to you?

VBAjedi

 

[sleipnir214]

That should be okay.



Want the best answers? Ask the best questions!

TANSTAAFL!!