SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LOAD DATA LOCAL INFILE doesn't work in MySQL 5.0

    Hello, I am trying to use the LOAD DATA LOCAL INFILE command to efficiently load a large amount of data into my tables.

    I am getting the following error message:
    "The used command is not allowed with this MySQL version"

    Here is the command I am using:
    LOAD DATA LOCAL INFILE '.\site\populate2\test.sql' INTO TABLE table_name;

    I am using MySQL 5.0 and invoking this command from PHP Version 5.0.0RC1
    Is there a way of knowing what version of MySQL, the PHP is working with?
    what could cause this problem?

  2. #2
    SitePoint Member
    Join Date
    May 2004
    Location
    sf, ca, usa
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The LOAD DATA LOCAL INFILE problem

    Hi davidklonski. I've been using Server Logistics' "Complete MySQL". Apparently, it is compiled such that LOCAL does not work. I posted the following message to the Server Logistics community forum, maybe it can be helpful for you, or maybe you can think of a solution:

    --------------------------------------
    Hi MySQL gurus,
    I count at least 3 (and maybe as many as 5) independently-initiated Topics where someone is stymied by the inability to do this traditional MySQL query:

    LOAD DATA LOCAL INFILE 'data.file' INTO TABLE name_of_table;

    And the answer seems to be that Server Logistics' Complete MySQL is compiled to disable LOCAL (--enable-local-infile=0), period.

    You can sudo data.txt into the database's data directory (inside /Library/MySQL/var/name_of_database/) and then do this:

    LOAD DATA INFILE 'data.txt' INTO TABLE name_of_table;

    but you need Administrator privileges to do the sudo.

    Alternatively, you can use INSERT statements instead of LOAD.

    Problem is that I have a tab-delimited table with > 14000 records to upload into my database, and I don't have Administrator privileges. So what's a poor MySQL peon to do?

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

    BTW, no one at Server Logistics forum has answered.

  3. #3
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Can't you download the source and compile it to your requirements or have I overlooked something simple?

  4. #4
    SitePoint Member
    Join Date
    May 2004
    Location
    sf, ca, usa
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DJ P@CkMaN
    Can't you download the source and compile it to your requirements or have I overlooked something simple?
    No, you haven't overlooked something simple. But I don't have administrative privileges.

    Also, one of the reasons to use Server Logistics' Complete MySQL is that it is pre-compiled with a number of MacOS X - specific features.

    So the ideal solution would be one that would not require pestering either Server Logistics or my admin.

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I first started workingn with MySQL - not too long ago - I immediately ran into a problem importing csv files. So I downloaded a trial version of a commercial product that did the job. I think it was called EMS, or something like that, and I used it together with phpMyAdmin.

    I just recently found time to start studying MySQL again. I thought I could fix the import problem this time around, but it's too complex for me to understand, and my free EMS trial ran out. (It seems like a pretty good program, but I'm really pinching pennies, and I wasn't sure if it would really be the best investment.)

    Anyway, I just downloaded another program called MySQL Front, which works beautifully. It's very intuitive. It takes me a long time to learn these things, and I was importing data with MySQL Front very quickly.

    So I'm now using phpMyAdmin to create my tables, MySQL Front to import the data, and phpMyAdmin to make modifications. Actually, I had a hard time figuring out how to move fields to different positions with phpMyAdmin, so I'm doing that with MySQL Front, too.

    I think this is a commercial product, too, with a 30-day free trial. I'll probably buy it, but it look like there are a lot of other programs out there.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i believe mysql-front is free

    i use it on my windows desktop computer to access my remote mysql database

    i've used phpmyadmin and mysql-front has it beat hands down
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i believe mysql-front is free

    i use it on my windows desktop computer to access my remote mysql database

    i've used phpmyadmin and mysql-front has it beat hands down
    So do you just use MySQL Front to access your remote database, or do you use it for almost everything related to MySQL? I'm still using phpMyAdmin for most tasks, but it might make sense to simply ditch phpMyAdmin - except that I have to use it when setting up MySQL accounts on my control panel.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1. i have only the one remote database, on my host, i do not have mysql here on my local machine (but if i did, i'm sure i'd use mysql-front on it)

    2. i set up my database through my host's control panel (not a mysql panel of any kind) and other than that i know nothing about setup
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •