Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > ISPConfig 2 > Installation/Configuration

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 15th September 2006, 14:28
tgip tgip is offline
Junior Member
 
Join Date: Sep 2006
Location: Portugal
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Post activate load data local on mySQL with ISP config

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.
Reply With Quote
Sponsored Links
  #2  
Old 16th September 2006, 11:48
till till is online now
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 34,647
Thanks: 794
Thanked 5,002 Times in 3,911 Posts
Default

Please try to add add:

local-infile = 1

in your my.cnf file in the sections [mysql] and [mysqld] and restart mysql.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #3  
Old 18th September 2006, 11:18
tgip tgip is offline
Junior Member
 
Join Date: Sep 2006
Location: Portugal
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
local-infile = 1
[...]

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
local-infile = 1

[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
Reply With Quote
  #4  
Old 19th September 2006, 14:57
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,711
Thanks: 1,900
Thanked 2,702 Times in 2,545 Posts
Default

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

Quote:
MySQL server 4.0.15 supports the "LOAD DATA LOCAL INFILE" feature, as does the MySQL ODBC Driver 3.15. However, in order for this to work, one must:

1 - Have a c:/my.cnf file on the server host containing a section
[mysqld]
...
local-infile=1

2 - Have a c:/my.cnf file on the client host containing a section
[odbc] (or [client])
local-infile=1

3 - Use the 65536 ODBC option ("Read options from C:\my.cnf") on the client side. Hence if you use Access you would configure your DSN with OPTION = 1 + 2 + 65536 = 65539.

Thanks to E. Warren for indicating this to me through the MyODBC mailing list.

C. Roche
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #5  
Old 20th September 2006, 10:48
tgip tgip is offline
Junior Member
 
Join Date: Sep 2006
Location: Portugal
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 by tgip; 28th September 2006 at 16:05.
Reply With Quote
  #6  
Old 21st September 2006, 14:17
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,711
Thanks: 1,900
Thanked 2,702 Times in 2,545 Posts
Default

Quote:
Originally Posted by tgip
P.S. I found this thread at PHP bug ... http://bugs.php.net/bug.php?id=21356 which looks interesting ...
Which PHP version do you use?
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #7  
Old 28th September 2006, 10:58
tgip tgip is offline
Junior Member
 
Join Date: Sep 2006
Location: Portugal
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default load data local with ISPconfig

I've got 5.1.2-27 installed
Reply With Quote
  #8  
Old 28th September 2006, 16:04
tgip tgip is offline
Junior Member
 
Join Date: Sep 2006
Location: Portugal
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
 
Default 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 ...
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
import mysql data - specifiy charset adam Installation/Configuration 1 16th September 2006 23:30
Howto suggestion suse PhP ver 4 + Ver 5 wwparrish Suggest HOWTO 11 7th August 2006 13:29
Problem with the apache (I can't start ist) M.Behrens Installation/Configuration 11 31st March 2006 10:48
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04
Config PHP with MySQL ProTrooper Installation/Configuration 4 6th October 2005 10:33


All times are GMT +2. The time now is 22:33.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.