I have a 26 + MB sql file that needs importing to an external database.

The problem is that I cannot:

Upload it via phpMyAdmin as it errors out (memory usage).
Type (copy paste) it into phpMyAdmin, same reason
Upload it to the server via FTP (filesize limits)

Is there any way in which I can do all of the queries (all IPB insert queries) successfully (apart from doing them one by one, as it is verrrry big.)

Thanks

    if you can't upload the file to your server, I think you are out of luck unless you know how to write an application that can
    1) parse the data file record by record
    2) connect to your database over the internet
    3) insert a file one at a time.

    You might try converting file from sql to CSV by opening it in MS Word and doing some searching and replacing. sql files have a lot of redundant info so a CSV file might be considerably smaller which would let you upload it. then you can to a insert data infile command which can read the data quite quickly.

    My thinking is that if you can't get it uploaded to your server, you don' t really have a chance.

      Hmmm...

      It may be possible too parse it line by line, but its going to be tedious...

      I have already tried to connect to it via the internet and it says that I do not have access from my net thingy (you know when it gives u the 256453.ntl.net or something)

      They have a very old version of phpMyAdmin, without any features, its just the basics (great..)

      Looks like I'm gonna have to let alot of people down that are counting on me.

        phpMyAdmin is limited to about 2 megabytes for data import.

        I never suggested doing it line by line. with a few deft search/replace moves in MS word such as:

        find "INSERT INTO blah_blah VALUES(
        replace with nothing
        find p);
        replace with p

        then you can change it from highly redundant SQL insert statements into a much more efficient Comma-Separated-Value file (CSV). Open it in excel to see if it worked. I leave the actual mechanics of doing this properly as an exercise for you.

        that might reduce the file a bit. possibly quite a bit.

        and what do you mean 'connect to it via the internet'??? what 'net thingy?'??

        i'll say it again, if you can't upload it to where your server, you will probably not have much luck getting it imported....UNLESS you break the whole thing down into manageable sized chunks. try finding out just how many lines your phpMyAdmin can handle via web browser. IT could very well handle a thousand (or even a few thousand) lines at a time. 26 MB is large though. that would be a pain.

        ultimately, you SHOULD be able to upload a paltry 26MB file. If you are working on dialup, you should invest in DSL or cable modem. It would make your job a lot easier and would easily pay for itself in a week in terms of lost time.

          Was the sql dump done with extended inserts on, and is that 26meg as is, or gz / zipped.

          I've just downloaded the latest db backup and a 14 meg gz dump pumps up to a 54 meg sql file with extended inserts. A ratio of about 3.5, so you could possibly get your file under the 8 meg limit, and upload that.

          But try what the sneakster suggests, and try and set up and external host, % if need be, then use something like MySQL Administrator to log in and then perform a restore. Muck about with it locally first just to get the hang.

            Here's a cheating way to do it. Assuming you can put 26 megs on the destination, just not all at once, use the following linux commands:

            split -b 1M mybackupfile

            this will produce a series of files name aaa, aab, aac and so on (or something similar.)

            Now upload the pieces to the server. On the server, either use ssh if you can ssh / telnet in, or write this in a php script wrapped around with ` marks:

            cat aaa aab aac aad aae (and so on) >mybackup

            And then run the mysql import the same way.

              Dont have linux... lol

              Anyhoo, I had to do it basic SQL (not gzipped etc) because every time I tried to do it I got a server error

              I have managed to do it on my own PC from the file (having written a script to take the file and do the inserts one by one)

              This thread isnt resolved as such, but I have been assured that as this is a temporary host, that it is unneccesary to import the whole DB until we find a more permanent host.

              If only they didnt insist on hosting porn 😛

                whatever host you are using, if you can't upload a paltry 26MB file to the server, then you should get someone else. If they're doing porn, they will NEED that kind of bandwidth.

                Once you get it on the server, you should have some options -- linux or not.

                  madwormer2 wrote:

                  Dont have linux... lol

                  There are ways to do what I listed in windows, with the copy command and a /b switch.

                    5 years later

                    I have the same problem importing large files through phpmyadmin. I do no know if it was written with a lot of errors or what.

                    So what file on the server do you ftp the .sql to?? I am willing to try anything.

                    Thank you,
                    Gino

                      One thing that I learned in dealing with big DBs in phpMyAdmin was that if you are exporting, you need to set the "Maximal length of created query" to < 15000 lines for an import to run without problems.

                      It is usually due to memory buffer problems.

                      Now, if they file was created by another tool, then forget what I just said. ;-)

                        Write a Reply...