Export and Import of database errors, please advise.

[Kraai]

I am helping a website owner to transfer a site to new servers. All site files have been uploaded onto the new server, only thing left is the database.

In phpmyadmin, when I try to export the DATABASE to my local PC, using zip or gzip to compress, as this is a large database, the file doesn't want to uncompress on my local pc, I get an error message that it may be corrupt?

Then I exported the database without compressing it, and it downloaded without any problems.

As I am realy very novice in this, and only have a basic knowledge how to import this database from my local PC to the new server, using phpmyadmin, the first problem was that phpmyadmin only allows 51 mb to import this way, and this database is about 90 mb. I am very unlitterate in doing command line queries to do this import, so I contacted the support of the new host to help out, and they said I can upload the database via ftp to the server, and they wil install it from there.

I then uploaded the uncompressed .sql text file to a folder, via ftp, using binary mode.

When the host tried to install it, they got an error message in the syntax at a certain line, and asked me to correct it. I have no idea how to, as this database is just what came from the old server, nothing changed to it.

Is there any help or hope for me on this one?

Edit:

The full error message the host got was:

[root@cayenne /home/kletsker/mysqldump]# mysql kletsker_kletskerk < localhost.sql ERROR 1064 (42000) at line 89499: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''[quote=&quot;Barend&quot;:3v' at line 1

[peter_budo]

If you post the error along side of critical part of sql somebody should be able to help you...

[Kraai]

Thanks peter_budo, I just edited my original post including the error message.

[peter_budo]

That nice, but you see what it says

at line 89499: You have an error in your SQL syntax

So can you kindly provide that part of SQL and few lines above and bellow will not hurt anyone as long you not have some sensitive data such as contacts, acoount details etc.

[Kraai]

Is there a shortcut to find line 89499? When I look at the file on my local pc, it is not displaying anything in "line order" or "line number"

I'm sorry for my total stupidity, thats why my avater looks like me.

[peter_budo]

Ehmm that is good question...
I dont think you will be able to do so in phpMyAdmin (I'm not aware of such option), but I was able to open sql file (that what you get it after dumping isn't it?) with Dreamweaver, Microsoft SQL Server Managment Studio Express (is free) and I believe you should be able to it with MySQL Query Browser (for free part of MySQL tool package). Do not forget change setitng of chosen application to show line numbers

[Kraai]

I now tried the MySQL Query browser, but it is not giving me the option to search on my local pc c drive for loading the dumpfile. It only allows to connect to a server to browse the file.

The database is sitting now on my pc, and I need to find this line number there, I am viewing the file in notepad. If I do a search document, it returns many markers with that words in it, so how wil I know which one is the queried line?

I will pay someone to export/import this database for me, as I feel my knowledge is far too short now.

[peter_budo]

Right click on the sql file Open With >> Choose Program. Navigate to the location of MySQL Query Browser ( in my case also default location is C:\Program Files\MySQL\MySQL Tools for 5.0\MySQLQueryBrowser.exe ) and the file is open...

[Kraai]

Geewizzzzzz, thanks for reminding me the basics here.

Line 89499 happens to be the very last line of the file.

The line just above it starts with:

And then this users forum post follows.

Then the troubled line only looks like this:

