MySQL error - systemOutOfMemoryException

[mattastic]

HI Folks,

I'm trying to import a large .sql file (270Mb) using SQL Workbench.

I'm exporting from phpmyadmin v3.4.10.1: structure and data, all statements, with syntax INSERT INTO tbl_name (col_A,col_B) VALUES (1,2,3), (4,5,6), (7,8,9)

Does anyone know how I can import it without getting the error?

Its for a wordpress install.

Thanks in advance.

[cpradio]

Can you split the file into smaller chunks? Then do each small file one at a time?

[rcashell]

I found this: http://bugs.mysql.com/bug.php?id=55312

"The MySQL Workbench SQL Editor is by design not able to load such big scripts. The upper limit is around 100MB, depending on available RAM. A new tool will soon be available to allow executing large scripts (without source
code support, like syntax highlighting, or edit abilities)."

So it looks like you will have to manually go in and break this into several files each less than 100Mb.

[mattastic]

Thanks for the replies folks.

How is it best to split up the file and sql syntax?

Using notepad?

Would anywhere after a ';' be ok to split?

Thanks again

[r937]

not sure if notepad can load such a big file

ultraedit (my text editor) can, and there's a 30-day free trial

yes, anywhere after a semi-colon is safe to split the file

[SpacePhoenix]

What language is being used server-side at the destination? One option provided you're carefully would be to have the server-side language, work its way through the file and insert the rows in say batches of 50.

[K. Wolfe]

The workbench is not capable, but what about the command line tool? I feel like I have imported csv's well over several hundred MB last year.

[rcashell]

You can try the command line utility to see if that works. According to the bug report this seems to be isolated to only MySQL Workbench. It is always worth a try - you never know it might work!!

[K. Wolfe]

I've confirmed that my csv's from my last years project were >=140mb. I ran a bash script daily to first run a sqlplus dump from oracle into a csv then then into my local mysql db, never saw an issue other than some isolated data type problems.

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

[mattastic]

Thanks for all the replies folks

not sure if notepad can load such a big file

ultraedit (my text editor) can, and there's a 30-day free trial

yes, anywhere after a semi-colon is safe to split the file

Great stuff, just installed it, but how do you select an area of code? The page scrolls for miles x and y

[r937]

Great stuff, just installed it, but how do you select an area of code?

highlight by click and drag with the mouse, or shift plus arrow keys

[mattastic]

Still having trouble with this file.

I can't highlight and scroll through code to copy it, the files just too big and unmanagable.

Using phpmyadmin, can I export one table at a time or split it up somehow that way?

Thanks again

[rcashell]

If you can connect to the MySQL with the command utility you should really look at using that. I think you will save more time using the command line utility than attempting to split the file up.

[mattastic]

Thanks very much for the reply.

I can login to the server and see mysql installed under programs.

How do I access in via the command prompt, and is it simple to do?

[mattastic]

Hi Folks,

I've managed to create the db, and use the following prompt to import the database:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u xxx -p xxx benpct
< d:\websites\benpct\nhsbenco_nhsben_matts.sql

All That happens if I get a load of myql information and version information, it doesn't say its imported successfully.

Can anyone please help?

Thanks again