SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Query too long

  1. #1
    SitePoint Evangelist artcoder's Avatar
    Join Date
    Aug 2005
    Location
    Planet Earth
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query too long

    I'm importing an SQL file using phpMyAdmin and it breaks in the middle of import giving me the error "#2006 - MySQL server has gone away".

    I believe it breaks while importing into the customer table and the SQL insert statement in the SQL file is very very long because it inserts many records in one statement. I understand that the person who exported the file should export it with separate inserts for each record.

    But now that all I have is the exported SQL file, how do it get it into the database?

  2. #2
    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)
    when you have a long series of values...
    Code:
    INSERT INTO sometable (column, column, column, ... ) 
    VALUES
      ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    , ...
    you can easily break it up just by adding a new INSERT line as often as you wish -- say, every couple of hundred rows ...
    Code:
    INSERT INTO sometable (column, column, column, ... ) 
    VALUES
      ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    ;
    INSERT INTO sometable (column, column, column, ... ) 
    VALUES
      ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    , ( value, value, value, ... )
    , ...
    don't forget the semi-colon to terminate the preceding INSERT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist artcoder's Avatar
    Join Date
    Aug 2005
    Location
    Planet Earth
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I splitted the SQL statement and then got "max execution time exceeded" error. So I then splitted in into multiple SQL files. And I also had to use TypePad in order to be able to handle editing the large text file.

    But it finally worked. Thanks.

  4. #4
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's more effective to use mysql command line for import which can handle GB of data with ease.
    In case of phpMyAdmin you should increase the max post upload size & maximum execution time for handling large data.


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
  •