[quote=&quot;Barend&quot;:3v

Nothing else.

If I look at this, should the "[" at the start not also close "]" somewhere?

I am learning, thanks for your trouble.

[peter_budo]

I have distinct feeling that you main not pull all data from original database. What I mean is that you maybe just missing closing bracklet, but in worst scenario reminder of the post or other data entries. It is very difficult to advice on this as I do not know structure of database you trying to export/import. You need to closely examine previous data entries and work out appropriate structure. Also you should compare it with old database if still available what this data entry contained and if there are any data entries after it.
I will make exclusion in this case and if you wish you may send me personal message with some data examples (last 100 lines should do it). It is up to you to decide if you can handle also if you can pass such data on 3rd person

[Kraai]

I think you hit the nail dead center here. The data was downloaded via export function of phpmyadmin, and it seems now that the download stream may have been interupted or dipped or something, and there is some more data that is missing.

In the meantime, I re-exported the database, in gzip format, and this took only 2 seconds, which make me think that the zipfile may not even have something in it. On my pc, it does not want to open the gzip file, it tells me it may be corrupt.

However, I then tried to import the gzip to the server, and got the "not enough" memory error in phpmyadmin:

<b>Fatal error</b>: Allowed memory size of 16777216 bytes exhausted (tried to allocate 10201461 bytes) in <b>/usr/share/phpmyadmin/export.php</b> on line <b>81</b><br />

What does all this mean?

Thank you so much for your support and patience with me, and your proposal to view the data. If all else fails, I don't have a problem if you view the data.

[peter_budo]

Function has alocated only 10201461 bytes where as you can see you want 16777216 bytes. Try to create split version of whole DB. Like you have 10.000 records you put 5.000 in one file and other in next one. I can remember how I did it last time

[nav33n]

[root@cayenne /home/kletsker/mysqldump]# mysql kletsker_kletskerk < localhost.sql ERROR 1064 (42000) at line 89499: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''[ quote=&quot;Barend&quot;:3v' at line 1

I think (and I am not sure) the problem is with an extra quote ' in the query. It might have encountered a value like,for example, O'neil. This would terminate the query abruptly as in the following example. insert into table (name,address) values ('O'neil','some address'); Anyway, maybe this can help..
http://www.webdevelopment2.com/importing-large-mysql-databases-when-phpmyadmin-lets-you-down-get-bigdump/

[Kraai]

I have downloaded and installed bigdump now, and changed the settings to connect to database as specified, but even bigdump cannot connect to the database.

Database connection failed due to Access denied for user '******'@'localhost' (using password: YES)

Edit the database settings in /home/****/public_html/dump/bigdump.php or contact your database provider

Now, the settings is the same as the database name, user and password.

What am I doing wrong?

[Kraai]

Function has alocated only 10201461 bytes where as you can see you want 16777216 bytes. Try to create split version of whole DB. Like you have 10.000 records you put 5.000 in one file and other in next one. I can remember how I did it last time

I do not know how to do the split. What I am doing now, is to export/import the tabels one by one. I am now with the table containing the forum posts, and it looks like this table is the large one, as the import keeps on timing out.

Is there some geek out there that will help me export import this database I will pay for it if it is not too much you charge.

[nav33n]

Umm.. I just checked bigdump and it worked for me.. Well, I didn't check with over 50mb of data though. :-/

[Kraai]

Umm.. I just checked bigdump and it worked for me.. Well, I didn't check with over 50mb of data though. :-/

I am sure bigdump will do the trick, but what am I doing wrong in its settings, as it does not want to connect to the database at all.

[nav33n]

Are you sure you have changed these configuration ?

$db_server = 'localhost';
$db_name = '';
$db_username = '';
$db_password = '';

[Kraai]

jip, defnitly, I changed it, to the same details as created for this database. the server I left at "localhost"

[Kraai]

Here is an update!

To summarize:

The original problem was, a sytax error when trying to upload a database in phpmyadmin.

This error was due to the fact that the databse was not complete, when exported, also via phpmyadmin, and without any error warnings only exported a part of the database.

The new problem, was, how to get this database exported, completely, without SSH access, and also, I am a bit scared as a novice to use this line commands.

After many attempts to download this huge database, in phpmyadmin, it just never did download completely, and any zip format in phpmyadmin did not work either.

I then contacted the owner of the server where this database is currently hosted, and asked them to export it for me and make it available for me to download with FTP.

This was done by the server owner, and this is where I am now, busy to get this huge database via FTP to my local hard drive. The total size of the zipped file is almost 160 megs, so I can just imagine that if uncompressed, it should be like almost be a Gig?

My next step wil be, to import this database onto the new server. I would like to keep this thread open, untill I installed it.

I felt it necessary to wrap this threat with this update, as it is a shame that mostly, unresolved threats just hang in the air, and poor monkeys like myself, waste a lot of time reading them, without getting to the solution.

What I learned so far:

phpmyadmin cannot handle large files, which is very sorry, because otherwise, it is a great tool to use.