phpMyAdmin will only "Import" a SMALL sql file

[bdunning]

I have 1.7 million records I need to load into a MySQL table. I've been creating local SQL files with a single INSERT adding multiple rows. So far the biggest file I've been able to get to work is only 1,000 records. No way am I doing this manually 1,700 times to get all the data up!

When it fails with any file larger than that, MSIE/Safari spin their wheels for about 5 minutes, then the main frame in phpMyAdmin goes to a 404 page. A check of the db reveals that no records were added.

Any suggestions????

[agunther]

Upload the file via ftp:
/www/x/username/file.sql
Then execute this php script:

<?php
system( "/usr/local/bin/mysql --user=username --password=user_password --host=mysqlxx.powweb.com database_name < /www/x/username/file.sql");
?>


Taken from here:
http://forums.powweb.com/showthread.php?t=60555

Hope this works for you. (You need to modify the script with the right path, filename, username, database, database server, password).

Andre
http://www.aguntherphotography.com

[mixerson]

I had a similar problem with a 20,000 record database. MySQL was choking when I used a single INSERT for all records. Once I changed the SQL file to use a new INSERT for each record, it worked the first time.

If individual INSERT's don't work for 1.7M records, you may have to create multiple SQL files, then use Andre's code above in a loop. If you name the files file_1.sql, file_2.sql, etc., you could do something like:
foreach (glob("file_*.sql") as $sql_file)
{
system( "/usr/local/bin/mysql --user=username --password=user_password --host=mysqlxx.powweb.com database_name < /www/x/username/$sql_file");
}