Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2012
    Posts
    5

    Unanswered: Problem of insert instead of dump

    Hey all,
    I was wondering maybe someone can help me solving this little(or maybe not) problem, I just recently bought a new comp and I had a large MySQL DB stored on the old one, now when I moved all I had little time so I did copy create like insert to all the table(instead of exporting the tables) now the table are not small, and I get memory exceptions when I try to run the queries, I tried to cut the queries but its a lot of tables.... also not so wisely I format the old computer before I checked it was alright.
    thank you all, in advance

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What exactly is the error message you get when running your queries and from where are you getting the memory exceptions i.e. from your application or directly from the MySQL prompt?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Dec 2012
    Posts
    5

    Error message

    Hey Ronan,
    I am using TOAD but I get a similar error at the workbench,
    the error message is:
    System.OutOfMemoryException
    Exception of type 'System.OutOfMemoryException' was thrown.
    Stack Trace:
    at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
    at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
    at System.Text.StringBuilder.Append(Char[] value, Int32 startIndex, Int32 charCount)
    at System.IO.StreamReader.ReadToEnd()
    at Quest.Toad.ImportExport.ExportWizard.GetSql(String SqlScript)
    at Quest.Toad.ImportExport.ImportWizard.simpleButtonO penQueryFile_Click(Object sender, EventArgs e)
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at DevExpress.XtraEditors.BaseButton.OnClick(EventArg s e)
    at DevExpress.XtraEditors.BaseButton.OnMouseUp(MouseE ventArgs e)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at DevExpress.Utils.Controls.ControlBase.WndProc(Mess age& m)
    at System.Windows.Forms.Control.ControlNativeWindow.O nMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message& m)
    at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

    That's it.... glad if you can help in any way

  4. #4
    Join Date
    Dec 2012
    Posts
    5

    command prompt

    By the way as for if I run the script through the command prompt I get a
    "ERROR 2006 (HY000): MySQL server has gone away"

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Looking online there are some other posts with similar type issue. One solution is to change in the my.cnf file on the server the following parameter:

    max_allowed_packet=64M

    Try that and see if that helps?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Dec 2012
    Posts
    5

    Max_allowed_packet

    Hey and thanks again,
    Well I did tried it and it didn't work, in the smaller files (until 15M), but lets for the 100+ it didnt(although I extended the max allowed higher than 100, like 150 for example)any idea why?

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I am not sure I follow what you are saying. Are you saying that it worked for tables < 15M but over 15M there are still issues? What I would do is try to do a dump of the database and get all the information out. Then I would cleanup the database and insert the data back in. I suspect there is a corruption of the InnoDB tablespace which is caused by you using different versions of MySQL.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Dec 2012
    Posts
    5

    worked it out

    Hey again, I worked it out, for anyone having trouble in the future, first i tried extending the size of the max_alowed_packet through the command prompt and it didnt worked from some reason(still not realizing why exactly), then i just ran the query "SHOW VARIABLES LIKE 'max_allowed_packet'" and and saw which number was there(it was set for around 1.6m I think), then I ran "SET GLOBAL max_allowed_packet=32000000;" ran the query that I wanted(the size of the query was smaller than the 32 mega I set it to) and it worked when I finished i set the globals back to their original values.... not the best and mostelegant but worked for me! also thanks again to Ronan

Posting Permissions

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