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

[Peter Janett]

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