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

    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 ...

    [...]
    Code:
     # The MySQL server
    [mysqld]
    port        = 3306
    socket        = /var/lib/mysql/mysql.sock
    skip-locking
    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]
    [...]

    Code:
     # The safe_mysqld script
    [safe_mysqld]
    err-log=/var/lib/mysql/mysqld.log
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    [B]local-infile = 1[/B]
    
    [isamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    P.S. thanks for your swift reply
     
  4. falko

    falko Super Moderator

    I found this comment on http://dev.mysql.com/doc/refman/5.0/en/load-data.html :

     
  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...

    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 ... http://bugs.php.net/bug.php?id=21356 which looks interesting ...
     
    Last edited: Sep 28, 2006
  6. falko

    falko Super Moderator

  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 ...
    replace the LOAD DATA LOCAL INFILE with LOAD DATA INFILE ...

    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