Uploading large .sql file

[Banana Man]

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!

[Force Flow]

It probably would be best if you contacted your host about the issue.

[K. Wolfe]

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.

[Lemon Juice]

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.

[Banana Man]

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.

[Lemon Juice]

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.

[Banana Man]

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

[Banana Man]

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.

[Lemon Juice]

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.