[NCLUG] Getting around the LOAD DATA LOCAL with MySQL 3.23.49 + using PHP?

M Butcher mbutcher at aleph-null.tv
Mon Feb 17 22:15:27 MST 2003


I've been thinking about this same thing b/c I think the  LOAD DATA
thing is kinda ugly and difficult to use in shell scripts. I don't know
if your case is similar to mine, but all of my files are formatted as
tab-delimited files. This makes it really easy to parse. 

To take data and format it into a file of INSERT statements, you could
do something like this (I'll call it data2insert.pl):

#!/usr/bin/perl
$usage = "usage: $0 tab-file.dat insert-file.sql \n";
if (! defined(@ARGV) || length(@ARGV) < 1) {
    print $ARGV[1];
    print $usage;
    exit(1);
}
$data = $ARGV[0];
$insert = $ARGV[1];

# Open a file for output:
open(OUT, ">$insert") or die "can't open output file";

# Open the tab delimited data file and loop through the contents:
open(FILE, $data) or die "failed file open";
while(<FILE>) {
    @insert_val = split("\t", $_); # split by tabs
    foreach (@insert_val) { chomp }; # trim tabs and newlines
    # Write out an INSERT statement to the OUT file.
    # You would have to customize this part:
    print OUT "INSERT INTO mytable (name) VALUES '$insert_val[0]';\n";
}
close(FILE);
close(OUT);
#end

And then run:
./data2insert.pl my_data.dat insert_file.sql 

That should write out a file called "insert_file.sql" with a bunch of
insert statements. Then you could just do a 
	mysql mydb < insert_file.sql
to load the data. Would that work?

Matt

On Mon, 2003-02-17 at 19:09, Peter Janett wrote:
> I upgraded my MySQL on my web server, and the upgrade broke some PHP code
> that loaded a file.  I need to get my old PHP code to work with the new
> MySQL server, and I hope someone here can help.
> 
> Here's the details on the fact that newer versions of MySQL disable the LOAD
> DATA LOCAL function:
> http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html
> 
> I've read that, as well as:
> http://www.mysql.com/doc/en/mysql_options.html
> 
> But I'm not sure how to get my code to work.
> 
> I tried adding  --local-infile=1 to my MySQL server startup script, but that
> didn't seem to do it:
> <Start snip of mysql.server in init.d>
> case "$mode" in
>   'start')
>     # Start daemon
> 
>     if test -x $bindir/safe_mysqld
>     then
>       # Give extra arguments to mysqld with the my.cnf file. This script may
>       # be overwritten at next upgrade.
> 
> 
> 
> 
> 
> 
> $bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file --local-infile=1
> &
>       # Make lock for RedHat / SuSE
>       if test -w /var/lock/subsys
>       then
>         touch /var/lock/subsys/mysql
>       fi
>     else
>       echo "Can't execute $bindir/safe_mysqld"
>     fi
>     ;;
> <End snip>
> 
> Here's my php code that is dieing (I'm sure email will add line breaks):
> $load_file_sql   = 'LOAD DATA LOCAL INFILE
> \'/usr/web/htdocs/uploaded_data.txt\' REPLACE INTO TABLE
> `staging_player_tracker` FIELDS TERMINATED BY \'\t\' OPTIONALLY ENCLOSED BY
> \'"\' LINES TERMINATED BY \'\n\' (`First_Name`, `Last_Name`,
> `Player_Tracker_Number`, `Last_Visit`, `Point_Balance`)';
> 
> $load_file_result = @mysql_query($load_file_sql, $connection) or
> die("$load_file_sql failed Error #". mysql_errno() . ": " . mysql_error());
> 
> And, the error returned by the php script (again, email client will word
> wrap I'm sure):
>  LOAD DATA LOCAL INFILE '/usr/web/htdocs/uploaded_data.txt' REPLACE INTO
> TABLE `staging_player_tracker` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED
> BY '"' LINES TERMINATED BY '\n' (`First_Name`, `Last_Name`,
> `Player_Tracker_Number`, `Last_Visit`, `Point_Balance`) failed Error #1148:
> The used command is not allowed with this MySQL version
> 
> I'm running Red Hat Linux, Apache (Stronghold 3.x), and MySQL 3.23.52
> 
> Any help or hints most appreciated.
> 
> Thanks,
> 
> Peter Janett
> 
> New Media One Web Services
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> New Upgrades Are Now Live!!!
> Windows 2000 accounts - Cold Fusion 5.0 and Imail 7.1
> Sun Solaris (UNIX) accounts - PHP 4.1.2, mod_perl/1.25,
> Stronghold/3.0 (Apache/1.3.22), MySQL 3.23.43
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> PostgreSQL coming soon!
> 
> http://www.newmediaone.net
> webmaster at newmediaone.net
> (303)828-9882
> 
> 
> _______________________________________________
> NCLUG mailing list       NCLUG at nclug.org
> 
> To unsubscribe, subscribe, or modify your settings, go to:
> http://www.nclug.org/mailman/listinfo/nclug





More information about the NCLUG mailing list