posted October 13, 1999 01:25 PM            

I've been ghosting this board for awhile and
have decided there are a few bulbs here
brighter than my own, so I submit the
following quandry in hopes of finding some
illumination.

I'm developing some on-line mySQL managment tools. Most everything seems to work ok, except for the the "file data [local] infile" function.

Here's the input $sql and the output from $DBH->errmesg: (path names changed to protect the innocent. Lines are indented, non-indented lines are virtual wraps. Line
temination char is '\n')

#...input

$sql = "LOAD DATA LOCAL INFILE '$in_file' $slam_type INTO TABLE $tabl FIELDS TERMINATED BY '$split_type' LINES TERMINATED BY '$end_type'";

$sth = $dbh->query($sql) | | $error_message = $dbh->errmsg;
if ($error_message !eq "")(
print "Unable to add data to $tbl<br>";
print "Load Error Message: <br>$error_message<br>\n";
}
#...end input

#...output

Unable to add data to PswdHash.
Load Error Message:
parse error near 'LOCAL INFILE '/root/web/client/local_dir/data_file.txt'
IGNORE INTO TABLE PswdHash FIELDS TERMINATED BY '|' LINES TERMINATED BY ' '' at line 1

#...end output

The data file exists and is readable by all. I tried running $sql through $DBH->quote() first, but no difference. I've tried it with
varying tables and data files, and with every quote/escape sequence I can think of. The tables import the data without a problem when
I load the data line by line using INSERT.

Any and all suggestions welcome...Thanks.

posted October 13, 1999 02:11 PM            

Check what version of MySQL you have. LOCAL is available on 3.22.6 or later.

It's likely you have an earlier version since the parse error starts at the LOCAL keyword.

posted October 13, 1999 02:33 PM            

I was hoping you would see my q.

Ok, will check, but where does that leave
me if the host isn't using 3.22.6? I
already tried it without LOCAL and get
Access Refused. Do I just have to resort to
reading the in_file line by line and using
an insert? (Not an enticing prospect with
a 40K row DB.)

Oh, and while you are (hopefully) handy,
do I need to \0 fill blanks before I import
into NULL fields?

posted October 14, 1999 08:11 AM            

You tried it without LOCAL? Where does the file reside? If it's on the host then you don't use LOCAL. There's some other things to note when it's on the host. The file has to reside in the database directory or be readable by all. Also, you must have the "file" privilege on the server host. (That's the biggest problem if you're using a shared hosting system, you probably don't.) This is exactly why the LOCAL keyword was added so they file could be loaded over the network without having to worry about these permissions on the host. First thing I'd do is make sure the permissions on the file are correct for read by all. CHMOD xxn where n is 4 or higher.

If the file is on your computer, try uploading and setting the permission as above and see if it works.

If you still get access refused, you probably don't have file privilege. (this is my problem on my shared system)

The only way I've found around this is to set up my own machine with mysql and use LOAD DATA INFILE on that machine. Then use mysqldump to dump out the table. This creates a text file with the SQL code to create the table and INSERT the data. If the table already exists on your host, just remove the CREATE line and leave the INSERTS.
Also, you'll need to add a USE dbname command at the top of the file.

Upload this file to your host. Then from the command line do this:

mysql -u username -ppassword < /path/to/file.txt

HTH

Rod

posted October 14, 1999 11:40 AM            

Your answer was most helpful. I get focus lock when I hit a problem and find it
difficult to take a step back and reappraise
the situation. Your suggestion of FDU at
a machine where I do have grant table file
permissions and then exporting them is a good example. I should have thought of that.
Unfortunately, it's not a workable long term
resolution because I am just a consultant
and have to leave them with a system that
allows them to update hundreds of huge tables
nearly in real time (they don't deal with much static data). It's the typical brick and mortar story. They know they need on-line
presence but they are clueless and easily led astray. For now, I will just load the tables with inserts (and watch their already pitfully overloaded servers gnash through
millions of unnecessary cgi loops. D'oh!) I have already started the process of convincing the client that it is cost effective to own and operate their own servers. Wait until they get the CPU time and BW bill from VARIO when they start using it.

Butter you up? Well, momma always said you catch more flies with honey than vinegar, but
my remark was completely subjective. I have read every post here for months. While I would have been just as happy to have heard from westb or our moderator or a number of other people, you are by far the most active poster and after two days of pulling my hair over this, I was hoping for a quick answer from a reliable source. You were my best bet.

I really appreciate your taking the time to
help with this. I am in your debt. Feel free to e-mail me if I can ever be of assistance.

T