Issues restoring from dump

I'm having trouble restoring my SQL dump using phpMyAdmin and I suspect a syntax issue. I log into phpMyAdmin, select the database, go to the Run Queries section and use:

mysql -udbuser -p database_name < /home/mydumpfile.sql

phpMyAdmin reports back:

#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 'mysql -udbuser -p database_name < /home/mydumpfile.sql' at line 1

mysql -u DB_USER -p[PASSWORD] DATABASE_NAME < /home/USER/mydumpfile.sql

DB_USER is different to USER which is your id. Note you don't need to put your password which is why it's in square brackets. Note the space between the user switch and the user name, but no space between password switch and the password.

Thanks! However:

#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 'mysql -u DB_USER -p DATABASE_NAME < /home/USER/mydumpfile.sql' at line 1

(Needless to say I replaced the capitalized values above in my query)

That mysql line you are trying to run should probably be run from the shell on your own hosting server rather than through PHPMyAdmin.

- Dallas
- DreamHost Head Honcho/Founder

Having shell issues too (see my other post). What syntax would you recommend for restoring from this dump over phpMyAdmin, since that's currently my only means of accessing the database?

phpMyAdmin has an upload function. Use that. I'm sure it even has an Import function, too.

Oh. You was trying to use PHPmyadmin. Yes it needs the shell to use that command line.
PHPMyAdmin has two import functions. You can open the file in notepad and paste the contents into the sql tab, or import/upload the file.

As already posted the code you are trying to use is for the command-line version of MySQL, and needs to be run from the shell, not entered into an SQL window in PHPMyAdmin.

For importing a small file PHPMyAdmin is the best option, using the import tab to load the file directly from your local machine (it cannot load from a file on the server using this option). However this import is limited in both file size, and import time, so you will often find that a large backup file, even a gzipped one produced from PHPMyAdmin, will not process.

For a large file you are better using the command line import as above, but note you will need to unzip the file first if you zipped it for transfer. Also I would advise against using the 'extended inserts' option when creating the backup, since there is a limit on how many inserts you can do in a single statement.

Thanks again for all the replies.

Would the copy/paste option in phphMyAdmin be a possibility. of the SQL dump file is 15mb in size? Me thinks there would be a buffer issue but that's just a guess.

I'm having a heck of a time getting a commandline to work, although I may be partway there. At this point am wondering if I can ask DH to do that part for me...

All right, getting a little closer, I can feel it.

When I run this command:

mysql -u DBUSER -p -h DBHOST.HOST.net DBNAME

I have success and see a mysql> prompt.

Then I run:

mysql -u DBUSER -p DBNAME < /home/USER/DUMPFILE.sql

and I get "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 (spits back the syntax I used above)"

Pulling my hair out, but still moving forward...

you forgot the host in your second command. That'll make a difference.

As for the error, there's something in your dump file it's not liking. Your first post said ' at line 1'. Is that where the error is? Normally line 1 is just comments...

In reply to:

---

you forgot the host in your second command

---

Ok, I'll give it a shot again.

In reply to:

---

As for the error, there's something in your dump file it's not liking. Your first post said ' at line 1'. Is that where the error is? Normally line 1 is just comments...

---

Yes, the first 17 lines are all comments. Here's line 1.

# phpMyAdmin MySQL-Dump

My assumption was that when MySQL complained about line 1, it was actually referring to the command I had just run.

Edit: Yep, I have verified it has to be my syntax used in the SSH session. I ran the command:

mysql -u DBUSER -p DBHOST.HOST.net DBNAME < /home/USER/DUMPFILE.sql

and get the same error. Then I made garbage changes to the path and file name, and got the exact same complaint:

"ERROR 1064 (42000): 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 'mysql -u DBUSER -p DBHOST.HOST.net DBNAME < /home/USER/DUMPFILE.sql' at line 1"

I've been told elsewhere to remove the space between -u and DBUSER but I get the same results.Edited by silurius on 06/07/05 11:06 AM (server time).

You mentioned before that you logged in and THEN tried the command. You should actually write that "mysql -u USER.." on the DH/SSH command line and not logged into mysql.
Other than that it's pretty strange...
If you are in SSH then put your dump file in the domain.com folder (web root folder) by FTP first. Then "cd domain.com" in SSH. And then just use "< dump.sql" at the end of the mysql command.

I am beginning to believe the export file was corrupt, after all, and is related to inherant limitations in phpMyAdmin for larger databases. I'm working with the old hosting company to run a new SSH-based export since they do not provide that to customers. Thanks, everyone, for your attention here.