activate load data local on mySQL with ISP config

Discussion in 'Installation/Configuration' started by tgip, Sep 15, 2006.

  1. tgip

    tgip New Member

    I have a new ISP Config instalation on Suse 10

    I need to activate "load data local" on a client's site so that we can update a database from a text file.

    I get an error "The used command is not allowed with this MySQL version"

    Do I have to recomplile MySQL with the --enable-local-infile option, or is there a way to enable this option for just one client?

    if so ... would you be so kind as to give me a step-by-step so that I don't destroy my ISP Config instalation ?

    Thank you in advance.
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Please try to add add:

    local-infile = 1

    in your my.cnf file in the sections [mysql] and [mysqld] and restart mysql.
  3. tgip

    tgip New Member

    MySQL load data local infile ...

    I've tried that and it did't work, is there a "user" version of my.cnf that I'm missing?
    I've edited the file /etc/my.cnf which now looks like this ...

     # The MySQL server
    port        = 3306
    socket        = /var/lib/mysql/mysql.sock
    key_buffer = 16M
    max_allowed_packet = 1M
    table_cache = 64
    sort_buffer_size = 512K
    net_buffer_length = 8K
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    [B]local-infile = 1[/B]

     # The safe_mysqld script
    max_allowed_packet = 16M
    # Remove the next comment character if you are not familiar with SQL
    [B]local-infile = 1[/B]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    P.S. thanks for your swift reply
  4. falko

    falko Super Moderator ISPConfig Developer

    I found this comment on :

  5. tgip

    tgip New Member

    I've seen that comment too, but it won't help in my situation since I'm not using an external client to access the database ( like MS Access ). My situation is much simpler, I have a .php page that the client can use to "load data local", which means it's already being run on the server.

    this is the offending code...

    $result = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error()); 
    mysql_select_db($database_prod, $prod);
    // Load Products into temporary table
    echo "<br>Loading temporary table ...";
    $result = mysql_query("LOAD DATA LOCAL INFILE '/srv/www/htdocs/website/administrator/products.txt' INTO TABLE prodtemp FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n'") or die(mysql_error());
    P.S. I found this thread at PHP bug ... which looks interesting ...
    Last edited: Sep 28, 2006
  6. falko

    falko Super Moderator ISPConfig Developer

  7. tgip

    tgip New Member

    load data local with ISPconfig

    I've got 5.1.2-27 installed
  8. tgip

    tgip New Member

    Load data ...

    I've discovered a quick fix ...

    copy the file to the server ...

    this requires you to have the file accessable ( readable by everyone ) which is not exactly good, but hey ... at least my client is up and running ...:)

Share This Page