More mysqldump errors.

When trying to read back a large dumped file as below, I get fatal error 1064 at line 516383 : Maximum execution time of 300 seconds exceeded.
This is a large file; actually, my boss broke it up into two files because he got a similar error when creating the .sql file.

Looks like you'll need to break it down some more or go into your mysql config and (temporarily) push the execution threshold out to a larger number.

Ok, where would I find that? It doesn't appear to be in the my.ini file.
Even with the split I'm not sure it'll work. When I tried importing the second file, I got an error that the table already existed.
Thanks.

dhouston and salvelinus...

I have been learning MySQL 4 for 15 days. I'm going to write a little project using Java and MySQL. But the problem I'm currently encountering is "Database Backup". I want to back up the whole database but I'm missing some clues.

mysqldump usage is not very familiar with me. I have tested mysqldump before but I got some errors.
Please let me know how to back up the entire database easily.

Salvelinus, if the table already exists and you've got create table statements in your dump, that's why you're getting the error. Drop your table before trying to slurp in the files, and make sure each section of the file doesn't contain redundant create table statements.

Summer_Breeze, try:



If you're running as root, you can probably omit everything in brackets. The "-A" flag means "dump all databases." And the "> file.txt" redirects the results to the file specified.

I don't have the option of making the files myself, that's done by the boss from the main server. I don't know if he did it correctly. I think the only reason he broke the file is because of the same timeout error when creating it.
How do I go into mysql config to change the execution time?

I imagine you can change the execution time in the mysql config file (my.ini on Windows, I think). Check out the handy reference at mysql.com for details. Also, you should be able to modify the files your boss output, right? There is syntax you can add to a create table statement that checks first to see if it's already been created (something like "CREATE TABLE blah IF NOT EXISTS" that might help you out. Keep in mind, though, that you'll probably need to dump the table, or at least the data, before running this again. If you don't, you'll probably wind up with duplicate key errors because the values in the first file have already been inserted.

Thanks, I'll look into that. I didn't see any execution time in the my.ini file (copy below), but I'll check out mysql.com.
Maybe there's a flag to set to check the table creation. The first file alone is over 10K pages in Word, although most of that's data. Still, there are probably 100+ tables, it would be a pain to code for them all.
Maybe I can just import the structure, w/o the data, then import that separately. Hmmm...

Nothing's working. I don't see any setting for maximum execution time, or anything set to 300 seconds. Googling the problem indicates it's a php issue, although why php would be involved at the command line I don't know. No fixes there work either, either in php.ini, or in phpmyadmin (where I did set set_time_limit(0) in read_dump.php).
I've checked for this setting in Windows, in IIS, in PWM as well.

Ah, shoot, that execution time error's a PHP config option, not a mysql option. Check your php.ini file for the "max_execution_time" line and boost that a bit. Make sure you restart apache after changing the value so it'll reload the config.

Thanks, but I've done that, increased it to 900 seconds, it still times out at 300 seconds. Using IIS, not Apache. Can I use Apache as my local server if everyone else uses IIS?
I've increased memory_limit, upload_file_size, etc. The only place I've found a limit of 300 seconds is in read_dump.php in phpmyadmin, which I increased to 0 (unlimited), but that didn't work either. I've tried command line and phpmyadmin.
The file is 175 mb, maybe that's just too big. What do big outfits do, though? Some large companies must have gig size dbs.

Resolved, sort of.
Never did get rid of the timeout error, but used the data that had imported, then ran the second sql file, and it seems to be ok. Had to drop two tables in between the first and second imports because they were recreated in the second.
Thanks for all your help.

I don't remember whether or not you were able to try executing the dump at the command line. As PHP's giving you problems, that might be your only option.

You could probably use apache internally, but you'd have to get it to listen on a different port, and I have no idea how you'd even begin to open up another port on Windows.

A 175 mb file would be pretty big to generate in memory from a Web script. To give you a reference point, it took about 20 seconds for me to generate a 20 mb file using the command line tool and redirecting to a file. This is a much easier solution than wrangling with Web timeouts, etc. Your boss needs either to dump the database for you or to give you access to do so. You might also try getting something like mysqlfront or mysqlcc and seeing if they've got utilities that'll do a dump for you.

Oops, that's what I get for multi-tasking and waiting too long to finish my post! Glad you found a solution.

Thanks. I did try from the command line, many times.
Command line imported tables till it erred out, so there was some stuff in the db. phpmyadmin didn't import anything when it timed out (assuming it timed out, same time but "page not found" error.