Bug #525 Enhance LOAD_DATA page with hints relative paths and path portability
Submitted: 28 May 2003 8:26 Modified: 29 May 2003 3:17
Reporter: Ralf Hauser Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:r 4.0.12-max-nt for NT on i32 OS:Microsoft Windows (win2k)
Assigned to: CPU Architecture:Any
Triage: D4 (Minor)

[28 May 2003 8:26] Ralf Hauser
Description:
Suggestion to enhance http://www.mysql.com/doc/en/LOAD_DATA.html 

In a distributed development effort, multiple participants have both their database and clients installed in different locations.

Typically, one would load the database with a set of 
LOAD DATA INFILE 'path/filename.txt'  
instructions in a script.sql that would be in the version control, e.g. CVS

How to repeat:
How do you recommend to do this in view of the following two issues:
1) some team members might be on a windows platform, some (and typically the production servers) on a linux platform
2) even if all were on the same platform, their clients and mysql servers my still be installed into different directories.

Suggested fix:
What do you recommend?
e.g.: - always copy all the text files to a standard location first (e.g. the DB server's mysql/data directory?) and only then load them?
[29 May 2003 3:17] Alexander Keremidarski
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

This is not MySQL related issue. You refer to a problem from completrly different realm. Version control, Protablility issues, Development style and guidelines are something we can't adise our users on. Best practices or Conventional Filenames or locations are so out of scope of MySQL Manual.

There is no such thng as "Typically ..." We in MySQL AB for example use BitKeeper as version contol system not CVS. Some Linux developers will use convention "put all files under /tmp", but many SysAdmins will forbid you to use /tmp for this purpose and so on. 

LOAD DATA INFILE Syntax is flexible enough meaning that it accepts Fully Qualified File name as parameter. File can reside everywhere on filesystems. The only restrtion it applies is that file must be readable by mysqld (or by client in case LOCAL keyword is used). 
If relative filename is specified mysqld looks in curent database directory. 

Wheter this is good idea or not to put non-table files in Database directory is quite arguable - you can hear lot of opposing oppinions on it.
[29 May 2003 3:44] Ralf Hauser
Sure, the ultimate conlusions to my questions are beyond the realm of mysql.

But I guess I have to expand a little bit further.
My question still applies irrespective whether you use BitKeeper, CVS or any other. 
Is there a clear definition of "Fully Qualified File name" of INFILE Syntax - I didn't find a link for it on the page.

In short what kind of a path needs to be put into a script.sql containing INFILE statements for it to be usable on any platform (in particular by Windows and *NIX and both by "mysql" or "mysqld"):
1) forward slashes or double backward slashes?
2) are spaces and other special characters permitted in Windows Paths o.k. for a *nix mysqld or mysql command-line (e.g. colon's of drive letters)

Therefore, my suggestions:
In the LOAD_DATA page, section on INFILE 
i) add a link to a description of your "Fully Qualified File name" Syntax
ii) include the answer to 2) on the page
iii) include the answer to 3) on the page

Sorry, if I am a little bit persistent - I hope this will help many others to easily use the INFILE syntax and save them a lot of time and frustration.
[28 Jun 2004 15:45] Debbie Peckenpaugh
It would be nice to see this explanation of relative pathnames for the load local infile in the tutorial documentation.  I am very new to relational databases, but I have found your documentation to be very clear and helpful until I got to this point.  The tutorial doc simply states 'save file "pet.txt" [as an example], then run "load local infile pet.txt..." etc.' without stating where the file should be saved or how to enter the proper pathname if the file is saved outside of the mysql/bin directory.  It only took me about 5 minutes of moving files around to find the proper directory, but it might save some confustion and hassle for other new users if the documentation were more specific at that point.