SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Uploading large .sql file

    Hi,

    I am having big problems trying to move my 560Mb vBulletin database to a new shared host. The hosting company does not support Shell access for security reasons so i tried to use bigdump to upload the .sql file to a new database on the new host. Bigdump gives me an error when i try to upload:

    At this place the current query includes more than 300 dump lines. That can happen if your dump file was created by some tool which doesn't place a semicolon followed by a linebreak at the end of each query, or if your dump contains extended inserts or very long procedure definitions. Please read the BigDump usage notes for more infos. Ask for our support services in order to handle dump files containing extended inserts.
    Does anyone know how to turn off extended inserts or deal with long procedure definitions in phpMyAdmin? Im hoping this will finally get it sorted but the whole process of uploaded the .sql file to a server takes about 4 hours so its a laborious task

    Thanks!

  2. #2
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    It probably would be best if you contacted your host about the issue.
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Banana Man View Post
    Hi,

    I am having big problems trying to move my 560Mb vBulletin database to a new shared host. The hosting company does not support Shell access for security reasons so i tried to use bigdump to upload the .sql file to a new database on the new host. Bigdump gives me an error when i try to upload:



    Does anyone know how to turn off extended inserts or deal with long procedure definitions in phpMyAdmin? Im hoping this will finally get it sorted but the whole process of uploaded the .sql file to a server takes about 4 hours so its a laborious task

    Thanks!
    Remote MySQL connections allowed? You should be able to turn those on in your control panel. Create a simple php script that uses fgetcsv to run individual inserts into your db after createing the tables that are within that sql file.

  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    I agree with the others. If you are going to host a database of this size then it's essential that you choose a hosting company that allows some form of remote db access, otherwise you are limited to db administration through unreliable PHP applications. As a last resort you might try SQLyog - it allows remote connections through HTTP tunneling. I've never tried it so I don't know how well it works.

  5. #5
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I got in contact with my host and they can upload it for me. Now the problem is corrupted .sql files. They have tried it twice and it's failed twice with 2 .sql files i have uploaded to my FTP. When i looked at the first one it was very short and clearly was missing most of the file. The second one i tried to look at in my browser and it seemed to be much much longer but eventually i think my browser had a problem with it because the file was too big. Next i'll attempt to do it with a .sql.gz. The first 2 attempts had no compression.

  6. #6
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Well, so your primary problem is to repair your sql dump first! I would suggest to first test it locally - install mysql and try loading the dump to your server on localhost. It can be tough to fix such a large dump but if you are lucky to find a spot with the error you can use a hex editor like Hex Editor Neo to edit the file (an ordirary notepad can corrupt data if the dump contains binary segments).

    There is also one small hope - it is possible that you dump file contains queries too long for the server to accept. If that is the case you can try increasing max_allowed_packet.

  7. #7
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    Well, so your primary problem is to repair your sql dump first! I would suggest to first test it locally - install mysql and try loading the dump to your server on localhost. It can be tough to fix such a large dump but if you are lucky to find a spot with the error you can use a hex editor like Hex Editor Neo to edit the file (an ordirary notepad can corrupt data if the dump contains binary segments).

    There is also one small hope - it is possible that you dump file contains queries too long for the server to accept. If that is the case you can try increasing max_allowed_packet.
    This does not sound good. I am a MySQL novice so by the looks of things i could be in trouble here? Im too busy with other projects to spend a week trying to fix this. When you say it can be tough to fix a large dump is it likely that i will just not be able to transfer the file at all?

    It's a vBulletin forum that's been running for about 7 years. I dont like the idea of starting from scratch and losing just over half a million posts

  8. #8
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    So i finally got a working .sql copy uploaded to the new database. I got the old host company to do a download with shell access which has worked. Looks like phpMyAdmin export doesn't work so well on large files.

  9. #9
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Glad you managed to do it. A few years ago I also got burned with a corrupt phpmyadmin export and since then I know to avoid this tool.


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
  •