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

[M Butcher]

Oh, and if you wanted to do it from PHP, you could probably skip the
writing to file and do something like this (assuming the db connection
is open, and the var $line contains the current line from the data
file.):

$insert_val = preg_split("/\t/", $line);
$query = "INSERT INTO table (name) VALUES ${insert_val[0]}";
mysql_query($query);
// etc. etc.

On Mon, 2003-02-17 at 22:15, M Butcher wrote:
> 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
> 
> 
> _______________________________________________
> NCLUG mailing list       NCLUG at nclug.org
> 
> To unsubscribe, subscribe, or modify your settings, go to:
> http://www.nclug.org/mailman/listinfo/nclug