#1
  1. No Profile Picture
    ThomasM
    Guest
    Devshed Newbie (0 - 499 posts)
    Greetings,

    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.


    ------------------
    Tom
    thomas@3dvr.net
  2. #2
  3. No Profile Picture
    rod k
    Guest
    Devshed Newbie (0 - 499 posts)
    Thomas,

    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.

    Rod
  4. #3
  5. No Profile Picture
    ThomasM
    Guest
    Devshed Newbie (0 - 499 posts)
    Thanks Rod

    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 fill blanks before I import
    into NULL fields?

    Thanks again..Tom
  6. #4
  7. No Profile Picture
    rod k
    Guest
    Devshed Newbie (0 - 499 posts)
    Thomas,

    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

    PS: I'm flattered you were hoping that I would see your question (if in fact you weren't trying to butter me up), however, I think your trust is misplaced. Many people out there know much more than I.
  8. #5
  9. No Profile Picture
    ThomasM
    Guest
    Devshed Newbie (0 - 499 posts)
    Ok, I broke down and asked tech support
    (at VARIO for reference) what the deal is.
    They told me they don't support File Upload
    for "security reasons" and that I should
    load the data line by line with inserts.
    This is clearly obfuscation since they do
    allow multipart forms and fileloads, which
    is the component of the process that is risky.

    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


    ------------------
    Tom
    thomas@3dvr.net

Similar Threads

  1. load data infile
    By deterset in forum MS SQL Development
    Replies: 1
    Last Post: October 9th, 2003, 02:34 PM
  2. load data infile
    By jcvega247 in forum MySQL Help
    Replies: 4
    Last Post: July 16th, 2003, 12:31 PM
  3. LOAD DATA INFILE - does not ignore header row
    By aliciawee in forum MySQL Help
    Replies: 0
    Last Post: March 20th, 2003, 10:11 PM
  4. LOAD DATA INFILE question
    By susquie in forum MySQL Help
    Replies: 0
    Last Post: October 7th, 2002, 03:25 PM
  5. Extra Variables or fields for load data infile...
    By emelsaya in forum PHP Development
    Replies: 11
    Last Post: September 2nd, 2002, 11:14 PM

IMN logo majestic logo threadwatch logo seochat tools logo