Uploading big files into a MySQL database

[rogier.de.groot]

Ok, so there are plenty of examples on how to get MySQL to
accept files bigger then 1MB, but now I need to configure a MySQL
server on one machine & an Apache/PHP server on another to
accept files up to 64MB in size.
Up to about 15MB seems to work just fine, but above that I
get various error messages, mostly my PHP script running
out of virtual memory (I know how to fix that myself) and
the MySQL server "going away".
I've seen that problem before, and I have set
max_allowed_packet and thread_stack and friends but it still
won't work!
Anybody have any tips?

Machines:
Apache/PHP-server on Pentium 133, 32MB RAM, 256MB virtual
memory, Red Hat Linux 8.0
MySQL-server on K6 350, 512MB RAM, 1GB virtual memory,
Red Hat Linux 8.0

Apache, PHP and MySQL are all the latest stable versions, not
those that ship with Red Hat Linux 8.0

If anyone has any pointers I'd be happy to hear them,
TIA rogier.

[yelvington]

Pumping 64MB files through the Web server is probably not the most efficient way of loading data into the database. You might also consider FTP'ing the data and then processing it with a script (or LOAD DATA INFILE if it's in a sane format). Or configure the database server so that it can be accessed remotely, and use a remote mysql client connection.

The database "going away" problem is probably a natural result of the data volume ... it's going to take time to process! Especially if you're doing a ton of inserts or updates of indexed data.

You might need to drop the indexes, load the data, then re-create the indexes.

Deletion also has the same issues, by the way.

[rogier.de.groot]

The problem is that the system is designed for users who don't have a clue what FTP is and the clients (WinXP) don't have MySQL client programs on them either.
I really don't think the users will accept anything more complex than the current "select a file & click next" setup.

Oh well, I guess tomorrow (today is Sunday) I can start experimenting with all those settings again. I get paid by the hour anyway.

Thanx all the same, rogier.

[yelvington]

You might fix the Web side of the problem by poking around on the resource limits in php.ini, especially upload_max_filesize and max_execution_time.

On the mysql loading issue, you might decouple it from the Web process. Drop the uploaded files into a directory after they're complete. Write a script to check that directory, write a lockfile, load the data, and delete the lockfile. In other words, don't let two copies of that script run at the same time! Then put the script (which could be written in PHP) into a crontab.

There are a couple of mySQL clients for Windows, but they're probably too complicated for end users.

Or you could write your own client. :-)

[stolzyboy]

Originally posted by rogier.de.groot
The problem is that the system is designed for users who don't have a clue what FTP is and the clients (WinXP) don't have MySQL client programs on them either.
I really don't think the users will accept anything more complex than the current "select a file & click next" setup.

Oh well, I guess tomorrow (today is Sunday) I can start experimenting with all those settings again. I get paid by the hour anyway.

Thanx all the same, rogier.

that isn't a problem, visit www.php.net/ftp, and there you can use php with it's ftp functions to do what you want

[rogier.de.groot]

Silly me.
In a moment of insight I rewrite the PHP code to write the SQL query it's trying to do to a file named query.txt
When the PHP code fails again, I try running query.txt using the
mysql commandline client. It works. I realise I accidentally
compiled PHP with support for MySQL 3.x rather then MySQL 4.x.
I recompile, getting it right this time, everything works fine.
Slow, but fine.

Problem solved :)