Using phpMyAdmin 'Export' and 'Import' to Move a Database to New Computer

MySQL database
#1

I have used the phpMyAdmin Export/Import functions many, many times to move a MySQL database from one computer to another. It has always worked beautifully and very simply.

I have a new computer to which I want to move six databases.

The six .sql files that are being imported range in size from 7 KB to a bit over 1 MB.

Only the smallest file was successfully imported. All the others complain of a syntax error here or there.

If I break up the import .sql file into smaller chunks, the database can be recreated perfectly – no syntax errors reported.

Might there be a parameter that limits the size of the imported .sql file? I found “max_allowed_packet”, but it is set to 1 MB.

Any suggestions?

Len Jacobson

#2

Well, it might be a server issue. But since they are small you can try using SQL to run the files instead of importing them. Usually, when you move a database from server to server, it is a one-time issue. Therefore, you do whatever is needed to get it to work. We can help you further if you tell us the syntax error you received. But, size only up to 1 MB is never an issue. Quite often it is the server type you are using on both ends. Also, it can be the database type. There are many possible problems here. You can edit the file, one database, copy it to the clipboard, log into your control panel and select SQL instead of IMPORT and just paste it there and run it. Then, you will see exactly what line or command is failing.
I really do not think it is a size issue! I have done 12 meg imports before without an issue. Tell us the error or try using SQL…

#3

Ernie,

I hope I understood your recommendation. I copied all the contents of the 4,000+ line file I was to import and instead clicked on the “SQL” button in phpMyAdmin and pasted all that content. I then hit the “Go” button.

It responded with:

2 errors were found during analysis.

1. Ending quote ’ was expected (near “” at position 14005)

2. 6 values were expected, but found 51 (near “(” at position 4705)

It then spit out the many lines of the SQL query and ended with:

#1064 - 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 ‘Reunion Party – 09-11-2010’, ‘img072.jpg’, ‘Marilyn Lewis’, 0, 0),
(285, ‘Reun’ at line 56

There is NO syntax error in the SQL code. As I have said, if I break up the file into smaller pieces, and do them one by one, I encounter no errors at all.

I gather that you are really suggesting that I do break up the SQL request into smaller pieces and run them one after the other. A pain, but of course that is doable.

It’s just so frustrating to me as I swear I have done this operation tens of times in the past and have never once encountered the slightest problem.

Len

#4

max_allowed_packet might be restricting how much SQL MySQL will accept from an import. You say your database is “a bit” over 1MB; that’s over the maxed allowed packet size right? Try changing the packet size to 2MB and see what you get.

#5

PM me your entire sql file. Pretty sure I know what the problem is.

#6

Only one of these .sql files exceeds 1 MB in size. The smallest of the failed files is 61 KB in size.

#7

I have sent ‘benanamen’ one of the failed .sql files.

Len Jacobson

#8

So, my guess from the error messages above is that you are using two different database systems.
But, it looks like a quote issue. Benanamen is a good programmer, he will fix you up!

#9

I just tried to import those seven SQL databases on a new laptop – they were imported without any problem.

So it has to be a problem with the previous laptop on which I was trying to do the same thing.

Len Jacobson

#10

Interesting… Are you importing it into a local server like WAMP or XXAMP???
If so, it is probably the database setup… But, at least you got it working on the the new laptop! Nice…

#11

Yes. I am importing it into UniServerZ (http://uniformserver.com).

#12

The phpmyadmin version OP used to export DB is ten versions behind current release.

#13

Well, we did not see that listed, so did not know this. That explains the errors, then.
Glad you sorted it out for him.

#14

Thank you EVERYONE for your help. I really appreciate it!!

Len

Sponsor our Newsletter | Privacy Policy | Terms of Service