Large SQL script problem in Mysql

bazcaz

I have a large sql script containing enron employee emails that i want to execute in mysql,its 1gb in size when its uncompressed and when i attempt to run it it says out of memory.

I would have no idea how to break it up,it wont even let me look at the script in word or notepad because its so big so I have no idea how i would split it anyway.

Any ideas??

OMcGovern

Executing SQL statements from a script shouldn't take up memory.
The file should be read sequentially, ie. not loaded into memory. So the script execution program only has to have a few lines in memory at any one time.

What I suspect is happening, is that the 1Gb file contains lots of SQL insert statements. The script considers the 1Gb script to be a single transaction, and it doesn't "commit" the inserts until it finishes the script.

I'd investigate whether you can have an autocommit setting, that can be set to true, or even better for performance reasons, autocommit after N records.
So you may set it to commit after every 50 records.

Failing that..... I'd probably try writing a little program to parse the 1Gb file, and
after every semi colon it finds ( the SQL statement delimiter ), maybe
add a "commit;" after every 50 statements. Might be messy if there's semi colons within your data fields.

regards,
Owen

rsynnott

Are you using transactional tables in MySQL? Do you need to be? If not, then don't. If you do need to, then as said above, write a script (should be a couple of lines) to insert a COMMIT once every x lines.