SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL error - systemOutOfMemoryException

    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.

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,142
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Can you split the file into smaller chunks? Then do each small file one at a time?

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,029
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    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.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!!

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    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

  10. #10
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all the replies folks

    Quote Originally Posted by r937 View Post
    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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mattastic View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  13. #13
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  15. #15
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •